Before, current, and ending units based on dates

[Guest]

I'm new to these newsgroups, so bear with me. I am trying to create a query
based on my Inventory Transactions table. It has UnitsReceived and
UnitsInstalled. I'm trying to create a report so that it will show Beginning
Balance, SumofUnitsReceived, SumofUnitsInstalled. All of these are to be
based off a [StartDate] and [EndDate]. If I run a query on Beginning
balances based on "<[StartDate]", everything shows. If I run a query on
SumofUnitsReceived and SumofUnitsInstalled based on "Between [StartDate] and
[EndDate], everything shows. I need to combine the results of these, but if
there was no Beginning balance of a product, the product doesn't show. Also,
if there was no activity during the two dates, the product doesn't show. If
you need more information, let me know.

TransDate, ProductID, UnitPrice, UnitsReceived, UnitsInstalled
9/30/04 Apples 3.00 2 0
9/30/04 Bananas 2.25 1 0
10/2/04 Apples 3.00 0 2
10/3/04 Apples 2.25 3 0

 

[[MVP] S.Clark]

It sounds like you need to save the output from one query result and combine
with the result of a second. If so, then use and Append query to write the
data to a temp table.

 

[Guest]

It worked. Because of the information price information, date sorting, and
the difficulty querying, I had to create two append query and update the same
temp table. I created another query off of that table so that I could run a
report from it. From there I created a macro to turn off SetWarning, run a
delete query, and run the queries with new parameters. This is being set to
run when the report is being opened. The only issue I have is the one query
is a beginning balance query which I have to enter a "before start date" and
the other query is transactions between two dates so I have to enter a start
date and end date. This requires me to enter a start date twice. Oh well,
not a huge deal considering how much time this has caused me. I thank you so
much!!!

It sounds like you need to save the output from one query result and combine
with the result of a second. If so, then use and Append query to write the
data to a temp table.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

I'm new to these newsgroups, so bear with me. I am trying to create a
query
based on my Inventory Transactions table. It has UnitsReceived and
UnitsInstalled. I'm trying to create a report so that it will show
Beginning
Balance, SumofUnitsReceived, SumofUnitsInstalled. All of these are to be
based off a [StartDate] and [EndDate]. If I run a query on Beginning
balances based on "<[StartDate]", everything shows. If I run a query on
SumofUnitsReceived and SumofUnitsInstalled based on "Between [StartDate]
and
[EndDate], everything shows. I need to combine the results of these, but
if
there was no Beginning balance of a product, the product doesn't show.
Also,
if there was no activity during the two dates, the product doesn't show.
If
you need more information, let me know.

TransDate, ProductID, UnitPrice, UnitsReceived, UnitsInstalled
9/30/04 Apples 3.00 2 0
9/30/04 Bananas 2.25 1 0
10/2/04 Apples 3.00 0 2
10/3/04 Apples 2.25 3 0