Query for SearchForm that fetches records in nested tables

[GrandMasterTuck]

Okay... here's the newest issue I'm having. I once again humbly request any input from the geniuses herein.

I have three tables, tblPeople, tblAddresses and tblVehicles. I have a form called frmPeople that shows a single person record, and has subforms with the list of addresses and vehicles for that person (each record has several). I want to create a single search form with each of the fields in my tblPeople, tblAddresses and tblVehicle tables, and allow the user to type anything into any of the fields on the search form, and have the search form fetch records that match.

So far, I have the query built, and the tables all linked the right way (I think), and I have all the search fields coded. And it works GREAT... except for this one little issue: If I do a search, and I put "Smith" in the last name field, I will get several Smiths in my search result, one for each address and vehicle combination. In other words, if I have ONE Smith in the table, and two vehicles and two addresses for that Smith, the query results give me FOUR Smiths! One with Vehicle1 and Address1, one with Vehicle2 and Address1, one with Vehicle1 and Address2, and one with Vehicle2 and Address2.

What I would LIKE is to have ONE record in the query results, and that record shows me that one Smith. And if I then search for "Plymouth" in the VehicleType field, and I have Smith in the name field, I'll get ONE record, and it will be the one of Smith with his Plymouth (Vehicle2).

Here is how my query looks right now (this one ONLY has the last name and vehicle search function to save space), and this is the one that gives me multiple results (which I do NOT want, unless multiple PEOPLE match the search criteria):

SELECT DISTINCTROW tblPeople.LastName, tblPeople.FirstName, tblPeople.PersonID, tblAddresses.Address, tblVehicles.Vehicle, tblVehicles.Plate, tblVehicles.VehicleYear
FROM tblPeople LEFT JOIN tblAddresses ON tblPeople.PersonID = tblAddresses.PersonID
WHERE (((tblPeople.LastName) Like "*" & [NameField] & "*")
AND ((tblVehicles.Vehicle) Like "*" & [VehicleField]));

Any ideas? Did I write the query wrong? Okay... that's a dumb question... of COURSE I wrote it wrong... that's why I get duplicate search results... Any help anyone can lend? Thanks a million, you guys.

 

[JHB]

... So far, I have the query built, and the tables all linked the right way (I think), and I have all the search fields coded. And it works GREAT... except for this one little issue: If I do a search, and I put "Smith" in the last name field, I will get several Smiths in my search result, one for each address and vehicle combination. In other words, if I have ONE Smith in the table, and two vehicles and two addresses for that Smith, the query results give me FOUR Smiths! One with Vehicle1 and Address1, one with Vehicle2 and Address1, one with Vehicle1 and Address2, and one with Vehicle2 and Address2.

You can narrow the above down to get a result of two, if the address is the same for both vehicle, if you use DISTINCT instead of DISTINCTROW.

If you want a result of one, you need to take out the Vehicle, Plate, VehicleYear from the Select, or use First/Last/Min/Max for the 3 fields.

 

[GrandMasterTuck]

The ADDRESS and VEHICLE are in different tables, both of which are linked to the main table PEOPLE. I want only individual PEOPLE records to show in the search results, ONLY ONE TIME, and ONLY if they have either an ADDRESS or a VEHICLE that matches the query criteria.

I want a person to be able to open this database, say to themselves, "Yeah, I know his name was SMITH," or maybe, "I know he drives a Volvo," or maybe even "I know that he lived on James St. and he had a Chevy," and they can run those searches. In each example, the system will look at all three tables, and return only records from the PEOPLE table that have matching associated records in either ADDRESS or VEHICLES with data elements that match the queried terms.

I hope that's not confusing. I can tell you that Access IS confusing to me. There seems to be a thousand ways to accomplish each task, and I can solve about 90% of the problems I encounter, but once in a while I find a problem like this that stumps me, and no matter what I try, I can't figure it out... I hope you know the answer! Thanks again for checking in, though.

 

[JHB]

.. I want only individual PEOPLE records to show in the search results, ONLY ONE TIME, and ONLY if they have either an ADDRESS or a VEHICLE that matches the query criteria.
...

How do you want the result returned if a person have 3 vehicle and you only put in the name in the search?

 

[GrandMasterTuck]

Whoo! Back again after an intense summer of work. Sorry I've been off the grid for so long, JHB (I hope you're still around here...). Here's the answer to your question:

If I only put the name in the search, I just want results that match the name. If one of the named results has three vehicles, I still only want his name once in the search results. The purpose of the database is people searching, so any and all results will be lists of peoples' names. If I search for ONLY a car, I want the PEOPLE that have a car that matches my search criteria. If I search for a name and a car, I want only people who match BOTH name and car (so the search results NARROW as you add more criteria). If I search for a name, car and house color, I want only results that match ALL THREE.

So if I search for the name SMITH with a car CHEVY and a house color BLUE, and there's a Dave Smith with a Chevy Cavalier and a blue house, he'll show up, but the Joe Smith with a Chevy Malibu and a red house will NOT show (because he doesn't match ALL THREE of the filled in fields on the search form).

I hope that makes sense. And thanks again, if you get the chance to wander back into this long-dead thread and reply again.