?
Solved

Query Assistance in MS Access

Posted on 2011-02-16
12
Medium Priority
?
322 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:anthonyhardy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 4
12 Comments
 
LVL 58

Expert Comment

by:HainKurt
ID: 34911065
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

0
 
LVL 58

Expert Comment

by:HainKurt
ID: 34911087
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

0
 
LVL 58

Expert Comment

by:HainKurt
ID: 34911099
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

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:RgGray3
ID: 34911116
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
0
 

Author Comment

by:anthonyhardy
ID: 34911146
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?
0
 

Author Comment

by:anthonyhardy
ID: 34911229
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
0
 
LVL 11

Assisted Solution

by:RgGray3
RgGray3 earned 800 total points
ID: 34911247
If you can query the data you can directly,  you can write SQL something like

StudentNumber, [Hispanic]="Y", [NativeAmerican]="Y", [Asian]="Y", [AfricanAmerican]="Y", [Caucasian]="Y", [Hawaiian]="Y"
which in Access would result in -1 or 0  (True or False)

or alternatly ABS([NativeAmerican]="Y") which would return 1 or 0

This would be the key to easily totaling each row to see if it has more than 1 Yes

and allow you to total the column

Still not sure I fully understand your end result....  

From the question and its sample...  it looks as if you are just looking to determine if the student is Hispanic or not...

Please clarify
0
 
LVL 11

Expert Comment

by:RgGray3
ID: 34911263
OK...  got your last example
Understand the incomming
see my previous
but is the goal Hispanic/NonHispanic...  logic would change slightly
0
 
LVL 11

Expert Comment

by:RgGray3
ID: 34911269
I believe converting the Text into valid Numeric values is key to your solution
0
 
LVL 58

Accepted Solution

by:
HainKurt earned 1200 total points
ID: 34911313
yes, try this

select
iif(hispanic="Y", "Hispanic","Non-Hispanic") Ethnicity,
sum(iif(Asian&..&Hawaiian = "YNNNNN",1,0)) NativeAmerican,
sum(iif(Asian&..&Hawaiian = "NYNNNN",1,0)) Asian,
...
sum(iif(Asian&..&Hawaiian = "NNNNNY",1,0)) Hawaiian,
sum(iif(len(replace(Asian&..&Hawaiian,"N",""))>1,1,0) Multi
from myTable
group by hispanic
0
 

Author Comment

by:anthonyhardy
ID: 34911509
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

0
 

Author Closing Comment

by:anthonyhardy
ID: 34911522
Great work guys.  I've learned some new tricks too that will be extremely helpful.  Thanks!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question