UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Update Query, Access 2013    
 
   
bakersburg9
post Sep 9 2019, 12:20 PM
Post#1



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


I have a field Named Job Number, with Job numbers like AW7-57-07959, or 9J9-27-62495 .... the Character after the dash determines the office - but there's a caveat that if the first character in the 3rd group (the last five of the job number) is a 6, then it's a different office, if that makes sense

I created an additional field to handle this, called "OfficeNum_Alt" - so if it does NOT have the the last 5 of the job number starting with "6," then just leave the office number as is: 1,2,3,4,5,6,7,8 or 9 - but, BUT if the last 5 of the job number, like with the example above, Job# 9J9-27-62495, then update the "OfficeNum_Alt" field with an "x" at the end

In this case, because the fifth character in 9J9-27-62495 is a "2" I want it to change the 2 to "2x," because the last 5 starts with X - I can't even get off the ground, because I wanted to do a preliminary query to catch the ones where the last five of the job number started with a "6," but even that didn't work:

CODE
Test: IIf(Mid([Job Number],8)="6","Yes","No")
pullhair.gif

The screen shot should clear things up

Edit: So I'm trying to write an update query to basically add an "x" to the value in OfficeNum_Alt IF the last 5 of the job number STARTS WITH a "6" - so for AW7-57-07959, nothing would happen, but for 9J9-27-62495, the value in that ALT field would update from 2 to 2x
This post has been edited by bakersburg9: Sep 9 2019, 12:24 PM
Attached File(s)
Attached File  UA_ChallengeSS.png ( 37.36K )Number of downloads: 2
Attached File  UpdateQueryChallenge_UA.zip ( 633.35K )Number of downloads: 3
 
Go to the top of the page
 
theDBguy
post Sep 9 2019, 12:34 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,425
Joined: 19-June 07
From: SunnySandyEggo


Hi. What was the code you're using for the last column (alt)?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
bakersburg9
post Sep 9 2019, 12:40 PM
Post#3



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


db Guy - I never got there - the code for my 'transitory' query is shown in my post - but that doesn't work - everything comes back the same Result: "No"

so in English / psuedo code, it would be like: If the value for the 5th character is "6," then update the value in "OfficeNum_Alt" to the value of OfficeNum_Alt+x, else return the value of the current value in Office_Num_Alt
This post has been edited by bakersburg9: Sep 9 2019, 12:44 PM
Go to the top of the page
 
theDBguy
post Sep 9 2019, 12:49 PM
Post#4


Access Wiki and Forums Moderator
Posts: 76,425
Joined: 19-June 07
From: SunnySandyEggo


Okay, let's start slow. Try the following as a new column:
CODE
IIf(Mid([FieldName],5,1)="6","X","")
What do you get?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
RJD
post Sep 9 2019, 01:00 PM
Post#5


UtterAccess VIP
Posts: 10,101
Joined: 25-October 10
From: Gulf South USA


Hi: See if this does what you want ...

UPDATE Jobs SET Jobs.[Job Number] = Left([Job Number],5) & "x" & Mid([Job Number],7), Jobs.OfficeNum_Alt = [OfficeNum_Alt] & "x", Jobs.OrigOffice = Mid([Job Number],5,2)
WHERE Mid([Job Number],5,1)="6";

See the revised db attached. Notice that I copied the Jobs table and used Jobs_NewCode in the update in the db. This is to show how this works with the entire record set without the update (SELECT) and then with the update (UPDATE).

HTH
Joe

Attached File(s)
Attached File  UpdateQueryChallenge_UA_Rev1.zip ( 630.76K )Number of downloads: 4
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
bakersburg9
post Sep 9 2019, 01:13 PM
Post#6



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


DbGuy,
Not sure what you were going for - what is "FieldName" ? were you wanting me to replace it with 'Job Number,' or 'OfficeNum_Alt"

I did:
CODE
NewColumn: IIf(Mid([Job Number],5,1)="6","X","")


that just returned nothing or an "x" in the alias field named "NewColumn"

To clarity, there are only 2 meaningful components of the job number, which are always a total of 12 characters...... Including the dashes, the 5th character tells you (to a POINT) what the office number is, but 9 of our offices share the same number - you only know exactly what OFFICE it is if you know the 2nd piece of information, and that's whether the last five of the entire job number starts with a "6" - the way I chose to handle this (and there may be a much better way) is to create a separate field to give me a unique identifier to tell you what office it is exactly -

for example, Anaheim and Chicago are both office number 1 - the way you can tell if a job originated in Chicago is if the last five of the job number starts with a "6" - more specifically, if the 8th character is a 6

Dallas and Los Angeles are both office number 2 - but if the job is like 8B4-23-68849 then it's Dallas, because the last five of the job number starts with a 6 - and you can tell it's either Dallas or Los Angeles, because that 5th character is a "2" - so if it's Dallas, I want it to be officeNum_Alt = 2x - so all that have the last 5 of the job number start with a 6 will have an "x" suffix after the office number - so the office number + x
Go to the top of the page
 
theDBguy
post Sep 9 2019, 01:16 PM
Post#7


Access Wiki and Forums Moderator
Posts: 76,425
Joined: 19-June 07
From: SunnySandyEggo


I see. What do you get with this?
CODE
OfficeNum_Alt: IIf(Mid([Job Number],8,1)="6",Mid([Job Number],5,1) & "x","")

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
bakersburg9
post Sep 9 2019, 01:27 PM
Post#8



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


Db Guy,
Nothing - unless I didn't understand how to apply it, sorry...
Go to the top of the page
 
bakersburg9
post Sep 9 2019, 01:37 PM
Post#9



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


Hey, Joe, I guess I'm not seeing what you were trying to do - are you trying to save me a step of populating the Alt Office numbers with the 1, 2, 3, etc. as a first step, and then going in an running a separate query to update the alt office numbers to add an "x" IF that 8th character is a 6 - otherwise, just leave it alone

This is updating 2 fields....
Attached File(s)
Attached File  UA_UpdateTwoFields.png ( 9.54K )Number of downloads: 0
 
Go to the top of the page
 
theDBguy
post Sep 9 2019, 01:46 PM
Post#10


Access Wiki and Forums Moderator
Posts: 76,425
Joined: 19-June 07
From: SunnySandyEggo


Hi. This is what I meant. Try opening Query1 in the attached.

Attached File(s)
Attached File  UpdateQueryChallenge_UA.zip ( 634.9K )Number of downloads: 7
 

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
RJD
post Sep 9 2019, 02:04 PM
Post#11


UtterAccess VIP
Posts: 10,101
Joined: 25-October 10
From: Gulf South USA


QUOTE
Hey, Joe, I guess I'm not seeing what you were trying to do - are you trying to save me a step of populating the Alt Office numbers with the 1, 2, 3, etc. as a first step, and then going in an running a separate query to update the alt office numbers to add an "x" IF that 8th character is a 6 - otherwise, just leave it alone

Not sure I am interpreting your comment correctly. The SELECT query is just an example showing how the fields can be changed - not part of the UPDATE solution. And the UPDATE is how I interpreted your requirements... update the [Job Number] character 6 to an "x" if character 8 is a "6", update the Alt Office to add the "x", again if character 8 is a "6" and update (actually enter a value) in the original office field, once again, if character 8 is a "6". If you do not wish to change the [Job Number] itself, just remove that from the UPDATE query.

Again, the SELECT query was just a display to show you what was going to be changed in the UPDATE query so you could verify that's what you wanted to do - not required by the UPDATE at all, just verifying the SQL logic.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
bakersburg9
post Sep 9 2019, 02:08 PM
Post#12



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


OK, so that looks great
CODE
SELECT Jobs.[Job Number], IIf(Mid([Job Number],8,1)="6",Mid([Job Number],5,1) & "x","") AS OfficeNum_Alt, Mid([Job Number],8,1) AS JobChar8
FROM Jobs;


... it took the records where the 8th character in the job number was "6," and changed the alt office number to like a 7x from 7, etc. - how do I convert it to an update query and just update the alt office number field ?

Thanks so much for your help !

Steve
Go to the top of the page
 
bakersburg9
post Sep 9 2019, 02:09 PM
Post#13



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


RJD, Got it thanks - sorry, I'm really sick and not sharp today at all... thanks for all your help!
Go to the top of the page
 
theDBguy
post Sep 9 2019, 02:33 PM
Post#14


Access Wiki and Forums Moderator
Posts: 76,425
Joined: 19-June 07
From: SunnySandyEggo


Hi Steve. Looks like you already got it fixed using Joe's approach. Good job!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
RJD
post Sep 9 2019, 04:10 PM
Post#15


UtterAccess VIP
Posts: 10,101
Joined: 25-October 10
From: Gulf South USA


You are very welcome, Steve.

Yes, I guess my inclusion of the SELECT query was a bit confusiing in my attempt to clarify the logic before moving on to the UPDATE query. Sorry about that. Looks like you figured that out, though.

Continued success .. and hope you feel better!

Regards
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd October 2019 - 09:24 PM