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... :(I'd use:SELECT ownerID
FROM cars
GROUP BY ownerID
HAVING 1 < Count(*);

-PatP|||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!|||Picky, picky, picky! Ok, at least the logic is good. Now let's try: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|||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!|||No problemo! Always glad to "stir the pot" any way I can.

-PatPsql

List Database ownership

Is there a way to list the owners of all databases? Right clicking in Ent
Manager and selecting properites is TOO time consuming!
select name,suser_sname(sid) as 'owner'
from master.dbo.sysdatabases
However, if databases are restored/attached you might not get a match for
suser_sname(sid)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jay Griffin" <JayGriffin@.discussions.microsoft.com> wrote in message
news:EC0435CD-9517-47E2-89F6-C98435AA815E@.microsoft.com...
> Is there a way to list the owners of all databases? Right clicking in Ent
> Manager and selecting properites is TOO time consuming!
|||Thank you Jasper!
"Jasper Smith" wrote:

> select name,suser_sname(sid) as 'owner'
> from master.dbo.sysdatabases
> However, if databases are restored/attached you might not get a match for
> suser_sname(sid)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Jay Griffin" <JayGriffin@.discussions.microsoft.com> wrote in message
> news:EC0435CD-9517-47E2-89F6-C98435AA815E@.microsoft.com...
>
>

List Database ownership

Is there a way to list the owners of all databases? Right clicking in Ent
Manager and selecting properites is TOO time consuming!select name,suser_sname(sid) as 'owner'
from master.dbo.sysdatabases
However, if databases are restored/attached you might not get a match for
suser_sname(sid)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jay Griffin" <JayGriffin@.discussions.microsoft.com> wrote in message
news:EC0435CD-9517-47E2-89F6-C98435AA815E@.microsoft.com...
> Is there a way to list the owners of all databases? Right clicking in Ent
> Manager and selecting properites is TOO time consuming!|||Thank you Jasper!
"Jasper Smith" wrote:

> select name,suser_sname(sid) as 'owner'
> from master.dbo.sysdatabases
> However, if databases are restored/attached you might not get a match for
> suser_sname(sid)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Jay Griffin" <JayGriffin@.discussions.microsoft.com> wrote in message
> news:EC0435CD-9517-47E2-89F6-C98435AA815E@.microsoft.com...
>
>

List Database ownership

Is there a way to list the owners of all databases? Right clicking in Ent
Manager and selecting properites is TOO time consuming!select name,suser_sname(sid) as 'owner'
from master.dbo.sysdatabases
However, if databases are restored/attached you might not get a match for
suser_sname(sid)
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jay Griffin" <JayGriffin@.discussions.microsoft.com> wrote in message
news:EC0435CD-9517-47E2-89F6-C98435AA815E@.microsoft.com...
> Is there a way to list the owners of all databases? Right clicking in Ent
> Manager and selecting properites is TOO time consuming!|||Thank you Jasper!
"Jasper Smith" wrote:
> select name,suser_sname(sid) as 'owner'
> from master.dbo.sysdatabases
> However, if databases are restored/attached you might not get a match for
> suser_sname(sid)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Jay Griffin" <JayGriffin@.discussions.microsoft.com> wrote in message
> news:EC0435CD-9517-47E2-89F6-C98435AA815E@.microsoft.com...
> > Is there a way to list the owners of all databases? Right clicking in Ent
> > Manager and selecting properites is TOO time consuming!
>
>