Solved

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

Posted on 2014-02-23
5
417 Views
Last Modified: 2014-02-24
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
Comment
Question by:ferguson_jerald
  • 2
  • 2
5 Comments
 
LVL 6

Accepted Solution

by:
Patrick Tallarico earned 300 total points
ID: 39881737
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
 

Author Comment

by:ferguson_jerald
ID: 39881787
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
 
LVL 6

Expert Comment

by:Patrick Tallarico
ID: 39881838
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
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 200 total points
ID: 39881876
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
 

Author Comment

by:ferguson_jerald
ID: 39883216
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question