DSumExpression

[Andy]

Hello friends,
I asked this Question for 2-3 times but nobody answered me !!!!!!

Question is I need to do Accumulated sum in ToDateQty Field from
ToDay's Qty field.

Example:

date Lot # SubLot# ToDay'sQty ToDateQty
03/27/07 1 1 500 500
03/27/07 1 2 500 1000
03/27/07 1 3 300 1300

03/28/07 1 3 200 1500
03/28/07 1 4 500 2000 and so
on...
I tried on Report with Running Sum which works good But I cannot use this
field in other calculated Field in this report. So I would like to have Query
for ToDaDateQty field. I think DSum will work but I don't know how?

Also Max subLot# is 10 ie ToDateQty is more then 5000 SubLot# should Start
from 1 again and Lot# should Increased by 1. SoWhat Criteria I Should use for
SubLot# ?

Is there any expression That without Query I can bring in all records from
Table for the Last date or Max Date ie in above Ex Date 03/28/07 both
records, in Report?

Andy

 

[Andy]

I asked this Question for 2-3 times but nobody answered me !!!!!!

Question is I need to do Accumulated sum in ToDateQty Field from
ToDay's Qty field.

Example:

date Lot # SubLot# ToDay'sQty ToDateQty
03/27/07 1 1 500 500
03/27/07 1 2 500 1000
03/27/07 1 3 300 1300

03/28/07 1 3 200 1500
03/28/07 1 4 500 2000 and so
on...
I tried on Report with Running Sum which works good But I cannot use this
field in other calculated Field in this report. So I would like to have Query
for ToDaDateQty field. I think DSum will work but I don't know how?

I answered this part yesterday. Just in case you can't find
your post or see my reply, here it is again
--------------------------------
Why can't you use the running sum text box in other
calculations?

The DSum would be something like this air code:

DSum("[ToDay'sQty]", "the table", "([date] < " &
Format([date], "\#m\/d\/yyyy\#") & ") OR ([date] = " &
Format([date], "\#m\/d\/yyyy\#") & " And [SubLot#] <= " &
[SubLot#]))

You don't really have a field named Date do you? Date is
the name of a function that returns the date the query is
executed.
------------------------------

Also Max subLot# is 10 ie ToDateQty is more then 5000 SubLot# should Start
from 1 again and Lot# should Increased by 1. SoWhat Criteria I Should use for
SubLot# ?

I don't understand what you want here. Please provide more
information.

Is there any expression That without Query I can bring in all records from
Table for the Last date or Max Date ie in above Ex Date 03/28/07 both
records, in Report?

What?? How can you "bring in" anything "without query"???

You can use this kind of query to retrieve all the records
with the latest date:

SELECT *
FROM table
WHERE [date] = (SELECT Max(X.[Date])
FROM table As X)

 

[Marshall Barton]

I asked this Question for 2-3 times but nobody answered me !!!!!!

Question is I need to do Accumulated sum in ToDateQty Field from
ToDay's Qty field.

Example:

date Lot # SubLot# ToDay'sQty ToDateQty
03/27/07 1 1 500 500
03/27/07 1 2 500 1000
03/27/07 1 3 300 1300

03/28/07 1 3 200 1500
03/28/07 1 4 500 2000 and so
on...
I tried on Report with Running Sum which works good But I cannot use this
field in other calculated Field in this report. So I would like to have Query
for ToDaDateQty field. I think DSum will work but I don't know how?

I answered this part yesterday. Just in case you can't find
your post or see my reply, here it is again
--------------------------------
Why can't you use the running sum text box in other
calculations?

The DSum would be something like this air code:

DSum("[ToDay'sQty]", "the table", "([date] < " &
Format([date], "\#m\/d\/yyyy\#") & ") OR ([date] = " &
Format([date], "\#m\/d\/yyyy\#") & " And [SubLot#] <= " &
[SubLot#]))

You don't really have a field named Date do you? Date is
the name of a function that returns the date the query is
executed.
------------------------------

Also Max subLot# is 10 ie ToDateQty is more then 5000 SubLot# should Start
from 1 again and Lot# should Increased by 1. SoWhat Criteria I Should use for
SubLot# ?

I don't understand what you want here. Please provide more
information.

Is there any expression That without Query I can bring in all records from
Table for the Last date or Max Date ie in above Ex Date 03/28/07 both
records, in Report?

What?? How can you "bring in" anything "without query"???

You can use this kind of query to retrieve all the records
with the latest date:

SELECT *
FROM table
WHERE [date] = (SELECT Max(X.[Date])
FROM table As X)