Numbering Raws

[doughnut]

Hi,

My query gets date from several tables: Tpurchase, TProducts, TContract,
TBuyer...
After ordering the query data by Contract ID (many contracts to one dealer)
and by purchase date (many purchases to one contract), I would like to add
another field as raw number.
This is so i could later create a running sum for the purchase amount by
purchase date rather then by purcahse ID. The problem is that purchases from
earlier dates may have higher ID since they are entered in delay.

So... How do I created numbering for the orders?

 

[John W. Vinson]

Hi,

My query gets date from several tables: Tpurchase, TProducts, TContract,
TBuyer...
After ordering the query data by Contract ID (many contracts to one dealer)
and by purchase date (many purchases to one contract), I would like to add
another field as raw number.
This is so i could later create a running sum for the purchase amount by
purchase date rather then by purcahse ID. The problem is that purchases from
earlier dates may have higher ID since they are entered in delay.

So... How do I created numbering for the orders?

You don't need to.

You can create a Totals Query to calculate the sum by date (including running
sums). The purchase ID need not even get involved in the query.

If you'll post the relevant table and fieldnames someone could suggest a Query
that would get you the running sum you want.

John W. Vinson [MVP]

 

[Dale Fye]

Since this looks like it is going to be in a report, I wouldn't even bother
creating the [RunningSum] value in the query, you can do it in the report.
Using the query you have, create a report that groups by contractID and sorts
by PurchaseDate within the ContractID.

Add the fields to your report, but add the [Purchase Amount] field twice.
Right click on the second [Purchase Amount] textbox, select Properties, and
in the property dialogs data tab, select "Over Group" in the Running Sum
property.

Run your report.

HTH
Dale

 

[doughnut]

Dear John,

I would be happy to calculate the running sum by date, but from reading
other threads, i got the idea this may create calulation problems. (such as
in the case of several orders in the same date).

my tables are:

Several terms for each contract
TContract TTerm (time period)
----------- --------
Contract ID TermID
No. Start Date
Buyer Name End Date
Range Min. Purchase

Several orders for each contract
TContract TPurchase
----------- -----------
ContractID contractID
Purchase ID
Date
delivered (yes/no)

Several Products in each order
TPurchase TPI
------------ -----
Purchase ID
line ID
Prod Name
Prod Qty
Prod Price

my query takes info from all the above.
it should determine:

1. the running sum of ALL orders per term
2. the difference amount between each delivered order's running sum and
minimum perchase field in the terms table.

I think i cant do this in a report since the report should only present
delivered orders and from specific dates (2007 only for example).
the contrat terms may be between April to may the next year and delivery can
be after the term has ended.

 

[doughnut]

Dale, thanks for your reply.

its really what i was afraid to find here.

I would love to jump the query section and use report directly but!

I think i cant do this in a report since the report should ONLY present
delivered orders and from specific dates (2007 only for example). BUT THE
RUNNING SUM SHOULD INCLUDE ALL ORDERS.

the contrat terms may be between April to may the next year and delivery can
be after the term has ended. SO I CAN'T THINK OF REPORT THAT MAY CALCULATE
ORDERS NOT FROM ITS RANGE.

Since this looks like it is going to be in a report, I wouldn't even bother
creating the [RunningSum] value in the query, you can do it in the report.
Using the query you have, create a report that groups by contractID and sorts
by PurchaseDate within the ContractID.

Add the fields to your report, but add the [Purchase Amount] field twice.
Right click on the second [Purchase Amount] textbox, select Properties, and
in the property dialogs data tab, select "Over Group" in the Running Sum
property.

Run your report.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hi,

My query gets date from several tables: Tpurchase, TProducts, TContract,
TBuyer...
After ordering the query data by Contract ID (many contracts to one dealer)
and by purchase date (many purchases to one contract), I would like to add
another field as raw number.
This is so i could later create a running sum for the purchase amount by
purchase date rather then by purcahse ID. The problem is that purchases from
earlier dates may have higher ID since they are entered in delay.

So... How do I created numbering for the orders?

 

[Dale Fye]

Now I understand a little better (emphasis on little). Given the structure
you posted for John, I think what you want to do is start out with TPurchase
and TPI.

Something like:

SELECT TP.ContractID, TPI.PurchaseDate, SUM(TPI.ProdQty * TPI.ProdPrice) as
DailySum,
(SELECT SUM(TPI2.ProdQty * TPI2.ProdPrice)
FROM TPurchase as TP2 INNER JOIN TPI as TPI2
ON TP2.PurchaseID = TPI2.PurchaseID
WHERE TP2.ContractID = TP.ContractID
AND TP2.PurchaseDate <= TP.PurchaseDate) as RunningSum
FROM TPurchase as TP INNER JOIN TPI
ON TP.PurchaseID = TPI.PurchaseID
GROUP BY TP.ContractID, TP.PurchaseDate

Basically, what this does is creates a query that groups by ContractID and
PurchaseID from your TPurchase table, and sums the product of the ProdQty
and ProdPrice for each of the ContractID, PurchaseDate (notice that I have
changed Date to PurchaseDate; Date is a reserved word in Access and should
not be used as a field name). After doing that, I've added a subquery to
the mix, that sums the product of ProdQty and ProdPrice for every item
purchased for that ContractID that was purcased before that date.

You will notice that in the subquery, I aliased the table names so that I
could keep track of the tables used in the outer query (TPurchase - TP and
TPI) and those used in the inner query (TPurchase = TP2, TPI = TP2).

HTH
Dale

Dale, thanks for your reply.

its really what i was afraid to find here.

I would love to jump the query section and use report directly but!

I think i cant do this in a report since the report should ONLY present
delivered orders and from specific dates (2007 only for example). BUT THE
RUNNING SUM SHOULD INCLUDE ALL ORDERS.

the contrat terms may be between April to may the next year and delivery
can
be after the term has ended. SO I CAN'T THINK OF REPORT THAT MAY CALCULATE
ORDERS NOT FROM ITS RANGE.

Since this looks like it is going to be in a report, I wouldn't even
bother
creating the [RunningSum] value in the query, you can do it in the
report.
Using the query you have, create a report that groups by contractID and
sorts
by PurchaseDate within the ContractID.

Add the fields to your report, but add the [Purchase Amount] field twice.
Right click on the second [Purchase Amount] textbox, select Properties,
and
in the property dialogs data tab, select "Over Group" in the Running Sum
property.

Run your report.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hi,

My query gets date from several tables: Tpurchase, TProducts,
TContract,
TBuyer...
After ordering the query data by Contract ID (many contracts to one
dealer)
and by purchase date (many purchases to one contract), I would like to
add
another field as raw number.
This is so i could later create a running sum for the purchase amount
by
purchase date rather then by purcahse ID. The problem is that purchases
from
earlier dates may have higher ID since they are entered in delay.

So... How do I created numbering for the orders?