How do i relate 2 customers to 1 sale record?

[gr8R]

I am an inexperienced user of Access so please reply in non technical language.

I have 2 tables. 1 table has specific details about the sale of a product (e.g. dates) and the other table has customer details.

Most of the time 1 record in the sale details table is related to 1 customer. However sometimes 2 customers (husband and wife) need to be related to a set of sale details. - but i don't know how to do this without creating 2 sale detail records - 1 for the husband and 1 for the wife.

I considered having 2 customer ID fields in a sale details record. However i don't know how to make this work.

Any help would be appreciated.

 

[ToeShot]

Could you just add A field(s) for all people associated to that sale?

 

[JimHorton]

The way this is done is with an associative table. This is common with addresses, which can be shared, ie a customer has several addresses: Home, Winter Address, Work Address, etc. Many customers may share the same Work Address, ie. So a third table is used, which contains, at the barest level, a CustomerID and AddressID.

This table goes conceptually 'in between' the Customer and Address table--or Customer and Sale table in your case--which I'll use as the example here. The Primary key of this table is the pair--CustomerID & SalesID.

Link CustomerTable CustomerID to CustomerID in the 'middle' table, we'll call it 'LinkTable' and link SalesID from Sales table to the SalesId in LinkTable. Both one<--->>Many relationship, with the many side for both relationships being the LinkTable.

This way, a customer can have many sales (the normal case), but also now the Sale can have many customers.

You can add fields to the LinkTable, such as flags, that might be 'Primary Customer of this Sale' or some such thing. Flags like that must be carefully maintained, and without db level triggers, that gets tricky.
--Jim