ACCESS FEED: Re: [MS_AccessPros] Finding Poverty Levels

Minggu, 27 Mei 2012

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]