Combining 2 queries into 1

[M8KWR]

I have got 2 queries which i would prefer to combine into 1, is this possible.

My first query is

SELECT CONTACT1.COMPANY
FROM CONTACT1
GROUP BY CONTACT1.COMPANY;

then my second uses the first query, which i called Query47

SELECT Count(Query47.COMPANY) AS CountOfCOMPANY
FROM Query47;


Many thanks in advance

 

[Remou]

How about:

[tt]SELECT Count(COMPANY) AS CountOfCOMPANY
FROM CONTACT1;[/tt]

 

[M8KWR]

It works but not want i required.

I will try and word what i am tryin to get; i have a table with 1000 records, but i need to get the number of unique values from a field within this 1000 records, so that 1 field may only have 400 unique vales, and it is this 400 number that i am trying to get....

So i group together this 1 field, to a query to display 400 lines, but then create another query to just get a count of that 400

But is this possible in just 1 query???

 

[Remou]

Or

[tt]SELECT Count(CustId) AS CountOfCustId
FROM (SELECT CustID FROM Orders2 GROUP BY CustID) A;[/tt]

 

[M8KWR]

i changed the code to

SELECT Count(COMPANY) AS CountOfCustId
FROM (SELECT COMPANY FROM CONTACT1 GROUP BY COMPANY) A;

But it is saying there is a "syntax error in FROM clause"

any ideas, many thanks for you time in this matter...

 

[PHV]

Which version of access ?
The inline view syntax (FROM (SELECT ...)) needs ac2k or above (in fact JetSQL 4.0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[M8KWR]

so using A97 would cause an issue....

to be honest what i am trying to do is do my queries in access and then transfer them to sql.... if that changes things....

many thanks

 

[PHV]

then transfer them to sql
Which sql ?

 

[PHV]

In the REAL sql world, the syntax is:
SELECT COUNT(DISTINCT Company) FROM Contact1

 

[M8KWR]

just what i required, just need to add "as Company_Count"; worked a treat

many thanks for your time