Query Based on Date and Equipment

[stlrain95]

I am needing a query for "Print Hours". How do I get this?

This is what I need:
I have 8 pieces of equipment
I have a date
and I need to calculate Print Hours using this formula
SELECT Sum(MASTER.[IMP])/4500 AS [Print Hours], MASTER.Equip
FROM MASTER
GROUP BY MASTER.Equip;

I need this query by date and equipment. Looking for suggestions on writing this.

Thank you,

 

[lespaul]

what is the structure of the table MASTER?

what is some sample data in this table?

What do you expect your final result to look like and how are those results calculated?



Leslie

 

[stlrain95]

Not sure about the structure Question...

Equipment/Cell Impressions Order Date
BOOKS- 4 DOC - 6180 1000 1/05/05
CNC- 1 DOC - 135 1000 1/05/05

I can print 4500 impressions/per hour on the DOC 6180

So in print hours: Sum(IMP)/4500 by date and machine

But machine per hours can vary....

So final results of query would look like this

1/5/2005 Books - 4 DOC - 6180 .22 hours
CNC - 1 DOC - 135 .22 hours

Etc....

make better sense?

 

[lespaul]

Like this:

SELECT [Order Date], Equip, Sum([IMP])/4500 AS [Print Hours]
FROM MASTER
GROUP BY [Order Date], Equip;


Leslie

 

[stlrain95]

Okay...it is asking me for the "order Date" which is fine.

How do I do 2 more things.

Print Hours is giving me 111.1111111 I only need 2 decimal points.

And is there a way to differentiate equipment based on times??

For instance, 1 piece of equip can handle 4500/per hour
While another 3500 per hour....so I need this tied in there somehow??

 

[lespaul]

you should add a field to a table that has the divider for each equipment type. It's asking for [Order Date] because your field doesn't have a space or something is different about the spelling, etc.

You will need to check into the FORMAT function (check the help, this syntax is probably wrong):

FORMAT(Sum([IMP])/4500, "#.##) AS [Print Hours]

Leslie

 

[stlrain95]

Here is what I have....

SELECT MASTER.Order_Date, MASTER.Cell_Loc, Format(Sum([IMP])/(Cell.PPM),"#.##") AS [Print Hours]
FROM MASTER, CELL
GROUP BY MASTER.Order_Date, MASTER.Cell_Loc, CELL.PPM;

Notice the group by with Cell.PPM....I don't want this part of the group..but can't take it out, won't work.

Another?

 

[PHV]

SELECT MASTER.Order_Date, MASTER.Cell_Loc, Format(Sum([IMP]/Cell.PPM),"#.##") AS [Print Hours]
FROM MASTER INNER JOIN CELL ON MASTER.Cell_Loc = CELL.Cell_Loc
GROUP BY MASTER.Order_Date, MASTER.Cell_Loc;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244