move records to different fields within table

[massa]

I have multiple fields in a table. I need to move duplicate records(they have
same id number and dollar amount) into the update field 3/31/2008. Hoe do I
accomplish this? Thanks.
doument id usd usd as of 3/31
222 1.10
222 1.10

 

[boblarson]

Short answer - you don't! That violates normalization rules and is not
recommended. You should have a separate history table like this:

HistoryID - Autonumber (primary Key)
HistoryDate - Date
USDValue - Currency

You should NOT have repeating fields in your tables. That is SPREADSHEET
thinking and not Relational Database thinking.
--
Bob Larson
Access World Forums Super Moderator

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[Evi]

Like Bob, my Design Alert Alarm is flashing but (eeuch!) we can't always
choose the dbs we inherit and it can be done.
What exactly do you want to see in your field which you have named after a
date (twitching neurotically at the thought!) Do you want to see 222 or 1.10
or both so that the field would read 222 1.10 or some such rubbish?
No, I can't stand this. Don't do it! At least give your field a sensible
name eg EndOfYearData and have a field with the EndOfYearDate where you can
put your 3/31/2008. Access HATES symbols like / in field names. And what
about next year. All your forms and reports will have to be redone.
Evi

 

[Margaret Bartley]

I have multiple fields in a table. I need to move duplicate records(they
have
same id number and dollar amount) into the update field 3/31/2008. Hoe do
I
accomplish this? Thanks.
doument id usd usd as of 3/31
222 1.10
222 1.10

Create a work table that is the result of a Totals query that lists the
document ids which have more than one record.

Then, loop through that work table.
1) read the document id
2) create a query set of all the records with that document id
3) loop through this second queryset
4) build the values you want to post in the "usd as of 3/31" field. I'm
not sure what you want in this field, but it might take some string
manipulation. You should show us what you want the update field to look
like after you've done your processing.
5) post the value you built in step 4 to the update field(s). You didn't
say if you want all the records to have the same value in the update field.