Combining 3 queries

L

Lissa

I am resubmitting this question since I somehow managed to delete it off the
discussion group...

I have 3 queries that I want to use for retrieving the Beginning Balance of
inventory up to a certain date, retrieving all Sales between two dates, and
retrieving all Purchases between two dates. All these need to come together
on one report, but list everything even if one or two of the queries return a
null value. I've listed all 3 queries plus the 4th query that joins the other
3. The 4th query works but only pulls information where Queries #2 & #3 match
Product Codes. I've tried using Nz in the original #2 & #3 queries, but I
still get the same results. Can someone help me?

The queries are as follows:

*** QUERY #1 (BegInv) ***
SELECT Products.[Product Code],
Products.[Product Name],
[4/1/06 Beginning Balance]+[Prior Purchases]-[Prior Sales] AS [Beginning
Balance],
CCur(Sum([4/1/06 Beginning Balance]*[Products].[Unit Price])+
(Sum([Inventory Prior Purchases].[Prior Purchase Value])-(Sum([Inventory
Prior Sales].[Prior Sales Value]))))
AS [Beginning Value],
Products.[4/1/06 Beginning Balance],
[Inventory Prior Purchases].[Prior Purchases],
[Inventory Prior Purchases].[Prior Purchase Value],
[Inventory Prior Sales].[Prior Sales],
[Inventory Prior Sales].[Prior Sales Value]
FROM [Inventory Prior Sales]
INNER JOIN ([Inventory Prior Purchases]
INNER JOIN Products
ON [Inventory Prior Purchases].[Product Code]=Products.[Product Code])
ON [Inventory Prior Sales].[Product Code]=Products.[Product Code]
GROUP BY Products.[Product Code],
Products.[Product Name],
Products.[4/1/06 Beginning Balance],
[Inventory Prior Purchases].[Prior Purchases],
[Inventory Prior Purchases].[Prior Purchase Value],
[Inventory Prior Sales].[Prior Sales],
[Inventory Prior Sales].[Prior Sales Value]
ORDER BY Products.[Product Name];

*** QUERY #2 (Sales) ***
SELECT Products.[Product Code],
Products.[Product Name],
Sum([Order Details by Customer Query].Quantity) AS Sales,
[Order Details by Customer Query].[Unit Price],
CCur(Sum([Order Details by Customer Query].[Quantity]*[Order Details by
Customer Query].[Unit Price]))
AS [Sales Value],
Format([Order Details by Customer Query].[Order Date],"mmmm yyyy") AS
[Month]
FROM [Order Details by Customer Query]
RIGHT JOIN Products ON [Order Details by Customer Query].[Product ID] =
Products.ID
WHERE ((([Order Details by Customer Query].[Order Date])
Between [Enter Start Date (mm/dd/yyyy)] And [Enter End Date (mm/dd/yyyy)]))
GROUP BY Products.[Product Code],
Products.[Product Name],
[Order Details by Customer Query].[Unit Price],
Format([Order Details by Customer Query].[Order Date],"mmmm yyyy")
ORDER BY Products.[Product Name];

*** QUERY #3 (Purchases) ***
SELECT Products.[Product Code],
Products.[Product Name],
Sum([Purchase Order Details].Quantity) AS Purchases,
[Purchase Order Details].[Unit Price],
CCur(Sum([Purchase Order Details].[Quantity]*[Purchase Order
Details].[Unit Price])) AS [Purchase Value],
Format([Date Received],"mmmm yyyy") AS [Month]
FROM Products
LEFT JOIN [Purchase Order Details] ON Products.ID = [Purchase Order
Details].[Product ID]
WHERE ((([Purchase Order Details].[Date Received])
Between [Enter Start Date (mm/dd/yyyy)] And [Enter End Date
(mm/dd/yyyy)]))
GROUP BY Products.[Product Code],
Products.[Product Name],
[Purchase Order Details].[Unit Price],
Format([Date Received],"mmmm yyyy")
ORDER BY Products.[Product Name];

*** QUERY #4 (Combines all 3 other queries) ***
SELECT Products.[Product Code],
Products.[Product Name],
Sum(Nz([Product Sales by Month].[Sales],0)) AS Sales,
CCur(Sum([Product Sales by Month].[Sales]*[Product Sales by Month].[Unit
Price])) AS [Sales Value],
Sum(Nz([Product Purchases by Month].[Purchases],0)) AS Purchases,
CCur(Sum([Product Purchases by Month].[Purchases]*[Product Purchases by
Month].[Unit Price]))
AS [Purchase Value],
[Product Sales by Month].Month
FROM [Product Sales by Month]
INNER JOIN ([Product Purchases by Month]
INNER JOIN Products
ON [Product Purchases by Month].[Product Code] = Products.[Product Code])
ON ([Product Sales by Month].[Product Code] = Products.[Product Code])
AND ([Product Sales by Month].[Product Code] = Products.[Product Code])
GROUP BY Products.[Product Code],
Products.[Product Name],
[Product Sales by Month].Month
ORDER BY Products.[Product Name];
 
K

KARL DEWEY

I did not look over everything but in query 4 use left join instead of inner
join. The left of the join needs to be the side that will have records. If
for some reason there are a mixture or records on one side and then the other
then you need to create union query to build a complete listing for the left
side of a join.
--
KARL DEWEY
Build a little - Test a little


Lissa said:
I am resubmitting this question since I somehow managed to delete it off the
discussion group...

I have 3 queries that I want to use for retrieving the Beginning Balance of
inventory up to a certain date, retrieving all Sales between two dates, and
retrieving all Purchases between two dates. All these need to come together
on one report, but list everything even if one or two of the queries return a
null value. I've listed all 3 queries plus the 4th query that joins the other
3. The 4th query works but only pulls information where Queries #2 & #3 match
Product Codes. I've tried using Nz in the original #2 & #3 queries, but I
still get the same results. Can someone help me?

The queries are as follows:

*** QUERY #1 (BegInv) ***
SELECT Products.[Product Code],
Products.[Product Name],
[4/1/06 Beginning Balance]+[Prior Purchases]-[Prior Sales] AS [Beginning
Balance],
CCur(Sum([4/1/06 Beginning Balance]*[Products].[Unit Price])+
(Sum([Inventory Prior Purchases].[Prior Purchase Value])-(Sum([Inventory
Prior Sales].[Prior Sales Value]))))
AS [Beginning Value],
Products.[4/1/06 Beginning Balance],
[Inventory Prior Purchases].[Prior Purchases],
[Inventory Prior Purchases].[Prior Purchase Value],
[Inventory Prior Sales].[Prior Sales],
[Inventory Prior Sales].[Prior Sales Value]
FROM [Inventory Prior Sales]
INNER JOIN ([Inventory Prior Purchases]
INNER JOIN Products
ON [Inventory Prior Purchases].[Product Code]=Products.[Product Code])
ON [Inventory Prior Sales].[Product Code]=Products.[Product Code]
GROUP BY Products.[Product Code],
Products.[Product Name],
Products.[4/1/06 Beginning Balance],
[Inventory Prior Purchases].[Prior Purchases],
[Inventory Prior Purchases].[Prior Purchase Value],
[Inventory Prior Sales].[Prior Sales],
[Inventory Prior Sales].[Prior Sales Value]
ORDER BY Products.[Product Name];

*** QUERY #2 (Sales) ***
SELECT Products.[Product Code],
Products.[Product Name],
Sum([Order Details by Customer Query].Quantity) AS Sales,
[Order Details by Customer Query].[Unit Price],
CCur(Sum([Order Details by Customer Query].[Quantity]*[Order Details by
Customer Query].[Unit Price]))
AS [Sales Value],
Format([Order Details by Customer Query].[Order Date],"mmmm yyyy") AS
[Month]
FROM [Order Details by Customer Query]
RIGHT JOIN Products ON [Order Details by Customer Query].[Product ID] =
Products.ID
WHERE ((([Order Details by Customer Query].[Order Date])
Between [Enter Start Date (mm/dd/yyyy)] And [Enter End Date (mm/dd/yyyy)]))
GROUP BY Products.[Product Code],
Products.[Product Name],
[Order Details by Customer Query].[Unit Price],
Format([Order Details by Customer Query].[Order Date],"mmmm yyyy")
ORDER BY Products.[Product Name];

*** QUERY #3 (Purchases) ***
SELECT Products.[Product Code],
Products.[Product Name],
Sum([Purchase Order Details].Quantity) AS Purchases,
[Purchase Order Details].[Unit Price],
CCur(Sum([Purchase Order Details].[Quantity]*[Purchase Order
Details].[Unit Price])) AS [Purchase Value],
Format([Date Received],"mmmm yyyy") AS [Month]
FROM Products
LEFT JOIN [Purchase Order Details] ON Products.ID = [Purchase Order
Details].[Product ID]
WHERE ((([Purchase Order Details].[Date Received])
Between [Enter Start Date (mm/dd/yyyy)] And [Enter End Date
(mm/dd/yyyy)]))
GROUP BY Products.[Product Code],
Products.[Product Name],
[Purchase Order Details].[Unit Price],
Format([Date Received],"mmmm yyyy")
ORDER BY Products.[Product Name];

*** QUERY #4 (Combines all 3 other queries) ***
SELECT Products.[Product Code],
Products.[Product Name],
Sum(Nz([Product Sales by Month].[Sales],0)) AS Sales,
CCur(Sum([Product Sales by Month].[Sales]*[Product Sales by Month].[Unit
Price])) AS [Sales Value],
Sum(Nz([Product Purchases by Month].[Purchases],0)) AS Purchases,
CCur(Sum([Product Purchases by Month].[Purchases]*[Product Purchases by
Month].[Unit Price]))
AS [Purchase Value],
[Product Sales by Month].Month
FROM [Product Sales by Month]
INNER JOIN ([Product Purchases by Month]
INNER JOIN Products
ON [Product Purchases by Month].[Product Code] = Products.[Product Code])
ON ([Product Sales by Month].[Product Code] = Products.[Product Code])
AND ([Product Sales by Month].[Product Code] = Products.[Product Code])
GROUP BY Products.[Product Code],
Products.[Product Name],
[Product Sales by Month].Month
ORDER BY Products.[Product Name];
Click to expand...
 
L

Lissa

Hi Karl...

Thanks for the quick response. Can you help me with some syntax on your
answer? I don't see that I have any LEFT joins in query #4, only INNER joins.

KARL DEWEY said:
I did not look over everything but in query 4 use left join instead of inner
join. The left of the join needs to be the side that will have records. If
for some reason there are a mixture or records on one side and then the other
then you need to create union query to build a complete listing for the left
side of a join.
--
KARL DEWEY
Build a little - Test a little


Lissa said:
I am resubmitting this question since I somehow managed to delete it off the
discussion group...

I have 3 queries that I want to use for retrieving the Beginning Balance of
inventory up to a certain date, retrieving all Sales between two dates, and
retrieving all Purchases between two dates. All these need to come together
on one report, but list everything even if one or two of the queries return a
null value. I've listed all 3 queries plus the 4th query that joins the other
3. The 4th query works but only pulls information where Queries #2 & #3 match
Product Codes. I've tried using Nz in the original #2 & #3 queries, but I
still get the same results. Can someone help me?

The queries are as follows:

*** QUERY #1 (BegInv) ***
SELECT Products.[Product Code],
Products.[Product Name],
[4/1/06 Beginning Balance]+[Prior Purchases]-[Prior Sales] AS [Beginning
Balance],
CCur(Sum([4/1/06 Beginning Balance]*[Products].[Unit Price])+
(Sum([Inventory Prior Purchases].[Prior Purchase Value])-(Sum([Inventory
Prior Sales].[Prior Sales Value]))))
AS [Beginning Value],
Products.[4/1/06 Beginning Balance],
[Inventory Prior Purchases].[Prior Purchases],
[Inventory Prior Purchases].[Prior Purchase Value],
[Inventory Prior Sales].[Prior Sales],
[Inventory Prior Sales].[Prior Sales Value]
FROM [Inventory Prior Sales]
INNER JOIN ([Inventory Prior Purchases]
INNER JOIN Products
ON [Inventory Prior Purchases].[Product Code]=Products.[Product Code])
ON [Inventory Prior Sales].[Product Code]=Products.[Product Code]
GROUP BY Products.[Product Code],
Products.[Product Name],
Products.[4/1/06 Beginning Balance],
[Inventory Prior Purchases].[Prior Purchases],
[Inventory Prior Purchases].[Prior Purchase Value],
[Inventory Prior Sales].[Prior Sales],
[Inventory Prior Sales].[Prior Sales Value]
ORDER BY Products.[Product Name];

*** QUERY #2 (Sales) ***
SELECT Products.[Product Code],
Products.[Product Name],
Sum([Order Details by Customer Query].Quantity) AS Sales,
[Order Details by Customer Query].[Unit Price],
CCur(Sum([Order Details by Customer Query].[Quantity]*[Order Details by
Customer Query].[Unit Price]))
AS [Sales Value],
Format([Order Details by Customer Query].[Order Date],"mmmm yyyy") AS
[Month]
FROM [Order Details by Customer Query]
RIGHT JOIN Products ON [Order Details by Customer Query].[Product ID] =
Products.ID
WHERE ((([Order Details by Customer Query].[Order Date])
Between [Enter Start Date (mm/dd/yyyy)] And [Enter End Date (mm/dd/yyyy)]))
GROUP BY Products.[Product Code],
Products.[Product Name],
[Order Details by Customer Query].[Unit Price],
Format([Order Details by Customer Query].[Order Date],"mmmm yyyy")
ORDER BY Products.[Product Name];

*** QUERY #3 (Purchases) ***
SELECT Products.[Product Code],
Products.[Product Name],
Sum([Purchase Order Details].Quantity) AS Purchases,
[Purchase Order Details].[Unit Price],
CCur(Sum([Purchase Order Details].[Quantity]*[Purchase Order
Details].[Unit Price])) AS [Purchase Value],
Format([Date Received],"mmmm yyyy") AS [Month]
FROM Products
LEFT JOIN [Purchase Order Details] ON Products.ID = [Purchase Order
Details].[Product ID]
WHERE ((([Purchase Order Details].[Date Received])
Between [Enter Start Date (mm/dd/yyyy)] And [Enter End Date
(mm/dd/yyyy)]))
GROUP BY Products.[Product Code],
Products.[Product Name],
[Purchase Order Details].[Unit Price],
Format([Date Received],"mmmm yyyy")
ORDER BY Products.[Product Name];

*** QUERY #4 (Combines all 3 other queries) ***
SELECT Products.[Product Code],
Products.[Product Name],
Sum(Nz([Product Sales by Month].[Sales],0)) AS Sales,
CCur(Sum([Product Sales by Month].[Sales]*[Product Sales by Month].[Unit
Price])) AS [Sales Value],
Sum(Nz([Product Purchases by Month].[Purchases],0)) AS Purchases,
CCur(Sum([Product Purchases by Month].[Purchases]*[Product Purchases by
Month].[Unit Price]))
AS [Purchase Value],
[Product Sales by Month].Month
FROM [Product Sales by Month]
INNER JOIN ([Product Purchases by Month]
INNER JOIN Products
ON [Product Purchases by Month].[Product Code] = Products.[Product Code])
ON ([Product Sales by Month].[Product Code] = Products.[Product Code])
AND ([Product Sales by Month].[Product Code] = Products.[Product Code])
GROUP BY Products.[Product Code],
Products.[Product Name],
[Product Sales by Month].Month
ORDER BY Products.[Product Name];
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

can't find the right query 0
3 queries into 1 8
combining queries into 1 8
Using Nz in inventory running balance 2
Reduce # of date columns in crosstab query 4
Adding up row values 4
group query 1
Set update query to reduce single field by user defined number 2

Top