Updating tables with Imports

[lordgeeuk]

Hi Im quite new to access, but Ive got a little database running thats contains balances of customers account over 30 days 60 days etc and logs querys on there accounts, Im given a spreadsheet each week which shows that the customer may have paid something to their account so I need to import these new balances into the table and update the record. Can anyone give me any pointers as how Id achieve this, as append query or import dont seem to fit what I need.

 

[lordgeeuk]

PS its only certain fields I need to updte not the whole record.

 

[ScottGem]

Link to the spreadsheet and run Update and/or Append queries. We would need to know more about the structure to help further.

 

[lordgeeuk]

Ive looked at linked sheets but the problem is this:

Ive designed a table based database that records all customer info balance, account number address general stuff, and field called query. This is where we record problems customer has on the account, how we deal it with it. Also there is a time factor for KPI based on a small macro of when customer logged problem when we called him back and when we resolved the query. This Macro works our the intervals and displays the ammount in days and if the KPI was achieved for each stage.

The spreadsheets (unfotunatley its split into four areas) that only contain the information accounts that have a problem, so if I linked it to the spreadsheets, any that we clear and dont have a problem will dissapear and all the details on what the problem was.

What I need to do is to be able to run the spreadsheets through the database and tie any updated data to the correct field on each customers account. Ive looked at update but that seems to be only update what I input not from the spreadsheet and append adds, but I dont need to add because they are already there.

I was thinking that I would use a delete query to delete just the fields that will be updated with the import, as the import is the up to date info. Then running the import through the database. Doing however leaves me stumped lol Any suggestions?

 

[ScottGem]

I'm not following the problem. Assuming your spreadsheet contains a primary key value that identifies the record you want to update, then you run an update query. If its a new record, then run an append query. If you run an update query and the record doesn't exist then that update is ignored. If you run and Append query and the record does exist then the record isn't added.

I do this all the time. I link to the sheet, and run an Update query. Then I run an Append query. then I delete the link (if applicable).

 

[lordgeeuk]

Hmmm Im a bit confused l presume I link the table to the spreadsheet? Ive tried that and it then only contains whats on the spreadsheet, loosing records that arent on the spreadsheet. Can I link the spreadsheet to the update query?

 

[ScottGem]

Apparently you aren't clear on what linking does. When you link to external data, whether it be a spreadsheet, text file, other database table or whatever, you are linking it to the MDB file NOT to a table within your MDB. Essentially you are creating a virtual table within your MDB. Once you have created the link, you can treat the link pretty much like you would any other table. so once you have created the link you use the linked table as the source for your Update and Append queries.

 

[Pat Hartman]

Have you considered getting rid of the Excel data entry piece and doing it with Access forms? The process will be much easier.

 

[lordgeeuk]

Well Im getting there, I got the spreadsheet as a linked table and a update query from the spread sheet of the fields I want to update into my main table but Im getting a Type Mismatch in expression in my update query. Ive narrowed it to two fields from the linked spreadsheet, it imports these to the linked sheet, (one of which is my primary key) as Numeric in the datatypes, but infact they are actually text as they are alphanumeric in my main table. Any ideas of how I alter the design of the linked table to make these import as text?

PS have a form for the mannual data to by the user.

 

[ScottGem]

Well Im getting there, I got the spreadsheet as a linked table and a update query from the spread sheet of the fields I want to update into my main table but Im getting a Type Mismatch in expression in my update query. Ive narrowed it to two fields from the linked spreadsheet, it imports these to the linked sheet, (one of which is my primary key) as Numeric in the datatypes, but infact they are actually text as they are alphanumeric in my main table. Any ideas of how I alter the design of the linked table to make these import as text?

PS have a form for the mannual data to by the user.

This is frequently a problem with importing from Excel. The best way I've found is to use a dummy first record that has Alspha characters in the text fields. This usually forces Access to import as a text field. Another thing you can try is to use the expression:

Keyfield: Str([keyfield])
To convert it to text.

 

[lordgeeuk]

Ahhh I see. Where would I use the expression?

 

[ScottGem]

I think you will need to use an interim query here. Create a Select query on the linked spreadsheet including that expression instead of the actual key column. Then use that query as the source for your Update query to join with the Access table.

 

[lordgeeuk]

Does the Keyfield: Str([keyfield]) go in the criteria or where im not 100% what you mean? I then use the query as a base for the query update?

 

[Pat Hartman]

When you have text data that Access assumes is numeric because the first n rows of the column are numeric, the best solution is to create a dummy table with the correct field definitions. Then rather than linking to the spreadsheet, import it into the dummy table. Run your append queries or whatever and then delete all the rows so the table is ready for the next import.

 

[ScottGem]

You might try Pat's suggestion for the import instead. My suggestion is that you create a query and add a column with the expression in the field row. You need to substitute the actual name of your keyfield. You can then use that query as the source for the Append query.

 

[lordgeeuk]

This is being used by people with relatively no access experience so Scotts would be easier for them to be honest. Ive created the query but the expression says it contains to many brackets. Should it look like this?

Keyfield: Str([Site Reference Number])
or
Keyfield: Str[Site reference Number]
or
Site Reference Number: Str[Site Reference Number]
All three of those create either invalid syntax or too many bracets error.

 

[ScottGem]

This is being used by people with relatively no access experience so Scotts would be easier for them to be honest. Ive created the query but the expression says it contains to many brackets. Should it look like this?

Keyfield: Str([Site Reference Number])
or
Keyfield: Str[Site reference Number]
or
Site Reference Number: Str[Site Reference Number]
All three of those create either invalid syntax or too many bracets error.

I don't think there is much difference in end user interaction between pat's suggestion and mine.

Keyfield: Str([Site Reference Number])

should be the correct syntax. The others are definitely wrong. Check the STR function in Access Help to confirm the correct syntax.

 

[Pat Hartman]

I doubt that the query suggestion will work. If Access determines that the source data is numeric, converting it to text in a query won' solve the problem because when it comes to the non-numeric source data, it will likely generate an error and be ignored.

Users should not have access to the database window so they would never be creating a table or query, you would. Your code would then automate the import process for them.

 

[lordgeeuk]

So Im confused, the best way is to import all the data into a temport update table then an update query from the update table to the permanent table?

 

[Pat Hartman]

Yes - if you are having a problem with Access failing to recognize that a field is text. The import facility for spreadsheets does not allow you to override what Access thinks a column's data type is. The only way to get past that problem is to import the spreadsheet into a pre-defined table that has the correct data types. Linking the spreadsheet and appending to the ultimate table will raise errors because of the data type issue.