Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Access question

Jun 22, 2004 12:38PM PDT

I am working with one table of data. The data has customer numbers (ex 1,2,3,4) and also products bought by those customer numbers (ex a,b,c,d). The customer number may have bought one or more products. Each purchase is a different entry or row of information.
I have around 100,000 rows.
I want to find some information in the simplest way possible.
1. How many and what customers have each of the possible combinations.
For example (customers 6,1000,and 16000 have only product d while customers 26, 88, and 4678 all have products a,b,c.)

There are 12 products of which a customer may have purchased one or more.

Any ideas of a simple way to get to this information and a sample query?
Thanks

Discussion is locked

- Collapse -
Re: Access question
Jun 22, 2004 6:48PM PDT

I think it can be done in a few steps. It takes some time however.

1. Make a query A with customer number and "A" for all distinct customernumbers having bought product A.
2. Same up to L.
3. Make a query with your customer table as primary entry and an outer join to query A through L. Output is customernumber, followed by 12 times a letter or a NULL. Say 6,,,D,,,,,,, and 26,A,B,C,,,,,,,,,
4. Make a query to convert the 12 values into one string products_bought, replacing NULL with space. So 6," D " and 26,"ABC ".
5. A group-by-query of this result by products_bought.

It isn't difficult, it's step by step, so you have ample opportunity to check the intermediate results.


Kees

- Collapse -
Re: Access question
Jun 23, 2004 12:42PM PDT

First, thank you for the help. I have completed steps 1-3. I am not sure how to write the query for step number 4. Where I take the product values and make them a string. Can you give me an example of how to write that query.
Thanks,
Leslie

- Collapse -
Re: Access question
Jun 23, 2004 8:27PM PDT

Icohn,

It's a combination of nz-function to make a NULL to a space and keep any not NULL, and string concatination:
nz(A," ") & nz (B," ") & ...

Visual Basic help will tell you all about the nz-function. String concationation is just an operator.

Hope this helps.


Kees