By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,473 Members | 3,234 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,473 IT Pros & Developers. It's quick & easy.

Insert QRY results into existing table/records

P: 76
Ok, here's another.........I've done it in the past, but am sick as dog right now and am not thinking properly..

I have an XLS that has the following info empUid, empStat, empPerf1, empPerf2, empPerf3.

The file gets emailed, updated then needs to be imported back into MDB table, respective fields.

Opening query, have the file linked, but I am completely forgetting how I inserted the information into the proper existing empUID (essentially updating their empty fields), rather than having it added to the end of the table?

PULLING MY LAST BITS OF HAIR OUT!
Jan 9 '09 #1
Share this Question
Share on Google+
7 Replies


Expert Mod 2.5K+
P: 2,545
Hi. You could try the following update statement which updates the fields of the table joined to the Excel linked table. You must substitute in the correct names of your access table and your linked excel table as appropriate.

Expand|Select|Wrap|Line Numbers
  1. UPDATE     [your access table]       AS A 
  2. INNER JOIN [your linked excel table] AS E ON
  3.            A.EmpUID   = E.EmpUID
  4. SET        A.empStat  = E.empStat, 
  5.            A.empPerf1 = E.empPerf1, 
  6.            A.empPerf2 = E.empPerf2, 
  7.            A.empPerf3 = E.empPerf3;
-Stewart
Jan 9 '09 #2

P: 76
Thanks Stewart!
Worked like a charm....
Now, one last "stupid" question......if, on the XLS, I would like to have "Employee Performance in Area 1" as the column heading, rather than empPerf1, is that possible?

You know, those outside....looking at the XLS need it to be pretty. :o)
Jan 12 '09 #3

Expert Mod 2.5K+
P: 2,545
Hi Art. You can set the Excel headings in any suitable way, then refer to them in the update SQL like this:

Expand|Select|Wrap|Line Numbers
  1. A.empPerf1 = E.[Employee Performance in Area 1]
It is essential to use the left and right square brackets to enclose any field name that contains spaces.

-Stewart
Jan 12 '09 #4

P: 76
Wooo-Hooo!
Worked like a charm!!!
Thanks and thanks to all that chimed in to help me out with this post.
:o)
Jan 13 '09 #5

P: 76
So....like I said above, this worked beautifully.

Now I'm just wondering if there is a way to be prompted for the the source table at qry execution? Or is this something that needs to be built into a form?

Expand|Select|Wrap|Line Numbers
  1. UPDATE     [PROMPT FOR TABLE???]       AS A 
  2. INNER JOIN [your linked excel table] AS E ON
  3.            A.EmpUID   = E.EmpUID
  4. SET        A.empStat  = E.empStat, 
  5.            A.empPerf1 = E.empPerf1, 
  6.            A.empPerf2 = E.empPerf2, 
  7.            A.empPerf3 = E.empPerf3;
I'm thinking something along these lines.....

Expand|Select|Wrap|Line Numbers
  1. SELECT MSysObjects.Name
  2. FROM MSysObjects
  3. WHERE (((MSysObjects.Type)=1) AND ((MSysObjects.Name) Like "*tbl*")) OR (((MSysObjects.Type)=6) AND ((MSysObjects.Name) Not Like "*~TMPCLP*"))
  4. ORDER BY MSysObjects.Name;
  5.  
Jan 14 '09 #6

Expert 100+
P: 1,287
Check out Everything About Using Parameters from Code. If you do this, you will probably want to make a form because the msg box asking for the parameter is so ugly and limited.
Jan 14 '09 #7

P: 76
Thanks for all the help!
Worked beautifully using a form and the DoCmd :o)
Jan 23 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.