Query headache

[fuecheefang]

Ive created a query to retrieve employee last name, first name, and the projects associated with each individual. What is a best way to add another field in this query to sum up the total numbers of projects assigned to each individual? My result shows the Employee multiple times with each Project they are associate with.

[fuecheefang]

I need the same employee to only appear once with the Allocation summed up....

[JoeM]

Create an Aggregate (Totals) Query by clicking on Totals button and changing the "Totals Row" value under the Allocation field from "Group By" to "Sum".

[fuecheefang]

Create an Aggregate (Totals) Query by clicking on Totals button and changing the "Totals Row" value under the Allocation field from "Group By" to "Sum".

Thanks JoeM, it worked perfectly. Issue SOLVED.

[Sam11420]

Hi
I have the very same problem but in my case I have no totals to group or sum - how do I get my data to NOT repeat itself

[JoeM]

Hi
I have the very same problem but in my case I have no totals to group or sum - how do I get my data to NOT repeat itself

Follow the same steps, except you do not change any of the fields Totals lines to "Sum". Simply leave them all as "Group By".

[Sam11420]

Hi I did as you suggested but they all still repeating itself

[JoeM]

So, are you clicking on the Totals button so that the words "Group By" are showing up in the Totals row in all fields you are returning?
Note that in order for the records to be grouped together, every single field you are returning to the query must be the same.

If you are still having issues, please switch your query to SQL View and copy and paste your code here and then post a small data sample.

[Sam11420]

SELECT tbl_EmplQualification.Qualification, [Surname] & " " & [Name] AS Emp, tbl_medical.[Expiry Date], tbl_yrsofexp.[Work Exp Type], tbl_yrsofexp.[Years of Exp]
FROM ((tbl_Employee LEFT JOIN tbl_EmplQualification ON tbl_Employee.link = tbl_EmplQualification.link) LEFT JOIN tbl_medical ON tbl_Employee.link = tbl_medical.link) INNER JOIN tbl_yrsofexp ON tbl_Employee.link = tbl_yrsofexp.link
GROUP BY tbl_Employee.Surname, tbl_Employee.Name, tbl_EmplQualification.Qualification, [Surname] & " " & [Name], tbl_medical.[Expiry Date], tbl_yrsofexp.[Work Exp Type], tbl_yrsofexp.[Years of Exp];



I think this is my problem, you see I am using different tables and they all linked to tbl_Employee
tbl_EmplQualification can have many qualifications linked to one employee and tbl_yrsofexp have many different types of work exp to one employee and tbl_medical only have one medical expiry date to one employee

I would really appreciate your help.

[Sam11420]

Data Sample

Qualification	Emp	Expiry Date	Work Exp Type	Years of Exp
	Caine Bradley Rayle	01-Dec-16	Inspector Exp	12
ANDTC RT 1	Caine Bradley Rayle	01-Dec-16	Inspector Exp	12
GRADE 11	Caine Bradley Rayle	01-Dec-16	Inspector Exp	12
Other Management	Caine Bradley Rayle	01-Dec-16	Inspector Exp	12
RT Safety	Caine Bradley Rayle	01-Dec-16	Inspector Exp	12
S.H.E First Aid Basic	Caine Bradley Rayle	01-Dec-16	Inspector Exp	12
S.H.E SAMTRAC HIRA ASHEPP	Caine Bradley Rayle	01-Dec-16	Inspector Exp	12
	Callaghan Reon Dane	15-Feb-16	NDT Assistant Exp	2
	Callaghan Reon Dane	15-Feb-16	Trade Pipe Fitter Exp	2
Other Security	Callaghan Reon Dane	15-Feb-16	NDT Assistant Exp	2
Other Security	Callaghan Reon Dane	15-Feb-16	Trade Pipe Fitter Exp	2
RT Safety	Callaghan Reon Dane	15-Feb-16	NDT Assistant Exp	2
RT Safety	Callaghan Reon Dane	15-Feb-16	Trade Pipe Fitter Exp	2
S.H.E Fire Technology	Callaghan Reon Dane	15-Feb-16	NDT Assistant Exp	2
S.H.E Fire Technology	Callaghan Reon Dane	15-Feb-16	Trade Pipe Fitter Exp	2
S.H.E First Aid Level 1	Callaghan Reon Dane	15-Feb-16	NDT Assistant Exp	2
S.H.E First Aid Level 1	Callaghan Reon Dane	15-Feb-16	Trade Pipe Fitter Exp	2
S.H.E SAMTRAC HIRA ASHEPP	Callaghan Reon Dane	15-Feb-16	NDT Assistant Exp	2
S.H.E SAMTRAC HIRA ASHEPP	Callaghan Reon Dane	15-Feb-16	Trade Pipe Fitter Exp	2

[JoeM]

So, I don't see a single duplicate in your sample data. Every single entry has a different field combination.
Look at the table in post #2 above. If we wanted to just remove duplicates when considering ALL the fields, the first and third second records would be duplicates, because EVERY single field in those two records are exactly the same. However, the second record is NOT a duplicate because the Allocation amount is different than the other two records. So doing an Aggregate Query on all the fields. Make sense?

What exactly are you looking to return?

[ssanfu]

@Sam11420,

FYI,
By posting your question under the thread by fuecheefang (the OP), you have done what is called "Hi-jacking a thread".
You should have started your own thread and referenced the thread by fuecheefang.

You will get more views by starting your own thread and referencing a thread by another poster.

[Sam11420]

Hi

oh my, my apologies did not even know there was such a thing as thread hi-jacking.

I created a post called multiple tables query. I am very new at access databases so please bear with me.

please can you have a look at my post to see where I am going VERY wrong