Query 2 columns in a table and then change one columns entry.

[glavster]

Hello, I have a table in an MS Access DB with about 12 columns. It has about 35,000 entries and I need to upload it to a MySQL database. Before I can do that I need to change entries in 3 Columns and all 3 columns are linked.

The first column was easy but the next 2 are a little more difficult. I need to query columns 1 & 2 for certain variables and then change the value of column 2 for all query results.

Then when I’ve all that done I need to do the same for column 3 but this time I have to compare all three columns.

Can anyone tell me how this can be done?

I hope this makes since.

Thanks,
Paul

 

[lameid]

Can you write the query that returns the records that should be affected and post the SQL here?

Secondly, what is it you want to change or what methodology?

 

[glavster]

Hello lameid and thanks for the reply. The problem with posting the sql here is there would need to be 70+ queries from the 2nd column and probably a lot more for the 3rd. Then for each query there has to be a different value replaced.
I'll need a form first to enter the data to search for and to enter the data to change. I'll do my best but I'm not sure if I can even do this correctly.

If I explain a little more it may help, the items table has about 35 thousand entries. 3 columns represent a tree category system. But the MySQL DB has a separate table for the categories and uses a numeric value to recognise what category it is represented by.

So the Access table has 3 columns for tree category (tools/sockets/3mm for example) where the MySQL only has 82 for example and this value represents tools/sockets/3mm.

What I intend to do is make my own categories table using the values from the 3 columns in the access table, Categories Column, Subcategories Column and Sub Subcategories Column. Out of the 35k entries there were only 9 unique entries in the categories column.

I think I need to explain how I got this far. First of all the original data is given to me as a Tab Delimited text file. I transferred this to a MS Excel file and then copied the categories column to a column in a new excel sheet. I then removed duplicates and I was left with nine unique entries. These 9 unique values were given values 1 to 9 and I used Find and Replace All to change the 35k entries to the correct value.

While doing all this I started to make my new Categories table with the following columns (category_id, parent_category_id, category_path, category_name, category_order) and here is a sample entries in these columns ; delimited for the previously mentioned Tools/Sockets/3mm (73;53;0,48,53,;3mm;1) the 0,48,53, tells the full category tree. 48 is Tools, 53 is Sockets and 73 is 3mm Sockets. But this is table is something I'll do myself; I just taught that it was important for you to understand.

I then copied the Categories Column and the Subcategories Column to a new excel Sheet and removed duplicates entries making sure to include the 2 columns. I was left with 70 unique entries. These Subcategories were given values from 10 to 79 and the new entries added to the new categories table, so far so good. Now to change the 35k entries in the Subcategories Column and this is where the problem arises for me, I can't do a Find and Replace All on the 35k entries in the subcategories column to replace the subcategory name with the corresponding new numeric value as some Subcategories names may have more than 1 Category values and therefore I can't do a replace on the subcategory column without considering the value in the Category column. And then when I go to Sub Subcategory column I will need to consider the values in the category and subcategory columns.

God I hope you had the patience to read all this as it took longer than I suspected. All help is appreciated.

Thanks,
Paul

 

[SkipVought]

Paul,

You're making this much too difficult.

Just post ONE of the SELECT queries, and that will be a starting point.

Kind of dicfficult to talk in generalities. Need SPECIFICS!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[glavster]

Thanks SkipVought, after I ran into the problem above all I did was transfer the excel file to access and nothing from there. As I mentioned above I'll try my best to get the sql query to you but I'm not sure I'll be able to do this correctly.

Thanks,
Paul

 

[SkipVought]

All we need is this part...

I need to query columns 1 & 2 for certain variables

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

glavster,

I read through your second post and I think I at least caugth the broad strokes...


It sounds like you have a composite key with optional values that you are trying to update to a single numeric key.

If I follow right you made a categories table that has columns 1-3 in it and some numeric key.

Obviously you need a numeric field to update so add it if you have not.

Secondly, Access will not join on nulls. So update both your Categories table and source table so that they contain zero length strings ("") as opposed to nulls.

Finally Inner join the two tables on the three columns and update the main table's FK.

I hope that made sense.

 

[glavster]

hmm, not sure if I understand any of that lameid.
You'll have to forgive me, as I may understand how DB'd work, I do not know how to get them to do the work.

Could you explain this a little more "composite key with optional values that you are trying to update to a single numeric key"

As for the categories table, columns 1-3 are made into 1 coulmn in the categories table.

Where do you want me to add this numeric field and why.

As far as I know there are no NULL fields but I'll make sure.

As for your last comment, tottaly lost on that one.

Paul

 

[lameid]

Composite Key - A key made up of multiple fields... You need multiple fields or columns to indentify the category or so I thought.

I thought you wanted to replace the three fields with a single number field.

I also got the impression you might want to replace each field named column with it's numeric counterpart.

Try this, list your tables, how the data relates between or among them, the fields and what field you want to be equal to what field.

 

[glavster]

The problem is there is a MySQL DB and I've to import a tab delimited text file into the products table of the MySQL DB.

I taught the MySQL DB had a table tbl_items with a categories field but it doesn't. There is a categories table called tbl_categories with the following fields (category_id, parent_category_id, category_path, category_name, category_order). Then there is a table called tbl_items_categories with the following fields (item_id, category_id, item_order). The categories table is linked to items table via item_id field in the table tbl_items_categories. The item_id field is auto incremented in the tbl_items table.

So this makes it even a little more complex but still possible, I think. You say list my tables, other than the MySQL tables that I've mentioned there are no other tables except for the tab delimited file that I transferred to MS Excel and MS Access. This file has a serious amount of fields and as I said 35k entries. But the only fields we are interested in are the 3 category fields, categories, subcategories and subsubcategories. At present this table has no relationship with any of the MySQL tables but it is possible for me to import the data into the tbl_items but there is nowhere to import the categories.

I'm going to have a go in doing this myself but I'll probably fail as I've no idea what I'm at and to be honest I don't understand what you’re saying. Thanks for the help though.

Paul

 

[glavster]

OK I managed to get an SQL query written to change the values.
Here it is

UPDATE C2K SET C2K.[Tree Class Classification] = "20"
WHERE (((C2K.[Tree Class Classification])="Projectors") AND ((C2K.[Tree Family Classification])=1));

But this means I've to change the variables manually every time which takes ages. I can import a table with the 3 columns and 70 lines of data needed to update and was wondering if the SQL could be improved to run through each 70 variables automatically.

Paul

 

[lameid]

Make a table that contains your criteria values and the update value, VariableTable in the example below.

UPDATE C2K Inner Join VariableTable ON 
C2K.[Tree Class Classification] = VariableTable.[Tree Class Classification] 
AND C2K.[Tree Family Classification] = VariableTable.[Tree Family Classification]

SET C2K.[Tree Class Classification] = VariableTable.[Tree Class Classification];

 

[glavster]

Hello lameid, I actually managed to figure it out myself but I'm still not certain that it's the way forward. It may be a better idea to write an app that can be used from the server to do the operation. The problem is even trying to import the file into MySQl times out.

So I may be getting back to you again if that's OK.

Thanks for all your help.

Paul

 

[glavster]

Hello again scholars, I'm working on a append query and I was wondering if it's possible to append a field with data from a field in another table but also add other data to it.

For example I want to append a field called category_path in the categories table with data from the field category in the items table. The data is numeric but I want to add "0," before the append and "," after the append.

For example the numeric data in the items table is 6, so I want to append the category_path to read "0,6," (with the quotation marks).

Thanks,
Paul

 

[glavster]

Hello again, I managed to figure out the last problem but now I've got another one. It's to do with the Update Query we mentioned allready. Here is the query so far,

UPDATE [Enhanced-GB] INNER JOIN va_categories ON ([Enhanced-GB].SubCategory = va_categories.parent_category_id) AND ([Enhanced-GB].Sub_SubCategory = va_categories.category_name) AND ([Enhanced-GB].Category = va_categories.category_path) SET [Enhanced-GB].Sub_SubCategory = [va_categories].[category_id];

the problem is with this line "AND ([Enhanced-GB].Category = va_categories.category_path)" The [Enhanced-GB].Category is not equal to va_categories.category_path but only to a part of it. It in this format "0,1,2, and [Enhanced-GB].Category is only equal to the 1.

Can anyone tell me how to fix that.

Thanks again,
Paul

 

[lameid]

FYI All question asked in new thread...

thread701-1565536

I recomend we all head there... I've got a meeting or I'd think about a solution now.