In Access view - linking orders and customers?

[paulobrad]

Hey,

I'm trying to set up an automated email system to mail customers a couple of weeks after orders are placed. I'm looking for a way of linking the Orders table in the MS Access view with the person table so that email addresses and orderdates can be combined.

Any ideas on the easiest way of combining these tables? There don't seem to be any primary keys in common.

Cheers.

[Bruce]

Hi Paul,

Please follow these steps:

1. Browse to C:\ProgramFiles\Actinic V7\Sites\Site1 and find ActinicCatalog.mdb. Open it in Access.

2. In the pane on the right hand side, click on Queries. If you just see two buttons Objects and Groups, click the Objects button. Now click on Queries.

3. Double click on 'Create Query in Design View'. In the Show Table window that opens, scroll down and find Person table. Select it and then click on Add and then Close.

4. Click on View menu and select SQL view. In the window that opens, copy this query in :

SELECT Person.*, Order.[Order Number]
FROM [Order] INNER JOIN Person ON Order.InvoiceContactID =
Person.ContactID;

Save as Query1 and close the window.

5. Repeat steps 3 & 4. Now in the SQL query window copy and paste this query :

SELECT Person.*, Order.[Order Number]
FROM Person INNER JOIN [Order] ON Person.ContactID =
Order.DeliverContactID;

Save as Query2 and close the window.

Now if you run the queries it will retrieve the data from the two tables alternately. If you wish to retrieve data from specific columns in the Person table, you can replace Person.* with Person.Name, and so on, the same applies for the Order table. To select multiple columns, insert a comma followed by Person.Telephone, for e.g. If you are selecting a column with a name that has spaces 'Order Number', for e.g., then you need to put [] around the field name.

Hope this helps.

Kind regards,