Query records, update then append? 
Author Message
 Query records, update then append?

Hello and thanks for any replies,
I need to run a query to find previous records based CustomerID and Date,
copy the query records then update them and append to the same table.
Is this possible with code?
The only way that I can see to do this is to have another table to paste the
records to and then do an update query followed by an append query back to
the original table, then delete the records in the temporary table. This
seems to be a very clunky method though.
Any ideas?
Ed.

--

  ,-._|\     Ed Adamthwaite
 /  Oz  \  Victoria, Australia.
 \_,--.x/   Remove "SpamMeNot" if using the reply button.
         v



Tue, 04 Nov 2003 21:25:36 GMT  
 Query records, update then append?
I'm not sure, but it might depend on exactly how you wanted to update them.
Could you give a little more detail?

--

Dirk Goldgar
www.datagnostics.com
(to reply via email, remove NOSPAM from address)

Quote:

>Hello and thanks for any replies,
>I need to run a query to find previous records based CustomerID and Date,
>copy the query records then update them and append to the same table.
>Is this possible with code?
>The only way that I can see to do this is to have another table to paste
the
>records to and then do an update query followed by an append query back to
>the original table, then delete the records in the temporary table. This
>seems to be a very clunky method though.
>Any ideas?
>Ed.

>--

>  ,-._|\     Ed Adamthwaite
> /  Oz  \  Victoria, Australia.
> \_,--.x/   Remove "SpamMeNot" if using the reply button.
>         v



Tue, 11 Nov 2003 05:13:01 GMT  
 Query records, update then append?
Hello Dirk,
all I want to do is:
copy certain records with a query based on CustomerID, OrderID,
DeliveryAddressID and DateRequired.
Alter the OrderID and DateRequired then append the records onto the same
table.
I have managed to do this with code by copying the records to a temp table,
then running an update query on the temp table, then appending the updated
records back onto the original table. Of course I clean up afterwards.
It seems to be a rather convoluted way of doing it, I just thought that
there may be a slicker method.

--

  ,-._|\     Ed Adamthwaite
 /  Oz  \  Victoria, Australia.
 \_,--.x/   Remove "SpamMeNot" to reply
         v


Quote:
> I'm not sure, but it might depend on exactly how you wanted to update
them.
> Could you give a little more detail?

> --

> Dirk Goldgar
> www.datagnostics.com
> (to reply via email, remove NOSPAM from address)


> >Hello and thanks for any replies,
> >I need to run a query to find previous records based CustomerID and Date,
> >copy the query records then update them and append to the same table.
> >Is this possible with code?
> >The only way that I can see to do this is to have another table to paste
> the
> >records to and then do an update query followed by an append query back
to
> >the original table, then delete the records in the temporary table. This
> >seems to be a very clunky method though.
> >Any ideas?
> >Ed.

> >--

> >  ,-._|\     Ed Adamthwaite
> > /  Oz  \  Victoria, Australia.
> > \_,--.x/   Remove "SpamMeNot" if using the reply button.
> >         v



Thu, 13 Nov 2003 19:55:19 GMT  
 Query records, update then append?
Ed -

Here's SQL for a sample query that may do the sort of thing you're looking
for:

    INSERT INTO tblOrders
        ( CustomerID, OrderID, DeliveryAddressID, DateRequired )
    SELECT CustomerID,
                DMax("OrderID","tblOrders")+1 AS OrderID,
                DeliveryAddressID,
                [Enter new DateRequired] AS DateRequired
    FROM tblOrders
    WHERE (((tblOrders.CustomerID)=[CustomerID to copy])
            AND ((tblOrders.OrderID)=[OrderID to copy]));

If something along these lines could do the job for you , I think it would
be "slicker".
--

Dirk Goldgar
www.datagnostics.com
(to reply via email, remove NOSPAM from address)

Quote:

>Hello Dirk,
>all I want to do is:
>copy certain records with a query based on CustomerID, OrderID,
>DeliveryAddressID and DateRequired.
>Alter the OrderID and DateRequired then append the records onto the same
>table.
>I have managed to do this with code by copying the records to a temp table,
>then running an update query on the temp table, then appending the updated
>records back onto the original table. Of course I clean up afterwards.
>It seems to be a rather convoluted way of doing it, I just thought that
>there may be a slicker method.

>--

>  ,-._|\     Ed Adamthwaite
> /  Oz  \  Victoria, Australia.
> \_,--.x/   Remove "SpamMeNot" to reply
>         v



>> I'm not sure, but it might depend on exactly how you wanted to update
>them.
>> Could you give a little more detail?

>> --

>> Dirk Goldgar
>> www.datagnostics.com
>> (to reply via email, remove NOSPAM from address)


>> >Hello and thanks for any replies,
>> >I need to run a query to find previous records based CustomerID and
Date,
>> >copy the query records then update them and append to the same table.
>> >Is this possible with code?
>> >The only way that I can see to do this is to have another table to paste
>> the
>> >records to and then do an update query followed by an append query back
>to
>> >the original table, then delete the records in the temporary table. This
>> >seems to be a very clunky method though.
>> >Any ideas?
>> >Ed.

>> >--

>> >  ,-._|\     Ed Adamthwaite
>> > /  Oz  \  Victoria, Australia.
>> > \_,--.x/   Remove "SpamMeNot" if using the reply button.
>> >         v



Fri, 14 Nov 2003 00:50:56 GMT  
 Query records, update then append?
Thanks Dirk,
It looks very "Slick". I'll give that a go tomorrow.

--

  ,-._|\     Ed Adamthwaite
 /  Oz  \  Victoria, Australia.
 \_,--.x/   Remove "SpamMeNot" to reply
         v


Quote:
> Ed -

> Here's SQL for a sample query that may do the sort of thing you're looking
> for:

>     INSERT INTO tblOrders
>         ( CustomerID, OrderID, DeliveryAddressID, DateRequired )
>     SELECT CustomerID,
>                 DMax("OrderID","tblOrders")+1 AS OrderID,
>                 DeliveryAddressID,
>                 [Enter new DateRequired] AS DateRequired
>     FROM tblOrders
>     WHERE (((tblOrders.CustomerID)=[CustomerID to copy])
>             AND ((tblOrders.OrderID)=[OrderID to copy]));

> If something along these lines could do the job for you , I think it would
> be "slicker".
> --

> Dirk Goldgar
> www.datagnostics.com
> (to reply via email, remove NOSPAM from address)


> >Hello Dirk,
> >all I want to do is:
> >copy certain records with a query based on CustomerID, OrderID,
> >DeliveryAddressID and DateRequired.
> >Alter the OrderID and DateRequired then append the records onto the same
> >table.
> >I have managed to do this with code by copying the records to a temp
table,
> >then running an update query on the temp table, then appending the
updated
> >records back onto the original table. Of course I clean up afterwards.
> >It seems to be a rather convoluted way of doing it, I just thought that
> >there may be a slicker method.

> >--

> >  ,-._|\     Ed Adamthwaite
> > /  Oz  \  Victoria, Australia.
> > \_,--.x/   Remove "SpamMeNot" to reply
> >         v



> >> I'm not sure, but it might depend on exactly how you wanted to update
> >them.
> >> Could you give a little more detail?

> >> --

> >> Dirk Goldgar
> >> www.datagnostics.com
> >> (to reply via email, remove NOSPAM from address)


> >> >Hello and thanks for any replies,
> >> >I need to run a query to find previous records based CustomerID and
> Date,
> >> >copy the query records then update them and append to the same table.
> >> >Is this possible with code?
> >> >The only way that I can see to do this is to have another table to
paste
> >> the
> >> >records to and then do an update query followed by an append query
back
> >to
> >> >the original table, then delete the records in the temporary table.
This
> >> >seems to be a very clunky method though.
> >> >Any ideas?
> >> >Ed.

> >> >--

> >> >  ,-._|\     Ed Adamthwaite
> >> > /  Oz  \  Victoria, Australia.
> >> > \_,--.x/   Remove "SpamMeNot" if using the reply button.
> >> >         v



Fri, 14 Nov 2003 19:13:48 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Append query that appends to and updates an archive table

2. VB6,Access97,ADO - Update query doesn't update all records

3. UPDATE query not updating records

4. Retrieve autonumber from record in append query

5. Appending records from SELECT query

6. repeat append query for specific records

7. Deleting Records With An Append Query

8. Make Table Query vs Append Query

9. Make-Table queries and Append Queries

10. Did update query change records?

11. Creating an Update to New Record Command Button on Form Based on Multitable Query

12. Jet 3.51 with VB5: Large SQL update query misses some records

 

 
Powered by phpBB® Forum Software