Referring to similar tables toward a common goal | Access forum

Dear Best STL

(Please see accompanying screen shots sent in email to info@stl-training.co.uk)

My database has groups of related tables, where each group is specific to a certain kind of contact. Some examples include:
Stores group (Stores_group.gif) - names of stores with their employees and related information (addresses, emails, tel. numbers etc)
Press group (Press_group.gif) - names of press houses (Elle, Vogue etc) with their employees and related information (addresses, emails, tel. numbers etc)
Contacts group (Contacts_group.gif) - names of people, associated company (if any) and related information (addresses, emails, tel. numbers etc)

Up until now, through my lack of Access knowledge, these groups have been pretty much used in isolation. This situation has to change, as apart from being inefficient (the groups could share the same report to make delivery lists, for example) it's got to a silly situation regarding orders of our products. Nearly all our sales are made to stores (Stores_related_tables_with_orders.gif), but someone working for a magazine (in the press group), may want to order a product; as could someone from the contacts group, or any other group. Up until now a person who is not part of the stores group, is added to this group, so I can process their order and create the necessary documents. This leads to two identical bits of information being used in the database, which I know is not good, apart from wasting my time because I have to copy contact information from one group into the stores group.

My question is, how can I refer to information kept in different tables so I can create orders for everybody entered in the database in whatever group they exist? Presumably a query is needed for the job, but how when the ID AutoNumber could be the same for two different contacts from two different groups.

Note 1: If you see any errors or improvements to be made in my table relationships, I'd of course be happy to hear them.
Note 2: Many other tables (with mostly one-to-one relationships) have been deleted from the screen shots for clarity.

Many thanks ... oh yes, and Happy New Year!
Joe