Search results

Sorry for the slow response. When I originally designed this db, I was short sighted and saw it as one house to many people. Then one family's child moved back home with her family and I now realize it should be one house to many families then the many people. I've attached a copy of my...

Table structure: Address-->Family-->People-->…beyond I have designed my underlying queries to be one to many between Address and Family, then Family and People, and People and beyond. I had thought the tab control would act as the intermediary (Family) between Address and People but I think...

Hi All, I have a working db, forms, and code with a minor glitch. I have a main form for street address and subform for the family. A problem arose when I had 2 families at one address; also, when I have an owner and renter--so multiple families per address is causing me a problem. I thought...

Thank you, both, for your replies. Unfortunately, nothing worked. I'm still getting the same error message. This all worked fine until I added the tab control.

Hello All, I'm new to the tab control. I've placed a tabbed control on my main form and populated it with my subform (which also contains subforms). Now my OnLoad for my main form isn't working. I've looked online on how to reference the subform and it seems simple subformname.sourceobject...

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...

Scrapped my test data and started over. Ran a query to pull all primary PeopleID out of tblPeople and appended it to a fresh tblFamily1. That should simplify the remaining info from tblAddress that's needed--another update query but should be straightforward, especially since you've given me...

Nope, didn't work…I have two tblPeople.AddressID = 79 -- PeopleID = 146 and 2382 (two families at one address) and the query duplicated tblFamily1.PeopleID = 2382. I'll try it again, using no duplicates in the field def of tblFamily1.PeopleID. The query also said it was updating 15 rows when...

Big head thunk! I can use tblPeople.PkRelationship=1 to pull the first person (Primary household member) to match each AddressID. UPDATE tblFamily1 INNER JOIN tblPeople ON tblFamily1.AddressID = tblPeople.AddressID SET tblFamily1.PeopleID = tblPeople.PeopleID WHERE tblPeople.PKRelationship=1...

Hi CJ, you're right. I need the first PeopleID for each unique AddressID. I'm going to spend a couple of hours and do it manually. Thank you for your help. I'll be back for my next query challenge. :0

"Finally - why do you need the subquery at all? you are updating tblFamily.PeopleID with A.AddressID which according to the subquery criteria will equal B.AddressID so you could simply have UPDATE tblFamily SET PeopleID = AddressID WHERE PeopleID is null" Hi CJ, My query isn't correct and...

Hi CJ, The link suggested by both you and pbaldy are clear as mud; I'm just not getting it. I did make the alias change you suggested, and my Access query is still looking for another db. It's been a long time since I've written SQL statements so have no idea where I'm going wrong. Thanks...

Yes, it's in Access. I've been online trying to find anything that might work and this is the closest I came to finding something that did anything. I'll incorporate both your changes and see if I can get any further. Thank you!

Hi All, I realized I need another table in my established db to go between two other tables. The new table, tblFamily, has one PK and two FK for the other two tables: tblAddress--->tblFamily-->tblPeople tblAddress: PK AddressID tblFamily: PK FamilyID tblFamily: FK AddressID tblFamily: FK...

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

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...

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.

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.

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...

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...