Re: [MS_AccessPros] Update and Insert in one step

 

Bill-

It's the DISTINCT that's getting you - that's not updatable.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On Mar 23, 2015, at 8:45 PM, wrmosca@comcast.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi John

It's a view. No unique index. I think you got it. Since this is just a once a month update I could probably take an extra step and dump the EPSi cost centers into a temp table. The view is a SELECT DISTINCT so I don't think I can index it on the back end, but I'll check it out.

Thanks!

Bill

---In MS_Access_Professionals@yahoogroups.com, <JohnV@msn.com> wrote :

Bill-

Is vEPSi_CostCenters a query?  If so, what's the SQL?

I'm thinking there needs to be a unique key on the CC field in the vEPSi dataset.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On Mar 23, 2015, at 7:36 PM, wrmosca@comcast.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi Gang

I'm attempting to use a query that John V. gave us years ago. It is supposed to update a table with data from another table, and if the records are not in the first table they should be inserted into it.

Example:

UPDATE tblCostCenters_lkp RIGHT JOIN vEPSi_CostCenters

       ON tblCostCenters_lkp.CostCenter = vEPSi_CostCenters.CC

SET tblCostCenters_lkp.CostCenter = [vEPSi_CostCenters].[CC]

  , tblCostCenters_lkp.CCDescr = [vEPSi_CostCenters].[CCDescr];

I try to run this and get a "not updateable query". It runs as a SELECT query without any complaints and I know all the data types mattch. tblCostCenters_lkp is certainly updateable because I've been doing this manually for a while now. Any ideas?

-Bill Mosca

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (4)

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___