Query - Look up a second version of the same table based on a concatenated field

[Shani]

Hi

I need to look up 3 description values from the same table based on various versions of the same field.

I have set up the fields using SQL (I couldn't work out how to do it in Access) as ClassCode1, ClassCode2 and ClassCode3.

ClassCode3 is the number as it appears in the normal table so I can do that one.

ClassCode1 & 2 however are the first 3/6 digits of ClassCode3 with "0"'s at the end.


This is my SQL to get this far which works:

SELECT dbo_Products.PartNo, dbo_Products.ManuID, dbo_Product_Class.Description, Left(dbo_Products.ClassCode,3) & "000000" AS ClassCode1, Left(dbo_Products.ClassCode,6) & "000" AS ClassCode2, dbo_Products.ClassCode AS ClassCode3
FROM (New_SKU INNER JOIN dbo_Products ON New_SKU.Field1 = dbo_Products.PartNo) INNER JOIN dbo_Product_Class ON dbo_Products.ClassCode = dbo_Product_Class.ClassCode;



I need to look up both ClassCode1 & 2 against the same dbo_Products.ClassCode to get their descriptions (as with ClassCode3) but I can't work out how to do it.

I tried adding a second version of dbo_Product_Class but I couldn't work out how to join it to my concatenated fields.

Any help would really be appreciated.

Thanks

Shani

[ItsMe]

I need to look up 3 description values...

I am not sure how you will use/apply your results. If you had three different query objects at your disposal, one for each of the versions, how could you use these objects in your application?

[Shani]

Hi

It's because the full code (classcode3) is made up of multiple parts i.e. Classcode1 Description = 'Homeware', ClassCode2 Description = 'Kitchen' and Classcode3 Description is 'Cooking Utensils' - but they are all in the same table and there are 2 million of them.

I need to export them in this format so I can import them into Omniture to use as categories.

Shani

[ItsMe]

I need to export them in this format so I can import them into Omniture to use as categories.

So each alias will populate a respective column in a new table? Sounds like committing spreadsheet. Otherwise.....

Might be able to nest an instant if and do it all at once. Not sure how much down time you have to work with...
or
Maybe create an append query using the original version and then run an action query to UPDATE using an instant if statement for your two alias'

[June7]

I'm a little lost. Description is already in the query - what description do you need to retrieve? Could you provide sample of raw data and desired output (tables in post or attach file).

[ItsMe]

I'm a little lost. Description is already in the query - what description do you need to retrieve? Could you provide sample of raw data and desired output (tables in post or attach file).

I am too. I may be assuming too much...

[Shani]

Sorry, The description for class code 3 is in the query and pulls fine. I can't UPDATE the master.

The same table holds the description for the other class codes which I need against all of them.

eg

Code	|Description
123465789	|Cooking Utensils
123456000	|Kitchen
123000000	|Homeware

To get the code itself I have taken the first 3 digits of classcode3 and added zeros (123000000), the second I have taken the first 6 digits and added zeros (123456000) - but I then need the names of these categories from the same table.

This is what is should look like

Product ID	Classcode1	Classcode2	Classcode3	Description Classcode1	Description Classcode2	Description Classcode3
abcdefg	123000000	123456000	123456789	Homeware	Kitchen	Cooking Utensils

I hope that makes sense.

[ItsMe]

Understanding the left() function and the alias' is not the issue.

[Shani]

Yes, ItsMe I want this in a new table but I can't amend the original. :/

[June7]

I am still confused. The 'raw data' does not show ManuID or PartNo fields. Where does ProductID come from?

Possibly you need to take the query shown in first post and use it in another query that pulls in the original table 3 times in joins to each of the Classcode fields.