Date based query problem

[Lissa]

I have an inventory database (in Access 2007) that calculates the Beginning
Inventory, # Cards Sold, # Cards Purchased, and Ending Inventory. I have a
query that pulls the # Cards Sold, # Cards Purchased, and calculates the
Ending Inventory based on the date parameters given to the report ("Enter
Beginning Date" and "Enter Ending Date"), but I need to pull the Beginning
Inventory information based on all data prior to the "Beginning Date" of the
report.

For instance... the date parameters are 5/1/07 (Beginning Date) and 5/31/07
(Ending Date), so the Beginning Inventory should pull all # Cards Sold and #
Cards Purchased from the beginning of time through 4/30/07 (one day prior to
the Beginning Date parameter). I can't seem to make this work no matter what
I do. I have created 2 queries -- one that pulls and calculates all the
information for the report parameters and another that calculates the
Beginning Inventory seperately. If I combine the information on the queries
the totals are all wrong and I can't figure out how to get the Beginning
Inventory field on the report when it's not from the same data source as the
data query.

Am I going about this the wrong way? Is there an easier way to do all this?
Any help would be greatly appreciated!! Thanks Lissa

 

[John Spencer]

SELECT Sum(CardsSold) as TotalSold
, Sum(CardsPurchased) as TotalBought
, (SELECT Sum(CardsPurchased)
FROM Inventory
WHERE Inventory.ActionDate < [Beginning Date]) as PriorPurchases
FROM Inventory
WHERE Inventory.ActionDate Between [Beginning Date] and [Ending Date]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Lissa]

Hi John...

Thanks for your really quick response to my message yesterday! I tried your
solution, but I keep getting a Syntax Error for a missing operator. It seems
to not like the AS at the end of the first WHERE clause. Following is my full
SQL code with your change added. Can you see where I'm going wrong? Thanks
again for your help!!

SELECT Products.[Product Code], Products.[Product Name], [Order
Details].[Unit Price], Sum([Order Details].Quantity) AS Sales
, CCur(Sum([Order Details].[Quantity]*[Order Details].[Unit Price])) AS
[Sales Value]
, Sum([Purchase Order Details].Quantity) AS Purchases
, CCur(Sum([Purchase Order Details].[Quantity]*[Order Details].[Unit
Price])) AS [Purchase Value]
, [Beginning Balance]+[Purchases]-[Sales] AS [Ending Balance]
, CCur(Sum([Purchase Order Details].[Quantity]*[Order Details].[Unit
Price])-(Sum([Order Details].[Quantity]*[Order Details].[Unit Price]))) AS
[Ending Value]
, Format([Order Date],"mmmm yyyy") AS [Month]
, Products.[4/1/06 Beginning Balance], [4/1/06 Beginning
Balance]+[Purchases]-[Sales] AS [Beginning Balance]
, CCur(Sum([4/1/06 Beginning Balance]*[Products].[Unit
Price])+(Sum([Purchase Order Details].[Quantity]*[Order Details].[Unit
Price])-(Sum([Order Details].[Quantity]*[Order Details].[Unit Price])))) AS
[Beginning Value]

FROM (Orders INNER JOIN (Products INNER JOIN [Order Details] ON Products.ID
= [Order Details].[Product ID]) ON Orders.[Order ID] = [Order Details].[Order
ID]) INNER JOIN [Purchase Order Details] ON Products.ID = [Purchase Order
Details].[Product ID]

WHERE (((Orders.[Order Date]) < [Enter Start Date (mm/dd/yyyy)]) AND
(([Purchase Order Details].[Date Received]) < [Enter Start Date
(mm/dd/yyyy)])) AS PriorInventory

FROM (Orders INNER JOIN (Products INNER JOIN [Order Details] ON Products.ID
= [Order Details].[Product ID]) ON Orders.[Order ID] = [Order Details].[Order
ID]) INNER JOIN [Purchase Order Details] ON Products.ID = [Purchase Order
Details].[Product ID]

WHERE (((Orders.[Order Date]) Between [Enter Start Date (mm/dd/yyyy)] And
[Enter End Date (mm/dd/yyyy)]) AND (([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], [Order
Details].[Unit Price], Format([Order Date],"mmmm yyyy"), Products.[4/1/06
Beginning Balance]

ORDER BY Products.[Product Name];

SELECT Sum(CardsSold) as TotalSold
, Sum(CardsPurchased) as TotalBought
, (SELECT Sum(CardsPurchased)
FROM Inventory
WHERE Inventory.ActionDate < [Beginning Date]) as PriorPurchases
FROM Inventory
WHERE Inventory.ActionDate Between [Beginning Date] and [Ending Date]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have an inventory database (in Access 2007) that calculates the Beginning
Inventory, # Cards Sold, # Cards Purchased, and Ending Inventory. I have a
query that pulls the # Cards Sold, # Cards Purchased, and calculates the
Ending Inventory based on the date parameters given to the report ("Enter
Beginning Date" and "Enter Ending Date"), but I need to pull the Beginning
Inventory information based on all data prior to the "Beginning Date" of
the
report.

For instance... the date parameters are 5/1/07 (Beginning Date) and
5/31/07
(Ending Date), so the Beginning Inventory should pull all # Cards Sold and
#
Cards Purchased from the beginning of time through 4/30/07 (one day prior
to
the Beginning Date parameter). I can't seem to make this work no matter
what
I do. I have created 2 queries -- one that pulls and calculates all the
information for the report parameters and another that calculates the
Beginning Inventory seperately. If I combine the information on the
queries
the totals are all wrong and I can't figure out how to get the Beginning
Inventory field on the report when it's not from the same data source as
the
data query.

Am I going about this the wrong way? Is there an easier way to do all
this?
Any help would be greatly appreciated!! Thanks Lissa

 

[John Spencer]

I really can't understand what you have done. Let's back up a step or
three.

Create a query that gets the totals up to the cut off date and save that.

Now using that as if it were another table, build your query with it and
your other tables.

First Query
SELECT [Purchase Order Details].[Product ID],
Sum([Purchase Order Details].Quantity) as BeginningBalance
FROM [Purchase Order Details]
WHERE [Date Received] < [Start Date]
GROUP BY [Product ID]

Now you can use that in your query as if it were another table


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John...

Thanks for your really quick response to my message yesterday! I tried
your
solution, but I keep getting a Syntax Error for a missing operator. It
seems
to not like the AS at the end of the first WHERE clause. Following is my
full
SQL code with your change added. Can you see where I'm going wrong? Thanks
again for your help!!

SELECT Products.[Product Code], Products.[Product Name], [Order
Details].[Unit Price], Sum([Order Details].Quantity) AS Sales
, CCur(Sum([Order Details].[Quantity]*[Order Details].[Unit Price])) AS
[Sales Value]
, Sum([Purchase Order Details].Quantity) AS Purchases
, CCur(Sum([Purchase Order Details].[Quantity]*[Order Details].[Unit
Price])) AS [Purchase Value]
, [Beginning Balance]+[Purchases]-[Sales] AS [Ending Balance]
, CCur(Sum([Purchase Order Details].[Quantity]*[Order Details].[Unit
Price])-(Sum([Order Details].[Quantity]*[Order Details].[Unit Price]))) AS
[Ending Value]
, Format([Order Date],"mmmm yyyy") AS [Month]
, Products.[4/1/06 Beginning Balance], [4/1/06 Beginning
Balance]+[Purchases]-[Sales] AS [Beginning Balance]
, CCur(Sum([4/1/06 Beginning Balance]*[Products].[Unit
Price])+(Sum([Purchase Order Details].[Quantity]*[Order Details].[Unit
Price])-(Sum([Order Details].[Quantity]*[Order Details].[Unit Price]))))
AS
[Beginning Value]

FROM (Orders INNER JOIN (Products INNER JOIN [Order Details] ON
Products.ID
= [Order Details].[Product ID]) ON Orders.[Order ID] = [Order
Details].[Order
ID]) INNER JOIN [Purchase Order Details] ON Products.ID = [Purchase Order
Details].[Product ID]

WHERE (((Orders.[Order Date]) < [Enter Start Date (mm/dd/yyyy)]) AND
(([Purchase Order Details].[Date Received]) < [Enter Start Date
(mm/dd/yyyy)])) AS PriorInventory

FROM (Orders INNER JOIN (Products INNER JOIN [Order Details] ON
Products.ID
= [Order Details].[Product ID]) ON Orders.[Order ID] = [Order
Details].[Order
ID]) INNER JOIN [Purchase Order Details] ON Products.ID = [Purchase Order
Details].[Product ID]

WHERE (((Orders.[Order Date]) Between [Enter Start Date (mm/dd/yyyy)] And
[Enter End Date (mm/dd/yyyy)]) AND (([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], [Order
Details].[Unit Price], Format([Order Date],"mmmm yyyy"), Products.[4/1/06
Beginning Balance]

ORDER BY Products.[Product Name];

SELECT Sum(CardsSold) as TotalSold
, Sum(CardsPurchased) as TotalBought
, (SELECT Sum(CardsPurchased)
FROM Inventory
WHERE Inventory.ActionDate < [Beginning Date]) as PriorPurchases
FROM Inventory
WHERE Inventory.ActionDate Between [Beginning Date] and [Ending Date]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have an inventory database (in Access 2007) that calculates the
Beginning
Inventory, # Cards Sold, # Cards Purchased, and Ending Inventory. I
have a
query that pulls the # Cards Sold, # Cards Purchased, and calculates
the
Ending Inventory based on the date parameters given to the report
("Enter
Beginning Date" and "Enter Ending Date"), but I need to pull the
Beginning
Inventory information based on all data prior to the "Beginning Date"
of
the
report.

For instance... the date parameters are 5/1/07 (Beginning Date) and
5/31/07
(Ending Date), so the Beginning Inventory should pull all # Cards Sold
and
#
Cards Purchased from the beginning of time through 4/30/07 (one day
prior
to
the Beginning Date parameter). I can't seem to make this work no matter
what
I do. I have created 2 queries -- one that pulls and calculates all the
information for the report parameters and another that calculates the
Beginning Inventory seperately. If I combine the information on the
queries
the totals are all wrong and I can't figure out how to get the
Beginning
Inventory field on the report when it's not from the same data source
as
the
data query.

Am I going about this the wrong way? Is there an easier way to do all
this?
Any help would be greatly appreciated!! Thanks Lissa

 

[Lissa]

Hi John...

Okay... Thanks to your last suggestion, I now have a working "Beginning
Balance" query (calculates the beginning balance based on all sales &
purchases up to the beginning date), a working "Purchases" query (pulls
purchases for the date range selected), and a working "Sales" query (pulls
sales for the date range selected).

Now I am trying to put them all in a report without losing any information.
For example, the report headings are:

ProdID ProdName BegBal Sales Purchases EndBal(BegBal+Purchases-Sales)

If I combine all 3 queries, I get the Beginning Balance to work, but the
only Sales and Purchases that show are the ones that are linked in the query.
I want ALL the Sales for the time period and ALL the Purchases for the
period, even if one of them has a zero balance.

Right now it looks like:

Product A 20 (BegBal) 15 (Sales) 2 (Purchases) 7 (EndBal)
Product C 0 (BegBal) 3 (Sales) 6 (Purchases) 3 (EndBal)
Product D 10 (BegBal) 1 (Sales) 2 (Purchases) 11 (EndBal)

However, this is how I want the inforamtion to look:

Product A 20 (BegBal) 15 (Sales) 2 (Purchases) 7 (EndBal)
Product B 15 (BegBal) 12 (Sales) 0 (Purchases) 3 (EndBal)
Product C 0 (BegBal) 3 (Sales) 6 (Purchases) 3 (EndBal)
Product D 10 (BegBal) 1 (Sales) 2 (Purchases) 11 (EndBal)

I tried using a Union Query, but that lists everything in one column so I
don't see how it would work for this issue. I also tried to use sub forms,
but I can't get it to layout the way I want.

Any ideas?

I really can't understand what you have done. Let's back up a step or
three.

Create a query that gets the totals up to the cut off date and save that.

Now using that as if it were another table, build your query with it and
your other tables.

First Query
SELECT [Purchase Order Details].[Product ID],
Sum([Purchase Order Details].Quantity) as BeginningBalance
FROM [Purchase Order Details]
WHERE [Date Received] < [Start Date]
GROUP BY [Product ID]

Now you can use that in your query as if it were another table


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John...

Thanks for your really quick response to my message yesterday! I tried
your
solution, but I keep getting a Syntax Error for a missing operator. It
seems
to not like the AS at the end of the first WHERE clause. Following is my
full
SQL code with your change added. Can you see where I'm going wrong? Thanks
again for your help!!

SELECT Products.[Product Code], Products.[Product Name], [Order
Details].[Unit Price], Sum([Order Details].Quantity) AS Sales
, CCur(Sum([Order Details].[Quantity]*[Order Details].[Unit Price])) AS
[Sales Value]
, Sum([Purchase Order Details].Quantity) AS Purchases
, CCur(Sum([Purchase Order Details].[Quantity]*[Order Details].[Unit
Price])) AS [Purchase Value]
, [Beginning Balance]+[Purchases]-[Sales] AS [Ending Balance]
, CCur(Sum([Purchase Order Details].[Quantity]*[Order Details].[Unit
Price])-(Sum([Order Details].[Quantity]*[Order Details].[Unit Price]))) AS
[Ending Value]
, Format([Order Date],"mmmm yyyy") AS [Month]
, Products.[4/1/06 Beginning Balance], [4/1/06 Beginning
Balance]+[Purchases]-[Sales] AS [Beginning Balance]
, CCur(Sum([4/1/06 Beginning Balance]*[Products].[Unit
Price])+(Sum([Purchase Order Details].[Quantity]*[Order Details].[Unit
Price])-(Sum([Order Details].[Quantity]*[Order Details].[Unit Price]))))
AS
[Beginning Value]

FROM (Orders INNER JOIN (Products INNER JOIN [Order Details] ON
Products.ID
= [Order Details].[Product ID]) ON Orders.[Order ID] = [Order
Details].[Order
ID]) INNER JOIN [Purchase Order Details] ON Products.ID = [Purchase Order
Details].[Product ID]

WHERE (((Orders.[Order Date]) < [Enter Start Date (mm/dd/yyyy)]) AND
(([Purchase Order Details].[Date Received]) < [Enter Start Date
(mm/dd/yyyy)])) AS PriorInventory

FROM (Orders INNER JOIN (Products INNER JOIN [Order Details] ON
Products.ID
= [Order Details].[Product ID]) ON Orders.[Order ID] = [Order
Details].[Order
ID]) INNER JOIN [Purchase Order Details] ON Products.ID = [Purchase Order
Details].[Product ID]

WHERE (((Orders.[Order Date]) Between [Enter Start Date (mm/dd/yyyy)] And
[Enter End Date (mm/dd/yyyy)]) AND (([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], [Order
Details].[Unit Price], Format([Order Date],"mmmm yyyy"), Products.[4/1/06
Beginning Balance]

ORDER BY Products.[Product Name];

SELECT Sum(CardsSold) as TotalSold
, Sum(CardsPurchased) as TotalBought
, (SELECT Sum(CardsPurchased)
FROM Inventory
WHERE Inventory.ActionDate < [Beginning Date]) as PriorPurchases
FROM Inventory
WHERE Inventory.ActionDate Between [Beginning Date] and [Ending Date]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have an inventory database (in Access 2007) that calculates the
Beginning
Inventory, # Cards Sold, # Cards Purchased, and Ending Inventory. I
have a
query that pulls the # Cards Sold, # Cards Purchased, and calculates
the
Ending Inventory based on the date parameters given to the report
("Enter
Beginning Date" and "Enter Ending Date"), but I need to pull the
Beginning
Inventory information based on all data prior to the "Beginning Date"
of
the
report.

For instance... the date parameters are 5/1/07 (Beginning Date) and
5/31/07
(Ending Date), so the Beginning Inventory should pull all # Cards Sold
and
#
Cards Purchased from the beginning of time through 4/30/07 (one day
prior
to
the Beginning Date parameter). I can't seem to make this work no matter
what
I do. I have created 2 queries -- one that pulls and calculates all the
information for the report parameters and another that calculates the
Beginning Inventory seperately. If I combine the information on the
queries
the totals are all wrong and I can't figure out how to get the
Beginning
Inventory field on the report when it's not from the same data source
as
the
data query.

Am I going about this the wrong way? Is there an easier way to do all
this?
Any help would be greatly appreciated!! Thanks Lissa