limiting query results

G

Guest

Hey guys. I need your assistance again. The query grabs name, date,
appointment # from the appointments table and links to the reports table to
get report information. The query is supposed to return the appointment info
and the first row of relevant records where the appointment # matches. The
reports table lists the appointment_num in each row of the report. A report
can be 50 lines long. Problem is, that the distinct or distinctrow operator
doesn't seem to be working.
To give you an idea:

SELECT appointments.name, appointments.data, appointments.appointment_num,
report.readingdoc, report.referringdoc
FROM appointments INNER Join reports.appointment_num on
appointments.appointment_num;
 
R

Roger Carlson

Hmmm. Sounds like a credentialing database.

I'm not really sure what you mean. Do you mean you want to return every
record from appointments and just the first record from report where the
appointment_num numbers match? If so, what criteria make the first record
first?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
G

Guest

Roger,

That's exactly what I mean. All from first, only the first record from the
second.
 
R

Roger Carlson

But again, HOW do you know which is the first record in the second table?
There is no intrinsic order in an Access table. You have to order it in
some way.

I can think of two approaches that might work. The first is to use an
aggregate query to return the Max value of the second table (assuming you
can define the Max). The other is to use a TOP query in the sense of the
TOP 1 for each record.

On my website (www.rogersaccesslibrary.com), are two small Access database
samples which illustrate these two approaches: "MaxQueryProblem.mdb" for the
first and "TopQuery.mdb" for the second.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
G

Guest

thanks Roger. Your examples offered much help.

Roger Carlson said:
But again, HOW do you know which is the first record in the second table?
There is no intrinsic order in an Access table. You have to order it in
some way.

I can think of two approaches that might work. The first is to use an
aggregate query to return the Max value of the second table (assuming you
can define the Max). The other is to use a TOP query in the sense of the
TOP 1 for each record.

On my website (www.rogersaccesslibrary.com), are two small Access database
samples which illustrate these two approaches: "MaxQueryProblem.mdb" for the
first and "TopQuery.mdb" for the second.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top