Help with getting desired results

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

Guest

I am running Access 97 and need to pull the difference in dollar values
between two records based on dates, which could be daily, weekly or monthly.
Below is a current sample of the current SQL statement as it is now. As you
know "ctr_Product_BrandyInfoData_Key" would be the unique key, then,
""dte_AuditDte" would be the date sequence and finally "ActualOnHndSales"
would contain the dollar value of each days totals. This would be kind like
an inventory tracking approach to things, if it helps to give an idea of what
I'm trying to do here.

There shoule be a simple way of getting what I want, but, it's not coming to
me currently. Thus, my request for help!

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;
===============

I appreciate any or all the asistance I can get on this.

Thanks, again!
 
Jay said:
I am running Access 97 and need to pull the difference in dollar values
between two records based on dates, which could be daily, weekly or monthly.
Below is a current sample of the current SQL statement as it is now. As you
know "ctr_Product_BrandyInfoData_Key" would be the unique key, then,
""dte_AuditDte" would be the date sequence and finally "ActualOnHndSales"
would contain the dollar value of each days totals. This would be kind like
an inventory tracking approach to things, if it helps to give an idea of what
I'm trying to do here.

There shoule be a simple way of getting what I want, but, it's not coming to
me currently. Thus, my request for help!

SQL as it is:
=============
SELECT
tbl_Product_BrandyInfoData_Collection_Temp.ctr_Product_BrandyInfoData_
Key,
tbl_Product_BrandyInfoData_Collection_Temp.dte_AuditDte,
([NumOfShots]*[str_Sell_Price])*[num_BtlsOnHand] AS ActualOnHndSales,
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;
===============

I appreciate any or all the asistance I can get on this.

Thanks, again!
Click to expand...

Jay,

You want to retrive the difference between the dollar amounts in two
different records based on date ranges?

The example query only selects one record by Primary Key. What other
record are we looking for to compare with?

One record by Primary Key and the other record by date ranges?

Both records by different date ranges?


Sincerely,

Chris O.
 
Back
Top