'keep details private'

[jxm28788]

On the customer tab of the order screen there is a checkbox for 'keep details private', and after reading some back posts I have figured out how this is supposed to work (and this is exactly what I wanted).

Unfortunately, I did not implement this originally and had used one of the 'user defined' boxes under 'General Information' to record whether or not the customer wanted to be contacted. And I now have a lot of customers...

Now I come to extract the data however I'm not sure if there is an easy way to get this information out (doh!).

Looking in the MDB file there is a table with all the customers in (person), and a table with all the user defined fields in (order). Is there any easy way to write a query that will produce a list of the people along with the information they entered intot he user defined field? (I'm completely clueless with access)

[Krithika]

Hi

Please follow these steps:

1. Browse to C:\ProgramFiles\ActinicEcommerceV6\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.sUserDefinedGeneral
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.sUserDefinedGeneral
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. To select multiple columns, insert a comma followed by Person.Telephone, for e.g.

Hope this helps.

Cheers

[jxm28788]

I think I'm doing something wrong... I ran query1 then query 2 and they both just gave me a list of all the people with their details (in fact it looks like the actual person table...)

Was I supposed to pick a different table in step 3 on the 2nd time round?

[Pneumatus]

The final column in the tables generated would have the user defined field text in.

[jxm28788]

ahah!

Unfortunately that particular column is blank - the one I want is 'Reason For Purchase', but when I change the query it doesn't like that name because it has spaces. I tried it with quotes and double quotes but it doesn't like those, and I took the spaces out and it pops up a window saying 'enter parameter value'...?

[Pneumatus]

Try putting [] around the field name if it has spaces in it.

[jxm28788]

ok, thats great! I've got what I wanted - a list with name, e-mail and 'do you want special offers'...

But just wondering why 2 queries...? They both appear to output the same results?

[Pneumatus]

The only difference is that the first query joins to show you the customer invoice address details and the second joins to show you the customer delivery address details.