J
JP
Hi. I am trying to sort two sets of related data by month
and year. I have been able to get two seperate queries to
total the data by month and year. Now I am trying to get
the two queries to join together.
Summary of query1 (works)(Select query):
Group sales information by product, total sales, and sales
qty by month. grouped by Jan 2003, Feb 2003, etc.
Summary of query2 (works)(Crosstab Query):
Group warranty information for each product in a crosstab
query by current status (yes, no, and ng). grouped by Jan
2003, Feb 2003, etc.
The raw date information for each query is entered in a
short date format. (1/1/03)
Summary of query3 (fails):
When I try to join the two queries in the Relationships
screen and rerun the new summary query3, it comes up
blank. I've noticed that after I use the Format()
function, the date is translated into a TEXT format.
Could this be my problem? Joining the Date entries and
displaying both comes up empty.
I'd like the query3, combining 1 and 2, to summarize the
sales and warranty data (from 1 and 2) by month/year, and
by warranty status (from a ColumnHeading controlled
crosstab column).
Any help would be much appreciated.
Here is the SQL for each Query
As you'll see, I'm specifying the date range in a user
input on the form - Warranty vs. Sales From.
Query1-
SELECT (Format$([SalesTbl].[Sale Date],"mmm yyyy")) AS
SDate, [Product by Kno Query].[Product Field], Sum
(SalesTbl.[Sales QTY]) AS [SumOfSales QTY], Sum
(SalesTbl.Subtotal) AS SumOfSubtotal
FROM [Product by Kno Query] INNER JOIN SalesTbl ON
[Product by Kno Query].[K-NO Field] = SalesTbl.[K No]
GROUP BY (Format$([SalesTbl].[Sale Date],"mmm yyyy")),
[Product by Kno Query].[Product Field], SalesTbl.[Sale
Date]
HAVING (((SalesTbl.[Sale Date]) Between [Forms]![Warranty
vs Sales Form]![from1] And [Forms]![Warranty vs Sales
Form]![thru1]))
ORDER BY [Product by Kno Query].[Product Field]
WITH OWNERACCESS OPTION;
Query2-
TRANSFORM Count(CA_TBL.[CA OK]) AS [CountOfCA OK1]
SELECT (Format$([WarTBL].[Charge Date],"mmm yyyy")) AS
CDate, WarTBL.Product, Count(CA_TBL.[CA OK]) AS [CountOfCA
OK]
FROM CA_TBL INNER JOIN WarTBL ON CA_TBL.[Warranty No] =
WarTBL.[Warranty No]
WHERE (((WarTBL.[Charge Date]) Between [Forms]![Warranty
vs Sales Form]![from1] And [Forms]![Warranty vs Sales
Form]![thru1]))
GROUP BY (Format$([WarTBL].[Charge Date],"mmm yyyy")),
WarTBL.Product
ORDER BY WarTBL.Product
PIVOT CA_TBL.[CA OK] In ("?","CA-NG","No","Yes")
WITH OWNERACCESS OPTION;
and year. I have been able to get two seperate queries to
total the data by month and year. Now I am trying to get
the two queries to join together.
Summary of query1 (works)(Select query):
Group sales information by product, total sales, and sales
qty by month. grouped by Jan 2003, Feb 2003, etc.
Summary of query2 (works)(Crosstab Query):
Group warranty information for each product in a crosstab
query by current status (yes, no, and ng). grouped by Jan
2003, Feb 2003, etc.
The raw date information for each query is entered in a
short date format. (1/1/03)
Summary of query3 (fails):
When I try to join the two queries in the Relationships
screen and rerun the new summary query3, it comes up
blank. I've noticed that after I use the Format()
function, the date is translated into a TEXT format.
Could this be my problem? Joining the Date entries and
displaying both comes up empty.
I'd like the query3, combining 1 and 2, to summarize the
sales and warranty data (from 1 and 2) by month/year, and
by warranty status (from a ColumnHeading controlled
crosstab column).
Any help would be much appreciated.
Here is the SQL for each Query
As you'll see, I'm specifying the date range in a user
input on the form - Warranty vs. Sales From.
Query1-
SELECT (Format$([SalesTbl].[Sale Date],"mmm yyyy")) AS
SDate, [Product by Kno Query].[Product Field], Sum
(SalesTbl.[Sales QTY]) AS [SumOfSales QTY], Sum
(SalesTbl.Subtotal) AS SumOfSubtotal
FROM [Product by Kno Query] INNER JOIN SalesTbl ON
[Product by Kno Query].[K-NO Field] = SalesTbl.[K No]
GROUP BY (Format$([SalesTbl].[Sale Date],"mmm yyyy")),
[Product by Kno Query].[Product Field], SalesTbl.[Sale
Date]
HAVING (((SalesTbl.[Sale Date]) Between [Forms]![Warranty
vs Sales Form]![from1] And [Forms]![Warranty vs Sales
Form]![thru1]))
ORDER BY [Product by Kno Query].[Product Field]
WITH OWNERACCESS OPTION;
Query2-
TRANSFORM Count(CA_TBL.[CA OK]) AS [CountOfCA OK1]
SELECT (Format$([WarTBL].[Charge Date],"mmm yyyy")) AS
CDate, WarTBL.Product, Count(CA_TBL.[CA OK]) AS [CountOfCA
OK]
FROM CA_TBL INNER JOIN WarTBL ON CA_TBL.[Warranty No] =
WarTBL.[Warranty No]
WHERE (((WarTBL.[Charge Date]) Between [Forms]![Warranty
vs Sales Form]![from1] And [Forms]![Warranty vs Sales
Form]![thru1]))
GROUP BY (Format$([WarTBL].[Charge Date],"mmm yyyy")),
WarTBL.Product
ORDER BY WarTBL.Product
PIVOT CA_TBL.[CA OK] In ("?","CA-NG","No","Yes")
WITH OWNERACCESS OPTION;