Re: Date based query problem

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" <Lissa@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4D101497-BB1D-4EF3-92B3-336A617EBB25@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Date based query problem
    ... FROM Inventory ... Inventory, # Cards Sold, # Cards Purchased, and Ending Inventory. ... Ending Inventory based on the date parameters given to the report ("Enter ... I have created 2 queries -- one that pulls and calculates all the ...
    (microsoft.public.access.reports)
  • Re: Date based query problem
    ... Create a query that gets the totals up to the cut off date and save that. ... FROM Inventory ... Inventory, # Cards Sold, # Cards Purchased, and Ending Inventory. ... Ending Inventory based on the date parameters given to the report ...
    (microsoft.public.access.reports)
  • Re: Help with table structure for manufacturing production counts
    ... I currently receive a weekly report from the plant managers that gives Model numbers, quantity built, and how many have each various options (ie: color, glass doors, casters, compressor hp, voltage etc). ... The buyers in the purchasing department use the data from this report to help them keep track of inventory of their purchased components and predict what they need to buy in the upcoming weeks. ... I began to set up a table to use to house the data that comes from the weekly production reports ...
    (microsoft.public.access.gettingstarted)
  • Re: SMS_Inventory_Data_Loader Errors
    ... - Inventory is on a weekly cycle ... - Resync request is sent to a client. ... last reporting time- will be a resync report. ...
    (microsoft.public.sms.inventory)
  • Re: Software Inventory fails to include some products
    ... > Can I do this in SMS 2003? ... > I need SMS report to distinguish between Standard and Professional version> of Office, also I am not a programmer and I can barely write script. ... >>> Software inventory is not capable of aggregating file properties to a>> particular product. ...
    (microsoft.public.sms.inventory)