Insert Query Result into an Existing Column

[schweer]

Hello again.

I've got a query that takes a part number, strips off the un-needed prefixes and suffixes, and gives me just the meat & potatoes of what I need.

I'd like to insert these results into a particular column in an existing table. Say the column name is Part_Number and the table name is CompletedWork. What would the SQL look like for that? I think this is relatively simple but my SQL skills would never be found in the same sentence as 'good'.

TIA for the help. This forum is an incredible source of information.

-Matt

 

[wazz]

make a backup before trying an action query like this. at least make copies of the tables and queries you will use and experiment with those copies.

try turning your query into an append query. when you click on the append query button you will have new options to select including which table(s) and which field(s) you want to append to. some things are filled in automatically if the names are the same. the query isn't run until you run it and if i'm not mistaken you can check the results before running it.

 

[neileg]

Sorry Wazz, you're off track here.

schweer, the first point to make is that you don't need to store this. You can use your query any place you would want to use the underlying table. What would happen to your data if the part number is updated but your 'meat and potatoes' field isn't?

If you have a sound reason for doing this, then you need to create a new field in your table and use an update query to plant the calculated data into this field.

 

[schweer]

The Meat & Potatoes data can stay the same; I'd prefer it to remain unchanged.

Basically, I need to take the PNs found in a column of a table, run a query that takes off unneeded characters, and insert these new part numbers back into the table. I'd like to try and do this WITHOUT altering the structure of the table.

Thanks for the help gents. I'll be checking this throughout the afternoon (I'm in Illinois, USA).

-Matt

Edit: I tried to write an update query to simply take the part_number and parse it, but the query would not make any changes. It ran, but nothing was changed. Here are the selections I made:

Field: Part_Number
Table: CompletedWork
Update To: IIf(Mid([Part_Number],1,1)=" ",Mid([part_number],2,7),IIf(Mid([Part_Number],1,5)="AAAAA",Mid([Part_Number],10,7) & Mid([Part_Number],6,2),IIf(Mid([Part_Number],1,5)="XAAA.",Mid([Part_Number],6,9),[Part_Number])))
Criteria: (left blank)

Any thoughts?

 

[wazz]

an update query (not append) will alter existing data where it is. (still backup the table just in case - the update cannot be undone). some sample data might be helpful. are all the prefixes and suffixes the same length? how about the core data you want to keep? a string extractor function might solve it (MID, INSTR, etc).

 

[schweer]

Here are some sample part numbers:

Part_Number
4D6742
8N1545
10R0969
4V9901
AAAAA -- 0R9910
XAAA.1411020 AA--
AAAAA --2376324
XAAA.1776113X AA00
1712215
6T6504
AAAAAM --2287609
1441451T
Empty
AAAAA -- 7N8139
2322415=
XAAA.1243333 AA--
AAAAA --1862776
7G7678
1741098E
XAAA.1516674H AA00
AAAAA --1343308
Empty
AAAAA -- 5T9597
2087568
XAAA. 4D2693 AA--
AAAAAN --0897890
1439796
3S4644
0813188N
6V4414L
2F2293
XAAA. 9W9626 AA--

The PNs with the Space in front of them are fine; they do not need to be parsed as the system recognizes that space. The others, however, do need to be parsed. Here is the parse logic that takes off the prefixes and suffixes:

ReconciledPN: IIf(Mid([part_number],1,1)=" ",Mid([part_number],2,7),IIf(Mid([Part_Number],1,5)="AAAAA",Mid([Part_Number],10,7) & Mid([Part_Number],6,2),IIf(Mid([Part_Number],1,5)="XAAA.",Mid([Part_Number],6,9),[Part_Number])))

That is taken from a simple Select Query. I'd like to take the data from that query and update the Part_Number column in the CompletedWork table.

 

[wazz]

backup your table.

start a basic qry, add your tblCompletedWork to the grid, add your Part_Number field to the design, change the query type to an Update Query, and in the 'Update To' portion of the design add your string extractor:

IIf(Mid([part_number],1,1)=" ",Mid([part_number],2,7),
IIf(Mid([Part_Number],1,5)="AAAAA",Mid([Part_Number],10,7) & Mid([Part_Number],6,2),
IIf(Mid([Part_Number],1,5)="XAAA.",Mid([Part_Number],6,9),[Part_Number])))

click View/SQL View to see the full SQL.
run the query (!). changes are made and cannot be undone.

btw: i don't think your extractor is quite right but maybe you know that already. test it by itself to view the results:

SELECT IIf(Mid([PartNumber],1,1)=" ",Mid([PartNumber],2,7),
IIf(Mid([PartNumber],1,5)="AAAAA",Mid([PartNumber],10,7) & Mid([PartNumber],6,2),
IIf(Mid([PartNumber],1,5)="XAAA.",Mid([PartNumber],6,9),[PartNumber]))) AS Extractor1
FROM tblPartNumber;

 

[schweer]

With an update query, what do I use in the SET statement?

Here's a rough sketch of the SQL:

Update CompletedWork
Set Part_Number = ??????????????????????????
Where ????????????????????????????????;

Where does my logic go?

Yeah, the parsing isn't 100% correct. I can't find a way to handle the leading space in some of the part numbers. No matter how hard I tried, I couldn't write the logic for it. I don't think Access likes nesting loops when the condition is true and false?

Anyways, thanks for your continued help.

-Matt

 

[wazz]

follow the previous post including:

click View/SQL View to see the full SQL.

you can see the entire SQL statement after clicking View/ViewSQL. note that the two bits of code are slightly different. with the first code you are working within an Update Query; with the second you are working within a Select Query. you should be able to see the SET features after clicking View/View SQL from within the Update Query.

you might simply run a couple of updates to fix the strings. do what you have there, then run another to get rid of the leading-space and maybe one more for the extra stuff at the ends. might be faster than figuring out one big function.

 

[schweer]

Thanks Wazz

Thanks Wazz. I guess you really need to do those steps in order. I kept trying to check Datasheet view (most of the time, that represents the query results) but that didn't work. After updating, it worked flawlessly. I added another query to remove that leading space.

Btw, could you take a look at this thread? I tried some of the things suggested, but never really got it to work.
http://www.access-programmers.co.uk/forums/showthread.php?t=88893

Again, much thanks.
Until my next moment of idiocy.

-Matt

 

[wazz]

hey. glad that's working.

i read through the other thread and it looks to me like scott nailed it down. what's not working? you might try continuing the thread by re-phrasing your problem with a new focus. work on it first, closing in on what was said regarding the idea that deleting a record will delete every field. try rewriting the query, eliminating all the field names just as it looks in the last couple of posts. pretty busy now but i or someone else might still be able to latch on to help out.

 

[schweer]

Both of my problems have been solved. I'm sure, after the trial next week, I'll be back with some more questions. Until then, my problems are solved.

Thanks again!

-Matt