Append/Update Query -- Need Help!

[su-san]

Hi all,

I'm very new to access. I was wondering if it was possible to append rows to a table with 2 primary keys. One key is the IDcode and the second key is the Date.

I'd like to append rows with data by IDcode by date in sequential order.

Sorry if this is confusing.



Thanks,
su-san

[TheShabz]

i dont understand why you want it entered in in chronological order. just enter it in however you want and then sort it by date.

[weekend00]

One table can has only one primary key.

[TheShabz]

I assumed it was a compound key.

[su-san]

i dont understand why you want it entered in in chronological order. just enter it in however you want and then sort it by date.

I'm trying to auto-update production by well (key 1) by date (key 2) from one program (production database - production table) to another (economics database - production table). My problem is appending rows with key 1 & 2 to the economics database - production table.

I need records to update to, correct?

Thanks for the help!

[TheShabz]

does the econ table not having matching keys?

[su-san]

The econ database has a matching key however it is on another table (property table).

[TheShabz]

you need a key to key match to make it work. We need to get from Prod-Prod to Econ-Prod. so far we have:

Prod-Prod -> Econ-Property -x Econ-Prod

So now you need to see if your Econ-Property table has a matching key for the Econ-Prod table. AS long as you can match keys and all tables have all of the records you want to up date, you should be good to go.

[su-san]

I have matching keys from Prod-Prod to Prod-Econ Prop to Econ Prop-Econ Prod.

I don't know how to append/update the data.

[TheShabz]

Ok. So you mentioned there are 2 databases involved. you will need to first link the table(s) so that you are only working out of one database. Add fields to the table you want to append the table to, naming them accordingly. then go ahead and toss all the tables in a query and join them. change the query type to "update query" and double click on the field you wish to update. where it says "update to" enter in tablename.fieldname and hit run.

of course the biggest things to consider are to ensure that the joins are correct and that all the tables involved have records for all the ones you want updated. for example.

if your source table has records 1,2,3 and 4, and your transition table has only records 1,2, and 3, you will not be able to update record 4.

I would really have to see the db to help you out any further than this. hope this works out.

[su-san]

So you mentioned if source table has 1,2,3 and 4 but transition table as 1,2,3. I would not be able to updated record 4. I understand that.

If my source table has data from 1/1/10 - 11/6/10 but my transition table has 1/1/10 - 10/31/10 (records or rows) how do I append addition 6 rows of data from the source table?

[TheShabz]

manually enter them into the table. if you dont have the data from your joins, you cannot do it through a query.

[su-san]

Back to square one. I don't know what/how to do, but I have a feeling it's very possible.

Thanks for the help.