Query Array? (1 Viewer)

[MSherfey]

I'm not even sure what to call this thread let alone how to search for it in the forum. Query Array is as close as I could get

I need to find out what customers exist in each of 6 groups as well as the combination of those groups. I know the customers in groupA, groupB, groupC, etc (very simple queries). Now I need to run various queries to see which ones are in each combination:

Which customers are in A+B, A+B+C, A+C, etc...all the way through the 63 different possibilities. I can either make 63 different queries (ouch) or see if there is a way for Access to do this automatically.

If I give the queries a standard name, like Q1-Q6, can I have Access go through a loop to test all the possibilities?

 

[stopher]

Interesting problem

Is each occurence of customer/group unique?

If so then I would create a query that generats a column of a binary interpretation of the group where:

A => 1
B => 10
C => 100
D => 1000
E => 10000
F => 100000
(you could put the above in a table)

So

customer___group___binary
cust1_______A______1
cust1_______B______10
cust2_______D______1000
cust3_______A______1
cust4_______A______1
cust4_______B______10
etc..

You can then sum this query by customer (sum binary). In the above example we end up with:

cust1____11
cust2____1000
cust3____1
cust4____11


You cab then sort the above by the binary

1_______cust3
11______cust1
11______cust4
1000____cust2

hopefully you can see here that 1 translates to just A, 11 is A & B, 1000 is D etc (which you could put in a 63 row table to aid decoding). This is easily presented in a report.

If each customer/group occurence is not unique then you just need to add an extra step at the the beginning to get the unique row e.g. using MAX.

Of course the number 63 is related to the binary system 2^6-1 (-1 since you have no interest in zero)

hth
Chris

 

[MSherfey]

Yes, I was thinking the same thing. Instead of binary, I just assigned values to each one.

A=1
B=3
C=5
D=10
E=20
F=40

In fact, I already have an Excel file with all the different combinations and their values. What I don't know how to do is get the customer count for each combination without creating 63 different queries.

Is there a way to have Access go through an array , filled with query combinations, and run the queries?

A INNER JOIN B
A INNER JOIN B INNER JOIN C
etc...


Am I asking this right? I'm not sure about the correct terminology.

 

[gemma-the-husky]

the thing is...

how can a customer be a member of multiple groups.

do you have 6 different yes/no fields, or what?

the easiest way (and the normalised way) to do this is to have a lookup table defining the possible groups, and allocate the customer to the appropriate group.

 

[MSherfey]

It's very possible. We're talking about psychographic marketing groups.

For example:

• GroupA contains all the customers who have spent 10K or more in a year, but have not purchased maintenance or a warranty. The high-rolling risk takers.
• GroupB contains the customers who buy all your products regardless of manitenance or warranty, they just like what you sell. The faithful.
• GroupC contains customers who work for non-profit organizations and schools. Basically the ones who want the cheapest price around and all the discounts they can have; the penny pinchers.

Each of these groups may require a different marketing plan and may have different feature requirements.

It's very possible to have a customer spend over 10K, not buy a warranty, and have all of your products while buying under a non-profit discount program...or any combination of the groups. You get the idea.

I already have all the separate queries for the various groups. Each query returns a unique set up customer IDs. Also, some of these groups should not contain duplicate data. When I find those, I can refine my queries better. I am hoping there is a way to find how they overlap without making 63 different queries by hand (ugghhh).

 

[stopher]

Good point about the values. You can usee the binary multiples again 1,2,4,8 etc like they do for encoding attritbutes of functions.

Anyway, I was able to do a report listing and counting the customers with just two queries. Maybe I'm missing the point of your question.

See my attached example.

Take a look at the report v the data in tblData

Chris

 

[gemma-the-husky]

but what i mean, is that these groups are non-related pieces of information.

a market sector is a completely different datum from a sales volume.

--------
so if you have 6 different sector types then to get all possible values you need more than 64 groups (2^6).


I think this is going to be not possible with a single query.

One way would be to provide a form with 6 drop-downs to allow the users to select the settings they want for the indivdual filters. Then have a function that tests the actual settings against the required settings, to determine whether ot include the row or not.

At some point the easiest way might be to dump all the data into excel, and let the users analyse it as they want.

 

[MSherfey]

Good point about the values. You can usee the binary multiples again 1,2,4,8 etc like they do for encoding attritbutes of functions.

Anyway, I was able to do a report listing and counting the customers with just two queries. Maybe I'm missing the point of your question.

See my attached example.

Take a look at the report v the data in tblData

Chris

Interesting solution. Instead of having 6 dynamic queries you created a couple tables and did it in two queries. I can easily enough add a 'group' field to each of my queries, run a union query to build a main table with all the customers and their groups, then run the different queries to sum/count their values.

I think my idea of using nested loops to build a query array was too complicated. I had the array built and the variables in the loops being the query name worked as well. I was having an issue with the JOIN for non-needed queries. Your idea gets rid of that complication. I'll play with this for a bit and reply to the thread.

Great idea, thanks!

 

[stopher]

Instead of having 6 dynamic queries you created a couple tables and did it in two queries.

The tables are not essential - just convenient. You could just as easily have a CASE statement for the first table and a function for the second.
hope it works for you
Chris