Hi and thanks for looking !!

I am a little stuck with a query I am trying to create and was hoping that someone could help me out !
I dont know if this is the right way but hopefully one of you guys will be able to advise.
I have a database with approx 700 golf club member in who all have a unique ID number (primary key) the records go back over 6 years and are grouped by competition date. the players score for each hole is entered into seperate columns within the table and here is what I am trying to achieve.

I need to create a query that displays the players lowest score on each hole throughout the season. So i want my query to return a single line for each ID and grab the lowest score from all dates for each hole.

I have a query

SELECT DISTINCTROW CHRECORDB.MEM, Min(CHRECORDB.GR1) AS [Min Of GR1], Min(CHRECORDB.GR2) AS [Min Of GR2], Min(CHRECORDB.GR3) AS [Min Of GR3], Min(CHRECORDB.GR4) AS [Min Of GR4], Min(CHRECORDB.GR5) AS [Min Of GR5], Min(CHRECORDB.GR6) AS [Min Of GR6], Min(CHRECORDB.GR7) AS [Min Of GR7], Min(CHRECORDB.GR8) AS [Min Of GR8], Min(CHRECORDB.GR9) AS [Min Of GR9], Min(CHRECORDB.GR10) AS [Min Of GR10], Min(CHRECORDB.GR11) AS [Min Of GR11], Min(CHRECORDB.GR12) AS [Min Of GR12], Min(CHRECORDB.GR13) AS [Min Of GR13], Min(CHRECORDB.GR14) AS [Min Of GR14], Min(CHRECORDB.GR15) AS [Min Of GR15], Min(CHRECORDB.GR16) AS [Min Of GR16], Min(CHRECORDB.GR17) AS [Min Of GR17], Min(CHRECORDB.GR18) AS [Min Of GR18]
FROM CHRECORDB
GROUP BY CHRECORDB.MEM;

The only problem is that at some stage of the season most players return a no score and a zero gets entered and the table from the above query returns

How do I get each row to return the min value as long as it is greater than zero ?

Thanks in advance

Hi and thanks for looking !!

I am a little stuck with a query I am trying to create and was hoping that someone could help me out !
I dont know if this is the right way but hopefully one of you guys will be able to advise.
I have a database with approx 700 golf club member in who all have a unique ID number (primary key) the records go back over 6 years and are grouped by competition date. the players score for each hole is entered into seperate columns within the table and here is what I am trying to achieve.

I need to create a query that displays the players lowest score on each hole throughout the season. So i want my query to return a single line for each ID and grab the lowest score from all dates for each hole.

I have a query

SELECT DISTINCTROW CHRECORDB.MEM, Min(CHRECORDB.GR1) AS [Min Of GR1], Min(CHRECORDB.GR2) AS [Min Of GR2], Min(CHRECORDB.GR3) AS [Min Of GR3], Min(CHRECORDB.GR4) AS [Min Of GR4], Min(CHRECORDB.GR5) AS [Min Of GR5], Min(CHRECORDB.GR6) AS [Min Of GR6], Min(CHRECORDB.GR7) AS [Min Of GR7], Min(CHRECORDB.GR8) AS [Min Of GR8], Min(CHRECORDB.GR9) AS [Min Of GR9], Min(CHRECORDB.GR10) AS [Min Of GR10], Min(CHRECORDB.GR11) AS [Min Of GR11], Min(CHRECORDB.GR12) AS [Min Of GR12], Min(CHRECORDB.GR13) AS [Min Of GR13], Min(CHRECORDB.GR14) AS [Min Of GR14], Min(CHRECORDB.GR15) AS [Min Of GR15], Min(CHRECORDB.GR16) AS [Min Of GR16], Min(CHRECORDB.GR17) AS [Min Of GR17], Min(CHRECORDB.GR18) AS [Min Of GR18]
FROM CHRECORDB
GROUP BY CHRECORDB.MEM;

The only problem is that at some stage of the season most players return a no score and a zero gets entered and the table from the above query returns

How do I get each row to return the min value as long as it is greater than zero ?

Thanks in advance


How about placing in the criteria line of your query

>0

Looking at your code I'm not sure if it will work or not, but give it a try.

Hi Statsman, thanks for looking in !!

I have tried that and it isnt what I am looking for. What happens is that any row that contains a number zero in is not returned in the data display.
I want a row for every ID number returning, I just want it to ignore any zeros and pick the next lowest score as the min for each hole.

hope i have explained it OK here

Thanks again for any help in advance

PS I have included a small test (access2002) db that hopefully shows what I am after

anyone looking will see that the query without any criteria in return a row for each of the 3 members, when I enter the greater than criteria it doesnt return data for member 5 as he has zeros in his table data.

The problem stems from the fact that your table is not normalised. Instead of a column for each hole you should have a record in a related table for each hole. Read up on normalisation in these forums. (Also spelled normalization).

Hi Neil,

The problem I have here is that I am creating the web site in ASP.net and recieve the access database from the golf club in its current format.

The people who have created the golf club stats software have setup the db like this and so as to enable me to just upload the db everyweek I am unable to modify its structure.

I was hoping that there would be a way to query the db in its current state.

thanks for looking

You should go to the software people and give them a good slapping. They obviously don't know what they're doing!

Two approaches spring to mind to address this. Neither are elegant!

You could use a query to mirror the table that tests each score. If the score is zero, add 1000 (or your choice of number) otherwise return the score. Then run the query you show above on that query.

Secondly, create a set of 18 queries, one for each hole, and then union all 18 queries together. This mimics the structure you would have in a normalised datatbase. You can then create an aggregate query that groups by player and hole, apply a criterion of >0 to the score and take the Min of the score.

Thanks Neil,

Followed your option 2 and created a separate query for each hole, entered the greater than criteria on each hole query then created a max date query and then finally one big query that does the job!!

It’s a pain as I have access 2002 and the golf clubs db is access2000, so I will have to upgrade the whole db, create the separate tables in my db then just keep importing and updating the clubs relevant tables on a weekly basis.

I agree with you on the software guys db creation skills, you ain’t seen the half of it, it’s a right pile of you know what.

Thanks again for your help on this !!

Regards Steve

Mmm...

Access 2002 should work in native 2000 format, so I don't know why you are having to convert.

Good luck, anyway.