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

Nathan Moore <nathan.moore (AT) cox (DOT) net> wrote in
news:gntyg.166531$k%3.42635@dukeread12:

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.

a database.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.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.

"nategoose (AT) gmail (DOT) com" <nategoose (AT) gmail (DOT) com> wrote in
news:1154119281.422134.319490 (AT) i42g2000cwa (DOT) googlegroups.com:

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

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