G
Guest
friends,
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
M
Michel Walsh
Q2:
SELECT a.date, a.lotNumber, a.subLotNumber,
SUM(a.HMAplacedToDay) As placedToday,
SUM(b.HMAplacesToDay) As placedToDate
FROM HMAdetail AS a INNER JOIN HMAdetail AS b
ON a.lotNumber=b.lotNumber AND a.subLotNumber=b.subLotNumber AND
a.date >= b.date
GROUP BY a.date, a.lotNumber, a.subLotNumber
Hoping it may help,
Vanderghast, Access MVP
SELECT a.date, a.lotNumber, a.subLotNumber,
SUM(a.HMAplacedToDay) As placedToday,
SUM(b.HMAplacesToDay) As placedToDate
FROM HMAdetail AS a INNER JOIN HMAdetail AS b
ON a.lotNumber=b.lotNumber AND a.subLotNumber=b.subLotNumber AND
a.date >= b.date
GROUP BY a.date, a.lotNumber, a.subLotNumber
Hoping it may help,
Vanderghast, Access MVP
Andy said:friends,Q.1: I have table "HMADetail" That has fields as
Date Lot# SubLot# HMAPlacedToDay HMAPlacedToDate .......
(1) (2) (3) (4) (5)
I Have not created Query table from this table but created report with
fields (4) and (5) same. I set property for Field (5) with Running Sum
Overall. This report works good. But it writes all records from begining
date
to end date. I need The end date records only. How do I get this
without query table?
Q2: If I have to creat query table then in calculatd fild (5) what should
I
write?
I tride with HMAPlacedToDate:
DSum("HMAPlacedToDay","HMADetail","[Lot#]&[SubLot#]="&[Lot#]&[SubLot#])
This doesn't work.
Andy
G
Guest
Michel,
Thank you verymuch for replaying me.
The Query you Suggested In which you entered a. and b. at the field's name
? and where should I write this query? I have only one table.
Do I have to add twice in query my HMA Detail Table with a and b name?
Andy
Thank you verymuch for replaying me.
The Query you Suggested In which you entered a. and b. at the field's name
? and where should I write this query? I have only one table.
Do I have to add twice in query my HMA Detail Table with a and b name?
Andy
Michel Walsh said:Q2:
SELECT a.date, a.lotNumber, a.subLotNumber,
SUM(a.HMAplacedToDay) As placedToday,
SUM(b.HMAplacesToDay) As placedToDate
FROM HMAdetail AS a INNER JOIN HMAdetail AS b
ON a.lotNumber=b.lotNumber AND a.subLotNumber=b.subLotNumber AND
a.date >= b.date
GROUP BY a.date, a.lotNumber, a.subLotNumber
Hoping it may help,
Vanderghast, Access MVP
Andy said:friends,Q.1: I have table "HMADetail" That has fields as
Date Lot# SubLot# HMAPlacedToDay HMAPlacedToDate .......
(1) (2) (3) (4) (5)
I Have not created Query table from this table but created report with
fields (4) and (5) same. I set property for Field (5) with Running Sum
Overall. This report works good. But it writes all records from begining
date
to end date. I need The end date records only. How do I get this
without query table?
Q2: If I have to creat query table then in calculatd fild (5) what should
I
write?
I tride with HMAPlacedToDate:
DSum("HMAPlacedToDay","HMADetail","[Lot#]&[SubLot#]="&[Lot#]&[SubLot#])
This doesn't work.
Andy
M
Michel Walsh
You write the code in a SQL view of a new query.
a and b are aliases (other names) for the table, kind of two trackers
for the records of the table, but such that at any moment, the trackers must
be such that: they each "point" on (different) rows but such that they have
same value for lotNumber, subLotNumber, but also that the date, pointed by
"b", is a date that occurred before the one pointed by "a".
You can do it graphically: bring the table HMAdetail twice, one will get an
_1 appended to its name. Next, join both references through their common
field lotNumber and SubLotNumber. Change the query type to a total query
(click on the summation button on the toolbar). A new line appear in the
grid.
Bring the date field from the first reference, into the grid, keep the
proposed Group By. Do the same for the field lotNumber and subLotNumber.
Bring the value of the HMAplacedToday into the grid, change the proposed
Group By to SUM.
Bring the value of the HMAplacedToday from the second reference, the one
with the _1 appended to its name, in the grid, change Group By to SUM.
Bring the date from the second reference, change the GROUP BY to WHERE, and
at the criteria line: <= [HMAdetail].[date]. That last criteria insure us
that the second reference is about all data that occurred 'up to' the date
of the first table reference. (Note: if the table name is NOT HMAdetail,
use the real table name, for the criteria just mentioned).
That should be about it.
Hoping it may help,
Vanderghast, Access MVP
a and b are aliases (other names) for the table, kind of two trackers
for the records of the table, but such that at any moment, the trackers must
be such that: they each "point" on (different) rows but such that they have
same value for lotNumber, subLotNumber, but also that the date, pointed by
"b", is a date that occurred before the one pointed by "a".
You can do it graphically: bring the table HMAdetail twice, one will get an
_1 appended to its name. Next, join both references through their common
field lotNumber and SubLotNumber. Change the query type to a total query
(click on the summation button on the toolbar). A new line appear in the
grid.
Bring the date field from the first reference, into the grid, keep the
proposed Group By. Do the same for the field lotNumber and subLotNumber.
Bring the value of the HMAplacedToday into the grid, change the proposed
Group By to SUM.
Bring the value of the HMAplacedToday from the second reference, the one
with the _1 appended to its name, in the grid, change Group By to SUM.
Bring the date from the second reference, change the GROUP BY to WHERE, and
at the criteria line: <= [HMAdetail].[date]. That last criteria insure us
that the second reference is about all data that occurred 'up to' the date
of the first table reference. (Note: if the table name is NOT HMAdetail,
use the real table name, for the criteria just mentioned).
That should be about it.
Hoping it may help,
Vanderghast, Access MVP
Andy said:Michel,
Thank you verymuch for replaying me.
The Query you Suggested In which you entered a. and b. at the field's
name
? and where should I write this query? I have only one table.
Do I have to add twice in query my HMA Detail Table with a and b name?
Andy
Michel Walsh said:Q2:
SELECT a.date, a.lotNumber, a.subLotNumber,
SUM(a.HMAplacedToDay) As placedToday,
SUM(b.HMAplacesToDay) As placedToDate
FROM HMAdetail AS a INNER JOIN HMAdetail AS b
ON a.lotNumber=b.lotNumber AND a.subLotNumber=b.subLotNumber AND
a.date >= b.date
GROUP BY a.date, a.lotNumber, a.subLotNumber
Hoping it may help,
Vanderghast, Access MVP
Andy said:friends,
Q.1: I have table "HMADetail" That has fields as
Date Lot# SubLot# HMAPlacedToDay HMAPlacedToDate .......
(1) (2) (3) (4) (5)
I Have not created Query table from this table but created report
with
fields (4) and (5) same. I set property for Field (5) with Running Sum
Overall. This report works good. But it writes all records from
begining
date
to end date. I need The end date records only. How do I get this
without query table?
Q2: If I have to creat query table then in calculatd fild (5) what
should
I
write?
I tride with HMAPlacedToDate:
DSum("HMAPlacedToDay","HMADetail","[Lot#]&[SubLot#]="&[Lot#]&[SubLot#])
This doesn't work.
Andy
G
Guest
Michel,
Thanks Again.
This query also doesn't work!!!!!!!!!
Let me put in Example what I need Ok.
Date Lot# SubLot# HMAPlacedToDay HMAPlacedToDate
Apr 01 1 1 500 500
apr 01 1 2 500 1000
apr 01 1 3 500 1500
apr 01 1 4 500 2000
apr 01 1 5 300 2300
apr 02 1 5 200 2500
apr 02 1 6 500 3000
apr 02 1 7 500 3500
HMAPlacedTDate is just like a Running sum in report. In report I get this
reasult but it gives me all records. I only need to see records for the Last
Date only. Thats why I need to creat query to achive Running sum in
HMAPlacedToDate.
I hope U can make solution for this. I am trying this for last 3 months !!
Can't Figured out !!
Andy
Thanks Again.
This query also doesn't work!!!!!!!!!
Let me put in Example what I need Ok.
Date Lot# SubLot# HMAPlacedToDay HMAPlacedToDate
Apr 01 1 1 500 500
apr 01 1 2 500 1000
apr 01 1 3 500 1500
apr 01 1 4 500 2000
apr 01 1 5 300 2300
apr 02 1 5 200 2500
apr 02 1 6 500 3000
apr 02 1 7 500 3500
HMAPlacedTDate is just like a Running sum in report. In report I get this
reasult but it gives me all records. I only need to see records for the Last
Date only. Thats why I need to creat query to achive Running sum in
HMAPlacedToDate.
I hope U can make solution for this. I am trying this for last 3 months !!
Can't Figured out !!
Andy
Michel Walsh said:You write the code in a SQL view of a new query.
a and b are aliases (other names) for the table, kind of two trackers
for the records of the table, but such that at any moment, the trackers must
be such that: they each "point" on (different) rows but such that they have
same value for lotNumber, subLotNumber, but also that the date, pointed by
"b", is a date that occurred before the one pointed by "a".
You can do it graphically: bring the table HMAdetail twice, one will get an
_1 appended to its name. Next, join both references through their common
field lotNumber and SubLotNumber. Change the query type to a total query
(click on the summation button on the toolbar). A new line appear in the
grid.
Bring the date field from the first reference, into the grid, keep the
proposed Group By. Do the same for the field lotNumber and subLotNumber.
Bring the value of the HMAplacedToday into the grid, change the proposed
Group By to SUM.
Bring the value of the HMAplacedToday from the second reference, the one
with the _1 appended to its name, in the grid, change Group By to SUM.
Bring the date from the second reference, change the GROUP BY to WHERE, and
at the criteria line: <= [HMAdetail].[date]. That last criteria insure us
that the second reference is about all data that occurred 'up to' the date
of the first table reference. (Note: if the table name is NOT HMAdetail,
use the real table name, for the criteria just mentioned).
That should be about it.
Hoping it may help,
Vanderghast, Access MVP
Andy said:Michel,
Thank you verymuch for replaying me.
The Query you Suggested In which you entered a. and b. at the field's
name
? and where should I write this query? I have only one table.
Do I have to add twice in query my HMA Detail Table with a and b name?
Andy
Michel Walsh said:Q2:
SELECT a.date, a.lotNumber, a.subLotNumber,
SUM(a.HMAplacedToDay) As placedToday,
SUM(b.HMAplacesToDay) As placedToDate
FROM HMAdetail AS a INNER JOIN HMAdetail AS b
ON a.lotNumber=b.lotNumber AND a.subLotNumber=b.subLotNumber AND
a.date >= b.date
GROUP BY a.date, a.lotNumber, a.subLotNumber
Hoping it may help,
Vanderghast, Access MVP
friends,
Q.1: I have table "HMADetail" That has fields as
Date Lot# SubLot# HMAPlacedToDay HMAPlacedToDate .......
(1) (2) (3) (4) (5)
I Have not created Query table from this table but created report
with
fields (4) and (5) same. I set property for Field (5) with Running Sum
Overall. This report works good. But it writes all records from
begining
date
to end date. I need The end date records only. How do I get this
without query table?
Q2: If I have to creat query table then in calculatd fild (5) what
should
I
write?
I tride with HMAPlacedToDate:
DSum("HMAPlacedToDay","HMADetail","[Lot#]&[SubLot#]="&[Lot#]&[SubLot#])
This doesn't work.
Andy
M
Michel Walsh
Ah, you don't *restart* the count at 0 for each subLotNumber.
SELECT a.date, a.lotNumber, a.subLotNumber,
SUM(a.HMAplacedToDay) As placedToday,
SUM(b.HMAplacesToDay) As placedToDate
FROM HMAdetail AS a INNER JOIN HMAdetail AS b
ON a.lotNumber=b.lotNumber
AND (a.date > b.date
OR
(a.date = b.date
AND a.subLotNumber >= b.subLotNumber))
GROUP BY a.date ASC, a.lotNumber ASC, a.subLotNumber ASC
so the sum of placedToDate would include anything, for a given lot number,
that occurred in the past, OR, if it occurred today, only if its
subLotNumber is smaller.
To read the query, start with the GROUP BY: each triple ( a.date,
a.lotNumber and a.subLotNumber ) supplies a row in the result. For such a
given row, b will be the records in the same table, but such that the ON
clause is respected. SO, b refers to what has to be considered having
occurred before the actual given row, and thus, SUM(b.HMAplacesToDay)
produces, indeed, the running sum.
Hoping it may help,
Vanderghast, Access MVP
SELECT a.date, a.lotNumber, a.subLotNumber,
SUM(a.HMAplacedToDay) As placedToday,
SUM(b.HMAplacesToDay) As placedToDate
FROM HMAdetail AS a INNER JOIN HMAdetail AS b
ON a.lotNumber=b.lotNumber
AND (a.date > b.date
OR
(a.date = b.date
AND a.subLotNumber >= b.subLotNumber))
GROUP BY a.date ASC, a.lotNumber ASC, a.subLotNumber ASC
so the sum of placedToDate would include anything, for a given lot number,
that occurred in the past, OR, if it occurred today, only if its
subLotNumber is smaller.
To read the query, start with the GROUP BY: each triple ( a.date,
a.lotNumber and a.subLotNumber ) supplies a row in the result. For such a
given row, b will be the records in the same table, but such that the ON
clause is respected. SO, b refers to what has to be considered having
occurred before the actual given row, and thus, SUM(b.HMAplacesToDay)
produces, indeed, the running sum.
Hoping it may help,
Vanderghast, Access MVP
Andy said:Michel,
Thanks Again.
This query also doesn't work!!!!!!!!!
Let me put in Example what I need Ok.
Date Lot# SubLot# HMAPlacedToDay HMAPlacedToDate
Apr 01 1 1 500
500
apr 01 1 2 500
1000
apr 01 1 3 500
1500
apr 01 1 4 500
2000
apr 01 1 5 300
2300
apr 02 1 5 200
2500
apr 02 1 6 500
3000
apr 02 1 7 500
3500
HMAPlacedTDate is just like a Running sum in report. In report I get this
reasult but it gives me all records. I only need to see records for the
Last
Date only. Thats why I need to creat query to achive Running sum in
HMAPlacedToDate.
I hope U can make solution for this. I am trying this for last 3 months !!
Can't Figured out !!
Andy
Michel Walsh said:You write the code in a SQL view of a new query.
a and b are aliases (other names) for the table, kind of two
trackers
for the records of the table, but such that at any moment, the trackers
must
be such that: they each "point" on (different) rows but such that they
have
same value for lotNumber, subLotNumber, but also that the date, pointed
by
"b", is a date that occurred before the one pointed by "a".
You can do it graphically: bring the table HMAdetail twice, one will get
an
_1 appended to its name. Next, join both references through their common
field lotNumber and SubLotNumber. Change the query type to a total query
(click on the summation button on the toolbar). A new line appear in the
grid.
Bring the date field from the first reference, into the grid, keep the
proposed Group By. Do the same for the field lotNumber and subLotNumber.
Bring the value of the HMAplacedToday into the grid, change the proposed
Group By to SUM.
Bring the value of the HMAplacedToday from the second reference, the one
with the _1 appended to its name, in the grid, change Group By to SUM.
Bring the date from the second reference, change the GROUP BY to WHERE,
and
at the criteria line: <= [HMAdetail].[date]. That last criteria insure
us
that the second reference is about all data that occurred 'up to' the
date
of the first table reference. (Note: if the table name is NOT
HMAdetail,
use the real table name, for the criteria just mentioned).
That should be about it.
Hoping it may help,
Vanderghast, Access MVP
Andy said:Michel,
Thank you verymuch for replaying me.
The Query you Suggested In which you entered a. and b. at the field's
name
? and where should I write this query? I have only one table.
Do I have to add twice in query my HMA Detail Table with a and b name?
Andy
:
Q2:
SELECT a.date, a.lotNumber, a.subLotNumber,
SUM(a.HMAplacedToDay) As placedToday,
SUM(b.HMAplacesToDay) As placedToDate
FROM HMAdetail AS a INNER JOIN HMAdetail AS b
ON a.lotNumber=b.lotNumber AND a.subLotNumber=b.subLotNumber
AND
a.date >= b.date
GROUP BY a.date, a.lotNumber, a.subLotNumber
Hoping it may help,
Vanderghast, Access MVP
friends,
Q.1: I have table "HMADetail" That has fields as
Date Lot# SubLot# HMAPlacedToDay HMAPlacedToDate .......
(1) (2) (3) (4) (5)
I Have not created Query table from this table but created
report
with
fields (4) and (5) same. I set property for Field (5) with Running
Sum
Overall. This report works good. But it writes all records from
begining
date
to end date. I need The end date records only. How do I get this
without query table?
Q2: If I have to creat query table then in calculatd fild (5) what
should
I
write?
I tride with HMAPlacedToDate:
DSum("HMAPlacedToDay","HMADetail","[Lot#]&[SubLot#]="&[Lot#]&[SubLot#])
This doesn't work.
Andy
G
Guest
Michel,
I appreciate your time and Help.
Any of these query didn't work. So I gave up. But If you have time pl try
with the Example Table I send before and see how can we get reasult through
Query.
Thanks again.
Andy
I appreciate your time and Help.
Any of these query didn't work. So I gave up. But If you have time pl try
with the Example Table I send before and see how can we get reasult through
Query.
Thanks again.
Andy
Michel Walsh said:Ah, you don't *restart* the count at 0 for each subLotNumber.
SELECT a.date, a.lotNumber, a.subLotNumber,
SUM(a.HMAplacedToDay) As placedToday,
SUM(b.HMAplacesToDay) As placedToDate
FROM HMAdetail AS a INNER JOIN HMAdetail AS b
ON a.lotNumber=b.lotNumber
AND (a.date > b.date
OR
(a.date = b.date
AND a.subLotNumber >= b.subLotNumber))
GROUP BY a.date ASC, a.lotNumber ASC, a.subLotNumber ASC
so the sum of placedToDate would include anything, for a given lot number,
that occurred in the past, OR, if it occurred today, only if its
subLotNumber is smaller.
To read the query, start with the GROUP BY: each triple ( a.date,
a.lotNumber and a.subLotNumber ) supplies a row in the result. For such a
given row, b will be the records in the same table, but such that the ON
clause is respected. SO, b refers to what has to be considered having
occurred before the actual given row, and thus, SUM(b.HMAplacesToDay)
produces, indeed, the running sum.
Hoping it may help,
Vanderghast, Access MVP
Andy said:Michel,
Thanks Again.
This query also doesn't work!!!!!!!!!
Let me put in Example what I need Ok.
Date Lot# SubLot# HMAPlacedToDay HMAPlacedToDate
Apr 01 1 1 500
500
apr 01 1 2 500
1000
apr 01 1 3 500
1500
apr 01 1 4 500
2000
apr 01 1 5 300
2300
apr 02 1 5 200
2500
apr 02 1 6 500
3000
apr 02 1 7 500
3500
HMAPlacedTDate is just like a Running sum in report. In report I get this
reasult but it gives me all records. I only need to see records for the
Last
Date only. Thats why I need to creat query to achive Running sum in
HMAPlacedToDate.
I hope U can make solution for this. I am trying this for last 3 months !!
Can't Figured out !!
Andy
Michel Walsh said:You write the code in a SQL view of a new query.
a and b are aliases (other names) for the table, kind of two
trackers
for the records of the table, but such that at any moment, the trackers
must
be such that: they each "point" on (different) rows but such that they
have
same value for lotNumber, subLotNumber, but also that the date, pointed
by
"b", is a date that occurred before the one pointed by "a".
You can do it graphically: bring the table HMAdetail twice, one will get
an
_1 appended to its name. Next, join both references through their common
field lotNumber and SubLotNumber. Change the query type to a total query
(click on the summation button on the toolbar). A new line appear in the
grid.
Bring the date field from the first reference, into the grid, keep the
proposed Group By. Do the same for the field lotNumber and subLotNumber.
Bring the value of the HMAplacedToday into the grid, change the proposed
Group By to SUM.
Bring the value of the HMAplacedToday from the second reference, the one
with the _1 appended to its name, in the grid, change Group By to SUM.
Bring the date from the second reference, change the GROUP BY to WHERE,
and
at the criteria line: <= [HMAdetail].[date]. That last criteria insure
us
that the second reference is about all data that occurred 'up to' the
date
of the first table reference. (Note: if the table name is NOT
HMAdetail,
use the real table name, for the criteria just mentioned).
That should be about it.
Hoping it may help,
Vanderghast, Access MVP
Michel,
Thank you verymuch for replaying me.
The Query you Suggested In which you entered a. and b. at the field's
name
? and where should I write this query? I have only one table.
Do I have to add twice in query my HMA Detail Table with a and b name?
Andy
:
Q2:
SELECT a.date, a.lotNumber, a.subLotNumber,
SUM(a.HMAplacedToDay) As placedToday,
SUM(b.HMAplacesToDay) As placedToDate
FROM HMAdetail AS a INNER JOIN HMAdetail AS b
ON a.lotNumber=b.lotNumber AND a.subLotNumber=b.subLotNumber
AND
a.date >= b.date
GROUP BY a.date, a.lotNumber, a.subLotNumber
Hoping it may help,
Vanderghast, Access MVP
friends,
Q.1: I have table "HMADetail" That has fields as
Date Lot# SubLot# HMAPlacedToDay HMAPlacedToDate .......
(1) (2) (3) (4) (5)
I Have not created Query table from this table but created
report
with
fields (4) and (5) same. I set property for Field (5) with Running
Sum
Overall. This report works good. But it writes all records from
begining
date
to end date. I need The end date records only. How do I get this
without query table?
Q2: If I have to creat query table then in calculatd fild (5) what
should
I
write?
I tride with HMAPlacedToDate:
DSum("HMAPlacedToDay","HMADetail","[Lot#]&[SubLot#]="&[Lot#]&[SubLot#])
This doesn't work.
Andy
M
Michel Walsh
Your date field is a date and time data type, not a string, isn't it? I am personally using yyyy.mm.dd format.
Example Date Lot SubLot Placed
2007.04.01 1 1 500
2007.04.01 1 2 500
2007.04.03 1 3 500
2007.04.01 1 4 500
2007.04.01 1 5 300
2007.04.02 1 5 200
2007.04.02 1 6 500
2007.04.02 1 7 500
Query73 date lot subLot placedToday placedToDate
2007.04.01 1 1 500 500
2007.04.01 1 2 1000 1000
2007.04.01 1 4 1500 1500
2007.04.01 1 5 1200 1800
2007.04.02 1 5 1000 2000
2007.04.02 1 6 3000 2500
2007.04.02 1 7 3500 3000
2007.04.03 1 3 4000 3500
--------------------------------------------------------------------
SELECT a.date, a.lot, a.subLot,
SUM(a.placed) As placedToday,
SUM(b.placed) As placedToDate
FROM Example AS a INNER JOIN Example AS b
ON a.lot=b.lot
AND (a.date > b.date
OR
(a.date = b.date
AND a.subLot >= b.subLot))
GROUP BY a.date, a.lot, a.sublot
Example Date Lot SubLot Placed
2007.04.01 1 1 500
2007.04.01 1 2 500
2007.04.03 1 3 500
2007.04.01 1 4 500
2007.04.01 1 5 300
2007.04.02 1 5 200
2007.04.02 1 6 500
2007.04.02 1 7 500
Query73 date lot subLot placedToday placedToDate
2007.04.01 1 1 500 500
2007.04.01 1 2 1000 1000
2007.04.01 1 4 1500 1500
2007.04.01 1 5 1200 1800
2007.04.02 1 5 1000 2000
2007.04.02 1 6 3000 2500
2007.04.02 1 7 3500 3000
2007.04.03 1 3 4000 3500
--------------------------------------------------------------------
SELECT a.date, a.lot, a.subLot,
SUM(a.placed) As placedToday,
SUM(b.placed) As placedToDate
FROM Example AS a INNER JOIN Example AS b
ON a.lot=b.lot
AND (a.date > b.date
OR
(a.date = b.date
AND a.subLot >= b.subLot))
GROUP BY a.date, a.lot, a.sublot