|
|
|
date: Mon, 18 Aug 2008 03:54:01 -0700,
group: microsoft.public.access.queries
back
Re: conditional appending
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
Azad wrote:
> 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
>
date: Mon, 18 Aug 2008 08:15:41 -0400
author: John Spencer
|
|
