mean mode help required

[Sausagefingers]

Hi,
I found a couple of short threads on the forum discussing the above. None of them go sufficiently far enough into detal to help me solve my (relatively simple) problem.

My boss has asked me to produce both mean and mode calculations for the number of pallets we ship to our branches on the 30+ routes that we service.

So far I have managed to implement mean but mode seems to have me stumped.

I'm aware there actually isn't a mode function in Access as such but like mode, is there a way to calculate the most frequent occurrence of a number over a time period?

My query is already producing Min, Max, Sum (and now mean) results for the pallets we ship, can someone offer me help on this?

All the code that I have retrieved so far seems to be based on something similar to this:

select top 1 with ties your_column
from your_table
group by your_column
order by count(*) desc

I have very little to no idea about how to implement this into my query. Any help would be appreciated.

Thanks In Advance.

sausagefingers

 

[ajetrumpet]

Is there a way to calculate the most frequent occurrence of a number over a time period?

A number of what? The number of shipments, or the number of times a CERTAIN NUMBER of PALLETS were shipped to a location?

All the code that I have retrieved so far seems to be based on something similar to this:

select top 1 with ties your_column
from your_table
group by your_column
order by count(*) desc

I have very little to no idea about how to implement this into my query.

I don't think you can. Certainly, not if you've already got other aggregates in it. And, getting a mode is easy, but how is the person reading the data going to know that it's the mode? In the datasheet, it's just another record. At any rate though, in a single statement, you can get the mode like this...

SELECT TOP 1 field, COUNT([field]) AS [Mode]

     FROM table

     GROUP BY field

ORDER BY COUNT([field]) DESC;

You may have to do this to get the mode on its own, and UNION it in the same query with the other SELECT statement that you already have written out.

 

[GaryPanic]

with Mode be careful as an anitlitical (?) fuction - its about as much use as a fart in a spacesuit - something you cannot get away from and pretty annoying as well .

 

[Sausagefingers]

Adam wrote :
"A number of what? The number of shipments, or the number of times a CERTAIN NUMBER of PALLETS were shipped to a location?"

Adam, thanks for your response. I should have been more specific in my explaination, sorry.

We have +300 branches to service on a daily basis. A branch may request any amount of goods from us and we then pick, collate and palletize those goods and deliver them the following day.

The minimum amount of delivered pallets may be as low as 1, a max amount may be 15 (for example). It all depends on our (daily) branches needs.

Somewhere in our recordset there must be, the pallet quantity that we deliver the most frequently to each of the branches we deliver to. Average is not suitable here as certain 'patterns' of delivery can skew the calculation too far one way or the other.

What I am looking for is the most frequently delivered number of pallets (or most frequent occurrence of a number) for EACH of the brances we have delivered to over the date range that exists in the recordset. Hope this makes more sense.

Yesterday, was toying with the example code I posted:

"SELECT TOP 100 PERCENT tbl_deliveryTransactions.pallDel, Count(*) AS Occasions
FROM tbl_deliveryTransactions
GROUP BY tbl_deliveryTransactions.pallDel
ORDER BY Count(*) DESC;"

As you suggest, I am able to get mode as a single statement. I really need to tie the calculation to the branchID field [brID] which also exists in the table. This would give me the pallet mode on a per branch basis, which is what my boss has requested.

Thanks for your input

Sausagefingers

 

[Sausagefingers]

with Mode be careful as an anitlitical (?) fuction - its about as much use as a fart in a spacesuit - something you cannot get away from and pretty annoying as well .

I shall pass your response directly to my boss! LOL!

 

[ajetrumpet]

I really need to tie the calculation to the branchID field [brID] which also exists in the table. This would give me the pallet mode on a per branch basis

I really think you would be better off doing this sort of stuff in Excel. I'm not saying that's it's a better program than Access, but it does combine certain processes and calculations, like these types, very well. But, this is still doable in Access. I would say, in order to go any further with this, forget using the built-in query function, unless you want to write 5 different queries. You will have to do that because the built-in functions are limited in their flexibility to multi-task with different functions. I would go strait to Visual Basic.

I have been known, and I'm sure I'm known around here for this now, for writing complex solutions to problems. Yours will probably need one. Do you know any VBA technique? Are you interested in going this route? If you are, there might be a few people around here that could get you started...

 

[Jon K]

I have a sample database, which includes the calculation of Mode.

You can open the form, enter a start date and an end date, and click on the Mode command button to run the query Mode3. The Mode is calculated using a series of three queries: Mode1, 2 and 3.

Hope the example helps.
.

 

[Sausagefingers]

I really think you would be better off doing this sort of stuff in Excel. I'm not saying that's it's a better program than Access, but it does combine certain processes and calculations, like these types, very well. But, this is still doable in Access. I would say, in order to go any further with this, forget using the built-in query function, unless you want to write 5 different queries. You will have to do that because the built-in functions are limited in their flexibility to multi-task with different functions. I would go strait to Visual Basic.

I have been known, and I'm sure I'm known around here for this now, for writing complex solutions to problems. Yours will probably need one. Do you know any VBA technique? Are you interested in going this route? If you are, there might be a few people around here that could get you started...

Adam, thanks again for your response. I had, in fact, resigned myself to passing the problem back to my boss and suggesting we used Excel for the task. We did. He got the data from me in .xls format and has since done the calculations in Excel himself. )

In response your second paragraph, yes, and yes. I am gradually becoming more proficient using VB. Nowhere near the same level as many of you guys but sufficient enough to be able to at least follow (if not write) semi-complex code examples.

There was sufficient 'donkey work' to do in Excel in order to get the results!
There are, as I mentioned, 300+ branches to calculate mode data for so it was very time-consuming Excel session, apparantly. Which leads me to believe that having this kind of solution available to Access, however complex, will be a bonus in the future.

Thanks

Sausagefingers

 

[Sausagefingers]

I have a sample database, which includes the calculation of Mode.

You can open the form, enter a start date and an end date, and click on the Mode command button to run the query Mode3. The Mode is calculated using a series of three queries: Mode1, 2 and 3.

Hope the example helps.
.

Hi Jon K,
thanks for the input. I have d/l'd your example and will take a good look at it tomorrow. Right now, I'm off home ;o)

Thanks

Sausagefingers

 

[Newbie100]

I played around with the Mode function in Excel and found this characteristic:

It returns only one mode.

So when there are more than one mode in the data, it just returns the one that appears in the list first e.g.

In 2, 2, 9, 7, 9, 2, 10, 9 - it returns the mode 2 and ignores the mode 9.
In 9, 2, 9, 7, 9, 2, 10, 2 - it returns the mode 9 and ignores the mode 2.

 

[Sausagefingers]

I played around with the Mode function in Excel and found this characteristic:

It returns only one mode.

So when there are more than one mode in the data, it just returns the one that appears in the list first e.g.

In 2, 2, 9, 7, 9, 2, 10, 9 - it returns the mode 2 and ignores the mode 9.
In 9, 2, 9, 7, 9, 2, 10, 2 - it returns the mode 9 and ignores the mode 2.

Hmmm...then perhaps GaryPanics "fart in a spacesuit" theory is more credible than I first thought
Seriously thought, I would have thought that Excel could handle mode in a more proficient manner since it is, after all, a statistical analysis tool.

 

[Sausagefingers]

I have a sample database, which includes the calculation of Mode.

You can open the form, enter a start date and an end date, and click on the Mode command button to run the query Mode3. The Mode is calculated using a series of three queries: Mode1, 2 and 3.

Hope the example helps.
.

Jon K,
just want to say thanks for the mode example database. After a bit of head-scratching I finally worked out what was going on. Up and running on my db with your help.

Thanks

Al