Compare and substract for Difference in Access 97

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

Guest

Hi Guys:

I am running three types of queries, one in run on daily, one is run weekly
and the other is run monthly. I think, that these would be just graduations
of the other. Now, What I need is to run a query or queries (one showing the
previous day and one showing current days values, then, one showing the
previous week and one showing current week values and one showing the
previous Monrth and one showing current Month values). What would be the best
mathod of doing or getting this accomplished?

The daily dates are as follows: 14-Oct-05 and I have a global function being
used to set the criteria called: Between RetrieveStartDateKey() And
RetrieveEndDateKey(). I need to run the previous and current week values
simultaneously to extract the difference in vuales.

How do I do that? Should I run the two queries as dsum(s) individualy and do
it that way? I am wide open for suggestions.

Thanks, looking forward to your input......
 
Sorry guys:

The dates and values are as follows: Date 14-Oct-05, Values $ 56.00, the
previous dates data values are as follows: Date 13-Oct-05, Values $
21.00. I need to show the differnce as being $35.00 dollars between the two
dates. Hope this helps...

Thanks,
 
Hi,



Assume you have three fields, Account, Amount and Sequence (Sequence can be
day, week, or a plain number, but without duplicated value).

For each record, you want to find the record for the same Account, but with
the Sequence value that precede the actual one. The "gap" between the two
sequence values can be different than 1.00.

Here is a solution.


SELECT a.account, a.amount, a.sequence, b.amount, b.sequence


would be what we want get, "a" being the actual record, "b" being the
previous record. Sure, both records come from the same table:


FROM myTable As a LEFT JOIN myTable As b ON a.account=b.account AND
a.sequence > b.sequence



is expected. We use an outer join since the "first" record, in a sequence,
won't have any "previous" record. An inner join would have remove that
record.

Now, there is a problem, since we pump ALL the previous record ( b.sequence
< a.sequence occurs multiple time). We want b.sequence equal to the maximum
possible value of sequence, for this group:


HAVING b.sequence = MAX(c.sequence)

but the NULL value creates a problem, that we solve with


HAVING COUNT(*)=1 OR b.sequence = MAX(c.sequence)


"c" is not define, but, as we have explained, it is "as" "b":

FROM (myTable As a LEFT JOIN myTable As b ON a.account=b.account AND
a.sequence > b.sequence)
LEFT JOIN myTable AS c ON a.account=c.account AND a.sequence >
c.sequence


where we can see that the FROM clause does not really differentiate "b" from
"c", so both "references" reach the same records. While we will keep "b" to
pump sequence and amount, c is only use to get the MAX value for sequence.

Since we invoke MAX, we need to define our groups:


GROUP BY a.account, a.sequence. b.sequence


and since the amount fields do not really make part of the groups, we have
to aggregate them. I personally use LAST in such cases, but MS SQL Server
user could use MIN or MAX for the same purpose, here, in this particular
case. So the overall query is:

=======================
SELECT a.account, LAST(a.amount), a.sequence, LAST(b.amount), b.sequence
FROM (myTable As a LEFT JOIN myTable As b ON a.account=b.account AND
a.sequence > b.sequence)
LEFT JOIN myTable AS c ON a.account=c.account AND a.sequence >
c.sequence
GROUP BY a.account, a.sequence. b.sequence
HAVING COUNT(*)=1 OR b.sequence = MAX(c.sequence)
=======================



Hoping it may help,
Vanderghast, Access MVP
 
Thanks Michel, however, I'm at a bit of a loss following your logic. Below is
a copy of the (query) SQL statement containing the data for September 26 and
27th. See if you can follow it and build it as you mentioned. That, would be
a great help to me. If you have any questions, please post back. Here is the
SQL as it is:
=============
SELECT
tbl_Product_BrandyInfoData_Collection_Temp.ctr_Product_BrandyInfoData_Key,
tbl_Product_BrandyInfoData_Collection_Temp.lng_Customer_Track_fkey,
tbl_Product_BrandyInfoData_Collection_Temp.lng_Liquor_Type_fkey,
tbl_Product_BrandyInfoData_Collection_Temp.lng_Shift_Rotation_fKey,
tbl_Product_BrandyInfoData_Collection_Temp.lng_Customer_Station_fkey,
tbl_Product_BrandyInfoData_Collection_Temp.dte_AuditDte,
tbl_Product_BrandyInfoData_Collection_Temp.tme_AuditTme,
tbl_Product_BrandyInfoData_Collection_Temp.chkBx_StockAdd,
tbl_Product_BrandyInfoData_Collection_Temp.num_Btles_Added,
tbl_Product_BrandyInfoData_Collection_Temp.dte_Stk_AddDte,
tbl_Product_BrandyInfoData_Collection_Temp.num_BtlsOnHand,
tbl_Product_BrandyInfoData_Collection_Temp.num_Liter_Qty,
[num_Liter_Qty]*33.81402 AS NetLiquidOunces,
tbl_Product_BrandyInfoData_Collection_Temp.curr_PlusAdds,
tbl_Product_BrandyInfoData_Collection_Temp.curr_MinusAdds,
tbl_Product_BrandyInfoData_Collection_Temp.str_Par_Level,
tbl_Product_BrandyInfoData_Collection_Temp.str_Btl_Price,
IIf(IsNull([str_Btl_Price]),0,CCur([str_Btl_Price])) AS BottlePrice,
tbl_Product_BrandyInfoData_Collection_Temp.str_Btl_Size,
Format(IIf(IsNull([str_Btl_Size]),Null,CDbl([str_Btl_Size])),"0.00") AS
BottleSize, tbl_Product_BrandyInfoData_Collection_Temp.str_Tare_Wt,
tbl_Product_BrandyInfoData_Collection_Temp.str_Shot_Size,
tbl_Product_BrandyInfoData_Collection_Temp.str_Sell_Price,
IIf(IsNull([str_Shot_Size]),0,CDbl([str_Shot_Size])) AS ShotSize,
[BottleSize]/[ShotSize] AS NumOfShots, [BottlePrice]/[NumOfShots] AS
CostPerShot, [BottlePrice]/[BottleSize] AS CostPerOz,
([NumOfShots]*[str_Sell_Price])*[num_BtlsOnHand] AS ActualOnHndSales,
([NumOfShots]*[str_Sell_Price])*[str_Par_Level] AS PotentialSales,
[str_Par_Level]-[num_BtlsOnHand] AS [Re-OrderQty],
[Re-OrderQty]*[str_Btl_Price] AS OrderValue
FROM tbl_Product_BrandyInfoData_Collection_Temp
WHERE
(((tbl_Product_BrandyInfoData_Collection_Temp.lng_Customer_Track_fkey)=RetrieveReportingCustomerKey())
AND ((tbl_Product_BrandyInfoData_Collection_Temp.dte_AuditDte) Between
RetrieveStartDateKey() And RetrieveEndDateKey()))
ORDER BY
tbl_Product_BrandyInfoData_Collection_Temp.ctr_Product_BrandyInfoData_Key;
===============
This is the field name in the table in which dates are set: "dte_AuditDte"
and "ActualOnHndSales" would contain the dollar values for that day or
previous day's value respectively.

I appreciate all the asistance on this. This SQL string may explain a better
what I'm trying to accomplish.

Thanks, again!
 
Hi,


Sorry for the delay, last day was quite busy.


Long table name make long SQL statement, isn't it?

so, basically I just replace amount with ActualOnHndSales, sequence with
dte_AuditDte and account with a.ctr_Product_BrandyInfoData_Key, and,
finally, myTable by yourQueryName. So, unless I boob:

========================

SELECT a.ctr_Product_BrandyInfoData_Key,
LAST(a.ActualOnHndSales),
a.dte_AuditDte,
LAST(b.ActualOnHndSales),
b.dte_AuditDte
FROM (yourQueryName As a LEFT JOIN yourQueryName As b
ON a.ActualOnHndSales=b.ActualOnHndSales AND
a.dte_AuditDte> b.dte_AuditDte)
LEFT JOIN yourQueryName AS c
ON a.ActualOnHndSales=c.ActualOnHndSales AND
a.dte_AuditDte> c.dte_AuditDte
GROUP BY a.ctr_Product_BrandyInfoData_Key , a.dte_AuditDte. b.dte_AuditDte
HAVING COUNT(*)=1 OR b.dte_AuditDte= MAX(c.dte_AuditDte)

=========================


Hoping it may help,
Vanderghast, Access MVP


Jay said:
Thanks Michel, however, I'm at a bit of a loss following your logic. Below
is
a copy of the (query) SQL statement containing the data for September 26
and
27th. See if you can follow it and build it as you mentioned. That, would
be
a great help to me. If you have any questions, please post back. Here is
the
SQL as it is:
=============
SELECT
tbl_Product_BrandyInfoData_Collection_Temp.ctr_Product_BrandyInfoData_Key,
tbl_Product_BrandyInfoData_Collection_Temp.lng_Customer_Track_fkey,
tbl_Product_BrandyInfoData_Collection_Temp.lng_Liquor_Type_fkey,
tbl_Product_BrandyInfoData_Collection_Temp.lng_Shift_Rotation_fKey,
tbl_Product_BrandyInfoData_Collection_Temp.lng_Customer_Station_fkey,
tbl_Product_BrandyInfoData_Collection_Temp.dte_AuditDte,
tbl_Product_BrandyInfoData_Collection_Temp.tme_AuditTme,
tbl_Product_BrandyInfoData_Collection_Temp.chkBx_StockAdd,
tbl_Product_BrandyInfoData_Collection_Temp.num_Btles_Added,
tbl_Product_BrandyInfoData_Collection_Temp.dte_Stk_AddDte,
tbl_Product_BrandyInfoData_Collection_Temp.num_BtlsOnHand,
tbl_Product_BrandyInfoData_Collection_Temp.num_Liter_Qty,
[num_Liter_Qty]*33.81402 AS NetLiquidOunces,
tbl_Product_BrandyInfoData_Collection_Temp.curr_PlusAdds,
tbl_Product_BrandyInfoData_Collection_Temp.curr_MinusAdds,
tbl_Product_BrandyInfoData_Collection_Temp.str_Par_Level,
tbl_Product_BrandyInfoData_Collection_Temp.str_Btl_Price,
IIf(IsNull([str_Btl_Price]),0,CCur([str_Btl_Price])) AS BottlePrice,
tbl_Product_BrandyInfoData_Collection_Temp.str_Btl_Size,
Format(IIf(IsNull([str_Btl_Size]),Null,CDbl([str_Btl_Size])),"0.00") AS
BottleSize, tbl_Product_BrandyInfoData_Collection_Temp.str_Tare_Wt,
tbl_Product_BrandyInfoData_Collection_Temp.str_Shot_Size,
tbl_Product_BrandyInfoData_Collection_Temp.str_Sell_Price,
IIf(IsNull([str_Shot_Size]),0,CDbl([str_Shot_Size])) AS ShotSize,
[BottleSize]/[ShotSize] AS NumOfShots, [BottlePrice]/[NumOfShots] AS
CostPerShot, [BottlePrice]/[BottleSize] AS CostPerOz,
([NumOfShots]*[str_Sell_Price])*[num_BtlsOnHand] AS ActualOnHndSales,
([NumOfShots]*[str_Sell_Price])*[str_Par_Level] AS PotentialSales,
[str_Par_Level]-[num_BtlsOnHand] AS [Re-OrderQty],
[Re-OrderQty]*[str_Btl_Price] AS OrderValue
FROM tbl_Product_BrandyInfoData_Collection_Temp
WHERE
(((tbl_Product_BrandyInfoData_Collection_Temp.lng_Customer_Track_fkey)=RetrieveReportingCustomerKey())
AND ((tbl_Product_BrandyInfoData_Collection_Temp.dte_AuditDte) Between
RetrieveStartDateKey() And RetrieveEndDateKey()))
ORDER BY
tbl_Product_BrandyInfoData_Collection_Temp.ctr_Product_BrandyInfoData_Key;
===============
This is the field name in the table in which dates are set: "dte_AuditDte"
and "ActualOnHndSales" would contain the dollar values for that day or
previous day's value respectively.

I appreciate all the asistance on this. This SQL string may explain a
better
what I'm trying to accomplish.

Thanks, again!




Michel Walsh said:
Hi,



Assume you have three fields, Account, Amount and Sequence (Sequence can
be
day, week, or a plain number, but without duplicated value).

For each record, you want to find the record for the same Account, but
with
the Sequence value that precede the actual one. The "gap" between the two
sequence values can be different than 1.00.

Here is a solution.


SELECT a.account, a.amount, a.sequence, b.amount, b.sequence


would be what we want get, "a" being the actual record, "b" being the
previous record. Sure, both records come from the same table:


FROM myTable As a LEFT JOIN myTable As b ON a.account=b.account AND
a.sequence > b.sequence



is expected. We use an outer join since the "first" record, in a
sequence,
won't have any "previous" record. An inner join would have remove that
record.

Now, there is a problem, since we pump ALL the previous record (
b.sequence
< a.sequence occurs multiple time). We want b.sequence equal to the
maximum
possible value of sequence, for this group:


HAVING b.sequence = MAX(c.sequence)

but the NULL value creates a problem, that we solve with


HAVING COUNT(*)=1 OR b.sequence = MAX(c.sequence)


"c" is not define, but, as we have explained, it is "as" "b":

FROM (myTable As a LEFT JOIN myTable As b ON a.account=b.account AND
a.sequence > b.sequence)
LEFT JOIN myTable AS c ON a.account=c.account AND a.sequence >
c.sequence


where we can see that the FROM clause does not really differentiate "b"
from
"c", so both "references" reach the same records. While we will keep "b"
to
pump sequence and amount, c is only use to get the MAX value for
sequence.

Since we invoke MAX, we need to define our groups:


GROUP BY a.account, a.sequence. b.sequence


and since the amount fields do not really make part of the groups, we
have
to aggregate them. I personally use LAST in such cases, but MS SQL Server
user could use MIN or MAX for the same purpose, here, in this particular
case. So the overall query is:

=======================
SELECT a.account, LAST(a.amount), a.sequence, LAST(b.amount), b.sequence
FROM (myTable As a LEFT JOIN myTable As b ON a.account=b.account AND
a.sequence > b.sequence)
LEFT JOIN myTable AS c ON a.account=c.account AND a.sequence >
c.sequence
GROUP BY a.account, a.sequence. b.sequence
HAVING COUNT(*)=1 OR b.sequence = MAX(c.sequence)
=======================



Hoping it may help,
Vanderghast, Access MVP
Click to expand...
Click to expand...
 
Back
Top