Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    Montreal, CANADA
    Posts
    3

    Unanswered: listing only owners with more than one car query help

    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...
    Last edited by TheSpike; 04-05-04 at 17:01.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    PHP Code:
    SELECT ownerID
    FROM cars
    GROUP BY ownerID
    HAVING 1 
    Count(*); 
    -PatP

  3. #3
    Join Date
    Apr 2004
    Location
    Montreal, CANADA
    Posts
    3
    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!

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  5. #5
    Join Date
    Apr 2004
    Location
    Montreal, CANADA
    Posts
    3
    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!

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No problemo! Always glad to "stir the pot" any way I can.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •