listing only owners with more than one car query help

TheSpike · #1 (**permalink**) 04-05-04, 15:55

Hi, here is the problem I have to resolve, I'll try to be very clear about the situation (using MS Access):

- I have 2 tables (well, I have more but only two are relevant to the query) one table stores owner information (owner #, name, surname, phone number...) and the other stores car info (car #, brand, type, color and owner #)

- I want to make a query that gives me the owner info but only for owners that have MORE than one car (ie 2 or more). I tried many approaches but without any success, so far..

- It doesn't matter if I get the same user information 2,3,4 times, I will take the query and build a report with it, and I'll group the cars by users.

Any ideas for me? This really bothers me because I really don't have a clue and I'm sure I missed something easy...

Pat Phelan · #2 (**permalink**) 04-05-04, 16:05

I'd use:

PHP Code:


			
SELECT ownerID

FROM cars

GROUP BY ownerID

HAVING 1 < Count(*);

-PatP

TheSpike · #3 (**permalink**) 04-05-04, 16:29

Thanks, Pat, this correctly lists people with more than one car

But if I want to da a report with the car info for each car that each owner of more than 1 car (err.. I wanna do a report with the user info + the car info in a report, grouped by the user), I must have the info of each car also.. Your query works like I want, but only list the users and I can't add car info on one line for each car.. Well, I know that sounded pretty bad.. Any idea?

Thanks for the quick answer there, Pat!

Pat Phelan · #4 (**permalink**) 04-05-04, 16:35

Picky, picky, picky! Ok, at least the logic is good. Now let's try:

PHP Code:


			
SELECT *

   FROM cars AS a

   WHERE  1 < (SELECT Count(*)

      FROM cars AS z

      WHERE  z.ownerID = a.ownerID);

...and see if that am more gooder yet even. (How many ways can I butcher the English language... Is there a finite limit?)

-PatP

TheSpike · #5 (**permalink**) 04-05-04, 16:44

All I want to say is: "Thanks alot, this is exactly what I wanted.. I'll be trying to add informations about the owners from the owner's table now.. But this is exactly what I meant!

Thanks for this, Pat, you are a real pal!

Pat Phelan · #6 (**permalink**) 04-05-04, 16:56

No problemo! Always glad to "stir the pot" any way I can.

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech