Solved

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

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
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…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

910 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

23 Experts available now in Live!

Get 1:1 Help Now