Import Excel to Existing Access Database Table/ Only update certain fields

[gaker10]

An append query here won't work because one of the things I'm importing is a key field. If it already exists, I just want Access to throw whatever I'm importing into specific fields. If it does not exist, append it along with its information to the existing table. I have a split database. I tried an update query and nothing happened. I wish I could post sample data but there's just too much stuff in my database already.

My excel book has just one sheet where I do some calculations and filtering. When I get it down to what I need, I want to import only certain columns into access fields BASED on the key column (field). I do not want the entire table overwritten. This is more of an update than anything.

[ranman256]

You have to do 2 queries.
a update qry to update the existing ones
and append qry to get the new ones

(i do it everyday)

[gaker10]

I got it! I had my query backwards, hehe. Instead of updating my existing table, I was updating my imported table with my existing table x.X

You were right about the two queries, ranman256. My append query appends JUST the key column, and my update query updates everything else associated with that key column. Thanks a bunch

Screenshots if you're curious:

[bharatmvs]

Hi,
I have a similar query.
Suppose if one of my fields is Yes/No type, then how should I put in criteria? I tried various ways like [field1] = Yes, [field1] = False, etc. But none seems to work.

Please suggest.

Thank You,
Bharat

[gaker10]

@ bharatmvs: I solved the same issue. In Access, FALSE is 0, and TRUE is -1. So, for example, I have a linked Excel where I have a Yes/No field (drop down list). In the column next to this I have an IF formula which converts my FALSE's to 0, and my TRUE's to -1. Then, when I run my query, I tell it to just take my boolean field and put in my Access Yes/No field. Screenshots below:



The screenshot above is my Excel where I have my Yes/No drop down field, and my Boolean field. Note all my "Yes" cells convert to a -1, and my "No" cells convert to a 0. I usually hide my boolean column as the users of this Excel do not know what a boolean is.



The second screenshot above is my query criteria in Design Mode. I have an append query here, but the principal is the same for all query types I would think. So, instead of taking my "Amortization?" field, it takes my Boolean field and marks my records as a Yes/No based on "0" or "-1".

So, in your case, the field would either be 0 (No) or -1 (Yes).

Does this help?

Also, since this thread is already solved, I think the moderators would like a new thread started that references this thread via hyperlink rather than replying to a thread marked "Solved". I'm not sure though

[bharatmvs]

HI,
Thank You so much for the detailed explanation. But sadly, my query isnt opening in Grid form but only sql form saying limit of 1,024 exceeded. So I am unable to test it. Any idea how to check this?

[gaker10]

I'm afraid I am unfamiliar with this error. Usually when my Access size is exceeded through a query, my database just crashes.

I would suggest posting a new thread explaining your issue in detail and referencing the link to this thread.

[bharatmvs]

Sure. Thank You