Question Aggregating Data In Spreadsheets (simple Example)

[williamlove]

This is a real problem I'm working. I have not simplified it much...it is a simple problem already. My question is at the bottom in bold.

At the end of every week seven spreadsheets are provided each containing information about the number of alarms received each day. Three of the sheets are shown below to illustrate. I intend to import them into access and add up the totals of each alarm that appeared.

Sheet Monday
35 High Pressure
15 Low Flow
11 High pH

Sheet Tuesday
15 High pH

Sheet Wednesday
17 High Pressure
11 Low Level
19 Low Pressure
28 Low pH
10 Bad Quality
19 High Flow

There is no minimum or maximum number of rows in a sheet for a given day...it just depends on what happened that day. Typically several of the alarms appear every day while others appear only occasionally.

I want to add up the total number of occurrences of each alarm for the week. If an alarm appears even one day it would make the report. If an alarm occurred everyday the report would add them up and give the sum. (I say "report" but an Access table or Query would be okay.)

Can you explain how you would import the spreadsheets into a database and use SQL or some standard technique to do this?
It would be nice if the method was amenable to VBA automation because I hope to do that after I master the method from a manual standpoint.

 

[June7]

That looks like two questions. Do you know how to import records? Once imported, aggregate query or report design should be able to sumnarize data. Each record will need a date value to allow grouping.

Suggest you research aggregate (GROUP BY) query as well as Sorting & Grouping features of report.

 

[MarkK]

The main problem I see with the data as presented is that the date is not clear. As far as transferring data from Excel, you can look into the DoCmd.TransferText and DoCmd.TransferSpreadsheet commands, but in all cases, the date as stated, like "Monday" is not specific enough to automate an import. The user will need to interact with the process, and given that the date is not present in the incoming data, and it should be present--as June7 mentions--in each row of the target table, therefore it looks like this import will have to be a custom job with multiple steps. Maybe you import to a temp table saving the sheet name. Or maybe you can link to the spreadsheet too, but without the exact table design, and without the exact structure of the spreadsheet, there are too many variables to give you explicit directions.

For more specific help, show your table design, and show the exact structure of the excel file.

hth
Mark

 

[mike60smart]

Hi

As previously mentioned you need to sort the spreadsheet format to include a date as shown below:-

View attachment Totals.zip

 

[williamlove]

I was mainly looking for help on the query. Somebody made a query with my data (the data I put in the post) that did exactly what I wanted. They put it in an Access file which I have shared in this post in the attachment. My job now will be to use VBA to automate it.

The other thing they showed me was how to use DoCmd.TransferSpreadsheet as you suggested, Mark. Someone actually gave me sample code. I still have a lot of study and work to do but I'm in amazingly good shape now. In case anyone else wants to see it here is the post:
http://www.utteraccess.com/forum/index.php?showtopic=2049556&st=0#entry2687340

 

[Cronk]

What do you mean by "to use VBA to automate it"

Open the query by code? Then

docmd.openquery "qryOverallFrequencies"

 

[June7]

Could build a report with the query as RecordSource.

Or build report with raw data using Sorting & Grouping features with aggregate calcs. Report allows display of raw data as well as summary calcs.

 

[Mark_]

Williamlove,

When posting the same question on multiple sites, please let us know not only that you have done so, but also keep us updated if you get answers. That way we don't spend a lot of time trying to solve a problem that has already been solved.

As to entering the date, my recommendation would be to have an unbound form with two entry fields; first for the date and second (with file picker) for the file to import. This way you can use your "AlarmDate" field from your form to fill in the AlarmDate you should be storing in your table.

 

[williamlove]

docmd.openquery "qryOverallFrequencies"

Cronk: Thanks for the idea. Before I try that, I'm studying the query that RJD (other site) gave me and trying to build a holding table from the seven sheets using DoCmd.TransferSpreadsheet as suggested by MarkK and others. Then, I might design an interface...a form or report, and use that line of code you gave me. Is that what you had in mind? If you care to elaborate I'd be very interested to hear because it might save me a lot of time if I understand more before I start building.

Could build a report with the query as RecordSource.

June7: That is equally intriguing. If I understand it, I would not actually have to automate anything with VBA except perhaps the initial holding table build. I'd simply create a report and make the recordsource the query. I'm actually starting to wonder if that is a really simple solution.

 

[June7]

Same suggestion as was offered in post 2.

Code (VBA or macro) can be used to aid with applying filter to report as well as opening from button click.

If spreadsheet too complex for TransferSpreadsheet, see alternative examples at http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm