asked on

MS Access query breaks when I add new fields from the same table

Hello Experts!

I need some more help with queries in MS Access. I'm currently using MS Access 2013. The following query provides the names of all people that have made more than one request. I would like to display the foreign key (fID) also, but when I do the query returns nothing. The following is the query that works:

SELECT [scFN] & " " & [scLN] AS SubjectName, subjClassTbl.scTypeSrc
FROM subjClassTbl
GROUP BY [scFN] & " " & [scLN], subjClassTbl.scTypeSrc
HAVING (((Count([scFN] & " " & [scLN]))>1));

Open in new window

The following is the query that doesn't work - I'm just adding in the fID field:

SELECT [scFN] & " " & [scLN] AS SubjectName, subjClassTbl.scTypeSrc, subjClassTbl.fID
FROM subjClassTbl
GROUP BY [scFN] & " " & [scLN], subjClassTbl.scTypeSrc, subjClassTbl.fID
HAVING (((Count([scFN] & " " & [scLN]))>1));

Open in new window

All data is located in the same table ... subjClassTbl.

Any help on what I'm doing incorrectly would be greatly appreciated.

Thanks,
J

ASKER CERTIFIED SOLUTION

Patrick Tallarico

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ferguson_jerald

ASKER

Thanks for such a quick reply. I need the fID to display for all SubjectNames that have a count of >1. For example, if fID 20 and 24 have the same subject JohnDoe, then I would expect the query to return:

fID SubjectName
20 John Doe
24 John Doe

Do you have any other suggestions for how I could get the desired results?

thanks,
J

Patrick Tallarico

Is there actually a case where the fID is not unique?
Based upon your response, i would look to remove the Having clause and check your results. If fID is unique per record, then there will be no result returned from a query grouped by fID that would have a count of more than one. Try removing the having clause and add the count(...) as a result field to see what i mean.
Let me know what you find.

SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ferguson_jerald

ASKER

Thank you both for your assistance. Based on your feedback I was able to better understand how I needed to get the results needed. The following is the query I ended-up using:

SELECT scFN&" "&scLN AS SubjectName, fID, scID
FROM subjClassTbl
WHERE scFN&" "&scLN IN
(SELECT scFN&" "&scLN
FROM subjClassTbl
GROUP BY scFN&" "&scLN
HAVING COUNT(*)>1
)
ORDER BY scFN&" "&scLN;

Open in new window