The current database is structured somewhat horizontally, a structure commonly seen in spreadsheets, but one that just doesn’t work well in relational databases. In this post I intend to demonstrate how you could restructure the database to get it in normal form. I will be referring to the attached database where I normalized the assembly labor costs as an example. This need to be repeated for each of the problem areas which are those where you see repeating groups such as Inventory Components and Components.
The repeating groups need to be organized into to single tables. For the assembly costs I first created a query for each of the six groups (qryAssembly1 – qryAssembly6) in the attached database. These are identical in structure and just pull the data from six repeating sections in the BOM table. I also included in each query an expression which identifies the group it came from. I first didn’t think this would be necessary, but in a few cases the assembly type is repeated for the same part with different times. More about this later. To get the data into a single table the data from these queries needs to be stacked together vertically. We can do this with a UNION query.
Access doesn’t do much for you when it comes to UNION queries. When you create a UNION query with the Query Designer all it gives you is a blank area in which you can copy and paste in the SQL. So for each of the queries (qryAssembly1 – qryAssembly6) I opened them in design view, switch to SQL view, copied the SQL, switched to the UNION query and pasted the SQL. Then I put UNION ALL between these SELECT queries. The finally result is qryAssemblyUnion. You can use this UNION query as a source in make table query and create this table. In the attached database I name this table ASSEMBLY.
If you look at the contents of ASSEMBLY you will see that it only has 1598 records. This highlights the advantage to having a proper structure. In the existing structure there are six groups of these fields for each of the 1339 records in the BOM table. That means 6 times 1339 or 8034 calculations for this horizontal structure versus 1598 for the proper vertical structure.
Now please look at Relationships under the DATABASE tab. This shows how this new table is related to the BOM2 table and the LABOR table. Note that BOM2 is just the BOM table with the fields that were moved to the ASSEMBLY table removed.
qryAssemblyLabor is a query which joins the ASSEMBLY and LABOR tables and computes the Assembly Cost. This is the record source for the AssemblyLaborSub form. This is used in the subform of the BOM2 form.
Please open the BOM2 form. This shows how a one to many relationship such as BOM to ASSEMBLY is typically set up. The first ten records in the BOM2 table don’t have any related assemblies so you will have to scroll through some records before you will any data there but please use the Go To Part combo box to go to part 1422. This part demonstrates why I included the Assembly No in the table. There seem to be some meaning assigned to the positions as the ASSEMBLY type appears twice with different times. This is a design question for you. How do you want to distinguish duplicate assemble types for the same part? Maybe there is something better than just a number or can the duplicates be combined.
The BOM2 form also includes the sum of the assembly labor costs from the subform. This was added with the instructions from
this site.
Getting the database structure correct will help a lot in solving this slow query. Aside from reducing the computations this demo reduced the number of joins by five. These numerous join are also slowing down your query.
I suggest you read up on relational database design and normalization. Just google these subjects. You will need to know the basics of this to make sure your design is correct.
pen?
