Calculations and Unions in a query

[Chky071]

I am using access to gather data about individual products. I use this data to estimate manufacturing times for production planning. There are 85 different times and each is calculated in a different way. To calculate the times I pull from various tables.

My final query has the following headers:
Model, Category, Code, Time

Model: is the product
Category: is the department that the process occurs in
Code is the: individual step that occurs in the process
Time: is the time that the step takes

Right now I have five separate categories and I have split the process up into five separate querys

DFM_SA (25 process steps = 25 individual querys unioned together)
DFM_HA (2 process steps = 2 individual querys unioned together)
DFM_GA (22 process steps = 22 individual querys unioned together)
DFM_WE (10 process steps = 10 individual querys unioned together)
DFM_FI (26 process steps = 26 individual querys unioned together)

I will then combine the five querys using a Master query for a specific model. I will then dump the resulting data into a table so that I am not constantly rerunning this massive query. I use a delete and append query to update the table, but only for the specific model.

The query runs okay, but not as fast as I would like (which is to be expected due to the size). If I run this query while I have some other tables open I get the alarm that Access cannot open more databases. I imagine this is because of how massive the query is. I am only accessing four databases, but I am guessing that each query that I have unioned together is accessing each database separately and perhaps it is overloading the system.

I am wondering what the best practice would be for this process. I am always learning and am open to a better methodology. Is there a way I could utilize a macro or some other method for streamlining this process? I am concerned about the stability of the way I am currently doing this.

Thanks,
Chad

Example of one of my querys
SELECT ITEMS_RAWMAT.MODEL, "WE" AS AREA, "WESGSAMT" AS CODE, Sum([QTY]*[WESGSAMT]) AS [TIME]


FROM (ITEMS INNER JOIN ITEMS_RAWMAT ON ITEMS.ID = ITEMS_RAWMAT.[RAW MATERIAL]) INNER JOIN [P/N Times Table] ON ITEMS_RAWMAT.MODEL = [P/N Times Table].[PART NUMBER]
GROUP BY ITEMS_RAWMAT.MODEL, "WE", "WESGSAMT", ITEMS.CATEGORY
HAVING (((ITEMS.CATEGORY)="GRAPHITE"));
UNION
SELECT ITEMS_RAWMAT.MODEL, "WE" AS AREA, "WESGSASF" AS CODE, Sum([QTY]*[PERIMETER]*[WESGSASF]) AS [TIME]
FROM (ITEMS INNER JOIN ITEMS_RAWMAT ON ITEMS.ID = ITEMS_RAWMAT.[RAW MATERIAL]) INNER JOIN [P/N Times Table] ON ITEMS_RAWMAT.MODEL = [P/N Times Table].[PART NUMBER]
GROUP BY ITEMS_RAWMAT.MODEL, "WE", "WESGSASF", ITEMS.CATEGORY
HAVING (((ITEMS.CATEGORY)="GRAPHITE"));
UNION
SELECT ITEMS_RAWMAT.MODEL, "WE" AS AREA, "WEMBMT" AS CODE, Sum([QTY]*[WEMBMT])/2 AS [TIME]
FROM (ITEMS INNER JOIN ITEMS_RAWMAT ON ITEMS.ID = ITEMS_RAWMAT.[RAW MATERIAL]) INNER JOIN [P/N Times Table] ON ITEMS_RAWMAT.MODEL = [P/N Times Table].[PART NUMBER]
GROUP BY ITEMS_RAWMAT.MODEL, "WE", "WEMBMT", ITEMS.CATEGORY
HAVING (((ITEMS.CATEGORY)="VINYL"));
UNION
SELECT ITEMS_RAWMAT.MODEL, "WE" AS AREA, "WEMBSF" AS CODE, Sum([QTY]*[WEMBSF]*[PERIMETER])/2 AS [TIME]
FROM (ITEMS INNER JOIN ITEMS_RAWMAT ON ITEMS.ID = ITEMS_RAWMAT.[RAW MATERIAL]) INNER JOIN [P/N Times Table] ON ITEMS_RAWMAT.MODEL = [P/N Times Table].[PART NUMBER]
GROUP BY ITEMS_RAWMAT.MODEL, "WE", "WEMBSF", ITEMS.CATEGORY
HAVING (((ITEMS.CATEGORY)="VINYL"));
UNION
SELECT ITEMS_RAWMAT.MODEL, "WE" AS AREA, "WEPMMT" AS CODE, Sum([QTY]*[WEPMMT])/2 AS [TIME]
FROM (ITEMS INNER JOIN ITEMS_RAWMAT ON ITEMS.ID = ITEMS_RAWMAT.[RAW MATERIAL]) INNER JOIN [P/N Times Table] ON ITEMS_RAWMAT.MODEL = [P/N Times Table].[PART NUMBER]
GROUP BY ITEMS_RAWMAT.MODEL, "WE", "WEPMMT", ITEMS.CATEGORY
HAVING (((ITEMS.CATEGORY)="VINYL"));
UNION
SELECT ITEMS_RAWMAT.MODEL, "WE" AS AREA, "WEPMSF" AS CODE, Sum([QTY]*[PERIMETER]*[WEPMSF])/2 AS [TIME]
FROM (ITEMS INNER JOIN ITEMS_RAWMAT ON ITEMS.ID = ITEMS_RAWMAT.[RAW MATERIAL]) INNER JOIN [P/N Times Table] ON ITEMS_RAWMAT.MODEL = [P/N Times Table].[PART NUMBER]
GROUP BY ITEMS_RAWMAT.MODEL, "WE", "WEPMSF", ITEMS.CATEGORY
HAVING (((ITEMS.CATEGORY)="VINYL"));
UNION
SELECT ITEMS_RAWMAT.MODEL, "WE" AS AREA, "WEAS" AS CODE, Sum([QTY]*[PERIMETER]*[WEAS])/2 AS [TIME]
FROM (ITEMS INNER JOIN ITEMS_RAWMAT ON ITEMS.ID = ITEMS_RAWMAT.[RAW MATERIAL]) INNER JOIN [P/N Times Table] ON ITEMS_RAWMAT.MODEL = [P/N Times Table].[PART NUMBER]
GROUP BY ITEMS_RAWMAT.MODEL, "WE", "WEAS", ITEMS.CATEGORY
HAVING (((ITEMS.CATEGORY)="VINYL"));
UNION
SELECT ITEMS_RAWMAT.MODEL, "WE" AS AREA, "WEAGTI" AS CODE, Sum([QTY]*[PERIMETER]*[WEAS]*IIf([ITEMS]![PART NUMBER]="VYN233072.0001",[WEAGTI],0))/2 AS [TIME]
FROM (ITEMS INNER JOIN ITEMS_RAWMAT ON ITEMS.ID = ITEMS_RAWMAT.[RAW MATERIAL]) INNER JOIN [P/N Times Table] ON ITEMS_RAWMAT.MODEL = [P/N Times Table].[PART NUMBER]
GROUP BY ITEMS_RAWMAT.MODEL, "WE", "WEAGTI", ITEMS.CATEGORY
HAVING (((ITEMS.CATEGORY)="VINYL"));
UNION
SELECT ITEMS_RAWMAT.MODEL, "WE" AS AREA, "WESJT" AS CODE, Sum([WESJT])/2 AS [TIME]
FROM (ITEMS INNER JOIN ITEMS_RAWMAT ON ITEMS.ID = ITEMS_RAWMAT.[RAW MATERIAL]) INNER JOIN [P/N Times Table] ON ITEMS_RAWMAT.MODEL = [P/N Times Table].[PART NUMBER]
GROUP BY ITEMS_RAWMAT.MODEL, "WE", "WESJT", ITEMS.CATEGORY
HAVING (((ITEMS.CATEGORY)="VINYL"));
UNION SELECT [MFG Master Database].[PART NUMBER] AS MODEL, "WE" AS AREA, "WEVH" AS CODE, [VINYL HANGER QTY]*[WEVH] AS [TIME]
FROM [MFG Master Database] INNER JOIN [P/N Times Table] ON [MFG Master Database].[PART NUMBER] = [P/N Times Table].[PART NUMBER]
WHERE ((([VINYL HANGER QTY]*[WEVH]) Is Not Null));

[orange]

You have shown us HOW you have addresses your problem/opportunity.
Can you step back and describe the "business" in simple English?
What exactly is a Product? Tell us about the steps involved?
Do you have a data model?
Why do you have to use UNION queries? Why the "HAVING" construct?

[Chky071]

Orange,

We make industrial heating blankets and product a lot of custom product for our customers. Each custom product depending on the complexity can range from 30 minutes to 3-4 hours to manufacture. I built a tool in excel that can calculate the amount of time, but it was turning into a database with a ton of Vlookups. I decided that Access would be a much better fit for this application.

The process is that I get a drawing from engineering. I Then input all the parameters that affect how much time it will take to manufacture a blanket.

For example the number of straps, blankets could have 2 straps or 20 straps.

Each strap takes time to be sewn, marked on the blanket and then glued on. There are three distinct steps involved in attaching a strap to a blanket and each takes time.

There are 85 total different steps that I can use to determine how much time it will take to product a product. It is very accurate, but I am sick of using excel to manage all the data. I am working on migrating this to Access and am just looking for the best practice.

I am using union because of how my data is setup.

For example a strap is comprised of both a length and a qty. So I have one table that has Model, Strap_type, Length and QTY, I have another table that counts the number of buckles (same gluing process, but different sewing process). The buckles only have Model and QTY. I use union because the gluing time would fall under the same category, but the data that I would calculate the time from is in two separate formats. I don't know of another way (that is why I am here).

The having construct was due to another table that I have. Our blankets are made from raw materials such as vinyl, insulation and graphite. Each of these different products take different times to cut on our equipment. However they have all the same required information: Model, length, width, perimeter, area, material type. So I used having so that I could select all the same material types for a specific process for a product to calculate the correct time for that process. I was trying to avoid having three separate tables for each data type.

I hope that helps to clarify what I am trying to accomplish.

Thanks for your time.

[orange]

Yes, your description helps. As you are/will learn Access is different than Excel. The spreadsheet info will probably have to be manipulated into designed tables to remove the union queries. Because your current set up is working, albeit with some tediousness and frustration (based on your description), I don't think it is something that can be directly moved from spreadsheet to database.
Do you have a data model that supports the business of blanket planning and manufacture?

You may want to search the forum/internet for modelling business processes or similar.

I have seen such in the past, but nothing recently.

You may find a generic model at databaseanswers.org data models manufacturing

Here is one that may help, but it is quite high level.

Good luck with your project.