Six Queries and Merg Data

[winterh]

Hi All



If I explain what I have and what I want.

Table1
Client ID DateSold Product1 Product2 Product3 Product4 Product5 Product6
1 01/05/12 A A C D D C
2 01/06/12 C C C D C A
3 01/06/12 A D D D C A
4 01/07/12 C A A C B B

What I want is a query which gives me the below answer andthen I can make a chart.

DateSold Product(A)
May 2
June 1
July 2
Total 5

I have done six queries for each product line eg Query 1shows Product1, second query shows Product2.

Any help please J

[June7]

Need to normalize data. A UNION query will accomplish. There is no wizard or designer for UNION, must type in the SQL View window.

SELECT [Client ID], DateSold, Month(DateSold) As MonthSold, Year(DateSold) As YearSold, "1" As SourceField, Product1 As ProductCode FROM tablename
UNION SELECT [Client ID], DateSold, Month(DateSold), Year(DateSold), "2", Product2 FROM tablename
UNION SELECT [Client ID], DateSold, Month(DateSold), Year(DateSold), "3", Product3 FROM tablename
UNION SELECT [Client ID], DateSold, Month(DateSold), Year(DateSold), "4", Product4 FROM tablename
UNION SELECT [Client ID], DateSold, Month(DateSold), Year(DateSold), "5", Product5 FROM tablename
UNION SELECT [Client ID], DateSold, Month(DateSold), Year(DateSold), "6", Product6 FROM tablename;

Now use the UNION query as data source for graphing - do aggregate (GROUP BY) query, apply criteria to filter by ProductCode, rotate with crosstab to get ProductCode headings and count data.