Need a delete query

  • Thread starter Thread starter Brad
  • Start date Start date
B

Brad

I have an ORDERS table with all my order information
imported from my web site. I also have an INVENTORY table
with each record being a unique product. The reason for
having a seperate record for each item is because of the
item's physical location. I have many records in the
INVENTORY table with the same product ID. When I import
the days orders into my ORDERS table, I will also have
many of the same product ID's.

Currently I open the INVENTORY table and delete the items
that I ship out each day. How can I automatically compare
the ProductID fields of these two tables (INVENTORY &
ORDERS) and have the records from the INVENTORY table
deleted, or archived in a shipped items table?
 
What algorithm do you expect Access / JET to work out the
1-to-1 correspondence between Records in the ORDERS Table
and Records in the INVENTORY Table?

Until you can define this clear w.r.t. your Table
Structure, you cannot give clear instructions to Access /
Jet to delete, archive or move Records from the Table
INVENTORY.

Also, what happens to a multiple-item Order?

In fact, the delete / archive or move (to another Table)
action is a bit inefficient, I think. For me, simply mark
them as "sold" or in fact, flag then with the RecordID of
the corresponding ORDERS Record is more "information-rich"
as you know which ORDER Record has been filled by this
INVENTORY Record!

HTH
Van T. Dinh
MVP (Access)
 
Thanks for the information. I had not considered just
marking the item as sold. In order to see what items I
have in stock I just filtered my query to show the items
that have not been marked as sold.

Would you have any information on how to automatically
mark the orders as sold based on the ORDERS table? The
ORDERS table has many records. Each record represents and
order.

Thanks

Brad Vincent
 
That the essence of the first 3 paragraphs in my reply: YOU have to define
(at least conceptually) the algorithm of associating / assigning a
*particular* INVENTORY Record to a *particular* unfilled ORDER Record.
 
Back
Top