ACCESS FEED: Re: [MS_AccessPros] Finding Poverty Levels

John,
Perfect as Always.

With warm regards,

Arthur Lorenzini
Sioux Falls, South Dakota

________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Sunday, May 27, 2012 2:57 PM
Subject: RE: [MS_AccessPros] Finding Poverty Levels

Art-

It's a simple matter to insert the expression I gave you:

SELECT tblApplicationHousehold.ApplicationID, Count(tblApplicationHousehold.HouseholdID) AS NoPersons, Sum(IIf([tblApplicationHouseholdIncome].]IncomeAmount] > 3000, [tblApplicationHouseholdIncome].[IncomeAmount], [tblApplicationHouseholdIncome].[IncomeAmount] * 12))) AS Income
FROM (tblApplication INNER JOIN tblApplicationHousehold ON tblApplication.ApplicationID = tblApplicationHousehold.ApplicationID) INNER JOIN tblApplicationHouseholdIncome ON tblApplicationHousehold.HouseholdID = tblApplicationHouseholdIncome.HouseholdID
WHERE (((tblApplication.AppActiveFlag)=Yes))
GROUP BY tblApplicationHousehold.ApplicationID;

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

--------------------------------

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art Lorenzini
Sent: Sunday, May 27, 2012 9:31 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Finding Poverty Levels

John, this is the SQL now:

SELECT tblApplicationHousehold.ApplicationID, Count(tblApplicationHousehold.HouseholdID) AS NoPersons, Sum(tblApplicationHouseholdIncome.IncomeAmount*12) AS Income
FROM (tblApplication INNER JOIN tblApplicationHousehold ON tblApplication.ApplicationID = tblApplicationHousehold.ApplicationID) INNER JOIN tblApplicationHouseholdIncome ON tblApplicationHousehold.HouseholdID = tblApplicationHouseholdIncome.HouseholdID
WHERE (((tblApplication.AppActiveFlag)=Yes))
GROUP BY tblApplicationHousehold.ApplicationID;

I would set the threshold at $3000.00.

With warm regards,

Arthur Lorenzini
Sioux Falls, South Dakota

________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Sunday, May 27, 2012 12:31 PM
Subject: RE: [MS_AccessPros] Finding Poverty Levels

Art-

Tough to say. You could decide on a threshhold amount, perhaps $2000, and avoid the multiply. What's the SQL of your "fixed" query? It would take an expression something like:

Sum(IIf([Income] > 2000, [Income], [Income] * 12))

In other words, if the amount is greater than 2000, use it as the annual amount; otherwise, multiply by 12 to get the amount. You could play with the threshhold amount - it might take using 3000 or more. I note that the top "poverty" amount is $38,890, or $3,240 a month.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

--------------------

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art Lorenzini
Sent: Sunday, May 27, 2012 7:18 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Finding Poverty Levels

I made the join and the qryHouseholdIncome is working. I do have another question. When I run the query I see a few records that are over $100,000 and I am guessing that an annual amount had been entered. Is there a way to just to divide those by 12 in the same query?

With warm regards,

Arthur Lorenzini
Sioux Falls, South Dakota

________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Sunday, May 27, 2012 11:58 AM
Subject: RE: [MS_AccessPros] Finding Poverty Levels

Art-

OK, I'm convinced.

Let's start with your query to count members and add a calculation of the income amount:

qryHouseCountIncome:
SELECT tblApplicationHousehold.ApplicationID,
Count(tblApplicationHousehold.HouseholdID) AS NoPersons,
Sum(tblApplicationHouseholdIncome.IncomeAmount*12) As Income
FROM tblApplication INNER JOIN tblApplicationHousehold ON
tblApplication.ApplicationID = tblApplicationHousehold.ApplicationID
WHERE tblApplication.AppActiveFlag = Yes
GROUP BY tblApplicationHousehold.ApplicationID

Now let's do the funny join:

SELECT tblPovertyGuidelines.PersonInHousehold, tblPovertyGuidelines.PovertyGuideline,
Count(qryHouseCountIncome.ApplicationID) As IncomeLevelCount
FROM qryHouseCountIncome INNER JOIN tblPovertyGuidelines
ON qryHouseCountIncome.NoPersons = tblPovertyGuidelines.PersonInHousehold
AND qryHouseCountIncome <= tblPovertyGuidelines.PovertyGuideline
GROUP BY PersonInHousehold, PovertyGuideline;

You'll have to do that in SQL view.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-------------------------------

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art Lorenzini
Sent: Sunday, May 27, 2012 5:55 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Finding Poverty Levels

The tblApplicationHousehold is counting individuals in the household.

This is the structure for tblApplicationHousehold:

HouseholdID
ApplicationID
FirstName
MiddleName
LastName
SuffixID
MaidenName
RelationshipID
...

There are more fields but this will give you the gist.

With warm regards,

Arthur Lorenzini
Sioux Falls, South Dakota

________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Sunday, May 27, 2012 10:48 AM
Subject: RE: [MS_AccessPros] Finding Poverty Levels

Art-

But is tblApplicationHousehold counting PEOPLE or Households? That's what is not clear to me. I would have thought HouseholdID is unique for a household, and there should be some other ID like PersonID that is unique for the PEOPLE within the household. You want to count people, not households, right?

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

---------------------------------------

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art Lorenzini
Sent: Sunday, May 27, 2012 5:16 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Finding Poverty Levels

John,
Those numbers are coming from my household member table. There is only one household per application. I have to include the application table so I can get the active application.

With warm regards,

Arthur Lorenzini
Sioux Falls, South Dakota

________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Sunday, May 27, 2012 8:51 AM
Subject: RE: [MS_AccessPros] Finding Poverty Levels

Art-

Are you sure that first query gets you the number of people in each household?
That would seem to count the households that have all applied under the same
application ID. Do you have a household members table? I would think you would
count that.

I need your answer to the above questions before I can help you solve the final
problem. It'll involve doing a funky join with tblPovertyGuidelines on Count of
household members = PersonInHousehold and total income <= PovertyGuideline.

John Viescas, author

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Microsoft Access Applications

Microsoft Office Access 2003 Inside Out

SQL Queries for Mere Mortals

<http://www.viescas.com/> http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art
Sent: Sunday, May 27, 2012 2:56 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Finding Poverty Levels

I have to get a count of households that fall in the each poverty level and I am
not sure of the logic:

Here is the tblPovertyGuidelines:

PersonInHousehold PovertyGuideline
1 $11,170.00
2 $15,130.00
3 $19,090.00
4 $23,050.00
5 $27,010.00
6 $30,970.00
7 $34,930.00
8 $38,890.00

I know I have to get a count of people in each household:

SELECT Count(tblApplicationHousehold.HouseholdID) AS CountOfHouseholdID,
tblApplicationHousehold.ApplicationID
FROM tblApplication INNER JOIN tblApplicationHousehold ON
tblApplication.ApplicationID = tblApplicationHousehold.ApplicationID
GROUP BY tblApplication.AppActiveFlag, tblApplicationHousehold.ApplicationID
HAVING (((tblApplication.AppActiveFlag)=Yes));

Then I need to lookup each household member that has income and produce the
annual amount (they report monthly amounts):

SELECT tblApplicationHousehold.ApplicationID, [FirstName] & " " & [LastName] AS
[Member Name], tblApplicationHousehold.RelationshipID,
tblApplicationHouseholdIncome.IncomeTypeID,
tblApplicationHouseholdIncome.IncomeAmount, [IncomeAmount]*12 AS [Annual AMount]
FROM tblApplicationHousehold INNER JOIN tblApplicationHouseholdIncome ON
tblApplicationHousehold.HouseholdID = tblApplicationHouseholdIncome.HouseholdID;

THis is where I get lost. Now I need to put it all together, please help!

Thank you
Art Lorenzini
Sioux Falls, SD

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]