ACCESS FEED: RE: [MS_AccessPros] Grouping on multiple criteria question

Senin, 27 Mei 2013

RE: [MS_AccessPros] Grouping on multiple criteria question

Jim,
IMO, the best option would be to create a table with Area and Geo Code fields. Then maintain the table as and when needed rather than a complex expression. Maintain data, not expressions.

Duane Hookom MVP
MS Access

> To: MS_Access_Professionals@yahoogroups.com
> From: luvmymelody@yahoo.com
> Date: Sat, 25 May 2013 13:08:45 -0700
> Subject: Re: [MS_AccessPros] Grouping on multiple criteria question
>
> John,
>
> I was actually creating a column for each group. I saw that that was not happening. But your expression worked great.
>
> Thank You
>
>
> Jim Wagner
> ________________________________
>
>
>
> ________________________________
> From: John Viescas <JohnV@msn.com>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Friday, May 24, 2013 9:24 PM
> Subject: RE: [MS_AccessPros] Grouping on multiple criteria question
>
>
>
>
> Jim-
>
> Is that what you're putting in the Critera on the query grid? You would
> normally use LIKE with a wildcard of some sort. If you truly want to do
> grouping, then you should do something like:
>
> Geo Code Group: IIf([Geo Code] = "AB6 A" Or [Geo Code] = "AB6 B" Or [Geo
> Code] = "AB6 C" Or [Geo Code] = "CD6 A", 1, IIf([Geo Code] = "CD7 A" Or [Geo
> Code] = "CD7 C", 2, IIf( .... , 3, IIf( ... , 4, 5))))
>
> Then GROUP BY Geo Code Group.
>
> 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)
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of luvmymelody
> Sent: Friday, May 24, 2013 9:38 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Grouping on multiple criteria question
>
> Hello all,
>
> I have 20 geographical codes for an area that I would like to create a query
> that will group multiple geographical areas into 5 areas. I tried to create
> a query that had the following
>
> Geo Code 1: [Geo Code]=Like "AB6 A" Or "AB6 B" Or "AB6 C" Or " CD6 A"
> Geo Code 2: [Geo Code]Like "CD7 A" Or "CD7 C"
> Geo Code 3: [Geo Code]Like " CD6 B" Or "CD6 C" Or "CD6 D"
> Geo Code 4: [Geo Code]
> Geo Code 5: [Geo Code]
>
> But it returns no records after the first geo code 1.
>
> Is there a way of grouping these codes into bigger areas?
>
> Thank You
>
> Jim Wagner
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>

[Non-text portions of this message have been removed]