select query, displays in form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a select query that calls information from various
tables. In one table there is a business name that is
linked to audits by permit number. There can be multiple
audits for one business. From my select query i have
created a form which displays all this information.
However i have multiple entries for businesses due to the
fact that there is more than one audit. Is there anyway I
can get the query to only look up the last auit ( they are
numbered A01, A02 etc) and only display that one?
thanks
Nicole
 
I have a select query that calls information from various
tables. In one table there is a business name that is
linked to audits by permit number. There can be multiple
audits for one business. From my select query i have
created a form which displays all this information.
However i have multiple entries for businesses due to the
fact that there is more than one audit. Is there anyway I
can get the query to only look up the last auit ( they are
numbered A01, A02 etc) and only display that one?
thanks
Nicole
Click to expand...

A Subquery will do this. Create a Query joining the business table to
the audit table; on the Audit Number field criteria line put

=(SELECT Max([AuditNumber]) FROM [Audits] AS X WHERE X.[BusinessID] =
[Businesses].[BusinessID])
 
Back
Top