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. 4  1723 
Justin Koivisto <sp**@koivi.com > wrote in message news:<DE******* **********@news 7.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.TheF ield) FROM tbl1 WHERE
tbl1.theField = qry1.theField) AS Table1Count, (SELECT
Count(tbl2.theF ield) FROM tbl2 WHERE tbl2.theField = qry1.theField) AS
Table2Count
FROM qry1;
James A. Fortune
James Fortune wrote: Justin Koivisto <sp**@koivi.com > wrote in message news:<DE******* **********@news 7.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.TheF ield) FROM tbl1 WHERE
tbl1.theField = qry1.theField) AS Table1Count, (SELECT
Count(tbl2.theF ield) 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.
James Fortune wrote: Justin Koivisto <sp**@koivi.com > wrote in message news:<DE******* **********@news 7.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.TheF ield) FROM tbl1 WHERE
tbl1.theField = qry1.theField) AS Table1Count, (SELECT
Count(tbl2.theF ield) FROM tbl2 WHERE tbl2.theField = qry1.theField) AS
Table2Count
FROM qry1;
That worked great with a slight modification:
SELECT qry1.theField, (SELECT Count(tbl1.TheF ield) FROM tbl1 WHERE
tbl1.theField = qry1.theField) + (SELECT Count(tbl2.theF ield) 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.
Justin Koivisto <sp**@koivi.com > wrote in message news:<kW******* **********@news 7.onvoy.net>... James Fortune wrote:
Justin Koivisto <sp**@koivi.com > wrote in message news:<DE******* **********@news 7.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.TheF ield) FROM tbl1 WHERE
tbl1.theField = qry1.theField) AS Table1Count, (SELECT
Count(tbl2.theF ield) FROM tbl2 WHERE tbl2.theField = qry1.theField) AS
Table2Count
FROM qry1;
That worked great with a slight modification:
SELECT qry1.theField, (SELECT Count(tbl1.TheF ield) FROM tbl1 WHERE
tbl1.theField = qry1.theField) + (SELECT Count(tbl2.theF ield) 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.theFiel d, tblMade.Table2C ount FROM tblMade ORDER
BY tblMade.Table2C ount 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: administrata |
last post by:
Hello! :)
I've reading 'Python Programmin for the Absolute beginner'.
I got questions which is...
print "\t - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - "
rock = """
Igneous Sedimentary Metamorphic
Lava Grains Marble
|
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
asked
#if he would like to print out the answers.
list =
a = 1
print 'Enter numbers to add to the list. (Enter 0 to quit.)'
|
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 union but
only has the "startdate" portion and it sums all records prior to
whatever date I enter. Both reports have the same grouping/Sorting
(MatNumber)
I have a text box in the "MatNumber" header that picks up the
information from the sub...
|
by: Sean Dolan |
last post by:
typedef struct ntt {
int type;
union {
int i;
char* s;
};
}nt;
nt n;
n.i = 0;
|
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 maybe
you just want to inspect exactly what changes were related to a
specific bugdb issue.
Now I've searched hi and low for this and I now it's out there
somewhere bleeding obvious - can't imagine I'm the first to have this
thought. I just...
| | |
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 table C which
has field 1,6,7,8,9. I would like to join on field 1 and bring in the
other fields. I can join table C to A or B. I can union table A and B
but I do not know how to both union A and B then join C. Can someone
please help me? Thanks...
|
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 23:00:00.000
|
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 a drive crawler that will search for certain files (*.svx;*.mdb) and on the fly and check to see if there is a corresponding .xml file. (e.g. Find all "*.svx". If filename.svx, then is there a filename.svx.xml?). If there is a corresponding file,...
|
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
|
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 on a link and after a moment or two a file download dialog box pops-up in your web browser and prompts you for some instructions, such as “open” or “save“. I’m going to show you how to do that using a perl script.
What You Need
Any recent...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
