Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • Last Modified:

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
0
ferguson_jerald
Asked:
ferguson_jerald
  • 2
  • 2
2 Solutions
 
Patrick TallaricoCommented:
Check out your group by clause and having clause in the second query.  Since you are looking for fID field which is a foreign key, and unique (i assume), using the group by clause and a having clause is causing you to return no records. Since if the fID is a group by field, and unique, there will only be one record returned for each fID, whereas your previous query would return results having count(...) > 1, the grouping by the unique id field will cause each record to be unique, and therefore the count(...) will never be > 1.

If you just remove the fID field from the group by clause, then you should have the results from the first query with only one of the fID field values per record.
If you need all the fIDs, you could just remove the Having clause.
0
 
ferguson_jeraldAuthor Commented:
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
0
 
Patrick TallaricoCommented:
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.
0
 
Gustav BrockCIOCommented:
First, the query should read:

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

Then, if you include a unique ID, Count(*) will always be 1, thus no records are returned with the given criterium.

So you will have to group by a foreign ID, not a primary ID or a unique ID.

/gustav
0
 
ferguson_jeraldAuthor Commented:
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

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now