W
wdsnews
I've solved this particular puzzle, although I'd like to find a better
solution than mine. So my emergency is resolved, nevertheless if you like
puzzles, please read on...
The school district wants a CSV file with fields in a specific order and
with specific formatting. Here are the seven possible results for EthnicCd:
2 Char where
ML = Multiple Races
UN = Unknown or Declined to Report
IN = American Indian or Alaskan Native
AS = Asian or Pacific Islander
BL = African American
HI = Hispanic
WH = White
Our "People" database contains eight ethnicity Yes/No fields as required by
the State's enrollment application:
[Ethnic Declined]
[Ethnic White]
[Ethnic Hispanic]
[Ethnic African American]
[Ethnic American Indian]
[Ethnic Alaskan Native]
[Ethnic Asia Pacific]
[Ethnic Other]
My solution is a nested query where the first query returns four fields:
1. [People ID]
2. [Ethnics] a string with all the relevant two character codes
concatenated one behind the other, separated by a space: IIf([Ethnic
White],"WH ","") & IIf([Ethnic Hispanic],"HI ","") & IIf([Ethnic African
American],"BL ","") & IIf([Ethnic American Indian] Or [Ethnic Alaskan
Native],"IN ","") & IIf([Ethnic Asia Pacific],"AS ","") & IIf([Ethnic Other]
Or [Ethnic Declined],"UN ","")
3. [ELength] the text length of [Ethnics]. Len([Ethnics])
4. [Ethnic] IIf([ELength]=0,"UN",IIf([ELength]>3,"ML",Left([Ethnics],2)))
The second query links to the first on [People ID]. It then uses [Ethnic]
directly.
Now, having reached this solution, it seemed like a simple matter to role
the solution into a final one stage query and skip the first stage. Here
are the problems I ran into:
Since the final query must present it's data in a specific order, it wasn't
possible to let [Ethnics] or [ELengh] appear in the result. And since these
fields aren't visible, they can't be used in calculations. So, I tried
using the formula of [Ethnics] in each place where [ELength] would be used.
But that didn't work because the resulting formula was more than 1024
characters and Access truncated it.
So, I'm left wondering if there is a more efficient way to combine multiple
logical fields into a single 2 character text result?
thanks for your thoughts.
solution than mine. So my emergency is resolved, nevertheless if you like
puzzles, please read on...
The school district wants a CSV file with fields in a specific order and
with specific formatting. Here are the seven possible results for EthnicCd:
2 Char where
ML = Multiple Races
UN = Unknown or Declined to Report
IN = American Indian or Alaskan Native
AS = Asian or Pacific Islander
BL = African American
HI = Hispanic
WH = White
Our "People" database contains eight ethnicity Yes/No fields as required by
the State's enrollment application:
[Ethnic Declined]
[Ethnic White]
[Ethnic Hispanic]
[Ethnic African American]
[Ethnic American Indian]
[Ethnic Alaskan Native]
[Ethnic Asia Pacific]
[Ethnic Other]
My solution is a nested query where the first query returns four fields:
1. [People ID]
2. [Ethnics] a string with all the relevant two character codes
concatenated one behind the other, separated by a space: IIf([Ethnic
White],"WH ","") & IIf([Ethnic Hispanic],"HI ","") & IIf([Ethnic African
American],"BL ","") & IIf([Ethnic American Indian] Or [Ethnic Alaskan
Native],"IN ","") & IIf([Ethnic Asia Pacific],"AS ","") & IIf([Ethnic Other]
Or [Ethnic Declined],"UN ","")
3. [ELength] the text length of [Ethnics]. Len([Ethnics])
4. [Ethnic] IIf([ELength]=0,"UN",IIf([ELength]>3,"ML",Left([Ethnics],2)))
The second query links to the first on [People ID]. It then uses [Ethnic]
directly.
Now, having reached this solution, it seemed like a simple matter to role
the solution into a final one stage query and skip the first stage. Here
are the problems I ran into:
Since the final query must present it's data in a specific order, it wasn't
possible to let [Ethnics] or [ELengh] appear in the result. And since these
fields aren't visible, they can't be used in calculations. So, I tried
using the formula of [Ethnics] in each place where [ELength] would be used.
But that didn't work because the resulting formula was more than 1024
characters and Access truncated it.
So, I'm left wondering if there is a more efficient way to combine multiple
logical fields into a single 2 character text result?
thanks for your thoughts.