Insert QRY results into existing table/records

[artemetis]

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!

[Stewart Ross]

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.

UPDATE     [your access table]       AS A 
INNER JOIN [your linked excel table] AS E ON
           A.EmpUID   = E.EmpUID
SET        A.empStat  = E.empStat, 
           A.empPerf1 = E.empPerf1, 
           A.empPerf2 = E.empPerf2, 
           A.empPerf3 = E.empPerf3;

-Stewart

[artemetis]

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)

[Stewart Ross]

Hi Art. You can set the Excel headings in any suitable way, then refer to them in the update SQL like this:

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

[artemetis]

Wooo-Hooo!
Worked like a charm!!!
Thanks and thanks to all that chimed in to help me out with this post.
:o)

[artemetis]

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?

UPDATE     [PROMPT FOR TABLE???]       AS A 
INNER JOIN [your linked excel table] AS E ON
           A.EmpUID   = E.EmpUID
SET        A.empStat  = E.empStat, 
           A.empPerf1 = E.empPerf1, 
           A.empPerf2 = E.empPerf2, 
           A.empPerf3 = E.empPerf3;

I'm thinking something along these lines.....

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=1) AND ((MSysObjects.Name) Like "*tbl*")) OR (((MSysObjects.Type)=6) AND ((MSysObjects.Name) Not Like "*~TMPCLP*"))
ORDER BY MSysObjects.Name;
 

[ChipR]

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.

[artemetis]

Thanks for all the help!
Worked beautifully using a form and the DoCmd :o)