E
Eric
I usually try to fight with Access until I figure out how
to do something but this time I'm in way over my head...
I had an Inventory database that operates on a "first in,
first out" type of schedule (i.e. the oldest items in
inventory are the first to leave). I have set up a query
that lists all of the Receiving records (i.e. invoices of
items we have bought and placed in inventory) for each
part number. There is also a column in the query that
shows the current inventory of each part number (if there
is more than one receiving record for one part number, the
inventory column will be the same for all entries). I
would like the query to reference the number in the
inventory column and then filter out all of the old
records whose quantities are completely gone from the
inventory--that is, I only want to see the most recent
records that still have all or part of their received
quantities sitting in our warehouse.
The query should thus display the most recent record of
each part number and if the the qty. received on that
record is less than the inventory, it displays the next
most recent record, and so on, until the sum of the qty.
received for that part number is greater than or equal to
the amount in inventory, at which point it stops
retrieving records for that part.
I have tried playing around with WHERE clauses, IIF
expressions, and For...Each statements, but I think I'm on
the wrong track. Can anybody help me out?
Thanks,
Eric
to do something but this time I'm in way over my head...
I had an Inventory database that operates on a "first in,
first out" type of schedule (i.e. the oldest items in
inventory are the first to leave). I have set up a query
that lists all of the Receiving records (i.e. invoices of
items we have bought and placed in inventory) for each
part number. There is also a column in the query that
shows the current inventory of each part number (if there
is more than one receiving record for one part number, the
inventory column will be the same for all entries). I
would like the query to reference the number in the
inventory column and then filter out all of the old
records whose quantities are completely gone from the
inventory--that is, I only want to see the most recent
records that still have all or part of their received
quantities sitting in our warehouse.
The query should thus display the most recent record of
each part number and if the the qty. received on that
record is less than the inventory, it displays the next
most recent record, and so on, until the sum of the qty.
received for that part number is greater than or equal to
the amount in inventory, at which point it stops
retrieving records for that part.
I have tried playing around with WHERE clauses, IIF
expressions, and For...Each statements, but I think I'm on
the wrong track. Can anybody help me out?
Thanks,
Eric