Two fields to one record

[Guest]

Here is a detail example of what I am trying to do.
Delta Q table
Field=Charger, Data Type=Autonumber(Primary Key)
Field=Serial Number, Data Type=Text
LSV Table
Field=LSV-2472, Data Type=Autonumber(Primary Key)
Field=Serial Number, Data Type=Text
Field=Charger, Data Type=Number(Foreign Key)

That is how I have the two tables set up.
Now for each LSV that is auto numbered I need two Delta Q charger serial
numbers to go with it. here is an example
I have a one to one relationship between the two tables from Charger(primary
Key) on the Delta Q table to Charger(Foreign Key) on the LSV table.

Delta Q table info
1(Autonumbered) 1234(serial number I input)
2(Autonumbered) 5678(serial number I input)

LSV table what I am trying to do on the but I can't
1(Autonumbered) 000112(serial number I input)
!!!Problem I need to assign Both 1 and 2 (autonumbers) from the Delta Q
table into this one field(charger foreign key) because each LSV has two Delta
Q chargers inside it and i need to show both of their serial numbers.

Hope this is a detailed enough explaination. Once again thank you very much
for your time and patience.

Josh

 

[Guest]

Josh:

It sounds to me like you have a many-to-many relationship between the tables
here, in which case you need a third table to model it. This third table
should have Charger and LSV-2472 columns, not autonumbers but straightforward
long integer data types this time. Each is a foreign key referencing the
primary keys of the other tables. Together they form the composite primary
key of the table. Delete the Charger foreign key column form the LSV table.

For data entry have a form based on the LSV table and a subform based on the
new third table. Link them on the LSV-2472 columns. The subform will
probably best be in continuous form view and have a combo box bound to the
Charger column with a RowSource drawing on the Delta Q table such as:

SELECT Charger, [Serial Number]
FROM [Delta Q]
ORDER BY [Serial Number];

Set the combo box's properties as follows:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm (or rough equivalent in inches, but the first
dimension must be zero)

You'll see the serial numbers listed in the combo box so can just select
one. To associate two rows from Delta Q with the current LSV row simply add
two rows in the subform, selecting a different serial number in each case.

Ken Sheridan
Stafford, England