Multiple query updates in the same field

[Dugantrain]

Hi all, I'm looking for the SQL statement which will allow for multiple updates in the same field. Here is my current SQL statement:

UPDATE tblSpareSiteParts SET tblSpareSiteParts.fk_item_name = "IMAGE CD's set (approx 15 disks)"
WHERE (((tblSpareSiteParts.fk_item_name) Like "image cd*"));

I also need to update several other items in the field fk_item_name, all updated the exact same way as above.

 

[jdjewell]

Do you mean...

Is this what you are looking for? All you have to do is have all of your assignment statements (in the Set) separated by commas. I hope it's this easy and I'm not missing something.

Update TableX
Set ColumnA = 1,
ColumnB = 2,
ColumnC = 3
.
.
.
etc.

 

[Dugantrain]

Not quite so easy. I'm trying to make different updates in the same field based on different criteria. So my WHERE clause will be different for every criteria (or maybe I'm supposed to use IIF). In plain English, this is what I'm trying to do:

-Update to the name "Image CD's" all instances where the name is like "imag*"
-Update to the name "Netcensus CD's" all instances where the name is like "netc*"
-Update to the name "Blank/Imaged Hard Drives" all instances where the name is like "40 gi*" or "20gi*"

etc.

This is easy to do by running separate Update queries. I was using a command button which would in turn call each query and the user would hit "OK" for each update. This was fine when I was only updating 2 or 3, but now I'm going to have to do 30 or so a day so I'd like to be able to update all these different names within the same query.

 

[jdjewell]

Well...

...you are going to touch the same number of records no matter what (so, the user is going to get prompted each time); however, there is a work around. I would assume that you are doing something like this:

DoCmd.RunSQL (Your SQL Statement Here)

All you need to do is add:

DoCmd.SetWarnings False 'Before the RunSQL statement

-- this prevents the system from prompting the user for each update. To turn this "feature" back on, use:

DoCmd.SetWarnings True

 

[Dugantrain]

Hmmm. Yeah, I guess that would work. The only thing is that I would have to write 30 or so separate update queries and make 30 separate calls to these queries in the Click event. Logically, this would work fine, but I'm trying to cut down on new queries as I already have 80 or so written and finding what I need is already a pain. Thanks for the tip, though.

 

[jdjewell]

I'm still at a loss...

I don't fully understand your need to write that many queries. Can you elaborate any more on the specifics of your issue?

 

[Dugantrain]

This is my issue:

The db I'm working with is a hardware tracking tool. Every day, some 25 or so Techs send in Spreadsheets with items on the sheets and info on how many they used, how many were DOA, etc. Because there are so many techs and item listings, we copy and paste this data from the spreadsheets into my Access subform datasheet. The central problem is that the item names coming in are not consistent; i.e. one guy will send in an item as "20 gig hard drive", another will send the item in as "20gb hd", etc. When I try to output this info into crosstabs, reports, etc., the data is horrendous; sometimes in a crosstab I'll get 10 or more column headings just for the same item! Unfortunately, I have no control over their spreadsheets, so I'm stuck with what's coming in to me. To compensate for this, I need to write update queries which will capture all instances of the same item and crunch them all into one item name. In the example above, it would be:

UPDATE tblSpareSiteParts SET tblSpareSiteParts.fk_item_name = "20 GB HARD DRIVE"
WHERE (((tblSpareSiteParts.fk_item_name) Like "20*"));

This has worked fine so far, but I need to do this crunch for all of the items that come in, which is about 30 or so. 30 separate update queries called by one command button will accomplish this task, but I'd sure like to do it in one query, if that's possible.

 

[jdjewell]

or...else...

Well, that clarifies things for me a little bit. I think your best resolution will probably be to do it all in separate queries (and not try to put your 30 into 1). The reason I say this, is that the effort won't save you much if any coding (You will still have separate criteria (Where clauses) for each update), and the amount of time you will spend trying to get it to work just doesn't make sense, since there is not a good enough benefit.

Something that might be of use to you, I think that you can set up a query, with this type of SQL:

EXEC Query1
EXEC Query2
EXEC Query3
....EXEC Query 30

So, in essence you would only need to call this query (let's name it "qryUniversal," in order to execute all 30 of the other queries. Essentially meeting your need, I think.

 

[jdjewell]

...stop the press

I'm looking into that last solution I gave you "EXEC..." it looks like I might have been smoking too much crack before I posted that. I'll let you know here in a moment.

 

[jdjewell]

EXEC

I don't know about the whole EXEC thing that I was talking about (works fine in SQL Server, but I can't find any definitive information about it in Access). Sorry I can't be more help.

 

[Dugantrain]

No problem. I'll just call all of the queries separately. Since I will have to write them, it's really all the same anyway.

 

[Dugantrain]

Yes, I agree. A rigid template coming into us would be the easiest answer. The Project Manager has tried to implement this, but Part Listings change daily, therefore the template has to be changed daily. Techs get confused on which template is the most current, so we end up getting a lovely, random variety of info coming in every day. This ain't nothin' like the projects in the text book!