query help, duplicates

[rob1234]

I have a table in a healthcare patient database that stores Admitting Number, Scan Number, Last Name, and some other fields detailing the scan. Each patient can have multiple scans and I'm trying to design a query that lists how many scans each patient has had by finding all the duplicate admitting numbers. I'm using this SQL statement:

SELECT MultScans.Admitting_Number, Count(MultScans.Admitting_Number) AS Num_Of_Scans
FROM MultScans
GROUP BY MultScans.Admitting_Number
HAVING (((Count(MultScans.Admitting_Number))>1));

which works fine but I can't figure out how to also print out the last name, scan number and the other fields in this query. If I add the field to the select part it says I can't because the expression is not part of the aggregate function and if I add the expression to the 'group by' the query runs but displays all the duplicates. Thanks for any help.

 

[FoFa]

You would be better off just creating another query based on this query joined to the table you want to pull the other data from.

 

[rob1234]

Yeah, that's what I've been trying but it always ends up displaying the duplicates. I.E. if the first query has a record "Admitting_Number: 234, Num_Of_Scans: 2". Then when I create a new query joining this with the original table on Admitting_Number and include the Last_Name I'll get two records in the new Query for Admitting_Number 234. I've tried all the joins (left, right, inner and outer) I'm sure it's something simple I'm missing but it's been 2 years since I've done any database work. Thanks.

 

[rob1234]

Nevermind about this I just realized it won't work.