Insert Query Help (1 Viewer)

lookforsmt · Feb 17, 2018

HI! i have a table which is imported from excel every hour. From the cross-tab query the data is retrieved. Since the data is quite huge it cannot be stored in the db, so i only want to capture the summary every hour and store the values.

My challenge is how do i move the query data to "tbl_Summary" table as shown in attached snapshot

Below are the hours:
08:00 - 09:00
09:00 - 10:00
10:00 - 11:00
11:00 - 12:00
12:00 - 01:00
01:00 - 04:00

I can run the insert query every hour by changing the hour (01:00-04:00) field in the insert query each time. However i am looking at automation to avoid manually changing the hours.

Any possible solution.

Thanks

BigHappyDaddy · Feb 17, 2018

Two things:
First, I would need to see your query in order to tell you how to modify it.

Second, what would be triggering you automation? Has that been built?

The modified query would probably need to be somewhere in the automation trigger.

Mark_ · Feb 17, 2018

If you are importing from excel every hour, how is the data too huge to store in access? You could have easily over a million records in a table without problems.

lookforsmt · Feb 17, 2018

Thanks,

below is the query

Code:

TRANSFORM Count(tbl_DailyUpload.UserId) AS CountOfUserId
SELECT tbl_DailyUpload.UserId
FROM tbl_DailyUpload
GROUP BY tbl_DailyUpload.UserId
ORDER BY tbl_DailyUpload.NewEntry
PIVOT tbl_DailyUpload.NewEntry;

From the above cross-tab i create a table, "Tab-3" using make table query which gives 2 columns: UserId & Time

Code:

SELECT q_Tab_3_1.* INTO Tab_3
FROM q_Tab_3_1;

From here i want to update the table "tbl_Summary" which as mentioned earlier i want to update the hours based on the data.

hope i am clear.

lookforsmt · Feb 17, 2018

"NewEntry" field is the time entry which can be anyone of he below

Below are the hours:
08:00 - 09:00
09:00 - 10:00
10:00 - 11:00
11:00 - 12:00
12:00 - 01:00
01:00 - 04:00

Mark_ · Feb 17, 2018

I'm still not sure why you are making this difficult. Do you expect each user to have thousands of entries per hour? Accumulator records (such as what you describe) are normally only used when the underlying data is too extensive to process in a reasonable time frame OR is no longer available. Unless you are looking at each user being logged several thousand times per hour (with the original data archived due to space limits) I'd really suggest just importing the data in a more normal way THEN using your query for analysis.

lookforsmt · Feb 18, 2018

Yes, i am sure access has the capability to absorb thousands of records, but i am worried of the db slowness due to the high records.
In this db there wont be thousands of records per user, but there would be definitely quite a high records each user would be processing.

i am expecting approx 17K to 20K records split between 25 to 30 users per day. which gives approx less than 1K per user

i can store the entire record for a day and delete the same by end of day keeping only the summary for the day. But looking for something different.

Here i import data each hour and then pull a summary per user to know how much each user is processing.

When i perform the 1st summary say between 08:00 to 09:00 it will record in that field. But my challenge is when i perform the 2nd summary between 9:00 to 10:00 or the next hours, how do i record this in the same table which is the "tbl_Summary"

The_Doc_Man · Feb 18, 2018

But my challenge is when i perform the 2nd summary between 9:00 to 10:00 or the next hours, how do i record this in the same table which is the "tbl_Summary"

Old programmer's rule #2: Access won't tell you anything you didn't tell it first, or at least tell it HOW to tell you. How that applies to this question is that if you want your data to be time-tagged, you need to have a field in the table to HOLD the time tag. If you have the time and a place to store it, it seems you would be good to go.

So is your question how to store the time or how to determine the time now or how to determine the time on the imported spreadsheet? Or some other variation on this theme?

Mark_ · Feb 18, 2018

For myself, I'd keep the detail records... at least for a while. Say 2 months? 600,000 records isn't a lot and should allow you to verify anything you need to during the time. After that, you could accumulate everything more than 2 months old into accumulators that hold a total for each 15 minute increment. That would allow you to replace 20K records with 20-30. By that point you probably would be offloading the details for long term storage, so using accumulators for long term tracking and predictions should be fine.

Trying to update every hour from excel, that doesn't seem like a good option though. Too many multi-user issues to try and avoid. What in your business model is driving pulling hourly from excel files? More importantly, have you already ensured you won't run into issues with others using the files at the same time? Also how will you annotate which rows in the excel table you've already looked at?

Cronk · Feb 19, 2018

"Yes, i am sure access has the capability to absorb thousands of records, but i am worried of the db slowness due to the high records" ..but not worried about Excel being slower?

Second comment: Why have separate columns for each hour. Just record the userID, the hour, and other data read, so your table is normalized. Your cross tab query would handle that.

As to the method, create a table with the hours required each day, together with a corresponding check box for each hour. At the start of day, clear all check boxes. Have a timer event that every so often, checks to see whether the current time exceeds any unchecked hour record, and if so, perform the import and check the box for that hour.

Insert Query Help (1 Viewer)

lookforsmt

Registered User.

Attachments

BigHappyDaddy

Coding Monkey Wanna-Be

Mark_

Longboard on the internet

lookforsmt

Registered User.

lookforsmt

Registered User.

Mark_

Longboard on the internet

lookforsmt

Registered User.

The_Doc_Man

Immoderate Moderator

Mark_

Longboard on the internet

Cronk

Registered User.

Similar threads

Users who are viewing this thread