How do I use a table to validate data being entered into another

[maritta]

I have a table called DATA ENTRY into which data is being entered in a
CAMPAIGN field and 5 different UPC fields. The second table is called UPC
and has a CAMPAIGN field and one UPC field. This table has multiple records
with the same CAMPAIGN but each record has a unique UPC.

Now - each entry in the DATA ENTRY table's UPC fields needs to be looked up
in the UPC table to see if it is valid for the campaign. A "good" or "bad"
message should be returned to the form depending on the results of the lookup.

Please HELP - I am new to this!

 

[Paul Shapiro]

I have a table called DATA ENTRY into which data is being entered in a
CAMPAIGN field and 5 different UPC fields. The second table is called UPC
and has a CAMPAIGN field and one UPC field. This table has multiple
records
with the same CAMPAIGN but each record has a unique UPC.

Now - each entry in the DATA ENTRY table's UPC fields needs to be looked
up
in the UPC table to see if it is valid for the campaign. A "good" or
"bad"
message should be returned to the form depending on the results of the
lookup.

Please HELP - I am new to this!

The design of DataEntry seems questionable. You should not have repeating
fields like UPC1, UPC2, etc. Generally you would structure those 5 columns
as 5 rows, similar to what you describe for the UPC table. Does DataEntry
have additional fields? A better name might help indicate it's purpose. Data
entry happens in all tables, so that's not very helpful as a table name.
Also, it's generally considered confusing to have a table with the same name
as one of it's columns. The UPC table might be better named something like
CampaignItem, if that's what it is.

Anyway, to enforce the rule that the {Campaign, UPCn} values in DataEntry
must match existing {Campaign, UPC} values in UPC, you would setup
referential integrity. It's easier with well-designed data structures, but
with your existing structure you would create 5 enforced relationships from
UPC to DataEntry. Each relationship would have UPC{Campaign, UPC} as the
parent table, and one of the five DataEntry{Campaign, UPCn} as the child
table.

Since you didn't mention what Access version you're using, check the online
help for "referential integrity" for more instructions. Access 2007 has some
reasonably extensive discussion along with the instructions. I don't
remember what was in earlier versions.