I am going to stab this stupid subquery in the face (Access troubles) (SOLVED, nevermind)

ElJeffe

Okay, I'm trying to run a query that incorporates a subquery. Or maybe I don't need a subquery, fuck if I know.

Anyway.

I have a database that contains, among other things, the fields IDnumber, firstname, lastname, and ParentID. The ParentID is how the database matches related records together. If the record is related to another record, the ParentID contains the IDnumber of the other record. If the record isn't related to anything, the ParentID just contains the IDnumber of its own record.

So you might have something that looks like:

IDnumber    FirstName    LastName    ParentID
00001       Joe          Smith       00001
00002       Bob          Smithie     00002
00003       Ed           Eddies      00003
00004       George       Brown       00001
00005       Matt         Eddies      00003

I want to run a search that pulls values from a form for the IDnumber, firstname, and lastname fields, and returns all records which match based on those values, AND all records that are related to anything that matched. If, for example, the LastName search field contains "smith", I want the query to return the records for 00001, 00002, and 00004.

I tried doing this by doing an initial query for just the records that match the search fields (00001 and 00002), and then feeding the results of that query into a second query (basically returning all records for which either the IDnumber OR the ParentID existed in the list of IDnumbers returned by the first query), but it yells at me because "at most one query can be returned by this subquery". And I don't know how to work around that. My subquery-fu is weak, and I don't really speak SQL.

It's entirely possible that a subquery isn't the way to go here, but I'm not sure how to accomplish what I need to accomplish.

ElJeffe

AAAAAND nevermind.

I threw the subquery in as a table in Design View and did a join that way and abracadabra now it works.

bowen

It's actually a lot more complicated than that because a subquery should only return one row when you do that. You're going to have to use joins and group by to do this. It'd be easier to separate it out if possible instead of doing it in one fell swoop.

Daenris

Well, without creating a new table, a query like this should do it:

SELECT * FROM DATA WHERE IDNumber IN (SELECT IDNumber FROM DATA WHERE LastName LIKE '%smith%') OR ParentID IN (SELECT IDnumber FROM DATA WHERE LastName LIKE '%smith');

Obviously that's for a specific case, so it'd need to be generalized however you currently have it based on the form input rather than a fixed string.