noob needs help with join/union/sub-select

[Justin Koivisto]

OK, out of my element here once again...

I want a query (or something I can use as a record source for a report)
to do the following:

* look in 2 tables (one field each) to find all entered values (without
duplicates)

* count the number of records in both tables that has that value for the
field.

Sounds simple, but I just can't quite get my head around that. I know
how I could do it using PHP and looping through result sets, but I need
this in an Access report. I assume that there is a way to do this
through the use of joins, sub-selects and unions.

Any help is greatly appreciated!

--
Justin Koivisto - sp**@koivi.com
PHP POSTERS: Please use comp.lang.php for PHP related questions,
alt.php* groups are not recommended.

[James Fortune]

Justin Koivisto <sp**@koivi.com> wrote in message news:<DE*****************@news7.onvoy.net>...

OK, out of my element here once again...

I want a query (or something I can use as a record source for a report)
to do the following:

* look in 2 tables (one field each) to find all entered values (without
duplicates)

* count the number of records in both tables that has that value for the
field.

Sounds simple, but I just can't quite get my head around that. I know
how I could do it using PHP and looping through result sets, but I need
this in an Access report. I assume that there is a way to do this
through the use of joins, sub-selects and unions.

Any help is greatly appreciated!

Access doesn't like Union Queries as subqueries so you need to get
'all entered values sans duplicates' separately:

qry1:
SELECT tbl1.theField FROM tbl1 UNION SELECT tbl2.theField FROM tbl2;

Then:
SELECT qry1.theField, (SELECT Count(tbl1.TheField) FROM tbl1 WHERE
tbl1.theField = qry1.theField) AS Table1Count, (SELECT
Count(tbl2.theField) FROM tbl2 WHERE tbl2.theField = qry1.theField) AS
Table2Count
FROM qry1;

James A. Fortune

[Justin Koivisto]

James Fortune wrote:

Justin Koivisto <sp**@koivi.com> wrote in message news:<DE*****************@news7.onvoy.net>...

OK, out of my element here once again...

I want a query (or something I can use as a record source for a report)
to do the following:

* look in 2 tables (one field each) to find all entered values (without
duplicates)

* count the number of records in both tables that has that value for the
field.

Sounds simple, but I just can't quite get my head around that. I know
how I could do it using PHP and looping through result sets, but I need
this in an Access report. I assume that there is a way to do this
through the use of joins, sub-selects and unions.

Any help is greatly appreciated!

Access doesn't like Union Queries as subqueries so you need to get
'all entered values sans duplicates' separately:

qry1:
SELECT tbl1.theField FROM tbl1 UNION SELECT tbl2.theField FROM tbl2;

Then:
SELECT qry1.theField, (SELECT Count(tbl1.TheField) FROM tbl1 WHERE
tbl1.theField = qry1.theField) AS Table1Count, (SELECT
Count(tbl2.theField) FROM tbl2 WHERE tbl2.theField = qry1.theField) AS
Table2Count
FROM qry1;

Thanks, I'll try it out and see what comes of it.

--
Justin Koivisto - sp**@koivi.com
PHP POSTERS: Please use comp.lang.php for PHP related questions,
alt.php* groups are not recommended.

[Justin Koivisto]

James Fortune wrote:

Justin Koivisto <sp**@koivi.com> wrote in message news:<DE*****************@news7.onvoy.net>...

OK, out of my element here once again...

I want a query (or something I can use as a record source for a report)
to do the following:

* look in 2 tables (one field each) to find all entered values (without
duplicates)

* count the number of records in both tables that has that value for the
field.

Sounds simple, but I just can't quite get my head around that. I know
how I could do it using PHP and looping through result sets, but I need
this in an Access report. I assume that there is a way to do this
through the use of joins, sub-selects and unions.

Any help is greatly appreciated!

Access doesn't like Union Queries as subqueries so you need to get
'all entered values sans duplicates' separately:

qry1:
SELECT tbl1.theField FROM tbl1 UNION SELECT tbl2.theField FROM tbl2;

Then:
SELECT qry1.theField, (SELECT Count(tbl1.TheField) FROM tbl1 WHERE
tbl1.theField = qry1.theField) AS Table1Count, (SELECT
Count(tbl2.theField) FROM tbl2 WHERE tbl2.theField = qry1.theField) AS
Table2Count
FROM qry1;

That worked great with a slight modification:

SELECT qry1.theField, (SELECT Count(tbl1.TheField) FROM tbl1 WHERE
tbl1.theField = qry1.theField) + (SELECT Count(tbl2.theField) FROM tbl2
WHERE tbl2.theField = qry1.theField) AS Table2Count
FROM qry1;

One more question: Is there a way to "ORDER BY Table2Count DESC LIMIT 30" ?

--
Justin Koivisto - sp**@koivi.com
PHP POSTERS: Please use comp.lang.php for PHP related questions,
alt.php* groups are not recommended.

[James Fortune]

Justin Koivisto <sp**@koivi.com> wrote in message news:<kW*****************@news7.onvoy.net>...

James Fortune wrote:

Justin Koivisto <sp**@koivi.com> wrote in message news:<DE*****************@news7.onvoy.net>...

OK, out of my element here once again...

I want a query (or something I can use as a record source for a report)
to do the following:

* look in 2 tables (one field each) to find all entered values (without
duplicates)

* count the number of records in both tables that has that value for the
field.

Sounds simple, but I just can't quite get my head around that. I know
how I could do it using PHP and looping through result sets, but I need
this in an Access report. I assume that there is a way to do this
through the use of joins, sub-selects and unions.

Any help is greatly appreciated!

Access doesn't like Union Queries as subqueries so you need to get
'all entered values sans duplicates' separately:

qry1:
SELECT tbl1.theField FROM tbl1 UNION SELECT tbl2.theField FROM tbl2;

Then:
SELECT qry1.theField, (SELECT Count(tbl1.TheField) FROM tbl1 WHERE
tbl1.theField = qry1.theField) AS Table1Count, (SELECT
Count(tbl2.theField) FROM tbl2 WHERE tbl2.theField = qry1.theField) AS
Table2Count
FROM qry1;

That worked great with a slight modification:

SELECT qry1.theField, (SELECT Count(tbl1.TheField) FROM tbl1 WHERE
tbl1.theField = qry1.theField) + (SELECT Count(tbl2.theField) FROM tbl2
WHERE tbl2.theField = qry1.theField) AS Table2Count
FROM qry1;

One more question: Is there a way to "ORDER BY Table2Count DESC LIMIT 30" ?

The non-elegant answer is to change qry2 into a Make Table query then
create qry3:

SELECT TOP 30 tblMade.theField, tblMade.Table2Count FROM tblMade ORDER
BY tblMade.Table2Count DESC;

I don't know if it's possible to adapt qry2 to do something like this.
I haven't had to use TOP much. I might try it again later.

James A. Fortune