SQL syntax help, I think (Access 97/no sr)

Topic

New Reply

I know that two separate queries will do the trick: 1st one is your select distinct. 2nd query is based on 1st query and does the count. If anyone knows if this is possible with only one query, please tell us how.

Reply | Quote

Replies

Ok, I’ve definitely done my homework on this one, but I’m still not getting it.

For giggles, I’ve checked;
SQLCOURSE.COM
SQLCOURSE2.COM
ACCESS DATABASE DESIGN & PROGRAMMING BY O’REILLY
ACCESS 97 UNLEASED BY SAMS
ACCESS 97 FOR WINDOWS FOR DUMMIES BY JOHN KAUFELD
BLAH, BLAH, BLAH, ETC…

I’m trying to write a very simple query with SQL. I think my problem is syntax, not content, so hopefully someone can just point right to it and say “There’s your problem…”

Let’s say my data looks like this (my magazine collection );

Taunton

Taunton

Taunton

Better Homes and Gardens

Better Homes and Gardens

August Home

August Home

August Home

I simply want to count the distinct values. I can do a SQL statement for distinct values; SELECT DISTINCT [main table].Company
FROM [main table];
That would return;

Taunton

Better Homes and Gardens

August Home

Instead, I want it to return;

3

All the reference that I’ve checked suggest that SELECT count([main table].Company)
FROM [main table];
will do the trick, but that statement is returning the number of all values, or

8

I know it’s simple, but it still eludes me, even after all of this searching… Can anyone help?

Reply | Quote

You don’t need the DISTINCT, you need a group by, or totals, query. Try something like this:

SELECT Company, Count([Company]) As CompanyCount
FROM [main table] GROUP BY [Main table].Company;

That will give you a list of Company values and the count for each listed Company.

Reply | Quote

Hi Charlotte,

Thank you for your reply, but that’s not quite what I was looking for. I don’t need a count of how many times each company is represented.

I need a count of unique values. In my first post above, there are 3 distinct companies. I need the SQL to return 3, not Taunton 3, Better Homes and Gardens 2, etc…

Reply | Quote

Hi,
Try:
SELECT count(sqryGroup.Company) as CoCount FROM (SELECT DISTINCT [main table].Company from [main table]) As sqryGroup
Hope that helps.

Reply | Quote

I didn’t think you could have a subquery inside a main query in Access97.
I have tried it and it came back with an error “Syntax error in From clause”.
When I clicked OK on the error dialogue box it highlighted the SELECT after the left hand bracket.

Pat

Reply | Quote

It’s legal, but it’s tricky to make work because the quey engine insists on changing the punctuation in SQL view. Don’t try to look at the SQL in A97. Create a new query based on the Orders table from the Northwind database, and create this expression as the ony field:

CoCount: Count(sqryGroup.CustomerID)

The query should run (at least it does in SR-2), but if you look at it in SQL view, it will probably give you the syntax error you described.

Reply | Quote

But how does it know what sQryGroup is, if you have not created a query called sQryGroup.
I am obviously missing something!

I went into a new query based upon a table of mine (in a table called [MidWk Hdr Det] theres a date field called DateofVenue) and created just this expression as you described “Cocount: Count(sQryGroup.DateofVenue)” and I get a dialog box requesting entry of parameter sQryGroup.DateofVenue

Pat

Reply | Quote

Sorry, I didn’t mean to confuse you. You can actually paste Rory’s SQL into a query in A2k, save it, and back save it to 97 and it will run … as long as you don’t open it in SQL view.

Otherwise, you have to create a query from the subquery in Rory’s code and use that as the “table” for your count. Since you were seeing brackets rather than parens, I assumed you had managed to save the query but were then having trouble getting it out of SQL view. I actually figured out a way to make that work years ago, but I’ve forgotten how. It had to do with tweaking the punctuation around the FROM subquery.

Reply | Quote

Hi Charlotte, Rory, et al.,

I’ve tried the methods suggested, and I’m still confused.

I built a query in the Northwind database based on Rory’s subquery. But then I wasn’t able to see how I could relate that to my query in my database.

Then I went into my database and created a new query with Rory’s subquery code. Then I tried creating a second query with the full code in his post, but I got the “…enclose the subquery in paren…” error, and I’m still not sure where to proceed.

I’m very new to all of this. Can someone spell it out for me step by step?

Reply | Quote

Create the “subquery” in your database and save it as a query. Then create a new query and in the Add Tables dialog, click on the queries tab. Your saved “subquery” should be in that list and you can add it to your new query just the same way you would a table. Does that help?

Reply | Quote

I have attached a WinZip file containing an example based on your original question.

It contains one table (Main Table) and two queries (qryUniqueCompanies and qryCountCompanies).

Note: Although it might be possible to do it in one query in Access 2000 and then convert it to 97, I don’t think that it is possible to create a single query in Acces 97.

Reply | Quote

I don’t see why you said my query didn’t work…

You can have
1 DOG
2 DOG
3 CAT
4 CAT
5 Cat
The number needed to be returned would be 2, that exactly why my query would do.

Reply | Quote

To jflores:
Perhaps there is a difference between our versions of Access. On my version, the query you suggested returns the number of records in the table, not the number of unique values.

Reply | Quote

HansV, Charlotte, Rory, and all,

Thank you very much, we finally got it.

I simply ended up copying HansV’s query’s from attachment, and pasting them in. Returns the number I needed.

Sorry I couldn’t use your SQL Rory, but I couldn’t get the subquery/query thing to work. Charlotte I followed your suggestion by making my subquery first, then adding it to my main query, but I just couldn’t get the two to relate. I’ll have to practice that some more.

Thanks again all!

Reply | Quote

SELECT DISTINCT COUNT(fieldname) AS howmany
FROM tablename

it’s that simple

Reply | Quote

Sorry, but that doesn’t work. It will return a count of every record in the table. What you’re telling the query engine is to Count the fieldname in tablename and return the unique instance of that count.

Reply | Quote

Well it works in Acess 2000 and XP and SQL 7.0 and 2000…….. try it

Reply | Quote

I don’t have XP on my machine, but I had already tested it in Access 2000 to make sure I hadn’t missed something before I posted. All that query will give you is a count of the records in the Access table. If you are using it in SQL Server, at least in SQL Server 2000, it gives exactly the same result from the query analyzer–you get the count of records in the table.

I just realized you might be getting your syntax confused with the SQL supported syntax that *does* work in SQL Server but is not the same thing at all:

SELECT COUNT(DISTINCT fieldname) FROM tablname

Access 97 and 2000 do NOT support COUNT DISTINCT, although SQL Server does.

Reply | Quote