Update records with new data for specific fields

[repco]

Hello
I am new to access, have no idea how it works, but need to do some things for work that I'm hoping someone can help me with.

I have a table with about 2000 records.
I also have a .csv export from AutoCAD map that contains more data that needs to be added to these records in access.
I have added the fields to access that I need to populate with the data from the .csv. Both access table and .csv contain a unique ID that does match.
I have tried a simple copy and paste from excel to access after sorting but because the .csv does not contain the exact same number of rows as the access has records this doesn't work.

I am hoping someone can explain a query or perhaps suggest a script that can look up the unique identifier in both lists and then populate the empty fields in my access table.

Many thanks in advance for your help.

 

[Ranman256]

theres a lot to do here.
1 save the csv file to the same place everytime, say, File2Import.csv
add the csv file as a linked table.
now make some queries.

Q1 is an append query to add all records in the csv table to the main data table that DONT have the unique ID. (done via an outer join)

Q2 is an update query to add all records in the csv table that DO have the unique ID. this is the update of existing records.

you need to make a macro.
this macro will have the append query, and the update query.

so your steps will be:
1. get new csv file, overwrite the File2Import.csv
2. run the import macro
done.

 

[jdraw]

repco,

As ranman says there is a lot to do. However, since you are self-defined as new to Access, I recommend you tell us about your requirements in plain English. Many people unfamiliar with database and Access do not get the tables and relationships designed correctly to match their requirements. And very few databases consist of 1 table.
Tell us more about what you're trying to do in simple plain English --just as you would tell an 8 year old or your 80 year old granny.

Good luck and welcome to the forum.

 

[repco]

ok jdraw, sorry "granny" here goes

my access database has 1 table which has around 2000 rows/records.
This is updated daily by another user adding new rows/records as required.
Each row contains simple text and number data that is typed in manually. There are also 2 more fields one which contains a link to a video file and another that is a picture attachment. Again added mandrolically.
This table or list is to be printed to pdf (probably using the report function) and included as an appendix in a report.

The database was set up in January 2016, by people of similar uselessness to Access as myself, and contains about 12 months work.

Our client has requested that we add two additional (number) fields/columns of information to each row/record. This information is in a .csv export from autocad. There is one unique 'ID' field/column in the .csv which will match the same field in the Access table. The autocad export will not necessarily contain the exact same number of rows or exact same ID's. It might contain other rows of data associated with some ID that are not required in the access table. So as Ranman suggested, an append query is not required as no new rows are to be added (or deleted) during this operation.

My .csv only contains the ID and the 2 new fields of info. My table already contains the 2 empty fields which are to be updated with the new data. Field/column names can be identical in both.

I was hoping there is a kind-of lookup function where access reads the unique ID in my .csv and then takes the two extra fields of data, finds the same ID/row in my access table and updates the two new empty fields of info by using the field/column name.

make sense now?

thanks in advance for your help,
repco