tables and relationships

[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

 

[Duane Hookom]

Apparently the field types you are attempting to join on aren't the same.
Since you didn't provide this information, we can't tell.

 

[Shreekant Patel]

Hi Duane,

LineID PK = AutoNumber , Long Integer, Increment

Asset_NumberID = Autonumber, Long Integer, Random

Asset_Number = Text

I am pretty new at access development so hopefully this is the
information you are looking for. If not please advise if neccessary.

Your help is much appreciated.

Many Thanks
Shreekant Patel.

 

[Shreekant Patel]

Hi Duane,

I have been working on the database and I have noticed that if i
changed the LineID data type to number in the Asset_Number table and
create the relationship, i can enforce the referential integrity and
have the one to many relationship.

I think I still need to construct a suitable SQL statement to enable
the selections to work i.e. if Line A is chosen, then only the
AssetNumbers for LineA appear in combo box Asset_Number.

Could you suggest how I may be able to accomplish this.

ManyThanks
Shreekant Patel.

 

[Duane Hookom]

Assuming you have the relationships set up correctly, you can create
cascading combo boxes using the examples on Roger Carlson's page of small
sample databases http://www.rogersaccesslibrary.com/TableOfContents3.asp.