Help buidling data input form, please?

[Ed from AZ]

I have three tables:
-- Vehicles - a text field for the vehicle and a Yes/No for active
status
-- Parts - a text field for part info and a Yes/No for active status
-- Parts Data - several fields to track usage of various parts across
several vehicles

I am trying to build a form to input data into the Parts Data table.

I have a query that returns only the active vehicles and a query that
returns only the active parts.

I created a new form using the wizard. I chose the Vehicles field
from the Active Vehicles query, the Parts field from the Active Parts
query, and all the fields except "vehicle" and "part" from the Parts
Data table.

When I click NEXT in the Form wizard, I get an error:
"You have chosen fields from record sources which the wizard can't
connect. You may have chosen fields from a table and a query based on
that table. If so, try choosing fields from only the table or only
the query." (NOTE: the two queries and their tables are not based in
the Parts Data table.)

How can I resolve this?

Ed

 

[Pete D.]

What you need is a many to many relationship. Look at this explanation and
post back if stuck.
http://www.databasedev.co.uk/many_to_many_example.html

 

[Ed from AZ]

Okay, Pete - I'm stuck!! 8>)

I'm using Access 2003. Corporate security settings prevent me from
downloading your sample database. (They've even screwed with the
cookie settings so bad I can't pull up the web-based Access help
files!!)

I did pull up the Vehicles query, Parts query, and Parts Data table
and create relationships between each query and the table - Vehicle in
the Vehicles query and Parts in the Parts query to the fileds of the
same name in the Parts Data table. Both joins are type 1, where
records from both must match. I only want the active vehicles and
active parts to show in the data input form.


Am I at least headed in the right direction?

Ed

 

[Pete D.]

Sounds like you may not have normalize correctly.
First you have a vehicle table
Second you have a parts table.
Not to be confused with query

next you create a table between them with vehicle id and part id, combine
these into one keyfield. The idea is you can have one vehicle with many
different parts and many parts apply to many different vehicles. The table
in the middle is the transaction and may also contain qty, date used and
such. For instance brake rotors front, 2, 18 Dec 2007

You link the vehicle id in vehicle table to the vehicle id in the table
between them. Then you link the part id to the part id in the same table.
To keep from confusing yourself make the between table field names slightly
different so you can tell which table your working. in.

I can't see your data so I'm assuming the vehicle id and part id are unique
and indexed fields no duplicates allowed.

Reading material Access help
Search "many to many", it will provide you table diagrams and explanations.

 

[Ed from AZ]

Don't know if it's right or if it will work, but here's what I came up
with:

-- A table, Vehicles, with two fields: Vehicle (text, primary key,
indexed, no dups) and Active (Yes/No)
-- A table, Parts, with two fields: Parts (text, primary key, indexed,
no dups) and Active (Yes/No)
-- A table, VehPartsID, created by copying the Vehicle and Part fields
from the previous two tables and pasting them into the design. Both
fields were selected and made primary key.
-- A table, Parts Data, with all the fields for all the data I want
for each part.

-- A relationship was created between Vehicles:Vehicle and
VehPartsID:VehicleID - one-to-one, join type 1.
-- A relationship was created between Partsartsand
VehPartsIDartsID - one-to-one, join type 1.
-- There is currently no relationship between the main Parts Data
table and any other table.

-- A query to the Vehicles table for all Vehicles where Active = Yes.
-- A query to the Parts table for all Parts where Active = Yes.

-- A single form with a drop-down for Vehicles linked to the Vehicles
query and a drop-down for Parts linked to the Parts query.
-- A form for the Parts Data table, with all fields _except_ Vehicle
SN and Part Type. Instead, the form with the drop-downs was inserted
as a sub-form.

Is this a good start to getting to where I want to go? Or did I take
off in a bad direction?

Ed

 

[Pete D.]

Getting there,
Understand that the middle table will have a many records for each vehicle,
many records for each part. Only one record per vehicle part combination.
Points inline below.

Don't know if it's right or if it will work, but here's what I came up
with:

-- A table, Vehicles, with two fields: Vehicle (text, primary key,
indexed, no dups) and Active (Yes/No)

Good start

-- A table, Parts, with two fields: Parts (text, primary key, indexed,
no dups) and Active (Yes/No) Okay

-- A table, VehPartsID, created by copying the Vehicle and Part fields
from the previous two tables and pasting them into the design. Both
fields were selected and made primary key.

You have related the correct fields but vehicle file will be a one and
vehpartsId will be a many.
Same goes for Parts will be a one and VehiclePartsID will be a many

To start with this file will be empty until you start creating records
When you select a part to that vehicle you will create a record in
vehpartsId with with the combination of vehicle and part.

Example data
Note that one vehicle may have multi entries but each is related to a
differnt book.
Note each book may have a multi entries but it is related to deferent
vehicles
When you create your form with a subform you can go either way. Main form
displays vehicle with subform displaying all books. Or Main form displays
one part with sub form displaying all vehicles with that part.
YearFix, Registration, ManualIdNumber
19 84B00856 36A9-2-59-1
19 84B02598 36A9-2-59-1
19 84D00158 36C18-43-1
19 84D00158 36C18-43-2
19 84D00158 36C18-43-4
19 84D01156 36C20-3-29-1

-- A table, Parts Data, with all the fields for all the data I want
for each part.

This works but, just relate the partsid from this to your parts table
partsid

-- A relationship was created between Vehicles:Vehicle and
VehPartsID:VehicleID - one-to-one, join type 1.

One to many refer to above

-- A relationship was created between Partsartsand
VehPartsIDartsID - one-to-one, join type 1. One to Many
-- There is currently no relationship between the main Parts Data
table and any other table.

Add it

Stop here and fix relations before going on. Wait on query/forms until you
have tables and relations right,

My email can be read from this email, you can send me your datafile so I can
have a better picture.

 

[Ed from AZ]

Pete, you are so kind for sticking with me!

One to many refer to above

I didn't get the option to do that when I created the joins.

This works but, just relate the partsid from this to your parts table
partsid

I don't have a comparable VehPartsID field in this table. That's
probably why I can't make the connection!! I will add that field.

I forgot - there is another twist in here: Position. So I should
probably (somehow!) make a vehicle-part-position combined key field?

So when I select my vehicle and I select my part from the drop-downs,
I would like to get another drop-down showing all of the positions
available for that combination.

The main Parts Data table would hold all the records for all the
history. So at any given time, there could be multiple records for
any combination of vehicle-part-position in that table.

Should I give up and go back to spreadsheets?? 8>\

Ed

(PS - mdb sent to you.)