Why in the heck doesn't this work?

Post #236,004 by folkert 11/25/05 3:58:01 PM 11/26/05 12:20:59 PM Reply	Why in the heck doesn't this work? This is Access SQL, the only way I can use Access to do queries is this way. I keep getting yelled at that I haven't included some aggregate function expressions. GRRRR. Please assume all punctuation and white space is correct. I stripped out all the brackets so Z wouldn't have a coronary. I am guessing, I don't know SQL as well as I thought... or Access is escaping my grasp or something. I have been reading, reading, reading. I've even gone as far as getting postgresql v8.1 setup on my Linux machine (not yet with data) versus an Access MDB. I am going to get the PG DB working over the weekend. We shall see what comes of it. If you have a quick or authoritative answer... please by all means let me know. (Corrected) SELECT 4_CDS_DelPntRec.ZIPCode,\n\t4_CDS_DelPntRec.CrRtID,\n\t4_CDS_DelPntRec.AddrType,\n\tCount(4_CDS_DelPntRec.ZIPCode) AS ZIP_Count,\n\tCount(4_CDS_DelPntRec.CrRtID) AS CRRT_Count,\n\tCount(4_CDS_DelPntRec.AddrType) AS AddrType_Count,\n\tzip2city.USPSCityName,\n\tzip2city.AcceptableCityName,\n\t4_CDS_DelPntRec_AddrType.TypeDesc,\n\t4_CDS_DelPntRec.DelTypeCode,\n\t4_CDS_DelPntRec_DelTypeCode.ResOrBus,\n\t4_CDS_DelPntRec_DelTypeCode.GenYstNst,\n\t4_CDS_DelPntRec.AddrVac90Plus,\n\t4_CDS_DelPntRec.DelPntDrop,\n\t4_CDS_DelPntRec_DelPntDrop.CMRA_Drop_NotDrop,\n\t4_CDS_DelPntRec.DelPntBusFamServicedCount,\n\t4_CDS_DelPntRec.Seasonal,\n\t4_CDS_DelPntRec_Seasonal.Yes_Not_Edu\nFROM zip2city \n\tINNER JOIN (4_CDS_DelPntRec_Seasonal\n\t\tINNER JOIN (4_CDS_DelPntRec_DelTypeCode\n\t\t\tINNER JOIN (4_CDS_DelPntRec_DelPntDrop\n\t\t\t\tINNER JOIN (4_CDS_DelPntRec_AddrType\n\t\t\t\t\tINNER JOIN 4_CDS_DelPntRec\n\t\t\t\t\t\tON 4_CDS_DelPntRec_AddrType.AddrType = 4_CDS_DelPntRec.AddrType)\n\t\t\t\t\tON 4_CDS_DelPntRec_DelPntDrop.DelPntDrop = 4_CDS_DelPntRec.DelPntDrop)\n\t\t\t\tON 4_CDS_DelPntRec_DelTypeCode.DelTypeCode = 4_CDS_DelPntRec.DelTypeCode)\n\t\t\tON 4_CDS_DelPntRec_Seasonal.Seasonal = 4_CDS_DelPntRec.Seasonal)\n\t\tON zip2city.ZIPCode = 4_CDS_DelPntRec.ZIPCode\nGROUP BY 4_CDS_DelPntRec.ZIPCode,\n\t4_CDS_DelPntRec.CrRtID,\n\t4_CDS_DelPntRec.AddrType\nORDER BY 4_CDS_DelPntRec.ZIPCode,\n\t4_CDS_DelPntRec.CrRtID,\n\t4_CDS_DelPntRec.AddrType; Edit made a correction. -- [link\|mailto:greg@gregfolkert.net\|greg], [link\|http://www.iwethey.org/ed_curry\|REMEMBER ED CURRY!] @ *i_wethey* Freedom is not FREE. Yeah, but 10s of Trillions of US Dollars? SELECT * FROM scog WHERE ethics > 0; 0 rows returned. Edited by folkert Nov. 26, 2005, 12:20:59 PM EST Expand All History
Post #236,021 by jb4 11/25/05 6:25:36 PM Reply	Here's something I do when working with Excess uh...Access I get the query working using the graphical query thinger. (So what if it isn't "manly". this is Excess we're talking about, and any resemblance between its query language and SQL is purely accidental.) When i get it producing a properly anwer table, I switch the query view to SQL, if I'm feeling particularly annoyed with what I see, I try cleaning up the extraneous parentheses and occasional inner joins that it likes to produce, then copy the garbage into the Access VBA script. Dunno if that any help... Also, I didn't know that you could name a table starting with a number... jb4 shrub●bish (Am., from shrub* + rubbish, after the derisive name for America's 43 president; 2003) n. 1. a form of nonsensical political doubletalk wherein the speaker attempts to defend the indefensible by lying, obfuscation, or otherwise misstating the facts; GIBBERISH. 2. any of a collection of utterances from America's putative 43rd president. cf.* BULLSHIT
Post #236,024 by broomberg 11/25/05 6:29:59 PM Reply	Everything after the counts are illegal. You gotta group on everything that your are not running a function on. I haven't looked at anything else such as the joins.
Post #236,025 by ben_tilly 11/25/05 6:30:54 PM Reply	Add more stuff to the GROUP BY All fields that appear in the SELECT must either be aggregate functions (count, sum, etc) or must appear in the GROUP BY. You're missing zip2city.USPSCityName, zip2city.AcceptableCityName, 4_CDS_DelPntRec_AddrType.TypeDesc, 4_CDS_DelPntRec.DelTypeCode, 4_CDS_DelPntRec_DelTypeCode.ResOrBus, 4_CDS_DelPntRec_DelTypeCode.GenYstNst, 4_CDS_DelPntRec.AddrVac90Plus, 4_CDS_DelPntRec.DelPntDrop, 4_CDS_DelPntRec_DelPntDrop.CMRA_Drop_NotDrop, 4_CDS_DelPntRec.DelPntBusFamServicedCount, 4_CDS_DelPntRec.Seasonal, and 4_CDS_DelPntRec_Seasonal.Yes_Not_Edu. Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #236,026 by broomberg 11/25/05 6:31:53 PM Reply	Beat ya
Post #236,027 by ben_tilly 11/25/05 6:33:00 PM Reply	Yeah, yeah. I gave a longer answer. I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #236,033 by broomberg 11/25/05 6:48:53 PM Reply	I gave less on purpose. Made him think about it, which in turn means he'll learn it better. hehe.
Post #236,040 by ben_tilly 11/25/05 9:03:32 PM Reply	I just wanted to drive home... how much Access had to complain about. :-) Cheers, Ben I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #236,047 by broomberg 11/25/05 11:04:31 PM Reply	What? Everything after the counts are illegal isn't enough? How can there be more than Everything?
Post #236,064 by ben_tilly 11/26/05 2:18:45 AM Reply	Logic is distinct from visual impact. I was going for that. I have come to believe that idealism without discipline is a quick road to disaster, while discipline without idealism is pointless. -- Aaron Ward (my brother)
Post #236,075 by broomberg 11/26/05 11:37:08 AM Reply	Oh. You were padding. Got it.

Welcome to IWETHEY!