Report based on two similar queries

[Molly]

I need to get a report that compares inventory production
based on a per head potential bases( ie. 20 doses *
(total#of boars)) and the actual sales. I need subtotals
for each Stud as well as overall totals.

I have a report for one query with a subreport containing
the other query but can not get the overall report to
total the sum all Studs from the subreport and use it in
a calculation in the main report. I get the totals from
the last stud in the subreport list.

I've included the SQL's for more insight. These are the
two queries and you will note that the one breed is named
slightly different in the crosstab table queries.

PARAMETERS [forms]![report dialog]![textendingdate]
DateTime;
TRANSFORM Count([TBL-Boar Inventory].[BoarEntry#])
AS [CountOfBoarEntry#]
SELECT [TBL-Boar Inventory].BoarStudID, [TBL-AI
Centers].Company, Count([TBL-Boar Inventory].
[BoarEntry#])
AS [Total # of Boars], Sum([TBL-Boar
Inventory].InventoryValue) AS [Total Inventory $]
FROM [TBL-AI Centers]
INNER JOIN [TBL-Boar Inventory]
ON [TBL-AI Centers].BoarStudID = [TBL-Boar
Inventory].BoarStudID
WHERE ((([TBL-Boar Inventory].EntryDate)<=[Forms]![Report
Dialog]![TextEndingdate])
AND (([TBL-Boar Inventory].ExitDate) Is Null Or ([TBL-
Boar Inventory].ExitDate)>[Forms]![Report Dialog]!
[TextEndingdate]))
GROUP BY [TBL-Boar Inventory].BoarStudID, [TBL-AI
Centers].Company
PIVOT [TBL-Boar Inventory].BreedID In
("HT2","HT5","HT87","JP","V10","V22","V30","V300","V40","V
600","V700","V80");


PARAMETERS [forms]![report dialog]![textbeginningdate]
DateTime, [forms]![report dialog]![textendingdate]
DateTime;
TRANSFORM Sum([TBL-Order Details].Quantity) AS
SumOfQuantity
SELECT [TBL-AI Centers].Company, Sum([TBL-Order
Details].Quantity*[UnitPrice])
AS [Sales Total], Sum([TBL-Order Details].Quantity) AS
[Total Doses]
FROM [TBL-Products] INNER JOIN (([TBL-Customers]
INNER JOIN ([TBL-AI Centers] INNER JOIN [TBL-Orders] ON
[TBL-AI Centers].BoarStudID=[TBL-Orders].AICenter)
ON [TBL-Customers].CustomerID=[TBL-Orders].CustomerID)
INNER JOIN [TBL-Order Details]
ON [TBL-Orders].OrderID=[TBL-Order Details].OrderID)
ON [TBL-Products].ProductID=[TBL-Order Details].ProductID
WHERE ((([TBL-Products].Category)="Semen") And (([TBL-
Orders].ShipDate) Between forms![report dialog]!
textbeginningdate And forms![report dialog]!
textendingdate))
GROUP BY [TBL-AI Centers].Company
ORDER BY [TBL-AI Centers].Company
PIVOT [TBL-Order Details].ProductID In
("HT2","HT5","HT8700","JP","V10","V22","V30","V300","V40",
"V600","V700","V80");

Any possible ideas?

 

[Les]

Molly,
Try looking at "sub-report totalling", posted by
Howard, under Reports & Printing. I think it was on July
19th. Marshall Barton, one of the mvp's answered this
question or something very similar.

-----Original Message-----
I need to get a report that compares inventory production
based on a per head potential bases( ie. 20 doses *
(total#of boars)) and the actual sales. I need subtotals
for each Stud as well as overall totals.

I have a report for one query with a subreport containing
the other query but can not get the overall report to
total the sum all Studs from the subreport and use it in
a calculation in the main report. I get the totals from
the last stud in the subreport list.

I've included the SQL's for more insight. These are the
two queries and you will note that the one breed is named
slightly different in the crosstab table queries.

PARAMETERS [forms]![report dialog]![textendingdate]
DateTime;
TRANSFORM Count([TBL-Boar Inventory].[BoarEntry#])
AS [CountOfBoarEntry#]
SELECT [TBL-Boar Inventory].BoarStudID, [TBL-AI
Centers].Company, Count([TBL-Boar Inventory].
[BoarEntry#])
AS [Total # of Boars], Sum([TBL-Boar
Inventory].InventoryValue) AS [Total Inventory $]
FROM [TBL-AI Centers]
INNER JOIN [TBL-Boar Inventory]
ON [TBL-AI Centers].BoarStudID = [TBL-Boar
Inventory].BoarStudID
WHERE ((([TBL-Boar Inventory].EntryDate)<=[Forms]![Report
Dialog]![TextEndingdate])
AND (([TBL-Boar Inventory].ExitDate) Is Null Or ([TBL-
Boar Inventory].ExitDate)>[Forms]![Report Dialog]!
[TextEndingdate]))
GROUP BY [TBL-Boar Inventory].BoarStudID, [TBL-AI
Centers].Company
PIVOT [TBL-Boar Inventory].BreedID In
("HT2","HT5","HT87","JP","V10","V22","V30","V300","V40","V
600","V700","V80");


PARAMETERS [forms]![report dialog]![textbeginningdate]
DateTime, [forms]![report dialog]![textendingdate]
DateTime;
TRANSFORM Sum([TBL-Order Details].Quantity) AS
SumOfQuantity
SELECT [TBL-AI Centers].Company, Sum([TBL-Order
Details].Quantity*[UnitPrice])
AS [Sales Total], Sum([TBL-Order Details].Quantity) AS
[Total Doses]
FROM [TBL-Products] INNER JOIN (([TBL-Customers]
INNER JOIN ([TBL-AI Centers] INNER JOIN [TBL-Orders] ON
[TBL-AI Centers].BoarStudID=[TBL-Orders].AICenter)
ON [TBL-Customers].CustomerID=[TBL-Orders].CustomerID)
INNER JOIN [TBL-Order Details]
ON [TBL-Orders].OrderID=[TBL-Order Details].OrderID)
ON [TBL-Products].ProductID=[TBL-Order Details].ProductID
WHERE ((([TBL-Products].Category)="Semen") And (([TBL-
Orders].ShipDate) Between forms![report dialog]!
textbeginningdate And forms![report dialog]!
textendingdate))
GROUP BY [TBL-AI Centers].Company
ORDER BY [TBL-AI Centers].Company
PIVOT [TBL-Order Details].ProductID In
("HT2","HT5","HT8700","JP","V10","V22","V30","V300","V40",
"V600","V700","V80");

Any possible ideas?


.