calculations

[frustratedwithaccess]

Howdy folks,

Hoping someone can help me with some calculations in a query

I need to take sum and average data in one column based on values in 4 other other columns.

The fields I have are as follows, and basically what the data looks like with the calculation I need to do:

ProjectID....Space...Time...Date...........Number. ..Expr1


1 ..............2 .........5pm....06/22/13.....2...........2.5
1 ............. 2 .........5pm....06/22/13.....3...........2.5
2...............5......... 3pm ...01/24/14.....8...........4.5
2...............5..........3pm ...01/24/14.....1...........4.5
3...............1..........6am....02/02/14.....10.........8.3
3...............1..........6am....02/02/14.....6...........8.3
3...............1..........6am....02/02/14.....9...........8.3

So if ProjectID, Space, Time, and Date have equal values in multiple records, I need to sum the values in the field "Number" for those records, and then divide by the number of records, for the final values in Expr1.


Hope someone can offer some insight into creating an expression for this.

Cheers!

[rpeare]

SELECT * FROM <TABLENAME> GROUP BY ProjectID, Space, [Time], [Date], [Number]

this is an aggregate query, it will give you one record for each combination of projectid, space, time and date field. As a side note, time, date and number are ALL reserved words in access which is why I put them in [] brackets. Don't use reserved words for field names, it only leads to aggravation.

From here put in two new fields

NumberAverage: number
NumberSum: number

in the TOTALS line of your aggregate query use the
AVERAGE
and
SUM

Functions respectively.

[frustratedwithaccess]

OK, awesome! I will give it a go and keep you posted.

[rpeare]

SELECT ProjectID, Space, [Time], [Date] FROM <TABLENAME> GROUP BY ProjectID, Space, [Time], [Date]

I erred, your aggregate query should look like this first, then add the formulas (average and sum fields)

[frustratedwithaccess]

Ok... Thanks again for the help. It seems to be working!

How do I mark a thread resolved?

[ssanfu]

Do you really have field names "Space", "Time", "Date" & "Number"? These are reserved words in Access and shouldn't be used as object names. Plus they are not very descriptive.
You will probably have lots of headaches with these field names.....

See http://www.allenbrowne.com/AppIssueBadWord.html


--------
To mark solved, look for "Thread Tools" just above the first post (right hand side - in green bar).