Some guidance would be appreciated.

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

Guest

I am creating a database in Access 97 for a manufacturing facility. The table
layout is as follows:

Product No.
Date of Production
Beg. Inventory
Sales
Purchases
End Inventory

What I need is either a query or some logic that will go through the entire
table after it is sorted by Product and Date and pick up the ending inventory
from the most recent transaction and update the beginning inventory on the
record it is currently on. I need this to be as quick as possible. Any help
would be appreciated.
 
RecentDate = DMAX("[Date of Production]","TableName","[Product No.] =
'product'")
NewBegInv = DLookUp("[End Inventory]","TableName", "[Product No.] =
'product' And [Date of Production] = " & RecentDate)

Since I don't have the data to test this, the syntax may be a bit off. You
can check Access Help for the particulars, but here is how it works (or
should):
The DMAX function will return the most recent date for all the recoreds for
the product number. Then, using that date and the product number, use the
DLookUp function to find ending inventory value for that product number on
that date. The only thing I don't know is whether you can have more than one
record for a product code on the same date. If you can, then this will not
work. There is, however, a piece of code I got off this site that can fix
that.

A couple of pointers - Don't user spaces or other special characters (like
the .) in names except for the underscore _ It can cause problems.
 
NewBegInv = DLookUp("[End Inventory]","TableName", _
"[Product No.] = 'product' And " & _
"[Date of Production] = " & RecentDate)
Click to expand...


The second part of the criterion will fail: you need to format the date
value to a jet-compatible date (or SQL Server if this is an adp):

NewBegInv = DLookup( etc, etc, "... AND " & _
"[Date of Production] = " & Format(RecentDate, "\#yyyy\-mm\-dd\#") _
)


Hope that helps



Tim F
 
Back
Top