Re: Noob MSAccess query struggle
Hi Walter, You may need to post a small sample of data to help us understand this issue. how does petID crop up in the data for example. On 10/22/06, Walter Conti [EMAIL PROTECTED] wrote: cfquery name=getSigs datasource=firme select signerName, signerLocation, signerComment, dateSigned, MIN([dateSigned]) as startDate, MAX([dateSigned]) as endDate from signatures where signatures.petId ='#id#' group by signerName, signerLocation, signerComment, dateSigned order by dateSigned desc /cfquery returns the same (MAX) date for MIN and MAX even though the date range is ample. The error is laughing at me but I cannot see it. Thanks for helping. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:257696 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Noob MSAccess query struggle
Yes, and if the id field is numeric, not sure using the apostrophes around #id# will work correctly... using apostrophes is usually reserved for text fields I believe -Original Message- From: Michael Traher [mailto:[EMAIL PROTECTED] Sent: Sunday, October 22, 2006 3:55 AM To: CF-Talk Subject: Re: Noob MSAccess query struggle Hi Walter, You may need to post a small sample of data to help us understand this issue. how does petID crop up in the data for example. On 10/22/06, Walter Conti [EMAIL PROTECTED] wrote: cfquery name=getSigs datasource=firme select signerName, signerLocation, signerComment, dateSigned, MIN([dateSigned]) as startDate, MAX([dateSigned]) as endDate from signatures where signatures.petId ='#id#' group by signerName, signerLocation, signerComment, dateSigned order by dateSigned desc /cfquery returns the same (MAX) date for MIN and MAX even though the date range is ample. The error is laughing at me but I cannot see it. Thanks for helping. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:257697 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Noob MSAccess query struggle
Walter, You are grouping by dateSigned which ensures that the min() and max() will be the same (because you get a new record for each value of dateSigned). Remove dateSigned from your select statement and your group by statement and see if your results make more sense. Steve Bryant. Bryant Web Consulting LLC http://www.BryantWebConsulting.com/ http://steve.coldfusionjournal.com/ cfquery name=getSigs datasource=firme select signerName, signerLocation, signerComment, dateSigned, MIN([dateSigned]) as startDate, MAX([dateSigned]) as endDate from signatures where signatures.petId ='#id#' group by signerName, signerLocation, signerComment, dateSigned order by dateSigned desc /cfquery returns the same (MAX) date for MIN and MAX even though the date range is ample. The error is laughing at me but I cannot see it. Thanks for helping. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:257705 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Noob MSAccess query struggle
Walter Conti wrote: cfquery name=getSigs datasource=firme select signerName, signerLocation, signerComment, dateSigned, MIN([dateSigned]) as startDate, MAX([dateSigned]) as endDate from signatures where signatures.petId ='#id#' group by signerName, signerLocation, signerComment, dateSigned order by dateSigned desc /cfquery returns the same (MAX) date for MIN and MAX even though the date range is ample. The error is laughing at me but I cannot see it. Thanks for helping. You are grouping by the dateSigned column. The MIN and MAX will be on a group defined with that column...so in each row dateSigned = MIN(dateSigned) = MAX(dateSigned). If you want to return the individual dateSigned values and the related MIN and MAX for that id in each row, you will probably have to do it with subqueries...something like... SELECT signerName, signerLocation, signerComment, dateSigned, (SELECT MIN([dateSigned]) FROM signatures WHERE petId = '#id#') AS startDate, (SELECT MAX([dateSigned]) FROM signatures WHERE petId = '#id#') AS endDate FROM signatures WHERE signatures.petId ='#id#' ORDER BY dateSigned desc ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:257706 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Noob MSAccess query struggle
I don't think he can remove dateSigned from the group by statement, since you must have all non-aggregate or subquery columns in the group by statement. I think he needs to look at his data in the fashion that the query is grouping it. It may be the case that the min() and max() are the same for the signer/petid that is being returned. Cheers, Kris You are grouping by dateSigned which ensures that the min() and max() will be the same (because you get a new record for each value of dateSigned). Remove dateSigned from your select statement and your group by statement and see if your results make more sense. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:257708 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Noob MSAccess query struggle
I don't think he can remove dateSigned from the group by statement He should remove it from both the select AND the group statements. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:257712 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
