Solved

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

Posted on 2014-02-23
5
420 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 50

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

756 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