I don't understand what my query is doing

[MikeB]

If I run this query:

SELECT Count([Bid].[key]) AS Bids
FROM Bid;

I get a query with one field in one row telling me how many records
there are in that table. So I'd like a query telling me how many fields
there are in each of the tables in my data base.

Then I think the query should look like this:

SELECT Count([Bid].[key]) AS Bids, Count([Group].[key]) AS Groups,
Count([Listing].[key]) AS Listings, Count([Loan].[key]) AS Loans,
Count([Member].[key]) AS Members
FROM Bid, [Group], Listing, Loan, Member;

However, that query never completes. Literally not overnight. Even if I
try it on only two of the tables, I just never get a response. Am I
doing something bad like a cartesian join?

I've tried this, but it simply crashes my Excel XP (2002?) without so
much as a message:

SELECT Count([Bid].[key]) AS Bids;

or

SELECT Count([Bid].[key]) AS Bids, Count([Group].[key]) AS Groups,
Count([Listing].[key]) AS Listings, Count([Loan].[key]) AS Loans,
Count([Member].[key]) AS Members;

Please enlighten me.

 

[Roger Carlson]

You are indeed creating a cartesian join. Try this instead:

SELECT "Bids" as Bids, Count(*) AS CountOf FROM Bids
Union
SELECT "Listings" as Listings, Count(*) AS CountOf FROM Listing
Union
SELECT "Loans" as Loans, Count(*) AS CountOf FROM Loan
Union
SELECT "Members" as Members, Count(*) AS CountOf FROM Member

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[MikeB]

Thanks! Works a charm - and MUCH faster than my attempt! j/k

You are indeed creating a cartesian join. Try this instead:

SELECT "Bids" as Bids, Count(*) AS CountOf FROM Bids
Union
SELECT "Listings" as Listings, Count(*) AS CountOf FROM Listing
Union
SELECT "Loans" as Loans, Count(*) AS CountOf FROM Loan
Union
SELECT "Members" as Members, Count(*) AS CountOf FROM Member

--
--Roger Carlson
MS Access MVP
Access Database Samples:www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

If I run this query:

SELECT Count([Bid].[key]) AS Bids
FROM Bid;

I get a query with one field in one row telling me how many records
there are in that table. So I'd like a query telling me how many fields
there are in each of the tables in my data base.

Then I think the query should look like this:

SELECT Count([Bid].[key]) AS Bids, Count([Group].[key]) AS Groups,
Count([Listing].[key]) AS Listings, Count([Loan].[key]) AS Loans,
Count([Member].[key]) AS Members
FROM Bid, [Group], Listing, Loan, Member;

However, that query never completes. Literally not overnight. Even if I
try it on only two of the tables, I just never get a response. Am I
doing something bad like a cartesian join?

I've tried this, but it simply crashes my Excel XP (2002?) without so
much as a message:

SELECT Count([Bid].[key]) AS Bids;

or

SELECT Count([Bid].[key]) AS Bids, Count([Group].[key]) AS Groups,
Count([Listing].[key]) AS Listings, Count([Loan].[key]) AS Loans,
Count([Member].[key]) AS Members;

Please enlighten me.- Hide quoted text -- Show quoted text -