Query to return 4 records per table record

[Luke Bailey]

I have a table storing customers (using a 5-digit code as key) and a field
called ORDERS_CAT which stores A,B,C,D,E depending on category of the order.
Then another field called ORDERS_COUNT showing total count of the order.
There are many other fields, but these are the ones pertinent here. What I
want in the results is all 5-digit codes with each of the categories and a
count. But if there were no orders (for example) in ORDERS_CAT of B,C,D for
a 5-digit customer, the 5 digit customer only has 2 records in query. How do
I force a query to have 5 records in the query showing zeros for those
records with no entries?

Hope this makes sense.
And thanks for any ideas!

 

[John W. Vinson]

I have a table storing customers (using a 5-digit code as key) and a field
called ORDERS_CAT which stores A,B,C,D,E depending on category of the order.
Then another field called ORDERS_COUNT showing total count of the order.
There are many other fields, but these are the ones pertinent here. What I
want in the results is all 5-digit codes with each of the categories and a
count. But if there were no orders (for example) in ORDERS_CAT of B,C,D for
a 5-digit customer, the 5 digit customer only has 2 records in query. How do
I force a query to have 5 records in the query showing zeros for those
records with no entries?

Hope this makes sense.
And thanks for any ideas!

One way would be to create an OrderCat table with one field and five rows,
with A, B, C, D, and E in the field. Join this table to your customer table,
using a Left Outer Join - select the join row and choose option 2 (or 3),
"Show all records in OrderCat and matching records in <your table>". This will
return NULL for all records in your table if there is no order of that
category; a totals query summing these will give zero, or you can use the NZ()
function to convert the null to a zero.

John W. Vinson [MVP]

 

[Luke Bailey]

In this case, that does not return the values I am looking for.
I have in my ORDERS_CAT values like:

CUST_ID CAT ORDERS
12345 A 3
12345 D 4
12345 C 9

So customer 12345 has NO records for B and E so a select query returns only
the 3 records for this customer. I would like the query to return 5 records
showing CAT B and E as 0. A crosstab view does not work as I need to have
the 5 records.

Thanks for any ideas!

 

[John W. Vinson]

In this case, that does not return the values I am looking for.
I have in my ORDERS_CAT values like:

CUST_ID CAT ORDERS
12345 A 3
12345 D 4
12345 C 9

So customer 12345 has NO records for B and E so a select query returns only
the 3 records for this customer. I would like the query to return 5 records
showing CAT B and E as 0. A crosstab view does not work as I need to have
the 5 records.

That's exactly what my query will do, if you use the Left Join:

SELECT OrderCat.Cat, Orders.Cust_ID, Orders.Orders
FROM OrderCat LEFT JOIN Orders
ON OrderCat.Cat = Orders.Cat;

The Left Join will return five records, regardless of what's in your orders
table.

John W. Vinson [MVP]

 

[Luke Bailey]

Thanks John. Okay, YES, I see what you are saying. In the example I
provided I only had one customer and with a left join query, you get 5
records returned. The problem I am having is that I have MULTIPLE customers
in the table and need 5 records returned for each customer.


CUST_ID CAT ORDERS
12345 A 3
12345 D 4
12345 C 9
99999 B 1
99999 E 15

If you add 2nd customer with a couple of records and run the query as you
have shown, it will return 5 records. The results dataset I am looking for
is 5 records for each Customer.

 

[Luke Bailey]

Okay, I actually found a work-around for this, though it seems a BIT more
tedious than should need be. I'll put my solution below in case anyone else
runs into the same problem. I wrote a short peice of code that does the
following:

1) Make-table query runs with a GROUP BY on CUST_ID, creating a summary
table with unique CUST_ID's.
2) short peice of code runs with a loop 5 times (A,B,C,D,&E) doing a RUNSQL
statement, using the unique CUST_ID with a different CAT each time (doing an
INSERT statement). So it creates a table with all CUST_ID's with each CAT.
3) Bounce this new table against original table with left-join using NZ
statement to return a 0 for each null record for order.

This works fine and runs pretty fast. However, for future reference, if
anyone has a different and better solution, it would be greatly appreciated.
I'm guessing there has to be an easier way to do this through queries only.

Thanks again John for your ideas.