UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Aggregating Data In Spreadsheets (simple Example), Access 2013    
 
   
williamlove
post Jun 25 2018, 11:28 AM
Post#1



Posts: 134
Joined: 8-February 06



This is a real problem I'm working. I have not simplified it much...it is a simple problem already.
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.
This post has been edited by williamlove: Jun 25 2018, 11:34 AM
Go to the top of the page
 
kfield7
post Jun 25 2018, 12:53 PM
Post#2



Posts: 992
Joined: 12-November 03
From: Iowa Lot


I assume each sheet has the number of events in Col A, and the name of the event in col B?

I would use VBA. Many ways to accommodate the details, so here's an overview in psuedocode:


CODE
clear holding table
for each workday spreadsheet
    append SS to holding table (e.g., use transferspreadsheet method.)
next
view holding table - QA check
approve/append holding table to main table


then run your query(ies) as desired.

Let us know if you need help with the detail.
Go to the top of the page
 
williamlove
post Jun 25 2018, 01:18 PM
Post#3



Posts: 134
Joined: 8-February 06



Yes Col A and Col B are as you said. And I will use VBA to automate it after I understand how to do it manually. Since you gave me a little algorithm, I will try to get started by creating a project that makes the holding table, just to get warmed up. As I understand it, I will create a holding table of two fields (event and number of occurrences) and the loop will populate it with a number of records equal to the sum of the number of rows (minus headers) that exist in seven raw sheets. The transferspreadsheet method appears to be what I need.

I have actually created the holding table manually in Excel. That is when I realized I don't know how to get the totals. I'm sure it's elementary but I've never done it. So yes, it is that detail that I'm most weak on. I don't have a background it that sort of thing.

So lets say my holding table looks like this:

35 High Pressure
15 Low Flow
11 High pH
15 High pH
12 Bad Quality
17 High Pressure
11 Low Level
19 Low Pressure
28 Low pH
10 Bad Quality
19 High Flow
10 Low Flow

The "query" would produce this:

52 High Pressure
25 Low Flow
27 High pH
22 Bad Quality
11 Low Level
19 Low Pressure
28 Low pH
19 High Flow

I did that by adding in my head and typing. Where I am lacking knowledge is how to do that with a query. If you can explain that I think I can automate it once I can do it. Thanks!
This post has been edited by williamlove: Jun 25 2018, 01:19 PM
Go to the top of the page
 
RJD
post Jun 25 2018, 01:27 PM
Post#4


UtterAccess VIP
Posts: 10,018
Joined: 25-October 10
From: Gulf South USA


Hi: You would use a Totals (Group By) query to do this ... Something like this ...

SELECT Alarm, Sum(Frequency) AS SumOfFrequency
FROM tblMyRecords
GROUP BY Alarm
ORDER BY Alarm;

...using you own object names, of course.

See the demo attached.

HTH
Joe
Attached File(s)
Attached File  AggregatingData.zip ( 18.22K )Number of downloads: 11
 
Go to the top of the page
 
projecttoday
post Jun 25 2018, 01:40 PM
Post#5


UtterAccess VIP
Posts: 10,957
Joined: 10-February 04
From: South Charleston, WV


Are those the entire sheets?
Go to the top of the page
 
williamlove
post Jun 25 2018, 01:47 PM
Post#6



Posts: 134
Joined: 8-February 06



notworthy.gif Thanks everyone, that's good help.
kfield7 -- I will try the DoCmd.TransferSpreadsheet and see if I can make the holding table.
RJD -- I got your file and I am studying the query. I will learn a lot from it.
This post has been edited by williamlove: Jun 25 2018, 01:54 PM
Go to the top of the page
 
RJD
post Jun 25 2018, 01:57 PM
Post#7


UtterAccess VIP
Posts: 10,018
Joined: 25-October 10
From: Gulf South USA


Be sure to let us know how this works out for you and if we can be of further assistance.

Regards,
Joe
Go to the top of the page
 
ADezii
post Jun 25 2018, 02:24 PM
Post#8



Posts: 2,625
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
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.

  1. The entire process can be reduced to a few simple steps totally within Access:
    1. Create a Table named tblData with two Fields, namely: [Alarms]-{INT} and [Type]-{STRING}.
    2. From Access, open the Excel Workbook (Alarms.xlsx), clear tblData, iterate each Worksheet (Monday thru Sunday), and copy the Alarms and Type Data directly into tblData.
    3. Perform a few Clean UP chores.
    4. Execute the Aggregate Query as suggested by RJD.
  2. Code Definition:
    CODE
    'Set a Reference to the Microsoft Excel Object Library
    Dim appExcel As Excel.Application
    Dim wkb As Excel.Workbook
    Dim sht As Excel.Worksheet
    Dim intRow As Integer
    Dim strSQL As String

    Set appExcel = New Excel.Application
    'Change PATH and/or Filename if necessary
    Set wkb = appExcel.Workbooks.Open("C:\Test\Alarms.xlsx")

    intRow = 1          'Initiate Row Number

    'CurrentDB.Execute "DELETE * FROM tblData", dbFailOnError

    For Each sht In wkb.Worksheets      'Loop thru all Worksheets
      With sht
      Do While .Cells(intRow, "A").Value <> ""       'as long as Data exists in Column 'A'
        'strSQL = "INSERT INTO tblData([Alarm], [Type]) VALUES(" & .Cells(intRow, "A").Value & _
                 '", '" & .Cells(intRow, "B").Value & "')"
          'CurrentDB.Execute strSQL, dbFailOnError
          Debug.Print .Name, .Cells(intRow, "A"), .Cells(intRow, "B")
            intRow = intRow + 1         'Increment Row Number
      Loop
        intRow = 1                      'RRESET Row Number
      End With
    Next

    wkb.Close
    appExcel.Quit
    Set sht = Nothing
    Set wkb = Nothing
    Set appExcel = Nothing

    'You can Run the Aggregate Query as suggested by RJD here
  3. The Code has been tested and is fully operational except the Deletion of the Records in tblData and the actual Appending of the Records to tblData. This is only because I am in work and do not have Access on my PC, only Excel.

This post has been edited by ADezii: Jun 25 2018, 02:29 PM
Go to the top of the page
 
williamlove
post Jun 26 2018, 01:26 PM
Post#9



Posts: 134
Joined: 8-February 06



QUOTE
The entire process can be reduced to a few simple steps totally within Access


ADezii: I'm getting ready to try to use your code. I'll let you know how I do. I created frmData whose Record Source is tblData. I have six fields in the "real" data, not the two I used as my example. So I have to use the code as a guide. But that should not be too hard.

The only thing I might ask now is where should the code go?

At the moment my plan is to put a button on the form and run the code from that. Unless there is a better idea.
This post has been edited by williamlove: Jun 26 2018, 01:42 PM
Go to the top of the page
 
ADezii
post Jun 26 2018, 01:47 PM
Post#10



Posts: 2,625
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
is where would you put the code?

  1. The most logical location for the Code is within the Click() Event of a Command Button on a Form.
  2. Keep in mind some 'very important' points before you proceed:
    1. You need to change the following line of Code to point to the location of Alarms.xlsx on your PC.
      CODE
      Set wkb = appExcel.Workbooks.Open("C:\Test\Alarms.xlsx")
    2. You need to create tblData and two Fields exactly as described above.
    3. Each Worksheet in Alarms.xlsx, there should be seven of them corresponding to the days of the week, should have the same Format. The names of the Worksheets make no difference as long as there are seven of them and they are all consistent with each other.
    4. The Worksheets should have no Row Headers. If this is not the case then you must change the Value of intRow to reflect a Starting Row = 2.
    5. Column 'A' on each Worksheet must contain the Values and they must be Numeric.
    6. Column 'B' on each Worksheet must contain the Alarm Types and they must all be named exactly the same (Bad Quality <> Bad Qualtry).
    7. The Data in Columns A and B must be contiguous, there can be no missing/empty Rows.
  3. Should you run into any problems, we are here for you.
  4. Good Luck with your Project.

P.S. - One point I am sure on is to whether or not the Records need to be DELETED each and every time that this Code is run. Only you can answer this question.
This post has been edited by ADezii: Jun 26 2018, 01:49 PM
Go to the top of the page
 
williamlove
post Jun 26 2018, 04:50 PM
Post#11



Posts: 134
Joined: 8-February 06



I have gotten far enough that I'm certain your code will work. I am dealing with a number of issues. I am sure I will solve them all.

One problem is that one of the fields is a duration and the cell has a custom format in Excel [h]:mm:ss. The data looks like this when you look at it in Excel: 0:10:01 (the alarm was in active state a total duration of ten minutes and one sec on that day)
I temporarily avoided this problem by only reading the first five fields (the duration is the sixth field). I intend to come back to this and solve this later.

The next problem is the routine failed with a message about a missing operator. But it populated hundreds of records before failing. Then I realized that when my Description field has a single quote the
QUOTE
CurrentDB.Execute strSQL, dbFailOnError
fails.
This is the offending entry in the field that caused the failure: CH7-PIT212-0-02A OFA PRESSURE TO AHU'S LOW
I think I can figure out a solution to that. That is what I am working on now.

As I say this is clearly going to work.
Go to the top of the page
 
williamlove
post Jun 26 2018, 06:57 PM
Post#12



Posts: 134
Joined: 8-February 06



I got the duration to come in by changing the field in my table to date/time with h:mm:ss

I did some crazy stuff with double quotes to get it to accept a description that had an apostrophe in column C. Note the especially insane looking quadruple quote on the right side of the "C" field. I can't say I understand. But it works. If you understand it well and you want to explain it to me please feel free. I knew about the technique of adding double quotes but don't really understand that one.

CODE
  Do While .Cells(intRow, "A").Value <> ""       'as long as Data exists in Column 'A'
    strSQL = "INSERT INTO tblData([AlarmCount], [Point], [Description], [Resource], [AlarmClass], [TotalDuration]) VALUES(" & .Cells(intRow, "A").Value & _
             ", '" & .Cells(intRow, "B").Value & "'" & _
             ", """ & .Cells(intRow, "C").Value & """" & _
             ", '" & .Cells(intRow, "D").Value & "'" & _
             ", '" & .Cells(intRow, "E").Value & "'" & _
             "," & .Cells(intRow, "F").Value & ")"
       'Debug.Print strSQL
            
      CurrentDb.Execute strSQL, dbFailOnError
      Debug.Print .Name, .Cells(intRow, "A"), .Cells(intRow, "B"), .Cells(intRow, "C"), .Cells(intRow, "D")
        intRow = intRow + 1         'Increment Row Number
  Loop


Now my next task is to use the query that RJD gave me to design one for all six fields and the varied formatting. Then I will figure out where to put that query....another button? I have to think about that. But I'm definitely cruising along. Thanks!
thumbup.gif
Go to the top of the page
 
RJD
post Jun 26 2018, 07:15 PM
Post#13


UtterAccess VIP
Posts: 10,018
Joined: 25-October 10
From: Gulf South USA


QUOTE
Then I will figure out where to put that query....another button?

I would think you would want the results in a report. If this is correct, design the report with the query as the record source - and open the report (command button) (preview) when you want it - it will call the query by itself and present the data as you design it in the report.

The trick in all this will be how you want to deal with the alarm duration. You have formatted the duration as a point-in-time rather than as a duration. If it is text (or even a "time"), you will have to parse the components (hours, minutes, seconds), changing each component to a common measure (seconds) to add the values across the record group, then convert the result in the total to the format you want to see - known processes, but not especially trivial. There should be some code already around here somewhere to do both of these, or it can be constructed to your specific requirements.

HTH
Joe
Go to the top of the page
 
ADezii
post Jun 27 2018, 07:55 AM
Post#14



Posts: 2,625
Joined: 4-February 07
From: USA, Florida, Delray Beach


@williamlove:
It does appear that you have the situation well in hand, congratulations! thumbup.gif
  1. RJD does bring up some excellent points in Post# 13, Paragraph 2 regarding your [Duration] Field. In its present state you can do very little with it: you cannot add Durations, Aggregate them, etc. What you can do is to Convert these point-in-time Values to something more meaningful prior to actually bringing this Data into Access. As an example, I have converted the [Duration] Field which has a [h]:mm:ss Custom Format into a SINGLE Data Type Field via a Conversion Function. The whole number component is the Duration in Minutes, while the Fractional component represents Seconds as a percentage of a Minute (the ss in [h]:mm:ss). A SINGLE Data Field can now exist in tblData and mathematical operations can now be performed on it.
  2. Sample Data (Friday Worksheet) prior to conversion:
    CODE
    19    Low pH            0:13:23
    53    Low pH            1:02:58
    22    High Flow         0:00:45
    40    Low Pressure      2:47:00
    14    High Pressure     0:17:37
    75    High pH           0:16:00
    33    Bad Quality       3:00:00
  3. Function Code Definition:
    CODE
    Public Function fConvertDuration(varDuration As Variant) As Single
    Const conMult = 60
    Dim sngMins As Single

    sngMins = Format((Hour(varDuration) * conMult) + Minute(varDuration) + (Second(varDuration) / conMult), "Fixed")

    fConvertDuration = sngMins
    End Function
  4. Post conversation:
    CODE
    19    Low pH            0:13:23      13.38
    53    Low pH            1:02:58      62.97
    22    High Flow         0:00:45       0.75
    40    Low Pressure      2:47:00     167.00
    14    High Pressure     0:17:37      17.62
    75    High pH           0:16:00      16.00
    33    Bad Quality       3:00:00     180.00
  5. Obviously, you will need to adjust your SQL Statement (AIR CODE - NOT TESTED):
    SQL
    strSQL = "INSERT INTO tblData([Alarm], [Type], [Duration]) VALUES(" & .Cells(intRow, "A").Value & _
    ", '" & .Cells(intRow, "B").Value & "', " & fConvertDuration(.Cells(intRow, "B").Value) & ")"

    CurrentDB.Execute strSQL, dbFailOnError

RJD: If I happened to misinterpret anything I said regarding your Post# 13, please let me know, and I do apologize.
This post has been edited by ADezii: Jun 27 2018, 08:02 AM
Go to the top of the page
 
RJD
post Jun 27 2018, 09:32 AM
Post#15


UtterAccess VIP
Posts: 10,018
Joined: 25-October 10
From: Gulf South USA


ADezii

QUOTE
RJD: If I happened to misinterpret anything I said regarding your Post# 13, please let me know, and I do apologize.

Not at all. Perfect. I did not know if the OP had the time as a text field or point-in-time date/time format, so stopped short of a suggested parsing/summing solution - just some general comments. You went further with a solution, which is perfect. If the field is actually text, however, then it will have to be converted or parsed and calculated accordingly, but you led the way to the solution.

Thanks for stepping in on this ...

Regards,
Joe
Go to the top of the page
 
ADezii
post Jun 27 2018, 10:07 AM
Post#16



Posts: 2,625
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. Thanks Joe. My initial approach, assuming the Duration Values were TEXT, was to Parse them as follows:
    CODE
    Split("[h]:mm:ss", ":")(0)      'hour
    Split("[h]:mm:ss", ":")(1)      'minute
    Split("[h]:mm:ss", ":")(2)      'second
  2. Had problems getting this to work, so I took the alternative approach.

This post has been edited by ADezii: Jun 27 2018, 10:08 AM
Go to the top of the page
 
williamlove
post Jun 27 2018, 12:08 PM
Post#17



Posts: 134
Joined: 8-February 06



Attached File  AggregatingData.zip ( 101.13K )Number of downloads: 8
I have a new problem with my query. I will explain the problem at the bottom. First I will mention that ADezii's function to convert the duration to a decimal number worked and my current problem is not in any of the VBA code. For reference, here is the code that successfully produces a table from the worksheets:

CODE
Private Sub btnGetDataFromExcel_Click()
'Set a Reference to the Microsoft Excel Object Library
Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook
Dim sht As Excel.Worksheet
Dim intRow As Integer
Dim strSQL As String

Set appExcel = New Excel.Application
'Change PATH and/or Filename if necessary
Set wkb = appExcel.Workbooks.Open("C:\Users\lovewilx\Desktop\Alarms.xlsx")

intRow = 2          'Initiate Row Number

CurrentDb.Execute "DELETE * FROM tblData", dbFailOnError

For Each sht In wkb.Worksheets      'Loop thru all Worksheets
  With sht
  Do While .Cells(intRow, "A").Value <> ""       'as long as Data exists in Column 'A'
    
    strSQL = "INSERT INTO tblData([AlarmCount], [Point], [Description], [Resource], [AlarmClass], [TotalDuration], [Project]) VALUES(" & .Cells(intRow, "A").Value & _
             ", '" & .Cells(intRow, "B").Value & "'" & _
             ", """ & .Cells(intRow, "C").Value & """" & _
             ", '" & .Cells(intRow, "D").Value & "'" & _
             ", '" & .Cells(intRow, "E").Value & "'" & _
             "," & fConvertDuration(.Cells(intRow, "F").Value) & _
             ", '" & .Cells(intRow, "G").Value & "'" & ")"
            
      CurrentDb.Execute strSQL, dbFailOnError
      intRow = intRow + 1         'Increment Row Number
  Loop
    intRow = 2                      'RRESET Row Number
  End With
Next

wkb.Close
appExcel.Quit
Set sht = Nothing
Set wkb = Nothing
Set appExcel = Nothing

Public Function fConvertDuration(varDuration As Variant) As Single
Const conMult = 60
Dim sngMins As Single
sngMins = Format((Hour(varDuration) * conMult) + Minute(varDuration) + (Second(varDuration) / conMult), "Fixed")
fConvertDuration = sngMins
End Function
End Sub


The code above produces a table with seven fields. Eight if you include the Autonumber ID. Six of the seven fields--including the decimal duration--are ShortText. The AlarmCount is a number.

My problem is that my query works fine if I don't include Duration. As soon as I add Duration it stops aggregating the data. I left RJD's table and query in the database. My table, form and query are called tblData, frmData, qryData.
I am continuing to work on the problem but its so cut and dry that I'm very puzzled. Esp since Duration is a text item.
This post has been edited by williamlove: Jun 27 2018, 12:18 PM
Go to the top of the page
 
RJD
post Jun 27 2018, 12:30 PM
Post#18


UtterAccess VIP
Posts: 10,018
Joined: 25-October 10
From: Gulf South USA


Hi: I tested your query by adding TotalDuration to the query and choosing Sum as the aggregation. It added the text values without issue, converting the text to numeric.

But if you want to be safe (I did this in A2010) in your version of Access, then you might try ...

Sum(CDbl([TotalDuration])) AS DurationTotal

or even ...

Sum(CDbl(NZ([TotalDuration],0))) AS DurationTotal ... to deal with nulls if necessary

Try one of these and see how it goes.

HTH
Joe
Go to the top of the page
 
williamlove
post Jun 27 2018, 01:15 PM
Post#19



Posts: 134
Joined: 8-February 06



Your solution worked.

I will mention, the goal of this task was to look at nuisance alarms which are alarms that go off too often. So the AlarmCount is what me and the other engineers want. The duration is of limited interest because its meaning can be ambiguous for reasons I won't delve into. But it is not irrelevant and I'm glad to have it.

Until your post just now, it had just not occurred to me that the duration should be summed too. The other fields are the same for each record of an alarm which is why the aggregate works when they are all that's included. But the duration varies and that obviously is noticed by Access. Pretty impressive what the database designers have done.

I have been using the design view of the query. I added TotalDuration, changed "Group By" to "Sum" in the Total row. It worked. The AlarmTotals are the same as without including the duration and the duration gets summed. Pretty cool. I don't understand the SQL yet, at least not more than needed to fiddle with it a little. But I am studying it.

I might automate the initial task (manually done, not mentioned before) of making a single workbook with seven worksheets from seven separate workbooks. In my initial post I called them spreadsheets and everyone assumed I meant worksheets. But what I actually start with is seven workbooks. After you guys started helping I made a single workbook so that I could use ADezii's code. I am thinking I might put the workbooks in a folder and have a loop similar to the one I have now. I have a feeling I won't be able to use the nice For Each loop, because they are files, not Excel objects. I'm going to take a shot at this because its kind of fun and would make it a more complete solution.

Thanks to both of you for your continued interest, it has been extremely helpful! notworthy.gif
Go to the top of the page
 
ADezii
post Jun 27 2018, 01:53 PM
Post#20



Posts: 2,625
Joined: 4-February 07
From: USA, Florida, Delray Beach


First and foremost, it has been a pleasure working with you on this Project, good luck on its completion.
QUOTE
I am thinking I might put the workbooks in a folder and have a loop similar to the one I have now. I have a feeling I won't be able to use the nice For Each loop, because they are files, not Excel objects. I'm going to take a shot at this because its kind of fun and would make it a more complete solution.

  1. I do not think that this will be as difficult as it might seem.
  2. One possible solution could be:
    1. Load a 'MultiSelect' ListBox with your Workbook Names along with a hidden Column that contains their Absolute PATHs. These Names and PATHs can be stored in a Hidden Table.
    2. Select 1 or more of the Workbooks in the ListBox.
    3. Process each of the Workbooks that you selected. Here is where you can use the For...Each Loop that you mentioned, the Pseudo Code being:
      CODE
      Dim varItem As Variant

      For Each varItem in ListBoxName.ItemsSelected   'Workbook Names contained in the ItemsSelected Collection
        'process in turn
      Next
  3. For each Workbook in the For Each...Next Loop, you would simply reassign the Workbook and Worksheet Object Variables, then extract the Data from each in turn.
  4. Should you need a simple Demo, I would be more than happy to assist.
  5. Good Luck again with your Project.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    17th September 2019 - 10:10 AM