Hi Duane
Thanks for that link. I will consider this in future but I am not sure that it is the fact that I am using parameters in my dcount that is causing me the problems - it seems to be more getting to grips with the syntax
cheers
Phil
From: Duane Hookom <duanehookom@hotmail.com>
To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
Sent: Thursday, 9 January 2014, 17:33
Subject: RE: [MS_AccessPros] Dates in Dcount Criteria
To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
Sent: Thursday, 9 January 2014, 17:33
Subject: RE: [MS_AccessPros] Dates in Dcount Criteria
Phil,
IMO, you are wrong in using parameter prompts in queries. You might want to check www.tek-tips.com/faqs.cfm?fid=6763
Duane Hookom MVP
MS Access
________________________________
> From: pdk444444@yahoo.co.uk
>
> Hi John
>
> I need a few variations of this same sort of thing but I ran into more
> problems so I thought that maybe a way round it was to create a query
> which filtered the matches table first of all (eg by selecting records
> betwen 2 dates - the dates being parameters entered when the query is
> run) and then have a second query which includes the dcount field but
> which works on the 1st query rather than directly on the table
>
> The sql for the first query - called grid2a - is
>
> SELECT Matches.id, Matches.Div, Matches.[match code], Matches.totbooks,
> Matches.Hbooks, Matches.Abooks, Matches.BookSuprem, Matches.SendingsOff
> FROM Matches INNER JOIN Leagues ON Matches.Div = Leagues.code
> WHERE (((Month([date]))<>5) AND ((Matches.Date) Between [date1] And
> [date2]) AND ((Leagues.leagueorcup)="League"));
> (I haven't done the formatting of the dates in this instance as they no
> longer reside in the dcount function)
>
> and for the second query is
>
> SELECT Grid2a.Div, Grid2a.[match code], Count(Grid2a.id) AS [tot
> games], Avg(Grid2a.totbooks) AS [avg books], Avg(Grid2a.Hbooks) AS [avg
> H], Avg(Grid2a.Abooks) AS [avg A], Avg(Grid2a.BookSuprem) AS [avg sup],
> DCount("id","grid2a","[totbooks]=0 and [div]='" & [Div] & "' and [match
> code]=" & [match code] & " ")/[tot games] AS zerobooks
> FROM Grid2a
> WHERE (((Grid2a.[match code])=[enter match code]) AND
> ((Grid2a.Div)=[enter league code])) OR (((Grid2a.Div)=[enter league
> code]) AND (([enter match code]) Is Null)) OR (((Grid2a.[match
> code])=[enter match code]) AND (([enter league code]) Is Null)) OR
> ((([enter match code]) Is Null) AND (([enter league code]) Is Null))
> GROUP BY Grid2a.Div, Grid2a.[match code];
>
> when i run the query I get #error displayed in the zerobooks column.
>
> Am I a right in thinking that splitting it into 2 queries should work
> and if so, where am I going wrong?
>
> cheers
>
> Phil
>
> On Wednesday, 8 January 2014, 15:30, John Viescas <JohnV@msn.com> wrote:
>
> 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
>
> On Jan 8, 2014, at 4:18 PM, Phil Knowles
> <pdk444444@yahoo.co.uk<mailto: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<mailto: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<mailto: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<mailto: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
>
> On Jan 8, 2014, at 1:47 PM, Phil Knowles
> <pdk444444@yahoo.co.uk<mailto: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<mailto: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
>
> On Jan 8, 2014, at 1:00 PM, Phil Knowles
> <pdk444444@yahoo.co.uk<mailto: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<mailto: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
>
> On Jan 8, 2014, at 11:47 AM,
> <pdk444444@yahoo.co.uk<mailto:pdk444444@yahoo.co.uk>>
> <pdk444444@yahoo.co.uk<mailto: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
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)
<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
http://info.yahoo.com/legal/us/yahoo/utos/terms/
__._,_.___
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (18) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar