[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

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
0
anthonyhardy
Asked:
anthonyhardy
  • 4
  • 4
  • 4
2 Solutions
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
RgGray3Commented:
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
 
anthonyhardyAuthor Commented:
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
 
anthonyhardyAuthor Commented:
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
 
RgGray3Commented:
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
 
RgGray3Commented:
OK...  got your last example
Understand the incomming
see my previous
but is the goal Hispanic/NonHispanic...  logic would change slightly
0
 
RgGray3Commented:
I believe converting the Text into valid Numeric values is key to your solution
0
 
HainKurtSr. System AnalystCommented:
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
 
anthonyhardyAuthor Commented:
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
 
anthonyhardyAuthor Commented:
Great work guys.  I've learned some new tricks too that will be extremely helpful.  Thanks!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now