Access 97

[czink9]

I have two tables, Account and Location, and I am trying to verify that the sales code and location id in the account table have a corresponding row in the location table. However, my problem is that in the location table, I have multiple rows for the same sales code. Can someone please provide some assistance on how I can verify that the sales code and location id values for each account are equal to the sales code and location id in the location table?

 

[Remou]

Hmm, I was just about to answer your post in the Microsoft Office forum.
How about Dlookup?
DLookup Function Examples
faq705-4978

 

[czink9]

Will this work even though I am not putting them into a form or a report?

 

[Remou]

No. Perhaps you could provide a bit more detail? For example, if you are entering data directly into a table, you have very little control.

 

[czink9]

In the Account table, I have an account number, a sales code and a location id. In the Location table I have a sales code and a location id. All of this information has been imported from other sources. I need to perform a mass compare on the account table to verify that the sales code and location id have a record in the location table. Hope this clarifies this issue. If you need additional information, please ask.

 

[PHV]

Have a look at RelationsShip and Referential Integrity.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

A query may help show up existing problems, if you have already imported the data:

SELECT tblAccounts.AccountNo, tblAccounts.SalesCode, tblAccounts.LocationID, tblLocations.LocationID, tblLocations.SalesCode
FROM tblLocations RIGHT JOIN tblAccounts ON (tblLocations.SalesCode = tblAccounts.SalesCode) AND (tblLocations.LocationID = tblAccounts.LocationID)
WHERE (((tblLocations.LocationID) Is Null)) OR (((tblLocations.SalesCode) Is Null));

Then Referential Integrity will prevent any more problems.

Hope I have that right.

 

[TheAceMan1]

How are ya czink9 . . . . .

Have you tried the [blue]Find Unmatched Query Wizard![/blue]

See Ya! . . . . . .