Still can't get my head around many-to-many

[Milothicus]

Database: Invoices....

need a billing address, and a delivery address.

I have a table of addresses (of both kinds, of course), a table of invoices, and a linking table for the many-to-many, because each invoice has a delivery address, but may have a separate billing address.

in my linking table i have PKs from each table, but i also need to categorize each relationship as either a billing or a delivery address, so i added another number (1 = delivery, 2 = billing).

i've got a main query that populates general invoice Detail from just the main table (data source for main form)

the main form has two subforms, each one being populated by its own query with AddressType set to either 1 or 2, and form/subform relationships on the main table's PK.

i can't get my forms to update the linking table directly.

is this possible?

am i doing something wrong?

am i missing something obvious?

it would be nice to be able to search for the word "many".......

 

[Oyster]

Could you list the fields in each table so that we can get a better idea of your data flow? Sounds like you only have three tables? Addresses, invoices,
and a linking table? You really can't have a many to many relationship because at some point, there must be something that declares that each record is unique.

For example, an invoice number can belong to one (and only one) order. But the invoice can have different delivery addresses. So, you could have a table of invoices where each invoice number is unique(the primary key). Then you could have a table of Delivery addresses. On this table, the first field could be the invoice numer, second field could be the billing address. In this way, one invoice could be tied to many, many delivery addresses:
Invoices table.........................Delivery Address table
1001000..Bellvue Corp..............1001000............1212 Brown Avenue
............................................1001000............1212 Temple Street
............................................1001000............1414 Sherwood Forrest
1003000..Tillman Mfg................1003000............11 Cottonwood Lane

Hopes this helps!!!!!!!

 

[Pat Hartman]

This isn't really a many-to-many relationship. Generally, billing address is an attribute of customer and shipping address is an attribute of order.