Requesting info on Normalizing our Database (1 Viewer)
- Thread starter dgaletar
- Start date
The Parts table looks good, now you just have to use it properly. Service_Specs still contains text values for all the values in Parts. It should contain numeric data that links to the Parts table. Here's what I mean:
In the Service_Specs table find CustomerID=6.
In that record's [Oil Filter] field it is text data "51516", it should have numeric data 608.
Now open Parts table and find PartID=608, its the equivalent data ("51516").
The same goes for CustomerID=6 and the field Battery. It has "59-6YR", but should have 575 which in the Parts table is equivalent. That is how the Parts table and Spec Table should work.
In the Service_Specs table find CustomerID=6.
In that record's [Oil Filter] field it is text data "51516", it should have numeric data 608.
Now open Parts table and find PartID=608, its the equivalent data ("51516").
The same goes for CustomerID=6 and the field Battery. It has "59-6YR", but should have 575 which in the Parts table is equivalent. That is how the Parts table and Spec Table should work.
No problem, that's why we are here. You should create a new field in Service_Specs for each item that is going to link to the Parts table, create an update query to populate that new field and then delete the old field that holds the text data.
First though, you have some duplicate data in Parts, PartID=589 and PartID=590 are the same data. Delete one. Also, you have 2 Detail values that are "N/a". What does that mean exactly? I'd look to get rid of those 2 records.
Here's how you add that numeric field for each Part in Service_Specs. Let's use [Oil Filter] as an example (you should do all of the fields in Service_Specs that have data in Parts in this manner).
First create a new numeric field to hold that data, call it [OilFilterPartID].
Next create an UPDATE query linking Service_Specs.[Oil Filter] to Parts.PartDetails.
Bring down OilFilterPartID from Service_Specs and update it to Parts.PartID
To verify you've done it correctly create a query on Service_Specs and Parts linking OilFilterPartID to PartID, bring down [Oil Filter] and PartsDetail and they should all match. Once you've done every part like this, make a copy of your table and then delete the text fields that you have replaced with numeric.
First though, you have some duplicate data in Parts, PartID=589 and PartID=590 are the same data. Delete one. Also, you have 2 Detail values that are "N/a". What does that mean exactly? I'd look to get rid of those 2 records.
Here's how you add that numeric field for each Part in Service_Specs. Let's use [Oil Filter] as an example (you should do all of the fields in Service_Specs that have data in Parts in this manner).
First create a new numeric field to hold that data, call it [OilFilterPartID].
Next create an UPDATE query linking Service_Specs.[Oil Filter] to Parts.PartDetails.
Bring down OilFilterPartID from Service_Specs and update it to Parts.PartID
To verify you've done it correctly create a query on Service_Specs and Parts linking OilFilterPartID to PartID, bring down [Oil Filter] and PartsDetail and they should all match. Once you've done every part like this, make a copy of your table and then delete the text fields that you have replaced with numeric.
OK, the first part was a no brainer. Created the field and made it NUMERIC!
The second a bit tougher. I created a Select query with the following fields:
- Service_Specs.Oil Filter
- Parts.PartDetails
- Service_Specs.OilFilterPartID
- Parts.PartType
It says: "You are about to update 0 rows". I'm sure that's not right...
I also ran the "TEST" query like suggested, and all fields are empty.
Last edited:
Lets step through this:
>> UPDATE Parts INNER JOIN ServiceSpecs
That means you are updating the Parts table which is incorrect. You are want to update ServiceSpecs. That also means your join will be switched, you will be joining Parts to ServiceSpecs, not vice versa as you have done.
>> ON Parts.[PartID] = ServiceSpecs.[OilFilter]
You are trying to link PartID of Parts to OilFilter field of ServiceSpecs. That's not going to work--PartID has numeric data, OilFilter has text data. You need to link OilFilter to PartsDetail.
>> SET Parts.PartID = "OilFilterPartID";
You are setting PartID in the Parts table to the string "OilFilterPartID". First and second, as I said before you aren't updating any fields in Parts and PartID is numeric not text. You want to update the new field you created in your ServiceSpecs table called [OilFilterPartID] to the number in Parts.PartID
OK, I think that I am getting this. (I have to say that once I copied and pasted the SQL, it didn't look right to me. But I didn't know how to change it!)
So, the first step would be to UPDATE the ServiceSpecs table, and JOIN it to the Parts table:
>>UPDATE ServiceSpecs INNER JOIN Parts
The second step would then be to LINK ServiceSpecs.[OilFilter] to Parts[PartsDetail]:
>>ON ServiceSpecs.OilFilters = Parts.PartDetail
Then bring it home by UPDATING ServiceSpecs.[OilFilterPartID] to it's numeric value in the Parts.[PartsID] field, like this:
>>SET ServiceSpecs.OilFilterPartID = Parts.PartID;
Am I at least on the right track here??? Would this be the correct SQL for this task?
UPDATE ServiceSpecs INNER JOIN Parts
ON ServiceSpecs.OilFilters = Parts.PartDetail
SET ServiceSpecs.OilFilterPartID = Parts.PartID;
It seems right.
So, the first step would be to UPDATE the ServiceSpecs table, and JOIN it to the Parts table:
>>UPDATE ServiceSpecs INNER JOIN Parts
The second step would then be to LINK ServiceSpecs.[OilFilter] to Parts[PartsDetail]:
>>ON ServiceSpecs.OilFilters = Parts.PartDetail
Then bring it home by UPDATING ServiceSpecs.[OilFilterPartID] to it's numeric value in the Parts.[PartsID] field, like this:
>>SET ServiceSpecs.OilFilterPartID = Parts.PartID;
Am I at least on the right track here??? Would this be the correct SQL for this task?
UPDATE ServiceSpecs INNER JOIN Parts
ON ServiceSpecs.OilFilters = Parts.PartDetail
SET ServiceSpecs.OilFilterPartID = Parts.PartID;
It seems right.
Last edited:
OK, so I went in and changed the Parts.PartsDetail field to "Number", and it ran. I stopped it because I wanted to check with you first. As you know, many of the filters use letters in their part number. If I change the Parts.PartsDetail field to "Number" permanently, I will loose many records. Not to mention that I will not be able to enter part numbers properly.
Your thoughts???
Your thoughts???
Yeah, that's not the answer. PartsDetail is text and so should be ServiceSpecs.OilFilter. Is it?
Also, this is a little confusing--in the last file you sent the table name was 'Service_Specs' not 'ServiceSpecs'. And the field name was 'OilFilter' not 'OilFilters'.
Also, this is a little confusing--in the last file you sent the table name was 'Service_Specs' not 'ServiceSpecs'. And the field name was 'OilFilter' not 'OilFilters'.
OK, answer A = PartsDetail WAS Text, but I changed it to Number. ServiceSpecs.OilFilter WAS also Text, but I also changed it to Number. (this was over the course of this whole thread) I then changed ServiceSpecs.OilFilter BACK to TEXT, but for some reason left PartsDetail as a Number. NOW, BOTH ARE TEXT!!!
Answer B, well I think that was done as one of your suggestions. I got rid of all underscores (and the likes). As for 'OilFilter' not 'OilFilters', that was a typo in my last reply.
I need a minute to remember where it was that you told me how to populate the "Oil Filter" part of the new Parts table. I just erased all of the part numbers that had text in them.
Answer B, well I think that was done as one of your suggestions. I got rid of all underscores (and the likes). As for 'OilFilter' not 'OilFilters', that was a typo in my last reply.
I need a minute to remember where it was that you told me how to populate the "Oil Filter" part of the new Parts table. I just erased all of the part numbers that had text in them.
Similar threads
