Hello, It's been a while.

I'm in school for access, BUS 240.


I gotta make a database that helps calculate total income of a household for tribal members.
So I have a tblPerson and a tblHousehold, both have a related field anumHouseID so they can be brought together by a query.



So tblHousehold and tblPerson have a one-to-many relationship via anumHouseID.
this is because one person can live at a house with other people.

I want to be able to enter somebody's name as a parameter and pull up every record that shares the same value in the anumHouseID field.
EXAMPLE: I enter KingSatan into the prompt or form, it pulls up my name along with my two room mates because we each have the same value in anumHouseID.

If you need a clearer explanation, I can work on one.
If you understand and can help, thanks a ton in advance.

Don't name your tables with a tbl prefix. what's the difference between "num" and "anum" prefixes?

Regardless, to answer your question:

SELECT * FROM PERSON WHERE anumHouseId IN
(SELECT anumHouseId FROM PERSON WHERE PERSON.NAME LIKE '%KingSatan%')

Adjust as needed. not sure which name field you wanted to look at, if you wanted a case sensitive search, exact name search, what to do if there's more than one person with the name, etc.

thanks for the response

I was only using "Name" because it's easier to convey the idea.
Each person has a Tribal ID number separate from the primary key yet still unique. That's what I was gonna use in place of the name



e/ anum and num

anum is an autonumber
num is a number

This post was edited by NerdrageIMO on Jul 27 2014 05:54pm