Solved

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

Posted on 2014-02-23
5
409 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now