Max Question

I want to perform a query on a table that will group values that are the same and then count how many records of each I have:

so far I've used groupby [unit] and then count [unit] this gives me the list that I am looking for but now I want just one record, the highest one of them.

been trying to use the Max function but I can never get the syntax right. how do you write this? thanks

re: Max Question

This is not clear Jaad. it sounds like you are saying that they are all the same (GROUP BY) yet you want the Maximum of them all :S Surely there is no difference across the whole range if they are all the same.

If you are referring to another field then I can't see how you can post a question and forget to include such an important detail.

I have to admit I'm confused.

re: Max Question

forgive me it seems that I really am not made to work with this program...

I have a series of areas that I work with in a building and each [unit] is mutually exclusive to the next. each [unit] have made a certain amount of request for repairs. they are tracked by [workOrder] records.

example
so I have unit 102 made 6 calls repair
unit 208 made 3 calls for repair
unit 309 made 6 calls for repairs and so on

what I want to calculate is how many calls each unit made and which unit at the end made the most calls for repairs? is that clearer?

re: Max Question

A query can show 1 (The Count per Unit) or the other (Max of all such Counts) but not both. To get both to show you'd need to use some of the facilities in a report.

To get the Max use something like :

re: Max Question

Im not sure I understand how to apply what you've wrote Neopa.

I have the [unit] saved in the same table as with [WorkOrder]

SELECT [Unit] GROUP BY [Unit]and then beside it in the grid I have SELECT [Unit] COUNT

This gives me a list of [unit] grouped for each unit of the building and a number of records

By looking at it I can see that suite 308 was the one that made the most request for services. I just need the syntax to extract the record so I can put it on my statistic page where I keep other information.

re: Max Question

This makes little sense. Why don't you post the meta-data (info about the layout / structure) of the table in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively. Never use TABs in this as the layout gets mucked up. Use spaces and all is fine.
Table Name=[tblStudent]
You could also try reading my previous post again. I'll highlight the important part for you. I'm afraid I cannot make you understand it. I can only say it clearly. I cannot progress with this unless and until you understand what I've written earlier, as that is why your question doesn't make sense.