Before, current, and ending units based on dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm new 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
 
You are in for a treat,

Have you used union queries?
To help this problem you should have an index in the ProductId and Transdate
in your Transactions table, then start by creating 3 queries from the
transactions table:

Query 1,group by ProductID, a column for beginning_Balance
sum(UnitsReceived- UnitsInstalled), and a column for ending_Balance making
this a 0 (zero), where the transdate < [StartDate] and sum(UnitsReceived-
UnitsInstalled) >0 (for Beginning inventory), name it
Transaction_Beginning_Balance_qry

Query 2,group by ProductID a column for beginning_Balance making it =0, and
a column for ending_Balance sum(UnitsReceived- UnitsInstalled) where the
transdate < ([EndDate]+1) and sum(UnitsReceived- UnitsInstalled) >0 (for
Ending Inventory), , name it Transaction_Ending_Balance_qry

Query 3,group by ProductID, beginning_balance=0, ending_balance=0 where the
transdate between [StartDate] and [EndDate] (for product with activity
during period), , name it Transaction_Product_With_Activity_qry

Then create the union query (query 4, check your access help file for
details),

select * from Transaction_Beginning_Balance_qry

union

select * from Transaction_Ending_Balance_qry

union

select * from Transaction_Product_With_Activity_qry;

This query 4 will have all the productId's with beginning, ending, products
with activity, group by productid, sum(beginning_balance),
sum(ending_balance),name it Transaction_Product_with_Balance_And_Activity_qry

Now create query 5, selecting the transactions for the period between
[StartDate] and [Endate] and the fields you need to show in your report, name
it Transaction_Product_Activity_qry,

Your query #6 and final, create a left outer join between
Transaction_Product_with_Balance_And_Activity_qry and
Transaction_Product_Activity_qry, name it Transaction_Report_qry

Make this last query the record source for your report, and in the report
sort by product id, creating a heading for each productid where you are going
to show the beginning balance, and the footer for the productid where you'll
show the ending balance,

Let me know if this works

Jose Lara
(e-mail address removed)
www.joshdev.com

Windstorm said:
I'm new 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
Click to expand...
 
First, let me thank you sooo much for helping me out. I'm new to querying
and am very happy for any advice or information. I have set up the queries
to your specifications (I think). Most everything is working out. I know
have all lines showing, regardless of null values. I added the UnitPrice
field to all of the queries, because I need to track the units per price. I
have gotten all the information down to the Transaction_Report_qry and only
one thing is a problem. I've tried a couple of things, but to no avail.
Here's an example of the problem:

Should be:
ProductID UnitPrice BB Rec Inst EB (Abbr. for titles
for sake of ex.)
Apples 5.48 0 100 100 0
Apples 6.42 0 200 102 98

Query Results
ProductID UnitPrice BB Rec Inst EB
Apples 5.48 0 300 202 0
Apples 6.42 0 600 404 196

I believe it has something to do with the Union query not grouping. The
query shows:
ProductID UnitPrice beginning_Balance ending_Balance
41 $2.25 0 0
41 $2.25 0 19
41 $2.25 23 0
42 $2.25 0 0
42 $2.25 0 9
43 $4.90 0 0
43 $4.90 0 8
43 $4.90 6 0
44 $5.86 0 0
44 $6.42 0 0
44 $6.42 0 98

I think it adding UnitsReceived and UnitsInstalled for each time it is
listed in the Union Query. Any help would be appreciated. Thanks much!


jl5000 said:
You are in for a treat,

Have you used union queries?
To help this problem you should have an index in the ProductId and Transdate
in your Transactions table, then start by creating 3 queries from the
transactions table:

Query 1,group by ProductID, a column for beginning_Balance
sum(UnitsReceived- UnitsInstalled), and a column for ending_Balance making
this a 0 (zero), where the transdate < [StartDate] and sum(UnitsReceived-
UnitsInstalled) >0 (for Beginning inventory), name it
Transaction_Beginning_Balance_qry

Query 2,group by ProductID a column for beginning_Balance making it =0, and
a column for ending_Balance sum(UnitsReceived- UnitsInstalled) where the
transdate < ([EndDate]+1) and sum(UnitsReceived- UnitsInstalled) >0 (for
Ending Inventory), , name it Transaction_Ending_Balance_qry

Query 3,group by ProductID, beginning_balance=0, ending_balance=0 where the
transdate between [StartDate] and [EndDate] (for product with activity
during period), , name it Transaction_Product_With_Activity_qry

Then create the union query (query 4, check your access help file for
details),

select * from Transaction_Beginning_Balance_qry

union

select * from Transaction_Ending_Balance_qry

union

select * from Transaction_Product_With_Activity_qry;

This query 4 will have all the productId's with beginning, ending, products
with activity, group by productid, sum(beginning_balance),
sum(ending_balance),name it Transaction_Product_with_Balance_And_Activity_qry

Now create query 5, selecting the transactions for the period between
[StartDate] and [Endate] and the fields you need to show in your report, name
it Transaction_Product_Activity_qry,

Your query #6 and final, create a left outer join between
Transaction_Product_with_Balance_And_Activity_qry and
Transaction_Product_Activity_qry, name it Transaction_Report_qry

Make this last query the record source for your report, and in the report
sort by product id, creating a heading for each productid where you are going
to show the beginning balance, and the footer for the productid where you'll
show the ending balance,

Let me know if this works

Jose Lara
(e-mail address removed)
www.joshdev.com

Windstorm said:
I'm new 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
Click to expand...
Click to expand...
 
Here is some more information on the Left Outer Union Join. I'm not sure if
this is correct.

SELECT Transaction_Product_with_Balance_And_Activity_qry.ProductID,
Transaction_Product_with_Balance_And_Activity_qry.UnitPrice,
Sum(Nz([beginning_Balance],0)) AS Expr1, Sum(Nz([SumOfUnitsReceived],0)) AS
Expr2, Sum(Nz([SumOfUnitsInstalled],0)) AS Expr3, Sum(Nz([ending_Balance],0))
AS Expr4
FROM Transaction_Product_with_Balance_And_Activity_qry LEFT JOIN
Transaction_Product_Activity_qry ON
Transaction_Product_with_Balance_And_Activity_qry.ProductID =
Transaction_Product_Activity_qry.ProductID
GROUP BY Transaction_Product_with_Balance_And_Activity_qry.ProductID,
Transaction_Product_with_Balance_And_Activity_qry.UnitPrice;


Windstorm said:
First, let me thank you sooo much for helping me out. I'm new to querying
and am very happy for any advice or information. I have set up the queries
to your specifications (I think). Most everything is working out. I know
have all lines showing, regardless of null values. I added the UnitPrice
field to all of the queries, because I need to track the units per price. I
have gotten all the information down to the Transaction_Report_qry and only
one thing is a problem. I've tried a couple of things, but to no avail.
Here's an example of the problem:

Should be:
ProductID UnitPrice BB Rec Inst EB (Abbr. for titles
for sake of ex.)
Apples 5.48 0 100 100 0
Apples 6.42 0 200 102 98

Query Results
ProductID UnitPrice BB Rec Inst EB
Apples 5.48 0 300 202 0
Apples 6.42 0 600 404 196

I believe it has something to do with the Union query not grouping. The
query shows:
ProductID UnitPrice beginning_Balance ending_Balance
41 $2.25 0 0
41 $2.25 0 19
41 $2.25 23 0
42 $2.25 0 0
42 $2.25 0 9
43 $4.90 0 0
43 $4.90 0 8
43 $4.90 6 0
44 $5.86 0 0
44 $6.42 0 0
44 $6.42 0 98

I think it adding UnitsReceived and UnitsInstalled for each time it is
listed in the Union Query. Any help would be appreciated. Thanks much!


jl5000 said:
You are in for a treat,

Have you used union queries?
To help this problem you should have an index in the ProductId and Transdate
in your Transactions table, then start by creating 3 queries from the
transactions table:

Query 1,group by ProductID, a column for beginning_Balance
sum(UnitsReceived- UnitsInstalled), and a column for ending_Balance making
this a 0 (zero), where the transdate < [StartDate] and sum(UnitsReceived-
UnitsInstalled) >0 (for Beginning inventory), name it
Transaction_Beginning_Balance_qry

Query 2,group by ProductID a column for beginning_Balance making it =0, and
a column for ending_Balance sum(UnitsReceived- UnitsInstalled) where the
transdate < ([EndDate]+1) and sum(UnitsReceived- UnitsInstalled) >0 (for
Ending Inventory), , name it Transaction_Ending_Balance_qry

Query 3,group by ProductID, beginning_balance=0, ending_balance=0 where the
transdate between [StartDate] and [EndDate] (for product with activity
during period), , name it Transaction_Product_With_Activity_qry

Then create the union query (query 4, check your access help file for
details),

select * from Transaction_Beginning_Balance_qry

union

select * from Transaction_Ending_Balance_qry

union

select * from Transaction_Product_With_Activity_qry;

This query 4 will have all the productId's with beginning, ending, products
with activity, group by productid, sum(beginning_balance),
sum(ending_balance),name it Transaction_Product_with_Balance_And_Activity_qry

Now create query 5, selecting the transactions for the period between
[StartDate] and [Endate] and the fields you need to show in your report, name
it Transaction_Product_Activity_qry,

Your query #6 and final, create a left outer join between
Transaction_Product_with_Balance_And_Activity_qry and
Transaction_Product_Activity_qry, name it Transaction_Report_qry

Make this last query the record source for your report, and in the report
sort by product id, creating a heading for each productid where you are going
to show the beginning balance, and the footer for the productid where you'll
show the ending balance,

Let me know if this works

Jose Lara
(e-mail address removed)
www.joshdev.com

Windstorm said:
I'm new 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
Click to expand...
Click to expand...
Click to expand...
 
Looks that you can have the same product with different prices, in that case
do not add the unit price to the grouping, since it creates multiple rows,
but create a extended price field multiplying your quantity and the price,
and create a sum for that calculation in your query,

Windstorm said:
Here is some more information on the Left Outer Union Join. I'm not sure if
this is correct.

SELECT Transaction_Product_with_Balance_And_Activity_qry.ProductID,
Transaction_Product_with_Balance_And_Activity_qry.UnitPrice,
Sum(Nz([beginning_Balance],0)) AS Expr1, Sum(Nz([SumOfUnitsReceived],0)) AS
Expr2, Sum(Nz([SumOfUnitsInstalled],0)) AS Expr3, Sum(Nz([ending_Balance],0))
AS Expr4
FROM Transaction_Product_with_Balance_And_Activity_qry LEFT JOIN
Transaction_Product_Activity_qry ON
Transaction_Product_with_Balance_And_Activity_qry.ProductID =
Transaction_Product_Activity_qry.ProductID
GROUP BY Transaction_Product_with_Balance_And_Activity_qry.ProductID,
Transaction_Product_with_Balance_And_Activity_qry.UnitPrice;


Windstorm said:
First, let me thank you sooo much for helping me out. I'm new to querying
and am very happy for any advice or information. I have set up the queries
to your specifications (I think). Most everything is working out. I know
have all lines showing, regardless of null values. I added the UnitPrice
field to all of the queries, because I need to track the units per price. I
have gotten all the information down to the Transaction_Report_qry and only
one thing is a problem. I've tried a couple of things, but to no avail.
Here's an example of the problem:

Should be:
ProductID UnitPrice BB Rec Inst EB (Abbr. for titles
for sake of ex.)
Apples 5.48 0 100 100 0
Apples 6.42 0 200 102 98

Query Results
ProductID UnitPrice BB Rec Inst EB
Apples 5.48 0 300 202 0
Apples 6.42 0 600 404 196

I believe it has something to do with the Union query not grouping. The
query shows:
ProductID UnitPrice beginning_Balance ending_Balance
41 $2.25 0 0
41 $2.25 0 19
41 $2.25 23 0
42 $2.25 0 0
42 $2.25 0 9
43 $4.90 0 0
43 $4.90 0 8
43 $4.90 6 0
44 $5.86 0 0
44 $6.42 0 0
44 $6.42 0 98

I think it adding UnitsReceived and UnitsInstalled for each time it is
listed in the Union Query. Any help would be appreciated. Thanks much!


jl5000 said:
You are in for a treat,

Have you used union queries?
To help this problem you should have an index in the ProductId and Transdate
in your Transactions table, then start by creating 3 queries from the
transactions table:

Query 1,group by ProductID, a column for beginning_Balance
sum(UnitsReceived- UnitsInstalled), and a column for ending_Balance making
this a 0 (zero), where the transdate < [StartDate] and sum(UnitsReceived-
UnitsInstalled) >0 (for Beginning inventory), name it
Transaction_Beginning_Balance_qry

Query 2,group by ProductID a column for beginning_Balance making it =0, and
a column for ending_Balance sum(UnitsReceived- UnitsInstalled) where the
transdate < ([EndDate]+1) and sum(UnitsReceived- UnitsInstalled) >0 (for
Ending Inventory), , name it Transaction_Ending_Balance_qry

Query 3,group by ProductID, beginning_balance=0, ending_balance=0 where the
transdate between [StartDate] and [EndDate] (for product with activity
during period), , name it Transaction_Product_With_Activity_qry

Then create the union query (query 4, check your access help file for
details),

select * from Transaction_Beginning_Balance_qry

union

select * from Transaction_Ending_Balance_qry

union

select * from Transaction_Product_With_Activity_qry;

This query 4 will have all the productId's with beginning, ending, products
with activity, group by productid, sum(beginning_balance),
sum(ending_balance),name it Transaction_Product_with_Balance_And_Activity_qry

Now create query 5, selecting the transactions for the period between
[StartDate] and [Endate] and the fields you need to show in your report, name
it Transaction_Product_Activity_qry,

Your query #6 and final, create a left outer join between
Transaction_Product_with_Balance_And_Activity_qry and
Transaction_Product_Activity_qry, name it Transaction_Report_qry

Make this last query the record source for your report, and in the report
sort by product id, creating a heading for each productid where you are going
to show the beginning balance, and the footer for the productid where you'll
show the ending balance,

Let me know if this works

Jose Lara
(e-mail address removed)
www.joshdev.com

:

I'm new 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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top