asked on

union query alternative

Hi Experts!

I have a crosstab with the following data - customer id as the row, department as the column, customer count as the value. I need to determine the total count using two departments. for example, i need to get the total count of both the shoppers from men's and women's, then men's and boys, then men's and girls and a lot more.

the thing here is i have a union query but it it is taking a while. i am interested if there could be another solution for this.

here's a sample of my union query

SELECT "*Total Distinct" AS Department, COUNT (*) AS Count
FROM qryXtab AS q
UNION 
SELECT "Women's and Men's", COUNT (*)
FROM qryXtab AS q
WHERE q.[women's] <> 0 AND q.[men's] <> 0;
UNION 
SELECT "Women's and Young Men's", COUNT (*)
FROM qryXtab AS q
WHERE q.[women's] <> 0 AND q.[young men's] <> 0;
UNION 
SELECT "Women's and Juniors'", COUNT (*)
FROM qryXtab AS q
WHERE q.[women's] <> 0 AND q.[juniors'] <> 0;

Open in new window

Luke Chung

Use a SELECT query on the CROSSTAB query to display the fields you want and create calculated fields on the fields you want to add together.

GRayL

Can you show us a few records from the cross tab query? I'm having a hard time 'visualizing' what you are trying to do. From what I can see, all the COUNT (*) will give you in your second query is the number of records that two different departments were not zero, not the number of customers in both departments. It would also help if you could post the cross tab query?

Jarrod

try this:

SELECT [Department], [Count]
FROM 
   (
		SELECT 
			sum (1) as [*Total Distinct],
			sum (case when [women's] <> 0 AND [men's] <> 0 then 1 else 0 end) as [Women's and Men's],
			sum (case when [women's] <> 0 AND [young men's] <> 0 then 1 else 0 end) as [Women's and Young Men's],
			sum (case when [women's] <> 0 AND [juniors'] <> 0 then 1 else 0 end) as [Women's and Juniors']		
		FROM
			qryXtab
 ) t
UNPIVOT
   ([Count] FOR [Department] IN 
      ( [*Total Distinct], [Women's and Men's], [Women's and Young Men's], [Women's and Juniors'])
)AS unpvt
order by [Department]

Open in new window

j2jake

ASKER

Hi GRayL

Here is a sample of the crosstab result:

customer | total of cid_count | Boys | Girls | Women's | Men's |
233 | 4 | 1 | 1 | 1 | 1 |
6314 | 2 | 1 | | 1 | |
8365 | 1 | | | 1 | |

I have attached the crosstab query too. I have 2 tables, tblDept and tblTrail. The nested sql will get all the data in tblTrail and add the dept that corresponds to the category code. Then the crosstab query will run through that data. this was first conceptualized in excel. i created a pivot table from my data having the dept as the column headers and the customer_id as the rows then did a sumproduct. the problem with excel is that i have more than 1M rows so i have to use access.

Hi zadeveloper,

Isnt UNPIVOT only in SQL Server. My application is in ACCESS. Any suggestions?

Hi Luke,

still a newbie here. can you suggest how can i develop a better SELECT query?

TRANSFORM Count(cid_count) AS CountOfcid_count
SELECT customer_id AS customer, Count(cid_count) AS [Total Of cid_count]
FROM (SELECT t.customer_id, t.category_code AS category_code, d.department AS department, t.customer_id AS cid_count
FROM tblTrail AS t LEFT JOIN tblDept AS d ON t.category_code = d.product_category
GROUP BY t.month_year, t.customer_id, t.category_code, d.department
)  AS query
GROUP BY customer_id
PIVOT department;

Open in new window

ASKER CERTIFIED SOLUTION

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luke Chung

Isn't this just running the crosstab query and adding up a few of the columns into new columns? If so, this should be simply be a select query on the crosstab query. Really simple.

j2jake

ASKER

Hi GRayL,

Awesome! It worked a lot faster. Thanks!

Hi LukeChung-FMS,

Can you please give me an example?

Luke Chung

Not sure what additional information you need. Are you familiar with creating a select query on a table and creating a calculated field by adding two fields?
If so, instead of using a table as the query's datasource, use your crosstab query and do the same thing.
Here's a paper I've written covering lots of query basics and tips: http://www.fmsinc.com/tpapers/queries/index.html

j2jake

ASKER

Thanks!

j2jake

ASKER

Thanks everyone!

GRayL

Thanks, glad to help