Totals query with an expression

Right now I have my WorkedAreaHoursTotals query as a source for a crosstab query and it's working like I want it to. Now I want the hours on the crosstab query to show my hours as shifts worked rather than hours. A 12 hour period will be equal to 1. I know I need to fix this by changing the source query so here is the info about my source query. I will also want to only show the records for the current 1/2 year. So from Jan-June and July-Dec and I am not sure where I would add that criteria.

My query totals up hours employees work in a specific area. Our shifts are 12 hour shifts so I want the hours expressed in shifts worked and not hours. I think I need to add a field and make an expression using the WorkedAreaHours field which shows the sum when the query is run. I also think I need to divide by 12 so the expression will show 1 for 12, 2 for 24, 3 for 36 etc... sometimes people only work in an area for 4 or 6 hours so this needs to be able to show the answer in a decimal form....so if a person worked 12 hours one day and 6 another day in that area, the field would be 1.5.

Am I on the right track? If so, now I don't know how to make the expression or what it should say. I use the query grid to make my querys and not writing the sql.

Access 2000

Thanks,
Linda

Tried this and a bunch of other things too...Expr1: [Sum(QRYAreasWorked.WorkAreaHours)]/12

Didn't work. LOL
"LMB" wrote in message ...
Right now I have my WorkedAreaHoursTotals query as a source for a crosstab query and it's working like I want it to. Now I want the hours on the crosstab query to show my hours as shifts worked rather than hours. A 12 hour period will be equal to 1. I know I need to fix this by changing the source query so here is the info about my source query. I will also want to only show the records for the current 1/2 year. So from Jan-June and July-Dec and I am not sure where I would add that criteria.

My query totals up hours employees work in a specific area. Our shifts are 12 hour shifts so I want the hours expressed in shifts worked and not hours. I think I need to add a field and make an expression using the WorkedAreaHours field which shows the sum when the query is run. I also think I need to divide by 12 so the expression will show 1 for 12, 2 for 24, 3 for 36 etc... sometimes people only work in an area for 4 or 6 hours so this needs to be able to show the answer in a decimal form....so if a person worked 12 hours one day and 6 another day in that area, the field would be 1.5.

Am I on the right track? If so, now I don't know how to make the expression or what it should say. I use the query grid to make my querys and not writing the sql.

Access 2000

Thanks,
Linda

Hi Linda,

You seem to be much further advanced than I'd assumed from your "Best
Practices" post.

I'm not at all sure I understand the ins and outs of your question but would
something like this work:

Expr1: Format([Sum(QRYAreasWorked.WorkAreaHours)]/12 , "##.00")

HTH
--
-Larry-
--

"LMB" wrote in message
...
Tried this and a bunch of other things too...Expr1:
[Sum(QRYAreasWorked.WorkAreaHours)]/12

Didn't work. LOL
"LMB" wrote in message
...
Right now I have my WorkedAreaHoursTotals query as a source for a crosstab
query and it's working like I want it to. Now I want the hours on the
crosstab query to show my hours as shifts worked rather than hours. A 12
hour period will be equal to 1. I know I need to fix this by changing the
source query so here is the info about my source query. I will also want to
only show the records for the current 1/2 year. So from Jan-June and
July-Dec and I am not sure where I would add that criteria.

My query totals up hours employees work in a specific area. Our shifts
are 12 hour shifts so I want the hours expressed in shifts worked and not
hours. I think I need to add a field and make an expression using the
WorkedAreaHours field which shows the sum when the query is run. I also
think I need to divide by 12 so the expression will show 1 for 12, 2 for 24,
3 for 36 etc... sometimes people only work in an area for 4 or 6 hours so
this needs to be able to show the answer in a decimal form....so if a person
worked 12 hours one day and 6 another day in that area, the field would be
1.5.

Am I on the right track? If so, now I don't know how to make the
expression or what it should say. I use the query grid to make my querys
and not writing the sql.

Access 2000

Thanks,
Linda

Thanks Larry. I am just starting to "get" the concept of the relational database and because of that, I want to start doing more because I know what is possible from reading the posts in the group. I consider myself right about midbeginner level and just do basic things right now. Any of the expressions I have in my databases are all from help in here, and everyone is so nice! I did figure out how to type the concatenate expression from something similar someone else asked once. G When I get a chance, I'll try this below and let you know how it is working.

Thanks again.
Linda
"Larry Daugherty" wrote in message ...
Hi Linda,

You seem to be much further advanced than I'd assumed from your "Best
Practices" post.

I'm not at all sure I understand the ins and outs of your question but would
something like this work:

Expr1: Format([Sum(QRYAreasWorked.WorkAreaHours)]/12 , "##.00")

HTH
--
-Larry-
--

I pasted this in a new field and got an error. I went back to my table and found that the hours field was text instead of number so I changed it to number. All of my queries still return the right number but I still got the same error.

Expr1: Format([Sum(QRYAreasWorked.WorkAreaHours)]/12 , "##.00")

Error. "Invalid bracketing of name"

Here is the sql in case it helps you to see where I errored but I used the query grid to insert the expression. I've tried some different things and looked in my chapter about expressions in my QUE Access 2000 book but I can't understand it very well since I don't really understand the programming yet. It talks about operators, operands, literals, functions...I need more time to absorb that stuff....maybe like 10 years or so.

SELECT qryDepartmentEmployees.dttHireDate, qryDepartmentEmployees.strSSNumber, qryDepartmentEmployees.Name, QRYAreasWorked.WorkArea, Sum(QRYAreasWorked.WorkAreaHours) AS SumOfWorkAreaHours, Format([Sum(QRYAreasWorked.WorkAreaHours)]/12,"#.00") AS Expr1
FROM QRYAreasWorked RIGHT JOIN qryDepartmentEmployees ON QRYAreasWorked.EmployeeID = qryDepartmentEmployees.strEmployeeID
GROUP BY qryDepartmentEmployees.dttHireDate, qryDepartmentEmployees.strSSNumber, qryDepartmentEmployees.Name, QRYAreasWorked.WorkArea, qryDepartmentEmployees.strShift, Format([Sum(QRYAreasWorked.WorkAreaHours)]/12,"#.00")
HAVING (((qryDepartmentEmployees.strShift)="days"))
ORDER BY qryDepartmentEmployees.dttHireDate, qryDepartmentEmployees.strSSNumber;


Thanks
Linda
"Larry Daugherty" wrote in message ...
Hi Linda,

You seem to be much further advanced than I'd assumed from your "Best
Practices" post.

I'm not at all sure I understand the ins and outs of your question but would
something like this work:

Expr1: Format([Sum(QRYAreasWorked.WorkAreaHours)]/12 , "##.00")

HTH
--
-Larry-
--

After reading some more posts in the query group, I made another query. Now I have 4 queries just to get the answer I want. Does that sound normal? Here is my sql in case that will help.

TRANSFORM Sum(QRYAreasWorkedShiftCount.Expr1) AS SumOfExpr1
SELECT QRYAreasWorkedShiftCount.Name
FROM QRYAreasWorkedShiftCount
GROUP BY QRYAreasWorkedShiftCount.dttHireDate, QRYAreasWorkedShiftCount.strSSNumber, QRYAreasWorkedShiftCount.Name
ORDER BY QRYAreasWorkedShiftCount.dttHireDate, QRYAreasWorkedShiftCount.strSSNumber
PIVOT QRYAreasWorkedShiftCount.WorkArea;

Thanks,
Linda


"LMB" wrote in message ...
I pasted this in a new field and got an error. I went back to my table and found that the hours field was text instead of number so I changed it to number. All of my queries still return the right number but I still got the same error.

Expr1: Format([Sum(QRYAreasWorked.WorkAreaHours)]/12 , "##.00")

Error. "Invalid bracketing of name"

Here is the sql in case it helps you to see where I errored but I used the query grid to insert the expression. I've tried some different things and looked in my chapter about expressions in my QUE Access 2000 book but I can't understand it very well since I don't really understand the programming yet. It talks about operators, operands, literals, functions...I need more time to absorb that stuff....maybe like 10 years or so.

SELECT qryDepartmentEmployees.dttHireDate, qryDepartmentEmployees.strSSNumber, qryDepartmentEmployees.Name, QRYAreasWorked.WorkArea, Sum(QRYAreasWorked.WorkAreaHours) AS SumOfWorkAreaHours, Format([Sum(QRYAreasWorked.WorkAreaHours)]/12,"#.00") AS Expr1
FROM QRYAreasWorked RIGHT JOIN qryDepartmentEmployees ON QRYAreasWorked.EmployeeID = qryDepartmentEmployees.strEmployeeID
GROUP BY qryDepartmentEmployees.dttHireDate, qryDepartmentEmployees.strSSNumber, qryDepartmentEmployees.Name, QRYAreasWorked.WorkArea, qryDepartmentEmployees.strShift, Format([Sum(QRYAreasWorked.WorkAreaHours)]/12,"#.00")
HAVING (((qryDepartmentEmployees.strShift)="days"))
ORDER BY qryDepartmentEmployees.dttHireDate, qryDepartmentEmployees.strSSNumber;


Thanks
Linda
"Larry Daugherty" wrote in message ...
Hi Linda,

You seem to be much further advanced than I'd assumed from your "Best
Practices" post.

I'm not at all sure I understand the ins and outs of your question but would
something like this work:

Expr1: Format([Sum(QRYAreasWorked.WorkAreaHours)]/12 , "##.00")

HTH
--
-Larry-
--

Hi Larry,

I don't know if you have been back but just in case, I didn't want you to waste time working in this. I tried this question on the query group and one person suggested this

Expr1: Format([SumOfWorkAreaHours]/12, "#.00")
This worked for my query but I got a "data type mismatch in criteria expression" error in the crosstab query I made based on this query.

Another suggestion was to use the round function

Expr1: Round([SumOfWorkAreaHours]/12, 2)

This worked but as someone else pointed out, a number of 2.33 doesn't look real nice so I changed the 2 to a 1 and got a nice 2.3 answer for my crosstab query.

Thank-you very much for your help.

Linda




"LMB" wrote in message ...
I pasted this in a new field and got an error. I went back to my table and found that the hours field was text instead of number so I changed it to number. All of my queries still return the right number but I still got the same error.

Expr1: Format([Sum(QRYAreasWorked.WorkAreaHours)]/12 , "##.00")

Error. "Invalid bracketing of name"