Self Join and Totals Query ?

[fjdixon]

I have what should be a trivial question:

Table has data below
Name1,Name2
Name1 or Name2 could be null but not both

I would like to get result recordset of
Name, count1, count2

Where
Name is union of all name1 and name2 (distinct)
Count1 is count(Not Null) Group by Name1
Count2 is count(Not Null) Group by Name2

Query needs to be 255 chars max (Access db in VB6).

Any help would be appreciated.
Thanks

 

[Tom Ellison]

Dear FD:

My interpretation of your requirements leads me to this:

SELECT Name,
(SELECT Count(Name1)
FROM YourTable T
WHERE T.Name1 = U.Name)
AS Count1,
(SELECT Count(Name2)
FROM YourTable T
WHERE T.Name2 = U.Name)
AS Count2
FROM
(SELECT Name1 AS Name
FROM YourTable
UNION
SELECT Name2 AS Name
FROM YourTable) U
ORDER BY Name

Now that's more than 255 characters, formatted for nice readability.

SELECT Name, (SELECT Count(Name1) FROM YourTable T WHERE T.Name1 = U.Name)
Count1, (SELECT Count(Name2) FROM YourTable T WHERE T.Name2 = U.Name) Count2
FROM SELECT Name1 AS Name FROM YourTable UNION SELECT Name2 AS Name FROM
YourTable) U ORDER BY Name

Probably still too long, may depend on the length of your table name. So,
cut the alias lengths back to C1 and C2 instead of Count1 and Count2. Make
the UNION a separate saved query (can you?)

Primary message: If you have serious programming work to do, don't pick a
language that doesn't support serious programming work.

Secondary message: If you have to work under such crippling circumstances,
use one letter column and table names. Violate all the rules of sanity to
fit within the limitations imposed by a poor language/platform selection.

Or can you just write a decent query in Access and referenct the query from
VB6?

Tom Ellison

 

[fjdixon]

Dear Tom,
Thanks, with a little aliasing I have the len below 255 and it works
well.

I am actually using it to query a recordset that was a result of a
previous query (SBS Query A Recordset). For this reason I can not
predefine query in access.

Thanks again for your assistance and I shall study the syntax provided
for future use. Thanks to all for an excellent site

fd