relationships

[steve1111]

Hi i am somewhat new to access, self learning by google and these post for the past 6 months.

i have two tables,

Table 1 has Van Number and the vans gas card number (credit card)

Table 2 is uploaded from an excel report weekly into access. it has a lot of transaction details and the gas card number but no van number. each card can have multiple records per week, how to i automatically have the van column fill in Table 2 based on the card number for the record in table 2?

in other words card 1234 in table 2 look up the van number with 1234 in table 1 and enter that in the van column for table 2.

Ultimately, i want to find unmatched query with table 2 and table three which is data manually entered off gas receipts so we can find any errors with receipts not entered in table 3 reported by the fuel vendor in table 2.

Thanks for any help

 

[catalin.petrut]

In table 2 you dont have to enter car number, because the is a link betwen car and card. You should mention that a card is used by only one car or by many (drivers, whatever). Also, a car can use more cards? This kind of info are mandatory for db design.

 

[steve1111]

the card will always stay with the same van, regardless of driver. I guess i am not sure how to link the tables if i do not need the van number in table 2. If i run a query on van 101 for between [startdate] and [enddate] on a parameter form, how do i link it to pull all the transaction details in table 2?

 

[Pat Hartman]

You link the tables on the Card Number. Since Card number is unique and used by only one Van, you should add a unique index to the Van table on CardNumber. This is an unusual situation where you have two unique identifiers for the same table. The VanID should be the PK since after all, this is a table about the Van. The CardNumber is informational. It is an attribute of the Van and could change. you may decide to go from Master Card to Visa as your credit card provider. So the card numbers would all change but the Van numbers would remain constant.

 

[steve1111]

it looks like doing that and combining the tables in a query worked. thank you!!