473,228 Members | 1,868 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,228 software developers and data experts.

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 1701
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: administrata | last post by:
Hello! :) I've reading 'Python Programmin for the Absolute beginner'. I got questions which is... print "\t - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - " rock = """ Igneous ...
6
by: Igorati | last post by:
Ok, this is what I have so far: #This program will ask for a user to imput numbers. The numbers will then be calculated #to find the statistical mean, mode, and median. Finallly the user will be...
0
by: Len Coleson | last post by:
I am at wits end with this. I have a transaction report that has a union query as a record source that asks date parameters from an input form. The sub report is based on a variation of the same...
73
by: Sean Dolan | last post by:
typedef struct ntt { int type; union { int i; char* s; }; }nt; nt n; n.i = 0;
2
by: Holger | last post by:
I needed a tool for extracting patches from CVS based on the log messages. I.e. we mark our fixes and features with a "Bugdb XYZ" And sometimes you need to move a fix/feature to another branch or...
7
by: jason.langdale | last post by:
I have 3 tables I want to use in a view. Table A has field 1,2,3,4,5 and table B has field 1,2,3,4,5. I want to do a union on these. (I have done so successfully if I stop here) I also want to join...
4
by: jobs | last post by:
Three noob questions. Please help. 1. Is there a way to have arguments on subroutine that are optional? 2. Say I need to convert datetime to string..in this format : 2006-09-07...
2
by: athos | last post by:
OS: Windows 2000/XP (needs to run on 2 different machines) Language: Python 2.5 Programmer Level: Pathetically new to Python Goal: Using code I've altered for my needs, I'm attempting to create...
3
by: GKRAFT | last post by:
I'm trying to create a function which would look like this when running: >> union(,) >> union(,) I have written the following code: def union(l1, l2): finalList = i = 0
1
KevinADC
by: KevinADC | last post by:
Note: You may skip to the end of the article if all you want is the perl code. Introduction Many websites have a form or a link you can use to download a file. You click a form button or click...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.