Solved

Query Assistance in MS Access

Posted on 2011-02-16
12
303 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
  • 4
  • 4
  • 4
12 Comments
 
LVL 51

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 51

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 51

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
 
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 11

Assisted Solution

by:RgGray3
RgGray3 earned 200 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 51

Accepted Solution

by:
HainKurt earned 300 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

919 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now