468,533 Members | 1,892 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,533 developers. It's quick & easy.

noob needs help with join/union/sub-select

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.
Nov 13 '05 #1
4 1529
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
Nov 13 '05 #2
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.
Nov 13 '05 #3
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.
Nov 13 '05 #4
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
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by administrata | last post: by
6 posts views Thread by Igorati | last post: by
73 posts views Thread by Sean Dolan | last post: by
2 posts views Thread by Holger | last post: by
7 posts views Thread by jason.langdale | last post: by
4 posts views Thread by jobs | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.