Masking Query Results based on Criteria? Access 2010

[painterz]

The Family field ties the spouses, along with their children, together at the same address so they don't get mixed. Two families with the same last name and different addresses would have separate AddressIDs. Since children aren't listed in the directory, it's a moot point if Beth Jones is listed as the daughter of Dave Jones. Since she owns or rents the house down the street, her name is associated with her own Family code and AddressID.

If you have a suggestion for another table or different structure, I'm open. I can't see how to improve the structure--it's already straightforward. Did you look at qryDirectoryFinalbyName? Everyone lists along with their family code and I do show 2 families at one address, Marple and Chapman. For some reason, in the report Directory Sorted by Name, the husband's are hit and miss so I'm still trying to figure that one out.

 

[painterz]

This is the latest db with the Family table.

 

[plog]

Yes, my advice is to directly link spouses together via a SpouseID in tblPeople.

 

[painterz]

I did that but it's called Family in the Family and People tables. I think this is just semantics.

 

[plog]

tblFamily isn't properly placed with regard to the other tables. You have People, Addresses and Families in a circuitous relationship: Families are related to Addresses, Families are related to People, Addresses are related to People. There should only be one path among tables, your TblFamily essentially puts those 3 tables in a loop, which is poor structure.

Putting a SpouseID in tblPeople and eliminating tblFamily would be the simplest way to resolve it. But, its not the only way, you can make tblFamily work by removing AddressID from either tblPeople or tblFamily. I think it should only be in tblFamily. That way the table hierarchy looks like this:

tblAddress->tblFamily->tblPeople

I also think this is overkill. I think you can achieve this with tblAddress->tblPeople alone. You strip out PKRelationsh and MaritalStatus and simply put in SpouseID.

I also noticed that SourceYear in tblPeople is a text field. Isn't all data going into it going to be numeric? I also see ChildsPhone field, shouldn't that data be in tblPhone? Also, what are OwnerFname and OwnerLName fields?

 

[painterz]

After I posted my last version of my db I realized I do have a serious problem in the Family table. I got rid of the Family field and am using FamilyID as my foreign key in my People table. I also reworked my qryDirectory2 to link on FamilyID instead of PeopleID.

My intended structure is:

tblAddress-->tblFamily-->tblPeople

Right now, I've got a mess on my hands trying to fix my queries.

The children also have a PKRelationship key = 3. If I strip out PKRelationship, the kids will show up in the directory. For security reasons, we don't list the kids. I have to keep track of the kids for the newsletter, jobs, and pool.

The source year is the year someone is welcomed to the neighborhood. If it's blank, I know I don't have a directory form for them and I let the Welcome committee know. Yes, it should be a numeric field but I don't use it for anything other than reference. My data came from several Excel spreadsheets so that one must have slipped through.

OwnerLName and OwnerFName was my fix until I could figure out how to work out the families. These fields are for the owners of the rental properties. They are the ones who owe the HOA fee and they have to sign off on the pool tag registration form for their renters. In my tabbed subforms (yet to be designed) they will be my primary owners.

 

[plog]

Worry about fixing queries later. Get the structure right and then you move onto reports/queries.

That means you will need to have these fields in these tables, which involve rethinking your tblFamily.

tblAddress
AddressID, autonumber, primary key

tblFamily
FamilyID, autonumber, primary key
AddressID, number, foreign key to tblAddress

tblPeople
PeopleID, autonumber, primary key
FamilyID, number, foreign key to tblFamily
FamilyMember, text, values: Husband, Wife, Child

tblFamily shouldn't determine each person's place in a family, it should just define a family. To determine their person's place in a family you would do that in tblPeople with the FamilyMember field. If you want to track Married/Significant Other/Seperated, etc. You should just store that in a seperate text field in tblPeople.

 

[painterz]

I'm uploading my "fixed" db. The relationships have always been used in tblPeople (the people are in the relationship)--tblFamily doesn't use the relationships field.

Queries seem to report everything accurately.

Directory Sorted by Name report is still dropping off some husbands. I've yet to find the pattern.

 

[plog]

Attached is my database of your data. I structured like I detailed in my post, then I built the queries to generate your directory. I believe it works, but you have different data in it than your original one, so I have nothing to compare it to.

Give it a shot and let me know if anything is incorrect.

 

[plog]

Oh yeah, Family=2 was screwy in your People table. You had both of them married, but it wasn't to each other in the Relationship field. So I made one of those people their own family (FamilyID=24) to make them each have their own line in the directory.

 

[painterz]

That's what happens when one changes their data to protect the innocent--it's unrecognizable. I did change some of the info in the latest db so I could keep track of the people, evidently that's where I went haywire in the Family table. Thanks for you db, I've downloaded it.

 

[painterz]

Excellent! I like that your module combines the names. I've gotten so rusty with my coding I was trying to solve this with a query. I'm going to have to reverse engineer my database, modules, and forms but at least I can see that it does work and have an idea on how to accomplish it.

 

[plog]

Yeah, I used the bare minimum fields in my tables to achieve it. Let me know if you find any issues. And remember, work on the tables first, then queries/reports.

 

[painterz]

I know this a newbie question but I'll ask it anyway. In Directory_sub2, there is a one to one relationship between the three tables, t_people, t_phones and tblphone_types. With a 121 relationship between t_people and t_phone, why did it return all of the phone numbers associated with each person? I would have expected only one result per person.

 

[plog]

Which one would you expect?

People_ID=146 has 3 phone records. The query says wherever there's a match between a record in People and Phones based on People_ID show that record. All 3 match, so all 3 show.

There are aggregate functions which you could use to make it one per People_ID (e.g MAX, MIN). But in a non-aggregate query it shows everything that matches between those 2 tables.

 

[painterz]

You rock!!! Moving on to the next query question.

 

[painterz]

Hi Plog,

I just realized there's a design flaw using "primary" for each family at an address. The actual person who owns the house is the primary. They owe the HOA fees. The secondary family isn't responsible for the dues (this applies to owners and renters too). When I use a query to pull all people who owe HOA fees, both families will be in the results.

"Primary", "spouse", "child", all appear in a drop down box on my form when I make the designations. "Secondary Primary" is too long and a bit confusing. Any suggestions?

Thanks

 

[plog]

You are misusing the t_People.people_Role field. It is to designate a person's role in a family. There is no 'Secondary' in a family, there are Primary, Spouse and Dependent.

If you want to capture who owns a house, you should add a new field to t_People to designate that. Perhaps a Yes/No field called people_Owner