Query: convert multiple logical fields into single 2 character text result

[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.

 

[wdsnews]

My nested query didn't work out so well.

I built the Ethnic Calculation query described below, then I open the query
that builds the CSV and added the Ethnic Calculation query to it. I've
linked the two on [People ID] which is a key field in the People database.
My final result returns "UN" for all records. I believe this is the field
returned by the last record of the Ethnic Calculation query.

In other words, the data model of the consolidated query is the People table
linked to the Ethnic Calculation query. Now I wonder why the EthnicCd
doesn't return an appropriate result. It works when I run Ethnic
Calculation alone.

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.

 

[wdsnews]

ok. It works. I was fighting an unidentified GIGO problem.

My nested query didn't work out so well.

I built the Ethnic Calculation query described below, then I open the
query that builds the CSV and added the Ethnic Calculation query to it.
I've linked the two on [People ID] which is a key field in the People
database. My final result returns "UN" for all records. I believe this is
the field returned by the last record of the Ethnic Calculation query.

In other words, the data model of the consolidated query is the People
table linked to the Ethnic Calculation query. Now I wonder why the
EthnicCd doesn't return an appropriate result. It works when I run Ethnic
Calculation alone.

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.