conditional appending

[Azad, DH, BD]

Dear all Experts,

I need badly your help in urgent. Pls help me for the problems bellow by
giving me sample codes or proper advice. Pls pls pls help me out ASAP!

Your kind & wise help will be highly appreciated.

Validate the existing date in append query. I have a inventory table where
the whole month inventory data will be appended by an append query only once
in a month on the last date. If the records already exist by last date (i.e
all the records have last date field as bellow given table) , it will show me
a msg that the records have already been added & will not repetitively add
the data i.e will cancel the action. I have a command button that will run
the append query and if the last date matches, append query will cancel
appending the data to the inventory table. How can I do this?



Inventory table

Item Qty unit Consumption Last Date
Grey cement 500 Bag 230 6/30/2008
Local sand 1052 Cft 550 5/31/2008
Grey cement 562 Bag 256 5/31/2008
Local sand 1026 Cft 450 6/30/2008
Bricks 55630 Nos 6300 5/31/2008
Bricks 55230 Nos 6100 6/30/2008


Now it the records set bellow are attempted to append to the table , they
should be rejected because the last date 6/30/2008 are existing.

Item Qty unit Consumption Last Date
Grey cement 500 Bag 230 6/30/2008
Local sand 1026 Cft 450 6/30/2008
Bricks 55230 Nos 6100 6/30/2008

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Assumptions:
Inventory is the name of the source table
SomeTable is the name of the destination table
You know how to build a query in the SQL view. If not post back for
instructions on how to build this query using the query grid.

INSERT INTO SomeTable(Item, Qty, Unit, Consumption,[Last Date])
SELECT Inventory.Item
, Inventory.Qty
, Inventory.Unit
, Inventory.Consumption
, Inventory.[Last Date]
FROM Inventory LEFT JOIN SomeTable
ON Inventory.[Last Date] = SomeTable.[Last Date]
WHERE SomeTable.[Last Date] is Null


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County