Reference Previous Record

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

Guest

I'm trying to calculate a backlog (Amount of previous days backlog + amount
received today - Amount processed today= Backlog). Currently I have a query
that calculates amount received - amount processed, but I need it to
reference the previous days backlog to add that to the equation. Is that
possible?
 
Yes, it is possible, but how you do it will depend on your table structure
and what you want to do with the information? How do you want to display
this information; in the results of a query, in a cell on a form, or some
other method.
 
Still need to know your table structure (table name, field names) to help you
write the queries for what you want.

Do you want this value on a row for every date? What happens when your
backlog goes away and you produce more than you need, do you want to track
InStock as well? Something like?

Date Received_Today Processed_Today Backlog InStock

Dale
 
Table Structure: Returns Table
Sign Out Date, Sign Out Time, Sign In Date, Sign In Time, Warehouse,
Received in Warehouse, Received in Customer Service, Batch Number, Amount
Received, Batch Type, Agent Name, Amount Filed, Amount Sent to Accounting,
Amound Pended

Yes, the value on the row for each date. If there is no backlog from the
prior day that it should read 0 and there will only be a backlog the next day
if the amount received that day is not processed.
 
A couple more questions.

1. I see you have a Warehouse field in your table. Will the report you
generate be for a single warehouse, or multiple warehouses, each on a
separate "page"?

2. For your report, I assume that you will be entering a range of dates
(StartDate, EndDate) on a form to run this report. Is that a valid
assumption?

3. Which fields are you trying to track as your received and processed
columns?

Dale
 
See below

Dale Fye said:
A couple more questions.

1. I see you have a Warehouse field in your table. Will the report you
generate be for a single warehouse, or multiple warehouses, each on a
separate "page"?
Click to expand...

For this report the warehouse detail will not appear, I'm looking for a roll
up sum of all returns received regardless of warehouse. A separate report
will break down the amount received per warehouse.
2. For your report, I assume that you will be entering a range of dates
(StartDate, EndDate) on a form to run this report. Is that a valid
assumption?
Click to expand...

Yes, I would like the report to show a weekly snap shot, or at minimum a
daily report.
3. Which fields are you trying to track as your received and processed
columns?
Click to expand...
Received - Received in Customer Service (Date) and Amount Received (sum of
all received that date).
Processed - Sign In Date (Date Return was processed) and Amount Filed +
Amount Sent to Accounting + Amount Pended (Sum of these three equals Amount
Process for the Sign In Date).
 
Maybe something like:

SELECT a.product, a.dateTime, LAST(a.anyOtherRequiredField),
LAST(b.anyOtherRequiredField)

FROM (myTable AS a LEFT JOIN myTable AS b
ON a.product = b.product AND a.dateTime> b.dateTime)
LEFT JOIN myTable AS c
ON a.product=c.product AND a.dateTime > c.dateTime

GROUP BY a.product, a.dateTime, b.dateTime
HAVING b.dateTime = MAX(c.dateTime)




where b.fieldName refers to the record with the closest dateTime, same
product, that the one refered by a record with alias "a".


Vanderghast, Access MVP
 
Back
Top