ACCESS FEED: Re: [MS_AccessPros] Dates in Dcount Criteria

Rabu, 08 Januari 2014

Re: [MS_AccessPros] Dates in Dcount Criteria

Phil-

The key is you're grouping on Div and [match code], so you must filter on both those fields within the DCount to get the counts to match what's displayed on the current row. The first thing I did was change your filters to filter on the field values, not the parameters.

Good luck moving forward...

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jan 8, 2014, at 4:18 PM, Phil Knowles <pdk444444@yahoo.co.uk> wrote:

Hi John and Darrell

Thank you very much - I exactly copied the sql you gave John and it most certainly does what I want it to do.

I'm struggling a bit quite understanding why but I'll ponder over that more until it clicks - I now have to add yet another grouping level (of month) using the same principle so no doubt if I can now make that work I will have got it!

Many thanks

Phil

On Wednesday, 8 January 2014, 14:24, John Viescas <JohnV@msn.com> wrote:

Phil-

You're grouping by Div and match code, so you need to leave both those predicates in your DCounts. Here's a version where you can ask for all Div or match codes by leaving the parameter blank.

SELECT Matches.Div, Matches.[match code], Count(Matches.id) AS [tot games],
DCount("id","matches","[totbooks]<21 and [div]='" & [Div] & "' and [match code]=" & [match code] &
" and [date] between #" & Format([date1],"mm/dd/yyyy") & "# and #" & Format([date2],"mm/dd/yyyy") & "# ") AS [0-20],
DCount("id","matches","[totbooks]<41 and [div]='" & [Div] & "' and [match code]=" & [match code] &
" and [date] between #" & Format([date1],"mm/dd/yyyy") & "# and #" & Format([date2],"mm/dd/yyyy") & "# ") AS [0-40]
FROM Matches
WHERE (((Matches.Date) Between [date1] And [date2]) AND ((Matches.Div = [enter league code]) OR ([enter league code] IS NULL)) AND ((Matches.[match code] = [enter match code]) OR ([enter match code] IS NULL)))
GROUP BY Matches.Div, Matches.[match code];

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Jan 8, 2014, at 2:38 PM, Phil Knowles <pdk444444@yahoo.co.uk> wrote:

Hi John

Yes the SQL as posted does work and gives the correct counts but that is because the query only returns one row in it's current form (so it is not having a problem with the expression 0-20)

You have added another column to the query for 0-40 which again works fine when the query returns just one row (and that is indeed what I will do once I know the counting is working correctly for the 0-20 column)

But this isn't my problem - it is when I develop the query so that it returns more than one row - for example by removing the [enter match code] - in that case the query returns a row for each match code but the count in the 0-20 column is the same in every row.

The amended SQL for this is

SELECT Matches.Div, Matches.[match code], Count(Matches.id) AS [tot games], DCount("id","matches","[totbooks]<21 and [div]='" & [enter league code] & "' and [date] between #" & Format([date1],"mm/dd/yyyy") & "# and #" & Format([date2],"mm/dd/yyyy") & "# ") AS [0-20]
FROM Matches
WHERE (((Matches.Date) Between [date1] And [date2]))
GROUP BY Matches.Div, Matches.[match code]
HAVING (((Matches.Div)=[enter league code]))
ORDER BY Matches.Div;

I want it to give me the count of matches which have a value in totbooks of <21 for each different match code record

cheers

Phil

On Wednesday, 8 January 2014, 13:10, John Viescas <JohnV@msn.com> wrote:

Phil-

I think the problem is you're trying to use the filter values inside the DCount instead of the value found in the current row. Does the query work as currently coded? I'm thinking that it's not happy that the [0-20] expression isn't also in the GROUP BY clause. If it is working, then you could try:

SELECT Matches.Div, Matches.[match code], Count(Matches.id) AS [tot games],
DCount("id","matches","[totbooks]<21 and [div]='" & [Div] & "' and [match code]=" & [match code] &
" and [date] between #" & Format([date1],"mm/dd/yyyy") & "# and #" & Format([date2],"mm/dd/yyyy") & "# ") AS [0-20],
DCount("id","matches","[totbooks]<41 and [div]='" & [Div] & "' and [match code]=" & [match code] &
" and [date] between #" & Format([date1],"mm/dd/yyyy") & "# and #" & Format([date2],"mm/dd/yyyy") & "# ") AS [0-40]
FROM Matches
WHERE (((Matches.Date) Between [date1] And [date2]) AND (Matches.Div = [enter league code]) AND (Matches.[match code] = [enter match code]))
GROUP BY Matches.Div, Matches.[match code];

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Jan 8, 2014, at 1:47 PM, Phil Knowles <pdk444444@yahoo.co.uk> wrote:

Hi John

Just one table called matches - which is used to record football matches with associated data.

the fields that are relevant to what I am trying to do are
id - autonumber key
date - in UK date format
div - an alphanumeric code for the league or divison (or cup) that the match is part of
match code - a numeric rating
totbooks - a numeric value based on the number of bookings in the game - between 0 and say a max of 150

I want to be able to produce grids of information - an example of which is as follows

div match code month tot games 0-20 0-40 0-60 30-50 40-60

where I can enter a div (or select all divs)
I can enter a match code (or select all match codes) and
I can select the matches between 2 entered dates

I want to be able to count the number of matches that have a value in totbooks that falls into the ranges specified ( as you can see the ranges are not mutually exclusive) - the ranges I have given are just examples and I may vary them.

The query I have developed worked for just 1 div and 1 match code (before I tried to break it down into months) and the SQL is

SELECT Matches.Div, Matches.[match code], Count(Matches.id) AS [tot games], DCount("id","matches","[totbooks]<21 and [div]='" & [enter league code] & "' and [match code]=" & [enter match code] & " and [date] between #" & Format([date1],"mm/dd/yyyy") & "# and #" & Format([date2],"mm/dd/yyyy") & "# ") AS [0-20]
FROM Matches
WHERE (((Matches.Date) Between [date1] And [date2]))
GROUP BY Matches.Div, Matches.[match code]
HAVING (((Matches.Div)=[enter league code]) AND ((Matches.[match code])=[enter match code]))
ORDER BY Matches.Div;
Given what I want to do - not sure where to go from here.

cheers

Phil

On Wednesday, 8 January 2014, 12:15, John Viescas <JohnV@msn.com> wrote:

Phil-

Having a tough time picturing this. What is the SQL of your entire query?

What is the structure of your tables, and what are you trying to get out of them?

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Jan 8, 2014, at 1:00 PM, Phil Knowles <pdk444444@yahoo.co.uk> wrote:

Hi John
I guess 'a spot of bother' is very English !!!
Thank you - that sorted out my 'between dates' problem - but, as is always the case, I have now run into another problem.

I am trying to do this in query design (rather than any VBA) by the way.

The query itself is limiting the records it finds based on criteria entered and the field I am using the dcount function in is counting the number of records that not only satisfy all the query criteria but also 1 further test (the [totbooks]<21 element).

This works fine as long as the query returns just one record - because of grouping.
However I need the query to return several records and i need the dcount field to display the correct count for each subgrouping.

Is this possible to do using this approach and if so what do I put in the dcount criteria to achieve it?
or
should I be doing it in a different way - probably in VBA I imagine!

at the moment it asks for div, and 2 dates and then finds all the records for div between the 2 dates and I have a field that gives the count of all the records it has found and my dcount field then gives a count of all those records with totbooks <21.

I now need to subgroup the single record that the query returns by month (regardless of year) ie one record for all matches records with a January date and another record for Feb etc. - and for each of the query rows, a correct count of those records and a count of those records with totbooks <21

Once I have worked out how to do this I need to add several more columns of a similar nature but with different ranges for totbooks

Hope I have explained it adequately - if not please ask for clarificatiion

cheers

Phil

On Wednesday, 8 January 2014, 11:10, John Viescas <JohnV@msn.com> wrote:

Phil-

"Spot of bother" implies to me that you're not on a US machine. Date in predicates in SQL (which DCount uses) must be in US format if they're Short date. If the values in [Enter Date1] and [Enter Date2] are in European format (dd/mm/yyyy), you could get wrong results. For example 1/8/2013 (August 1, 2013 outside of the US) is interpreted by the query engine as January 8!

Try this:

DCount("id","matches","[totbooks]<21 and [div]='" & [enter league code] & "' and [date] between #" & Format([enter date1], "mm/dd/yyyy") & "# and #" & Format([enter date2], "mm/dd/yyyy") & "# ")

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Jan 8, 2014, at 11:47 AM, <pdk444444@yahoo.co.uk> <pdk444444@yahoo.co.uk> wrote:

Hi Guys,

I am having a spot of bother again - help please.

I am trying to do a count of a set of records for a field in a query.

I am user parameters and all was going well until I tried to change a date bit to between 2 dates - I am now getting odd results.

I have tried the following

DCount("id","matches","[totbooks]<21 and [div]='" & [enter league code] & "' and [date] between #" & [enter date1] & "# and #" & [enter date2] & "# ")

this returns a count that is much too high

and also

DCount("id","matches","[totbooks]<21 and [div]='" & [enter league code] & "' and [date] >= #" & [enter date1] & "# and [date]<= #" & [enter date2] & "# ")

this returns 0

I must admit it took me a while to work out all the delimiters needed to accomodate parameters and I wonder if they are not correct?

cheers

Phil