Query to group by two fields & average the final field (1 Viewer)

[PaulD2019]

Hi all,

I am sure I have done something like this before but I can't find the database I tried it on.

I would like to create a query to group the sample data shown below to use in my main database I am working on, the material type has up to three different project types that it can be carried out on, I would like to group the material type by the project type so there is only one material type shown for each project type then average the personal rate for each so the table below would look like this in the query

Material	ProjectType	PersonalRate
AIB Debris Removal	Licensed Works (ASB5)	0.03​
AIB Debris Removal	NNLW1 Works	0.00675​
AIB Debris Removal	Non-Licensed Works	0.007​
Excavation of Contaminated Soil (Cement Debris)	Licensed Works (ASB5)	0.0113​
Excavation of Contaminated Soil (Cement Debris)	NNLW1 Works	0.0075​
Cement Removal	NNLW1 Works	0.00933​
Cement Removal	Non-Licensed Works	0.01866​

any help with this would be appreciated, I have also uploaded the database with the sample data

Thanks in advance

 

[June7]

Options:

1) build an aggregate (GROUP BY) query using the Totals button on ribbon

SELECT tblPersonalTestingList.Material, tblPersonalTestingList.ProjectType, Round(Avg(tblPersonalTestingList.PersonalRate),5) AS AvgOfPersonalRate
FROM tblPersonalTestingList
GROUP BY tblPersonalTestingList.Material, tblPersonalTestingList.ProjectType;

2) use report Sorting & Grouping with aggregate calc in textbox - this allows display of detail data as well as summary info

 

[PaulD2019]

Thank you @June7

I will give that a try when i get home