Access Queries

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

Guest

I have a contact table and a company table: Contact table has a company id
based on the company id field in the company table. This is the relationship

I am building a report which lists the list of the top 20 companies based on
a selection. In addtion there are multiple contact persons available for
each company. One of the multiple contacts may or may not be the primary
contact person. There is a switch in the database for the user to select
whether or not it is the Primary contact.
My delimma, I need to list in a report the company name and the primary
contact person, however if there is no primary contact person, then I need to
list the next person available and on the report list that this is not the
primary contact. Also cannot pt more than one name per company, because it
all has to fit on one line in the report.

Any suggestions out there?
 
To select the top 20 companies, create a query into just the company table.
Use the Top Values property of the query so you get 20 only. By sure to add
the CompanyID field to the end of the OrderBy clause, so Access has some way
to distinguish between equals if the 20th and 21st are tied.

For the contacts, use a subreport. It will be linked to the main report on
CompanyID. The query for the subreport will look something like this:
SELECT TOP 1 tblContact.CompanyID,
tblContact.FirstName & " " & tblContact.Surname AS FullName
FROM tblContact
ORDER BY tblContact.IsPrimary, tblContact.ID;

The query sorts by the yes/no field, so Yes (-1) sorts before No (0). Again,
adding the primary key value to the ORDER BY clause gives Access a way to
distinguish between equals (e.g. where there are multiple contacts but none
are marked as primary).
 
Sounds like you would need to use a coordinated subquery with a top clause to
get what you want. That would look something like the following.

SELECT Company.*, Contact.Person
FROM Company INNER JOIN Contact
ON Company.CompanyID = Contact.CompanyID
WHERE Contact.ContactID IN
(SELECT TOP 1 C.ID
FROM Contact as C
WHERE C.CompanyID = Company.CompanyID
ORDER BY C.PrimaryFlag Desc, C.Person, C.ID)
 
Back
Top