Solved

Query Assistance in MS Access

Posted on 2011-02-16
12
301 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 11

Assisted Solution

by:RgGray3
RgGray3 earned 200 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Great work guys.  I've learned some new tricks too that will be extremely helpful.  Thanks!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

772 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

11 Experts available now in Live!

Get 1:1 Help Now