Plz help Table Relationships

[heominhon127]

Hi, I'm new to this forum as well as working with Access. I just got this job and they want me to create an Inventory Database from their Excel spreadsheets so they can upload to a sharepoint and use it through company employees. However, I created interfaces including forms and reports already. I could not figure out the relationships between these fields. So plz help me. I dont mind adding new fields to make this thing work....
So far I have


Table1 aka tblAsset: AssetID, Manufacturer, Model, Status, Condition, etc
Table2 aka tblManufacturer: Address, City, State, Zip Code, etc
Table3 aka tblContract: ContractType, Duration, Duration Period

Plz tell me how to add primary keys to each table in able to create the right relationship.

[ajetrumpet]

first thing you should do is take out the manufacturer name in ASSET table and replace it with a manufacturer ID field. Add the same field to the manuf's table as the PK for that table. that'll get you going.

the PK's for the other two tables should also be autonumbers, which will almost always be referenced at the table level and should never be visible at the form and report levels (though they can be referenced through invisible columns, other controls, etc...)

[heominhon127]

Hi, thank you very much I'll get it going to see if it works!

[ssanfu]

However, I created interfaces including forms and reports already. I could not figure out the relationships between these fields.

You're going about it totally backwards. Without a good foundation (the tables), the whole database will be difficult (at best) program.

Please attach your database (no sensitive info), if possible, so we can see the table info. Or post the table names, field names and field types.

You do have the database split, don't you??


Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

[heominhon127]

Here's my DB with all the fields that I want to use. This is my first attempt at creating an Access 2007 database. Your help is appreciated.

[ajetrumpet]

Here's my DB with all the fields that I want to use. This is my first attempt at creating an Access 2007 database. Your help is appreciated.

*add an ID field to each table (autonumber)
*And again, as I said previously, take out the manufacturer NAME in the ASSET table and replace it with an ID (number - LONG type)

you can relate your tables on the NAME, but IDs are better IMO because there's less chance for confusion when similar names get entered into the program.

there are other ways to do this: this is just my view.

[ssanfu]

Sorry, I can't look at your structure......I use A2K; your db is A2K7.

Don't give up..

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)