Combining Queries Problem

[vgersghost]

Combining Queries

The goal was to track equipment downtime, by each piece of equipment, but the WC might have multiple downtimes on multiples equipment each day. So you end up with equipment numbers in different parts of the table.(mach # 1234) I’ve been trying to figure out how to combine all the data without much luck. Joining Mach# and summing the times of minutes in the different fields. I tried making a new table and appending or updating, but that did not seem to work. The person entering data has six places on the form, so she can enter mach# in any of the six places.

Thank you for any ideas.


Example:

WC 1 has equipment problems on day 1

Equip # MC-1234 Mins 15 (stored in table as Maint1Mach and Maint1Min)
Equip # MC-4567 Mins 20 (stored in table as Maint2Mach and Maint2Min)

WC 1 has equipment problems on day 2
Equip # MC-4567 Min 90 (stored in table as Maint1Mach and Maint1Min)
Equip # MC-7890 Min 20 (stored in table as Maint2Mach and Maint2Min)
Equip # MC-1234 Min 20 (stored in table as Maint3Mach and Maint3Min)


Thank you
ATTCAHMENT:

 

[ajetrumpet]

The goal was to track equipment downtime, by each piece of equipment

group the records by Maint1 and Minutes1. then UNION all of the separate queries. Do you know how to do this??

 

[vgersghost]

Thank you ajetrumpet

I understand grouping. Each query group seperately, but I'm not sure about union queries. Any help would be appreciated.

 

[ajetrumpet]

To union them, make sure you have the same number of fields in each of the summed queries. Then...

SELECT [fields] FROM [query1]
UNION ALL
SELECT [fields] FROM [query2]
UNION ALL
SELECT...etc, etc....

 

[vgersghost]

Thank you again ajetrumpet

This is what I've started, but I get errors trying to group and sum. Not sure how to write this line of code.

SELECT tblShiftLogEntries.Maint1Mach, tblShiftLogEntries.Maint1Min, tblShiftLogEntries.WorkCenterID
FROM [qrymaint1]
UNION ALL
SELECT tblShiftLogEntries.Maint2Mach, tblShiftLogEntries.Maint2Min, tblShiftLogEntries.WorkCenterID
FROM [qrymaint2]

Group BY ????

 

[ajetrumpet]

SELECT tblShiftLogEntries.Maint1Mach, tblShiftLogEntries.Maint1Min, tblShiftLogEntries.WorkCenterID
FROM [qrymaint1]
UNION ALL
SELECT tblShiftLogEntries.Maint2Mach, tblShiftLogEntries.Maint2Min, tblShiftLogEntries.WorkCenterID
FROM [qrymaint2]

Group BY ????

The code says that you're selecting fields from the log entries table, but in the FROM clause you have queries listed. That's the first error. Remember too that when you create a field by writing an expression or function, you have to give it an alias for a field name so you can reference it later, or even in the same query.

Maybe the .zip will help you figure out what you need. Take a look...

 

[vgersghost]

Wow! Thanks

Thank you so much. I looked at the all the queries and learn a few simple ideas I can put to use in other places. I have one last question. The equipment numbers appear through out the list, but I need to group them along with the sum of minutes. I took the AEUnionMaint and made another query to group mach1 and sum minutes. Is this correct or could I put code in the AEUnionMaint. I'll play with it for awhile.

Thanks again

 

[ajetrumpet]

Welcome

Thank you so much.

Welcome!

The equipment numbers appear through out the list, but I need to group them along with the sum of minutes.

That would be your last step then (to get TOTAL downtime in minutes for each machine).

I took the AEUnionMaint and made another query to group mach1 and sum minutes.

That's what you need to do. Did it work?? (you could check it too by matching the total sum you got after the UNIONS to the sum of the five "minutes" fields in the orginal table. They should match)...

Thought of something else too...now that you've done this, you can simply click a botton the next time you want to view these totals. That'll sure be helpful when you start entering record # 5,000.

 

[vgersghost]

Thats what I did!

Made a query out of the AEUnionMaint, added goups and sums, it worked great. I cannot thank you enough. I'm going to use that query in a report. Like the buttom idea for the report or maybe a datasheet form.

Thanks again for all you help. I hope I will be able to help repay the favor to someone else on the forum, after getting maybe a little closer to your expertise level. Could take awhile.

dmh

 

[ajetrumpet]

Sounds like you've gained a few steps toward immortality...

Good Luck.