count problem again!!!!

[kita__n]

Sorry folks have asked this question before but I could not solve it the right way so will appreciate some more solutions!


SELECT Count([Work Orders By Today's Date].[WorkOrder#]) AS [CountOfWorkOrder#], [Work Orders By Today's Date].Craft, [Work Orders By Today's Date].Description, [Work Orders By Today's Date].Zone, [Work Orders By Today's Date].Building, [Work Orders By Today's Date].[Room#], [Work Orders By Today's Date].TypeOfRequest, [Work Orders By Today's Date].Priority, [Work Orders By Today's Date].[WorkOrder#]

FROM [Work Orders By Today's Date]

GROUP BY [Work Orders By Today's Date].Craft, [Work Orders By Today's Date].Description, [Work Orders By Today's Date].Zone, [Work Orders By Today's Date].Building, [Work Orders By Today's Date].[Room#], [Work Orders By Today's Date].TypeOfRequest, [Work Orders By Today's Date].Priority, [Work Orders By Today's Date].[WorkOrder#];



This query sorts all the "Work Order for today's date"

I have added a count function which will give me the total number of workorders when i run this query.
The work order number is a unique id no. that I enter....so i basically want it to be able to count the workorders for todays date!
For eg...if today work orders start from 102 and end at 128....I want it to tell me that No. of work orders in Todays date are 26.


But it dosen't do that! Rather it counts each workoder as 1 and does not increment!!
I want it to increment.....1-2-3-4....and so on! So when I look at the last workorder it gives the total count!

please help

 

[RichMorrison]

When you Select Count([AnyVariable])
AND
you Group By [AnyVariable]
the number will always be one.

So don't Group By Work Order .....

RichM

 

[Pat Hartman]

You cannot include work order number in the query if you are trying to count workorders. change the query to:

SELECT Count(*) AS [CountOfWorkOrder#], [Work Orders By Today's Date].Craft, [Work Orders By Today's Date].Description, [Work Orders By Today's Date].Zone, [Work Orders By Today's Date].Building, [Work Orders By Today's Date].[Room#], [Work Orders By Today's Date].TypeOfRequest, [Work Orders By Today's Date].Priority

FROM [Work Orders By Today's Date]

GROUP BY [Work Orders By Today's Date].Craft, [Work Orders By Today's Date].Description, [Work Orders By Today's Date].Zone, [Work Orders By Today's Date].Building, [Work Orders By Today's Date].[Room#], [Work Orders By Today's Date].TypeOfRequest, [Work Orders By Today's Date].Priority;


This will still not just give you a simple count of workorders since there are many other columns included in the query. To count just workorders:

SELECT Count(*) AS [CountOfWorkOrder#]
FROM [Work Orders By Today's Date] ;

 

[kita__n]

yup you were right!

It wont let me count the fields in the same query so I guess I will just run it seperately....I was hoping that it would let me make a report with pulling both the queries but that dint work either so this is it a sepearate query for count!

Thanks a bunch!

 

[Rich]

Why not do the Count on the report?

 

[kita__n]

hmm....

I tried to make the reports using the queries....but it does not allow me to use anyother query along with the Count query!
is there a way to solve this?

 

[Pat Hartman]

Base the report on the detail query and put calculated counts in unbound textboxes in the group footers:

=Count(*)