Requesting info on Normalizing our Database

[dgaletar]

Good morning plog! And I am happy to say that I am off to a roaring start!!!

I built the Append query and transferred everything back into the Parts table. I then ran the Update query for each part used, and everything went VERY smoothly! (except for the fact that I apparently can't spell battery!)

So now I am left with the individual tables (that I believe that I do not need anymore), and the original fields in the ServiceSpecs table (Oil Filter, Air Filter, Fuel Filter, & Battery).

Do I need to delete those columns now? And can I delete those individual tables now? And, lastly, is my form still going to work properly?

I await your response...

 

[plog]

Yes you can delete all of that. Before you do, make a copy of your database as it is now and put the date in the file name. You always want to have a good back up somewhere.

 

[dgaletar]

So, is that it??? Are we finished here???

And, do I still need that UPDATE query? I shouldn't, right???

 

[dgaletar]

NUTS! I knew it was too good to be true. Now my form is all messed up. Where does it get the list of filters for the drop downs on the forms now? From the ServiceSpecs table? Cause, if that's true, it needs to use the "OilFilterPartID" field, right? And, if that's true, it needs to be a Number, and NOT Text. And here I am running into the problem that I thought I was going to run in to... part numbers with letters in them.

Also, when I look at my records now (in the form view anyway), I'm getting the 3-digit PartID, and not the actual part number.

 

[plog]

You changed fields and their names, merged some tables: You essentially replaced your 2 by 4 and stucco foundation with a proper one. Of course a few doors based on that bad structure are now going to hang wrong. That's why you do the boring foundation work first then decide what pretty shade of perriwinkle to paint the front bathroom.

Your drop downs need to use the appropriate field(s). The Oil Filter drop down for example will most likely be based on 2 fields from Parts--PartID and PartDetails--you could even use a 3rd. You make your drop down a 2 or 3 column drop down box, but based on the PartID. You can then show all those fields, or just the 2 like you had before.

You should be able to search this form on how to do that, I even think a wizard helps you set it up.

 

[dgaletar]

OK, I'm breathing again. Please tell me that we will be able to paint this database a "pretty shade of periwinkle" soon!?! lol

Off I go to create new combo boxes!

In the words of a famous Governor, "I'll be back!”

 

[dgaletar]

OK, that fire is out! Next on the list... Reports.

I have a report for each of the filters (and the batteries), telling us how many of each filter was required for our vehicles. In other words, we had like 8 vehicles using Oil Filter #51036.

The reports don't work now, but I'm sure that can be fixed!!!

My goal is to have the report look like this:\

Oil Filters (Number) // Brand // Quantity

Here is the SQL that I am currently using:

SELECT Parts.PartType AS [Filter Type], Parts.PartDetails AS [Filter Part Number], Count(ServiceSpecs.OilFilterPartID) AS [Count Of Oil Filters Used], Parts.PartBrand AS [Filter Brand]
FROM ServiceSpecs INNER JOIN Parts ON ServiceSpecs.OilFilterPartID = Parts.PartID
WHERE (((ServiceSpecs.OilFilterPartID) Is Not Null))
GROUP BY Parts.PartType, Parts.PartDetails, Parts.PartBrand
HAVING (((Parts.PartType)="Oil Filter"));

The problem is that it is "Grouping" by [Filter Type], [Filter Part Number], AND [Filter Brand]. That means that only one brand will show at a time.

Is there a way around that? And, is that hard to do with the way that we set everything up?

 

[boblarson]

Sorry, I wasn't able to work on this with you. I have seen your current structure and it is still non-normalized. Your service specs table should NOT have fields like OilFilterID, AirFilterID, etc.

I think we need to revisit this. I will try to throw together what I think the table structure should look like.

 

[boblarson]

Well, this is going to take a lot of work, and time which I don't have. It may not be worth it at this point to go over it again. We'll go with what you have now.

 

[dgaletar]

I don't understand!?! plog and I spent over a week "Normalizing" this stupid thing! ARRRRGGG!

 

[boblarson]

I don't understand!?! plog and I spent over a week "Normalizing" this stupid thing! ARRRRGGG!

Plog apparently missed a few things.

1. There shouldn't really be separate fields for the different parts.

2. That should be done with a junction table.

3. The whole service specs table can be reduced down.

tblServiceSpecs
ServiceSpecID - Autonumber (PK)
CustomerID - Long Integer (FK)
ServiceTypeID - Long Integer (FK)
ServiceTypeValue - Text

tblServicetypes
ServiceTypeID - Autonumber (PK)
ServiceTypeDesc - Text


So the values in service types would be like

1     Tires
2     Wheel Torque
3     Oil Type
4     Oil Qty
5     Brake Fluid
...etc

And in the Service Specs table it would look like

1     1     1    215/70R/14
2     1     2    0
3     1     3    5W-30
...etc.

 

[dgaletar]

OK, I think I understand... a little, anyway.

So where would the "Parts" item numbers be? Like the oil filter numbers & brands, ect.? Would they be in their own tables? Like the wipers are now?

 

[boblarson]

Actually, wipers should not be in its own table either. It belongs in the parts table really.

In the service types table you would have (the ID numbers are just examples):

10      DS
11      PS
12      Rear

As for the Parts, The parts table would be like it is now but for ALL parts.

And you could make the specs table like this:

[B]tblServiceSpecs
[/B]ServiceSpecID - Autonumber (PK)
CustomerID - Long Integer (FK)
ServiceTypeID - Long Integer (FK)
ServiceTypeValue - Text
 
[B]tblServiceSpecParts[/B]
ServiceSpecPartID - Autonumber (PK)
ServiceSpecID - Long Integer(FK)
PartID - Long Integer (FK)

Then if there are any parts to go along with the service spec, they can be listed and more than one part can be included on a service spec.

And if a service doesn't require a part, then it would just be blank.

 

[dgaletar]

OK, I see where you are going with this. But you do understand that the ServiceSpecs table is ONLY for us to see what the specifications are for each vehicle, and actually has nothing to do with the monthly servicing of the vehicle, right?

 

[dgaletar]

I mean, i just want to make sure you understand how we are using this database. First and foremost to allow us to be able to look up the vehicle specifications in one quick easy to access location; and secondly to track specific work that we perform on a vehicle. We are using the "workorders" form to track that part of it.

 

[boblarson]

OK, I see where you are going with this. But you do understand that the ServiceSpecs table is ONLY for us to see what the specifications are for each vehicle, and actually has nothing to do with the monthly servicing of the vehicle, right?

Yep, and the actual Service tables will be set up in a similar way.

 

[dgaletar]

OK, I just didn't want you thinking we were using it differently.

I'm going to take the latest backup of this database, empty it, and start over the way that you suggested first thing tomorrow morning!

Thanks for the suggestions! promise you won't abandon me whilest I do this...

 

[boblarson]

Thanks for the suggestions! promise you won't abandon me whilest I do this...

I can't guarantee anything but I can promise I will try my hardest to stick with you on it. (work sometimes gets in the way LOL).

 

[dgaletar]

No worries. I'll keep the database that I currently have, and continue updating it as I go with vehicle information and workorders.

I'm in no hurry, so I think that we should be good.

FYI - I just copied the database and named it NEW1! I deleted EVERYTHING out of it except for the following tables:

• Department (which is a listing of all of the Departments that the vehicles belong to)
• Employees (which are the employees in our shop that will be doing the work)
• MyCompanyInformation (self explanatory)
• Parts (but I emptied it)
• Vehicles (but I also emptied it)

First thing tomorrow morning I will begin rebuilding the other tables as you laid them out.


Thanks!

 

[dgaletar]

Good morning Bob... may I call you Bob? Anyway, here is the work that I did this morning. If you wouldn't mind looking at it and pointing me in the next direction, I would GREATLY appreciate it!

Thanks!