Access - Relationships
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.
Have a look here and save yourself some time: Free Access Database Sample - Asset Management Database
This might save you time and effort :D
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 :D
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.
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):
tblMakeMode_lID - auto num (pk)
tblMakeMode_MakeID - num (fk)
tblMakeMode_ModelID - num (fk)
tblMake_ID - auto num (pk)
tblMake_Make - text
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:
Note that the @ModelID is the variable you want to look up.
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
If you're not sure what the above does here it is rewritten slightly differently:
select tblMake_Make from tblMake, tblMakeModel, tblModel
tblMake_MakeID = tblMakeModel_MakelID and
tblModel_ModelID = tblMakeModel_ModelID and
tblModel_ID = @ModelID
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):
select tblMakeAndModel_Make from tblMakeAndModel where tblMakeAndModel_Model = @variable
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.
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?