Unique Values in Crosstab.....Please

[MSherfey]

I am pulling my hair out on this one. How do you get unique values in a crosstab query? I've tried using DISTINCT and DISTINCTROW to no avail. I always get record count, and not unique.

In short, I have a table with about 30 fields ranging from CustomerID, CustomerSize, Geo, etc.....and of course product purchased, $.

What I want to do is create a crosstab query where the rows are CustomerSize, and Geo, the column is based on FY, and the value is a UNIQUE COUNT of the Customer IDs. Instead what I get is the number of records for that customer within those guidelines. Since many customers purchase throuought the year, this obviously throws the counts way off. Now I have to export to Excel and run a different function on an array to get unique values. Some of the tables are greater than 100K records and this takes some time.

Why am I having so much trouble with this?

2006 2007 2008
Large NA 5 8 10
Large EMEA 4 6 3
Large APAC 7 8 23
Medium NA 0 5 3
Medium EMEA 3 2 7
Medium APAC 4 6 8
etc...

Any help would be greatly appreciated and I will sing your praises to everyone whether they want to hear them or not!!!

 

[boblarson]

How about first creating a query with a unique count and using that as the basis of your crosstab?

 

[MSherfey]

I did that, but when I have to link it back to the main table to get the other information (Geo and Size), it gives me all the records which match the Customer ID by Geo and Size. The Crosstab just counts those and they're not unique.

Thanks though!

 

[boblarson]

Are you remembering to link all of the same named fields?

 

[ByteMyzer]

You might try the in-line subquery approach, like the following:

TRANSFORM Count(MyTable.MyFieldToCount)
SELECT MyTable.MyRowField
FROM (
      SELECT DISTINCT MyTable.MyRowField,
                      MyTable.MyColumnField,
                      MyTable.MyFieldToCount
      FROM MyTable
     ) AS MyTable
GROUP BY MyTable.MyRowField
PIVOT MyTable.MyColumnField;

 

[MSherfey]

boblarson - I'm only linking the CustomerID fields. The unique query only returns the grouped CustomerIDs to get a unique count. Once I add other fields to the query, I get duplicate CustomerIDs (depending on the fields of course).

ByteMyzer - I'll try your code. I don't get it right away, but I'm sure I can gifure it out. Thanks!!

 

[MSherfey]

This is killing me. Here's my two queries - very simple.

Query 1 gives me the unique count of customers because the 'group by' is working. It returns 644 unique 'large' customers in 'americas' in 2001.

SELECT DISTINCTROW [Main Invoice Data].[EndUser OCN:], [Main Invoice Data].[Geo1:], [Main Invoice Data].[Customer Size Segment:], [Main Invoice Data].[Invoice Fiscal Year:]
FROM [Item Number Info] INNER JOIN [Main Invoice Data] ON [Item Number Info].ITEM_NUM = [Main Invoice Data].[Item Number:]
WHERE (((([Item Number Info].BLUE_DESC_4) Like "*Desktop*" Or ([Item Number Info].BLUE_DESC_4) Like "*Suite*" Or ([Item Number Info].BLUE_DESC_4) Like "*Bun*" Or ([Item Number Info].BLUE_DESC_4) Like "*Configuration*") And (([Item Number Info].BLUE_DESC_4) Like "*Desktop*" Or ([Item Number Info].BLUE_DESC_4) Like "*Suite*" Or ([Item Number Info].BLUE_DESC_4) Like "*Bun*" Or ([Item Number Info].BLUE_DESC_4) Like "*Configuration*")) AND (([Main Invoice Data].[Invoice Amt USD])<>0))
GROUP BY [Main Invoice Data].[EndUser OCN:], [Main Invoice Data].[Geo1:], [Main Invoice Data].[Customer Size Segment:], [Main Invoice Data].[Invoice Fiscal Year:]
HAVING ((([Main Invoice Data].[Geo1:])="Americas") AND (([Main Invoice Data].[Customer Size Segment:])="Large Enterprise") AND (([Main Invoice Data].[Invoice Fiscal Year:])=2001))
ORDER BY [Main Invoice Data].[EndUser OCN:];

Query 2 gives me the record count for the category. I've tried to add 'DISTINCT' and 'DISTINCTROW' after the 'SELECT" command but it doesn't change the numbers at all. It returns a count of 1617 'large' customers in americas for the same year as query 1.

TRANSFORM Count([Main Invoice Data].[EndUser OCN:]) AS [CountOfEndUser OCN:]
SELECT DISTINCTROW [Main Invoice Data].[Geo1:], [Main Invoice Data].[Customer Size Segment:]
FROM [Item Number Info] INNER JOIN [Main Invoice Data] ON [Item Number Info].ITEM_NUM = [Main Invoice Data].[Item Number:]
WHERE (((([Item Number Info].BLUE_DESC_4) Like "*Desktop*" Or ([Item Number Info].BLUE_DESC_4) Like "*Suite*" Or ([Item Number Info].BLUE_DESC_4) Like "*Bun*" Or ([Item Number Info].BLUE_DESC_4) Like "*Configuration*") And (([Item Number Info].BLUE_DESC_4) Like "*Desktop*" Or ([Item Number Info].BLUE_DESC_4) Like "*Suite*" Or ([Item Number Info].BLUE_DESC_4) Like "*Bun*" Or ([Item Number Info].BLUE_DESC_4) Like "*Configuration*")) AND (([Main Invoice Data].[Invoice Amt USD])<>0))
GROUP BY [Main Invoice Data].[Geo1:], [Main Invoice Data].[Customer Size Segment:]
PIVOT [Main Invoice Data].[Invoice Fiscal Year:];

Arggghhhhhhh!

 

[ByteMyzer]

Try:

TRANSFORM Count(T1.[EndUser OCN:]) AS [CountOfEndUser OCN:]
SELECT T1.[Geo1:], T1.[Customer Size Segment:]
FROM (
      SELECT DISTINCT  [Main Invoice Data].[Geo1:],
                       [Main Invoice Data].[Customer Size Segment:],
                       [Main Invoice Data].[Invoice Fiscal Year:]
                       [Main Invoice Data].[EndUser OCN:]
                 FROM  [Item Number Info]
           INNER JOIN  [Main Invoice Data]
                   ON  [Item Number Info].ITEM_NUM
                     = [Main Invoice Data].[Item Number:]
                WHERE  [Main Invoice Data].[Invoice Amt USD]<>0
                  AND ([Item Number Info].BLUE_DESC_4 Like "*Desktop*" Or
                       [Item Number Info].BLUE_DESC_4 Like "*Suite*" Or
                       [Item Number Info].BLUE_DESC_4 Like "*Bun*" Or
                       [Item Number Info].BLUE_DESC_4 Like "*Configuration*"
                      )
     ) AS T1
GROUP BY T1.[Geo1:], T1.[Customer Size Segment:]
PIVOT T1.[Invoice Fiscal Year:];

 

[MSherfey]

ByteMyzer,
This worked!!! But I don't understand how I'll play around with it and see if I can figure it out. I'll need to duplicate it for later queries of course.


Thank you again!! This has been killing me lately!

 

[ByteMyzer]

You're very welcome, I'm glad it worked for you

 

[ByteMyzer]

Incidentally, this was accomplished by creating a TRANSFORM Query around an in-line subquery. The same thing could also be accomplished by two separate queries. For example:

Query1:

SELECT DISTINCT  [Main Invoice Data].[Geo1:],
                 [Main Invoice Data].[Customer Size Segment:],
                 [Main Invoice Data].[Invoice Fiscal Year:]
                 [Main Invoice Data].[EndUser OCN:]
           FROM  [Item Number Info]
     INNER JOIN  [Main Invoice Data]
             ON  [Item Number Info].ITEM_NUM
               = [Main Invoice Data].[Item Number:]
          WHERE  [Main Invoice Data].[Invoice Amt USD]<>0
            AND ([Item Number Info].BLUE_DESC_4 Like "*Desktop*" Or
                 [Item Number Info].BLUE_DESC_4 Like "*Suite*" Or
                 [Item Number Info].BLUE_DESC_4 Like "*Bun*" Or
                 [Item Number Info].BLUE_DESC_4 Like "*Configuration*"
                );

Query2:

TRANSFORM Count(Query1.[EndUser OCN:]) AS [CountOfEndUser OCN:]
SELECT Query1.[Geo1:], Query1.[Customer Size Segment:]
FROM Query1
GROUP BY Query1.[Geo1:], Query1.[Customer Size Segment:]
PIVOT Query1.[Invoice Fiscal Year:];

 

[MSherfey]

That's actually very interesting. I like how you broke it up into two separate parts. It makes it much easier for me to figure out what is happening. Very cool. Thanks!

I went ahead and bought two different books today which I hope will help.
1) Microsoft Access 2007 Data Analysis, by Michael Alexander
2) Microsoft Excel and Access Integrations..... , by Michael Alexander

I'm hoping these will help me understand some of the techniques I need. I've been doing too much manual work to get the same data you pulled in a single query. I knew I was making more work, but had no idea how much!

Thank you again for the help!

Now I'm off to try and figure out how to add a second value (revenue) to the same query. Basically, how much $ does wach of those unique values represent?