Form/Query question

[down in flames]

I have begun creating an access database listing the location(s) of a
specific component in automobiles. I have already set up the location table
by make/model/year/location and have created a form to enter this data into
the table. Where I am having problems is now I want to create a way for
end-users to actually input make/model/year and view the component location.
Any help would be appreciated.

 

[Wayne-I-M]

Don't do it !!

Well not yet anyway. Create you tables 1st - then look at the referencing.

You have some tables already

tblLocation
LocationID
Shelf
StoorRoom
etc

I know this will be wrong but you get the idea

Next create a table list the components (not the location) - the assumption
being that more than one item will be on the same shelf etc

tblCompoents
ComponentID
Discription - or whatever
If this component is only used in one car then also put in
make
model
year
location
If not then make another table for this.

Now create a jounction table

tblFinder
FinderID
LocationID
ComponentID
plus details of staff member searching or whatever

Now ou can create a query/form/report showing where each item is and for
what vehicle.

You can create a serch form - with casscasdeing combos that will allow users
to search for items for any make of vehicle.

etc

The basic answer is to create the tables "1st" then do the rest

 

[down in flames]

Thanks for the reply, I did not explain the situation well, sorry. Each
make/model will have a unique location (in the vehicle). For example, a
database of the airbag control modules by vehicle. That way, techs working in
the shop can enter what vehicle they are going to be working on and see where
the module is in that vehicle. Seems like this would be easy to accomplish
but I cant get my head around it.

 

[Wayne-I-M]

OK thats a little simpler.

You "may" to create a junction table - but to give you VERY basic method try
this

Create 2 tables

tblVehicle
VechicleID
VechicleMake
VechicleModel

tblComponent
ComponentID
VechicleID
ComponentDiscription

Open the relationships window and drag the VechicleID from tblVechicle over
the same field in tblComponent

Next create form based on tblVehicle and another based on the other table

Cloase both and then open tblVehicle in design view and (from the tool box)
add a subform - use the 2nd form you just made

Link them on VehicleID (which should be on both forms)

Now when you select a car the components for that car will show up in the
subform

BUT - this is only a very basic example and you really will to increase the
application to get it to work in a real world setting but it should point you
in the right direction.

Just an idea though. I would always create aquery from the table and then
base the forms on the queries not the tables - for lots of reasons ?

Hope this is a little help.

 

[down in flames]

That is very helpful thank you!

OK thats a little simpler.

You "may" to create a junction table - but to give you VERY basic method try
this

Create 2 tables

tblVehicle
VechicleID
VechicleMake
VechicleModel

tblComponent
ComponentID
VechicleID
ComponentDiscription

Open the relationships window and drag the VechicleID from tblVechicle over
the same field in tblComponent

Next create form based on tblVehicle and another based on the other table

Cloase both and then open tblVehicle in design view and (from the tool box)
add a subform - use the 2nd form you just made

Link them on VehicleID (which should be on both forms)

Now when you select a car the components for that car will show up in the
subform

BUT - this is only a very basic example and you really will to increase the
application to get it to work in a real world setting but it should point you
in the right direction.

Just an idea though. I would always create aquery from the table and then
base the forms on the queries not the tables - for lots of reasons ?

Hope this is a little help.