Link to home
Start Free TrialLog in
Avatar of anthonyhardy
anthonyhardyFlag for United States of America

asked on

Query Assistance in MS Access

New federal guidelines require that ethnicity is separated from race. No biggie.  Our ERP vendor supplied the collection tool, stuck the data in a table.  They updated their existing reports to use the new format, but have gone no further.  I need to pull the information from their table for ad-hoc and regularly required reports.

Without going into details, no, we cannot change vendors and no they do not support us properly.

Long story short, I'm using a mix of Access 2007 and 2010 to pull info.  We have the following table layout in a non-DB2 database on an AS/400 ( other technical details not important?):

StudentNumber
Hispanic
NativeAmerican
Asian
AfricanAmerican
Caucasian
Hawaiian

Each field is basically just a one character Y/N field (not 1/0, Y/N . hah).

Now, I need the data in a format that will complete the attached chart.  This will be the "new" way we will be pulling this type of info and we do it a LOT, so I need it to be as easy as possible.  The big hangup is multiple race.  Before, when we didn't record multiple race, this wouldn't be too hard, but now, I'm stumped.  If I were doing this in PHP, I could do it easily with a few nested arrays and counting.  I'm not advanced enough in MS Access to do this, so I turn to you guys!  Any assistance is appreciated.

Microsoft-Excel---Book1-2011-02-.png
Avatar of HainKurt
HainKurt
Flag of Canada image

try this


select 
iif(hispanic="Y", "Hispanic","Non-Hispanic") Ethnicity,
sum(iif(NativeAmerican="Y",1,0) NativeAmerican,
sum(iif(Asian="Y",1,0) Asian,
...
sum(iif(Hawaiian="Y",1,0) Hawaiian
from myTable
group by hispanic

Open in new window

you should have 2 rows (if hispanic="Y" & "N" only)

hope this is what you want... a bit reversed :)
Ethnicity    NativeAmerican ... Hawaiian
Hispanic     120            ... 23
Non-Hispanic 234            ... 45

Open in new window

oops, I forgot ) everywhere
select 
iif(hispanic="Y", "Hispanic","Non-Hispanic") Ethnicity,
sum(iif(NativeAmerican="Y",1,0)) NativeAmerican,
sum(iif(Asian="Y",1,0)) Asian,
...
sum(iif(Hawaiian="Y",1,0)) Hawaiian
from myTable
group by hispanic

Open in new window

Avatar of RgGray3
RgGray3

Sorry...  need clarification

What data do you have to work the logic off of
What are you trying to fill

I believe I understand you have a table with fields that "You have to fill" with the following structure

StudentNumber, Hispanic, NativeAmerican, Asian, AfricanAmerican, Caucasian, Hawaiian

 ID number          1chr("Y"or "N")...  each

Where are you getting the data that you are going to use to populate this table
What format is it in
Avatar of anthonyhardy

ASKER

The table orientation doesn't matter:).

I can get that far, but I need to determine if a student has a Y in more than one of the Five race fields and separate them out as "multiple race" in addition to Hispanic/non-Hispanic.  

In other words, if a student marks Hawaiian AND Caucasian, they go in NEITHER the Hawaiian nor Caucasian category, but in the multiple race category.  Does that make sense?
Hi RgGray3.  This is coming from a non-DB2 database on an IBM iSeries (AS/400) via ODBC using MS Access.  I have attached a "mock-up" of the table in which the data current resides and attached it to this reply.

What I need to do is be able to take the data from the table and query is to get totals as referenced by my first attachment.
Microsoft-Excel---Book1-2011-02-.png
SOLUTION
Avatar of RgGray3
RgGray3

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
OK...  got your last example
Understand the incomming
see my previous
but is the goal Hispanic/NonHispanic...  logic would change slightly
I believe converting the Text into valid Numeric values is key to your solution
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
HainKurt,

Bingo!  For the record:
 
SELECT DATA400_SR10.SR10_IPEDS_HISPANIC, Sum(IIf([DATA400_SR10]![SR10_INDIAN] & [DATA400_SR10]![SR10_ASIAN] & [DATA400_SR10]![SR10_BLACK] & [DATA400_SR10]![SR10_HAWAIIAN] & [DATA400_SR10]![SR10_WHITE]="Y    ",1,0)) AS NativeAmerican, Sum(IIf([DATA400_SR10]![SR10_INDIAN] & [DATA400_SR10]![SR10_ASIAN] & [DATA400_SR10]![SR10_BLACK] & [DATA400_SR10]![SR10_HAWAIIAN] & [DATA400_SR10]![SR10_WHITE]=" Y   ",1,0)) AS Asian, Sum(IIf([DATA400_SR10]![SR10_INDIAN] & [DATA400_SR10]![SR10_ASIAN] & [DATA400_SR10]![SR10_BLACK] & [DATA400_SR10]![SR10_HAWAIIAN] & [DATA400_SR10]![SR10_WHITE]="  Y  ",1,0)) AS AfricanAmerican, Sum(IIf([DATA400_SR10]![SR10_INDIAN] & [DATA400_SR10]![SR10_ASIAN] & [DATA400_SR10]![SR10_BLACK] & [DATA400_SR10]![SR10_HAWAIIAN] & [DATA400_SR10]![SR10_WHITE]="   Y ",1,0)) AS Hawaiian, Sum(IIf([DATA400_SR10]![SR10_INDIAN] & [DATA400_SR10]![SR10_ASIAN] & [DATA400_SR10]![SR10_BLACK] & [DATA400_SR10]![SR10_HAWAIIAN] & [DATA400_SR10]![SR10_WHITE]="    Y",1,0)) AS White, Sum(IIf(Len(Replace([DATA400_SR10]![SR10_INDIAN] & [DATA400_SR10]![SR10_ASIAN] & [DATA400_SR10]![SR10_BLACK] & [DATA400_SR10]![SR10_HAWAIIAN] & [DATA400_SR10]![SR10_WHITE]," ",""))>1,1,0)) AS MultiRace
FROM DATA400_SR10
GROUP BY DATA400_SR10.SR10_IPEDS_HISPANIC;

Open in new window

Great work guys.  I've learned some new tricks too that will be extremely helpful.  Thanks!