Putting a query into another query... trivial right?

[Susy]

Hi there,

I have these two queries:

Main query: "qryProdSize"

SELECT tblReportProduct.ReportID, tblReport.ActionDate, tblReportProduct.ProdSizeFROM tblReport INNER JOIN tblReportProduct ON tblReport.ReportID = tblReportProduct.ReportID
WHERE (((tblReport.ActionDate) Between [Date1] And [Date2]));

and Subquery: qryProdTotalSize

SELECT qryProdSize.ProdSize, Count(qryProdSize.ProdSize) AS CountOfProdSize
FROM qryProdSize
GROUP BY qryProdSize.ProdSize;

Do I just need the replace the word "qryProdSize" for the whole main query everytime?


Regards!

[aytee111]

Not sure what you are asking - "every time" what?

This can be done in one query.

[Susy]

I just want the query to give me the count of the products by product category in a specific period of time.

The first query (main query) gather all the values from the reports between the dates i specified.... but since some reports have same date, they appear several time, since the reportid is also there.

Second query aggregates all the reports by product category and give its count... within the period of time specified...


I wanted to create only one query which does that!, but I still cannot.

by the way, there are two tables in play: tblreportproduct and tblreport

The tblreportproduct contains all the transactions from a report... so a report can have many different products

the tblreport contains the ActivityDate ---(the form Report contains the subform ReportProduct)

Both tables are linked via the ReportID.

[aytee111]

Please use the same names from your first post - there are no fields call Product or Product Category, so I am confused!

I just want the query to give me the count of the products by product category in a specific period of time.

Group by Product category
Where period of time
Count records

Or if you need a prior query which first gets all unique product records, then use this query to count the records.

[Susy]

Sorry, I mixed them.. but it is the same idea...


main query: "qryProdCategory"

SELECT tblReportProduct.ProdCategoryFROM tblReport INNER JOIN tblReportProduct ON tblReport.ReportID = tblReportProduct.ReportID
GROUP BY tblReportProduct.ProdCategory, tblReport.ActionDate, tblReportProduct.ReportID
HAVING (((tblReport.ActionDate) Between [Date1] And [Date2]));

subquery: "qryProdTotalCategory" --- which gives me what I need..

SELECT qryProdCategory.ProdCategory, Count(qryProdCategory.ProdCategory) AS CountOfProdCategoryFROM qryProdCategory
GROUP BY qryProdCategory.ProdCategory;

but how can I create one query...???

[aytee111]

The query on the right: the ActionDate is "Where" and the ReportID is "Count".

This will count every record - is this what you want? You say that the Action Date is duplicated on some records, do you want to count it only once or once for every record? Which would give you:
Count how many times the product category and action date combination appear - is that it?

[Susy]

Hi aytee111,


thanks for your interest.

This is so good... Sometimes talking to you all help to understand what ones task is... I think I was doing something else.

I want to add the qty by product category sold in a specific period of time.

Voila!

Thanks