how to dup a record with changes

[Nathan Moore]

Could anyone help write an SQL statement to take this:

Table X ( key, Money, TheDate, first, last, ...) =
( 1, $1, #jan 4, 2006#, 'fred', 'willard', ...),
( 2, $4, #apr 1, 2006#, 'emanuel', 'lewis', ...),
( 3, $7, #may 8, 2006#, 'your', 'mama', ...)

and turn it into this:

Table X ( key, Money, TheDate, first, last, ...) =
( 1, $1, #jan 4, 2006#, 'fred', 'willard', ...),
( 2, $4, #apr 1, 2006#, 'emanuel', 'lewis', ...),
( 3, $7, #may 8, 2006#, 'your', 'mama', ...),
( 4, -$4, #jul 28, 2006#, 'emanuel', 'lewis', ...)

Where key is the auto incrementing primary key.

I'm copying record to, but need to negate the Money and use todays date
as TheDate in the new record, but copying all of the rest of the record
verbatim. The statement needs to not refer to the fields that don't
change by name as more fields may be added later and this code needs to
keep working properly without having to be modified to deal with table
schema growth.

The current code in my project does a query to get record 2, runs
another query to get the who table X in writable mode, adds a new
record, iterates through the fields copying them all (except the
first/key), then changes the fields that need to be different by
referencing them by name. I think it's ugly, but I can't figure a way
to fix it in a pretty way. This seems like it should have a very nice
"why didn't I think of that" solution, but no one else seems to be able
come up with anything either.

Thank you,
Nathan

[Bob Quintal]

Nathan Moore <na**********@cox.netwrote in
news:gntyg.166531$k%3.42635@dukeread12:

I'm copying record to, but need to negate the Money and use
todays date as TheDate in the new record, but copying all of
the rest of the record verbatim. The statement needs to not
refer to the fields that don't change by name as more fields
may be added later and this code needs to keep working
properly without having to be modified to deal with table
schema growth.

Copying all the other fields verbatim! That's because of poor
database design. All the fields that are constant should be in a
separate table that's linked to the first by a reference ID.

So you create a record and add the one piece of common
information, the 'Foreign Key' to your constants, and no matter
what fields you add to that table, you don't have to change
anything in the code to add a record to this one.

of that" solution, but no one else seems to be able come up
with anything either.

That's because you are trying to design a spreadsheet instead of
a database.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[nategoose]

Hey Bob,
I know that's bad DB design, but I didn't design it, and I can't change
it.
This table is really more of a list/log than a spreadsheet. Things are
appended but never taken out.
Also my example was designed to be easy for whoever read the question
to comprehend without having to think about that the actual table that
I'm working on has foreign keys in it that is part of the data that is
copied verbatim. It would be kinda silly to have a table that was just
foreign keys, but that's what it would take to be really well designed
(in the theoretical sense) if it were more than just one foreign key.
Anyway, I have a table* that I need this type of operation done on and
it seems too simple for there not to be a swift way to do it.


* In a product in thousands of offices around the US and I can't change
the schema except for just tacking stuff on top of what's already there
(and even then only on special occasions) as per company rules.

[Bob Quintal]

"na*******@gmail.com" <na*******@gmail.comwrote in
news:11**********************@i42g2000cwa.googlegr oups.com:

Hey Bob,
I know that's bad DB design, but I didn't design it, and I
can't change it.
This table is really more of a list/log than a spreadsheet.
Things are appended but never taken out.
Also my example was designed to be easy for whoever read the
question to comprehend without having to think about that the
actual table that I'm working on has foreign keys in it that
is part of the data that is copied verbatim. It would be
kinda silly to have a table that was just foreign keys, but
that's what it would take to be really well designed (in the
theoretical sense) if it were more than just one foreign key.
Anyway, I have a table* that I need this type of operation
done on and it seems too simple for there not to be a swift
way to do it.


* In a product in thousands of offices around the US and I
can't change the schema except for just tacking stuff on top
of what's already there (and even then only on special
occasions) as per company rules.

You can do it in code. Set up a recordset that takes the current
record. set up a second recordset that contains a new record.
Loop through the fields() index to copy all fields. Then null
the fields that you don't want to copy.

Air code:

rsto.addnew
'fields are numbered 0 to count-1, 0 is autonumber=skip
For idx = 1 to rsFrom.fields.count - 1
rsTo.fields(idx) = rsFrom.fields(idx)
next idx
rsTo.fields(datefield) = null
rsto.update

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[nategoose]

That's kinda what is already in the program, but thanks anyway. I was
wanting something that would be done entirely inside the DBMS without
copying the records to/from the application (even though this is an
Access DB, so it's just library code).

Thanks anyway,
Nathan