Table Relationship for database

[Shreekant Patel]

Hi

I was wondering if I could get some advice on the design and table
relationship for my database. The purpose of the database is for data
entry. There are some fields that have been populated with data and
some that need to be entered by our users.

On my form there are 3 main combo boxes, "Line",
"Asset_Number", and "E Asset Type".

The way I want to design the form is that if a user selects a certain
Line, all the Asset Numbers relevant to that particular Line appear in
the Asset_Number combo box.

The structure of the tables:

tblLine - holds the 10 different lines which can users select from
LineID PK
Line

tblAsset_Number - Each line has about 100 records which have an Asset Number.
Asset_NumberID PK
AsseT_Number

What I have tried doing is adding LineID to tblAsset_Number but I seem
to get an error when creating the relationship. I am dragging LineID
from tblLine to tblAsset_Number, LineID. I check Enforce Referential
Integrity and I get the following error:

"Relationship must be on the same number of fields with the same data
types."

If you require any more info please let me know. I appreciate the help
I get.

Thank You
Shreekant

 

[Barry Gilbert]

The data types must be the same in both tables. If they're numeric, they must
be of the same field size (integer, long integer, etc.)

HTH,
Barry

 

[Shreekant Patel]

Hi Barry,

Thanks for that, I worked on it and made some changes and I have the
relationship working. One thing I did notice is that the LineID in the
Asset_Numbers tables did not become a foreign key. Why is that?

I have another question to ask though, How do I create an sql statment
that will allow me to control what asset numbers appear in the
asset_numbers combo box depending on what Line is chosen?

E.g. if Line A is selected from the drop down, only the Asset Numbers
for Line A appear.

Your help is appreciated.

Thanks
Shreekant

 

[Barry Gilbert]

If you created the relationship successfully, then it is a foreign key.
Access will not exactly display which fields are foreign keys. If you look at
the indxes UI for Asset_Numbers, Access probably created an index on the
LineId column.

You combobox should have something like this in it's row source:

SELECT AssetNumber FROM Asset_Numbers WHERE Asset_Numbers.LineId =
Me.cboLineIds

Barry