Pulling correct data into a report if user has multiple entries

[LRay67]

I have created a database that tracks active and inactive contractors. This
one report captures all the information from contract dates, invoices paid
through this contract, total of contract, etc. Each contract has a separate
CID (primary), which is connected to other tables using the same CID. I am
using the LastName field as a lookup to find the information when the report
comes up. Using the LastName field works fine if and only if there is only
one entry for that particular LastName otherwise it does not work properly
since there are multiple entries for the LastName. How can I extract the
correct contract information based on the CID, Lastname, FirstName (most
likely the users of the database will need to also see the contract dates)
for them to select the correct inactive or active information. Any
suggestions would be greatly appreciated. Thanks

Linda

 

[Klatuu]

This is where you would want to use cascading combo boxes.

Since you don't have a properly normalized database (I know that because you
get multiple occurances of the name) you can use a query as the combo's row
source with the DISTINCT predicate in it. That will cause it to show only
one occurance of each name.

To get the other data, you can create a second combo that has a query as its
rowsource that filters the results on th selected customer. Then in the
AfterUpdate event of the first combo, requery the second combo.

 

[LRay67]

Maybe I didn't state in the correct manner. The primary table (which
captures the Contractors Name, Dates of Contract, Amount of Contract, etc.
(this data is primary information and not dealing with invoices, physicall
access to buildings etc. these are located in different tables) each contract
(not contractor) has a unique CID number. It doesn't matter if the
contractor has had many contracts with my company, they will still have an
unique identifer (which is the CID) per contract. My other tables are
related and linked by the CID in which pulls in the proper data for each
contract. It is just when I have multiple last names, or the same last name
with multiple contracts (whether active or inactive) that the data doesn't
get pulled in from. Your suggestion will it still work?

 

[LRay67]

Klatuu - my forms for pulling in this data works perfectly. It is just the
report I need a little assistance on.

 

[Klatuu]

I don't quite follow. I thought you were describing a form situation.
What are you wanting to happen on the report?