[ms office - 2007] Access - Relationships

[MatthewL]

For arguments sake in this case I want to create a database in Access for all my assets.

 

I have a table with all the makes in and have one with all the models in. The models on looks up to the makes one and pulls that information down.

 

I then want to create the assets table. I want to be able to select the make and in the model field I want it only to display the items that are under the model I selected in the previous field.

 

How do I do it?

 

For arguments sake I have 10 makes and 50 models. I will then say have some 2000 assets.

 

Can someone help please?

 

Thanks in advance.

[bossman]

@MatthewL:

 

Have a look here and save yourself some time: Free Access Database Sample - Asset Management Database

 

This might save you time and effort

 

Or if you still want to do it yourself here is a good tutorial: Building an Access Database From the Ground Up

 

Either way enjoy yourself

[MatthewL]

I was only using asset management as an example to try and explain what I was trying to achieve.

 

I will have a go myself and will have a read of that link.

[Pashers]

Are you new at databases? I would have three tables - one for the make and model (tblMakeModel) one for the Make (tblMake) and one for the Model (tblModel):

 

tblMakeModel:

tblMakeMode_lID - auto num (pk)

tblMakeMode_MakeID - num (fk)

tblMakeMode_ModelID - num (fk)

 

tblMake:

tblMake_ID - auto num (pk)

tblMake_Make - text

 

tblModel:

tblModel_ID - auto num (pk)

tblModel_Model - text

 

PK - Primary Key

FK - Foreign key

 

This would help save memory space and repeating your self.

The SQL would look something like:

select tblMake_Make from tblMake left outer join tblMakeModel 
tblMake_MakeID = tblMakeModel_MakelID
left outer join tblModel
tblModel_ModelID = tblMakeModel_ModelID 
where tblModel_ID = @ModelID

 

Note that the @ModelID is the variable you want to look up.

 

If you're not sure what the above does here it is rewritten slightly differently:

select tblMake_Make from tblMake, tblMakeModel, tblModel
where
tblMake_MakeID = tblMakeModel_MakelID and
tblModel_ModelID = tblMakeModel_ModelID and
tblModel_ID = @ModelID

Edited March 17, 2011 by Pashers

[Pashers]

Or a lazy/quick dirty way to do it (I would not recommend this as it means duplicating yourself but if it suites your needs and makes your life easier):

tblMakeAndModel

tblMakeAndModel_ID (PK)

tblMakeAndModel_Make (text)

tblMakeAndModel_Model (text)

 

select tblMakeAndModel_Make from tblMakeAndModel where tblMakeAndModel_Model = @variable[/Code]

[MatthewL]

I have done some database work, I will have gander when I get some time at them few bits and see where I can get.

[GREED]

If using Access there are a set of GUI options that will 'filter' available fields based on selections in other fields. I going to take a punt and say you are wanting to use combo boxes for these fields?