[mossman65]

Hello Everyone,

Thank you in advance.

My company is switching part numbers. We would like to display the new part number along with the old part number for the next few months to get customers and vendors used to the new system of numbers.

The main issue is that the data that was provided to me is set up totally different than our original data...

I would like to know how to do the following join inside of a cfquery/logic.

I have three tables:

• Original data (Year, make, model, description, etc all in their own columns)
  
• The new data (in one row i now have a "69 coronet, 70 corvette, 75 Camaro going on for 570 columns!!!!" along with other columns that provide specs for the part. I'm using this to build my drop downs)
  
• New Part Numbers and applications (vehicles they fit)

I would like to doing something similar to:

SELECT PartNumber FROM Apps WHERE EXISTS
(SELECT YMM FROM YMM WHERE Apps.App1 = '69 Coronet' OR Apps.App2 = '69 Coronet'
 OR Apps.App3 = '69 Coronet' OR Apps.App4 = '69 Coronet' OR Apps.App5 = '69 Coronet'
 OR Apps.App6 = '69 Coronet' OR Apps.App7 = '69 Coronet' OR Apps.App8 = '69 Coronet'
 OR Apps.App9 = '69 Coronet' OR Apps.App10 = '69 Coronet' OR Apps.App11 = '69 Coronet'
 OR Apps.App12 = '69 Coronet' OR Apps.App13 = '69 Coronet' OR Apps.App14 = '69 Coronet'
 OR Apps.App15 = '69 Coronet' OR Apps.App16 = '69 Coronet' OR Apps.App17 = '69 Coronet'
OR Apps.App18 = '69 Coronet' OR Apps.App19 = '69 Coronet' OR Apps.App20 = '69 Coronet'
OR Apps.App21 = '69 Coronet' OR Apps.App21 = '69 Coronet' OR Apps.App22 = '69 Coronet'
)

This returns any new part numbers that fit a 69 Coronet with specific engine configurations (12 results for this example).

At this point i would like query the old data for a "1969" "Dodge" "Coronet" bring back the results and compare the engine configurations from the new data, update the part number for any that will become custom builds, and also set a new part number for the ones that we will continue to carry in stock in a new column with the new part number.

I'm concerned that this is going to be crazy slow for a search...

I hope i wrote this alright... And any suggestions on a better way to approach this would be welcome.

[Craig328]

Moved this to Databases as the question is primarily SQL

[mossman65]

Craig328, on 09 May 2012 - 11:44 AM, said:

Moved this to Databases as the question is primarily SQL

Thank you.

[e_i_pi]

Are you able to provide a screenshot of part of the new data? If I understand you correctly when you say "this data goes on for 570 columns", then my first impression would be that you're boned.

[mossman65]

e_i_pi, on 09 May 2012 - 03:14 PM, said:

Are you able to provide a screenshot of part of the new data? If I understand you correctly when you say "this data goes on for 570 columns", then my first impression would be that you're boned.

Yeah, its pretty nasty data... Our products can fit multiple makes, models, and years. One of our radiators it fits 500+ vehicles spanning ten years.

Thank you!

Attached image(s)

• 

[e_i_pi]

Hmm, the headers would have been handy. Anyhow, I get the gist.

I take it column L onwards are the make/models that the part fits? You should be able to translate the data into a normalised format, but you'll need some sort of code to read the Excel file (or CSV, which you could save it as), interpret each line, and write separate lines to either a new CSV file or a database.

I could give you a good schema for the DB if you like, I'll need to know the headers of that image, but the data rewrite is another matter. That would need to be done in a programming language of some sort for best results.

[mossman65]

e_i_pi, on 10 May 2012 - 07:11 AM, said:

Hmm, the headers would have been handy. Anyhow, I get the gist.

I take it column L onwards are the make/models that the part fits? You should be able to translate the data into a normalised format, but you'll need some sort of code to read the Excel file (or CSV, which you could save it as), interpret each line, and write separate lines to either a new CSV file or a database.

I could give you a good schema for the DB if you like, I'll need to know the headers of that image, but the data rewrite is another matter. That would need to be done in a programming language of some sort for best results.

I would love a schema, Thank you! I'm asking one of our off site excel gurus if he can parse the data into a new row for each make/model that fits a particular part number. I wouldn't know where to begin to translate the data into a normalized format. Hopefully he can.

I have attached a small spreadsheet for your review. Thank you very much!

[e_i_pi]

Hmm, I can't see the attachment. I'm off to bed now anyhow, won't be able to look at it for another 8 hours or so. Catch you in the morning.

[mossman65]

e_i_pi, on 10 May 2012 - 07:21 AM, said:

Hmm, I can't see the attachment. I'm off to bed now anyhow, won't be able to look at it for another 8 hours or so. Catch you in the morning.

Thanks man!

Attached image(s)

• 

[e_i_pi]

Okay, at a pinch, a reasonable setup would be this:

Applications

ID (PK)
Description
MakeID (FK -> Makes.ID)

Makes

ID (PK)
Description

PartTypes

ID (PK)
Description

Parts

ID (PK)
PartNumber (Unique)
PartTypeID (FK -> PartTypes.ID)
CoreWidth
CoreLength
CoreThickness
TubeSize
Rows
Outlets
AdditionalInfo
Exception

ApplicationParts

ID (PK)
PartID (FK -> Parts.ID)
ApplicationID (FK -> Applications.ID)
ApplicationYear

You wouldn't necessarily need the Makes table, but I put it in there for completeness. You could also move the ApplicationYear column from the ApplicationParts table to the Applications table, depending on what level of normalisation / convenience you're after, but I'd need to know a lot more about your setup.

There's a few small issues with this schema though:

Firstly, do all part types (e.g. - radiator) have the same attributes, such as CoreWidth, CoreLength, TubeSize, Rows, etc? If not, then you'll need to abstract out the attributes into a separate table / tables, which tends to get a bit messy. There's a couple of ways to do this that I know of.

Secondly, the column labelled AddInfo2 only seems to have two values - N/A and TOC. Are these the only two values for that column? What do they mean? You could potentially compress two rows that differ only in AddInfo2 by using a flag, or pair of flags.

[mossman65]

e_i_pi, on 10 May 2012 - 03:41 PM, said:

Okay, at a pinch, a reasonable setup would be this:

Applications

ID (PK)
Description
MakeID (FK -> Makes.ID)

Makes

ID (PK)
Description

PartTypes

ID (PK)
Description

Parts

ID (PK)
PartNumber (Unique)
PartTypeID (FK -> PartTypes.ID)
CoreWidth
CoreLength
CoreThickness
TubeSize
Rows
Outlets
AdditionalInfo
Exception

ApplicationParts

ID (PK)
PartID (FK -> Parts.ID)
ApplicationID (FK -> Applications.ID)
ApplicationYear

You wouldn't necessarily need the Makes table, but I put it in there for completeness. You could also move the ApplicationYear column from the ApplicationParts table to the Applications table, depending on what level of normalisation / convenience you're after, but I'd need to know a lot more about your setup.

There's a few small issues with this schema though:

Firstly, do all part types (e.g. - radiator) have the same attributes, such as CoreWidth, CoreLength, TubeSize, Rows, etc? If not, then you'll need to abstract out the attributes into a separate table / tables, which tends to get a bit messy. There's a couple of ways to do this that I know of.

Secondly, the column labelled AddInfo2 only seems to have two values - N/A and TOC. Are these the only two values for that column? What do they mean? You could potentially compress two rows that differ only in AddInfo2 by using a flag, or pair of flags.

Shaweet, Thank you for all your help! It will make this allot easier now. I think our Excel guy is prepping the data. Once i get i will get it built and hopefully get your opinion. Have a great day!