Masking Query Results based on Criteria? Access 2010

[painterz]

Hello All,

I'm trying to create a neighborhood directory that will show both spouses' phone numbers. The report is based on nested queries. My tables have a one to many (12M) relationship: Address (pk=AddressID)-->People (pk=PeopleID)-->Phone (pk=PhoneID)

In the Directory, my goal is to have the spouses' names on one line along with their address and the phone numbers show up in the detail section of the report.

There is an underlying query that combines the names of the husband and wife in one record (field: Name). When this happens the PeopleID of the wife is lost so her phone number no longer associates with her record. The husband doesn't lose his phone numbers since his PeopleID is still associated with him. If I don't combine their names, spouses will be on two lines in the directory. To keep the wife's data, I still have FirstName and LastName fields.

In my report, I've tried grouping on Name, the husband and wife show (Jones & Smith-Jones, Roger & Clara), but then the wife (Smith-Jones, Clara) shows up again with her phone numbers. My first thought was to use the "hide duplicates" of the report, but since the wife's name isn't combined she's not a duplicate.

In my tables I have a Relationships table. PKRelationship 1 = husband and PKRelationship 2 = wife Is there a way to combine my lines in the report and mask the wife's name so her phone number will show up in the correct detail area?

Any suggestions?

Thanks,

painterZ

 

[plog]

What field do you make the husband/wife relationship in? How do you know if 2 records are spouses? What about a residence with more than 2 primary occupants, a la Three's' Company or some Utah situation?

Also, since this sounds like its ultimately a report issue, can you demonstrate with data what you want your report to look like? To do this provide me with 2 things:

A. Starting sample data from your tables. Include table/field names and enough sample data to cover all cases.

B. A mockup of your report based on the data you provide in A. Show me what the report should look like when you base it on A.

 

[painterz]

This is going to a long post, so I'll break it up. This is how I combine the last names:

LName: [tblPeople].[LastName] & IIf([tblpeople].[PKRelationship]=1,IIf((SELECT dupe.LastName
FROM tblPeople as dupe
WHERE tblPeople.AddressID = dupe.AddressID and dupe.PKRelationship=2 and tblPeople.LastName <>dupe.LastName)<>""," & " & (SELECT dupe.LastName
FROM tblPeople as dupe
WHERE tblPeople.AddressID = dupe.AddressID and dupe.PKRelationship=2 and tblPeople.LastName <>dupe.LastName),""))

So this says looking at the same AddressID, look for a husband's (PKRelationship =1) last name, then look for the wife's (PKRelationship =2), if they're different then combine the names, otherwise, report his name.

This is how I combine the first names:

FName: [tblPeople].[FirstName] & IIf([tblpeople].[PKRelationship]=1," & " & (SELECT dupe.FirstName
FROM tblPeople as dupe
WHERE tblPeople.AddressID = dupe.AddressID and dupe.PKRelationship=2),"")

Then in my record source query for the report I have Name: LName & ", " & FName to combine the names together.

I actually do now have addresses with two families and added a family table (not shown in my attachment) so I could have multiple families from one household. Once I get the wife's phone number(s) reported, I'll work on multiple families.

 

[plog]

So basically that last paragraph can be translated into 'Yes, plog your concerns are valid and I haven't addressed them'. Right?

Code, especially code that isn't working like people want, never really helps to clarify things for me. Can you please post sample data as I outlined in my previous post?

 

[painterz]

It takes time to alter data to protect the innocent. I've attached two reports:

DirectoryNoPKR2.pdf
DirectoryInclPKR2.pdf

DirectoryNoPKR2 looks like I want, however, the wife's phone number is missing. Notice the first record in both reports, Natasha has a cell phone but it doesn't show in the layout I want.

The second report, DirectoryInclPKR2.pdf shows the wife's info, but shows it on another line.

If I have three adults in one house and two are married and one is single, they all show--notice the three people on Day Glow Rd. A mother moved in with her married child.

The two entries on Ruby Red Rd are correct, they are sisters.

It falls apart if I have two couples in the same house (yes, I'm working on it), notice the family on Chartreuse Road.

Not everyone chooses to have their phones listed.

 

[plog]

Last chance. Please give me these 2 things:

A. Starting sample data from your tables. Include table/field names and enough sample data to cover all cases.

B. A mockup of your report based on the data you provide in A. Show me what the report should look like when you base it on A.

Quit trying to talk me through it, show me.

 

[painterz]

I'm pulling from 3 different tables using 4 nested queries. What data do you want? Do you want each table? Do you want each query result?

 

[plog]

I want data in your tables and then what that data should look like on your report. Think of me as google maps: Give me a starting point and a destination and I will fill in the route between them.

 

[painterz]

Database and directory exported to Word

 

[plog]

Now its time for explanations. Why do you want Joyce Dewitt to have her own line on the report, but not Darla Holiday?

They both have spouses and both are included on their spouses row. What makes Joyce special?

I don't think this is a simple oversight/typo, because you did the same thing with Susie Spears-Grainger, she is also in 2 spots on the report.

Ultimately, I think you determining your spouses incorrectly. You are trying to logically tease out the Husband/Wives, I think you need to explicitly capture it. That would mean a new field in the table called something like 'SpouseID' and it would hold the 'PeopleID' of the person who is their spouse.

 

[painterz]

No, it was a typo. I'm not used to seeing my data like this. Here's the new file.

 

[plog]

No problem. I really think explicitly declaring your spouses like I mentioned before is the way to go. With that said, you can build another query which you can use to limit what records show on the report.

You don't want any records from tblPeople with PKRelationship=2 to show on the report, so make a query to kick them out:

SELECT tblPeople.PeopleID
FROM tblPeople
WHERE (((tblPeople.PKRelationship)<>2 Or (tblPeople.PKRelationship) Is Null));

Save that and then bring it into the qryDirectoryFinalbyName and link it to qryDirectory2, so that it only shows matches between the two. That will fix your report...

However, that report already has some duct tape and glue holding it together. qryDirectoryPhone shouldn't be used as an underlying query. After you make the changes I showed you, run it and you will see 2 records for PeopleID=296. That's because he has 2 phone numbers--caused by the qryDirectoryPhone. You added a hack to the report to get around that. Instead you should really start from scratch and rebuild the queries that generate the data.

Again, though, before you do that, you should really evaluate how you determine spouses. That whole subquery system seems doomed to fail when you start throwing in edge cases.

 

[painterz]

If I understand correctly, your query will eliminate the wife. I need to group the wife's phone number in with the husband's without her name repeating. If you'll look at DirectoryNoPKR2.pdf (I've removed "or 2" from PKRelationship in the qryDirectory2; DirectoryInclPKR2 shows the wife) , the wife's name is grouped with the husband's but I've lost the wife's phone number. Look at the family on Aquamarine. There should be three phone numbers, 1 home and 2 cells.

Also, there is a one to many relationship between tblPeople and tblPhone. Some of of my households have 6 phone numbers. PeopleID=296 is suppose to have 2 phone numbers, but I also want PeopleID=297 to list as well.

I do think this is a reporting problem but I couldn't get any solutions when I posted to that forum so I thought I could address it with queries. I'm currently working to grouping families together and that may solve all of my problems.

 

[plog]

I see it now. Generating the report like you want is all predicated on getting a table/query that lines PeopleIDs together to form spouses. Right now you are doing it through a matching algorithm that I don't trust.

What's your family table going to look like? And have you given any thought to just adding a SpouseID field into tblPeople?

 

[painterz]

My Family table has PKFamilyID, Family, PeopleID, and AddressID. It will join between my address and people tables in my queries. I've been working on the report using Family (long int) and it works great--however, it sorts on the Family field so really makes no sense to the user, but does keep my families' phone numbers together. I'd like to be able to group on Family but sort on Name instead of nesting them. When Name becomes involved (regardless of level), the wife's info shows up in a new section.

Keep in mind the Relationships table goes beyond spouses. There's a unique number for children and significant others too and it also lets me manipulate the data based on married, divorced, separated, widowed, and single (NA).

I'm wondering if I could work with PKRelationship=12 instead of 1 and 2 and get the info grouped that way. The info's there, I just have to figure out how to get it out in a format I want.

 

[painterz]

I think Family will be equivalent to SpouseID. It will link both the Family and People tables.

 

[plog]

Yes, I think you need to rethink your relationships. What is the point exactly? As it stands now, it shouldn't be in the People table because a woman could be a wife, a child and married. That's a 1-many that doesn't really tell you to whom she's in the relationship with.

Actually, you really need to rethink what your entities are. I mean, how grainular do you need to be? What's the purpose of this database? At first I understood seperating out Addresses into its own table because you wanted to group everyone at an address together, but now you want to group families which may live at the same househould together.

What's the hierarchy of this? Do you have levels you don't need? Do you have levels you do need but don't? Do you need to know who a child's parents are? Do you need to know an unmarried couple is living together along with the woman's child? Do you need to know Ms. Smith is twice divorced and once widowed? Whats the ultimate purpose?

 

[painterz]

All good questions. This is a neighborhood database. We keep track of the kids to be listed in the newsletter for neighborhood jobs and happy birthday notices. Since we don't keep track of the adults birthdays, I will query on "widowed" to give the over 50+ club somewhere to start to invite members. The "separated" and "divorced" statuses we don't use anywhere. I use it as a tickler that there will probably be a name change for the directory.

"it shouldn't be in the People table because a woman could be a wife, a child and married"

If a woman is married, she is a wife. If she's a wife, it supersedes her being a child. With the Family table, I'll be able to show two families in the same household and they both get to have a listing in the directory.

I do need to know who the children and parents are because we have a community pool and if an issue comes up we need to get in contact with the parents.

The ultimate purpose is the database is a repository for the residents general information for the neighborhood HOA. For instance, before pool tags can be sold the resident has to be paid on their dues. When it's time to vote for their annual meeting, I have to generate a resident list for the proxies (primary family/owner).

My table relationships are simple, all are one to many. When I designed this 12 years ago, many people can live in one house; many phones per person. I knew I had a problem when an owner and renter both lived in the neighborhood, then a family's married child moved back home with her family. The family table should address both of those. I'm still at the design stage.

So now it's many families can live at one address, there can be many people per family, etc. I'm working on the db to upload.

 

[painterz]

In the Directory Sorted by Name, I finally got the Sugart's info to stay together, but all of the others fell apart. I've attached my new db with the Family table

 

[plog]

Again, you really need to think this through and get the structure right. This involves thinking about all the possibilites and making sure your tables can handle them.

Mary Smith is married to John Smith, they live together with their son James Smith and his wife Sally Smith. Can your database keep all that straight? I don't think so, the spouses get mixed.

Dave Jones is lives down the street from his daughter Beth Jones who lives with just her son Paul Jones. Can your database keep all that straight and accurate?

I think you are both capturing too much and not enough data. You need to assign spouses to each other, not just say people who live in the same house and have the same relationship code.