f



Getting 2 counts() of 2 foreign keys

Wondering if someone here could help me out, I've tried a number of
ways to do this, and while I have managed to get it to work, Im SURE
im not doing the optimal way, and it seems to be causing problems with
my form which is throwing up '#Name? ' in some fields(at seemingly
random times...)

Basicly I have 3 tables, one being the 'top' table, with only 2
fields, a primary key (ID) and a second field Name, this table is
called 'tblDests'

The two other tables are called tblAreas and tblRates, and both have a
field called DestID which is defined as a foreign key to the tblDests
ID field. Both tables have a many to one relationship with the
tblDests table (ie multiple entrys  in the two 'child' tables)

What I want to do is get a result set with the total number of records
in each child tables grouped by Name from the tblDests table, so my
result set would look like:

tblDests.Name | CountOfAreas | CountOfRates
--------------------------------------------
India         |     20       |     13
England       |     12       |     13

The way I'm doing it now involes first a Union query  like 
SELECT Name,Area, <fake rates> .... UNION SELECT Name ,<fake area>,
Rates
Then i do a totals query on top of that and get the First() from Area
and Last() from Rates. It works how i want, but like I said, there
surely must be a better way to do this??

Its worth noting that both child tables have no 'ID' field, their
primary key is a multiple field index so I use 'destid' in both cases
(the foreign key)

Thanks for any help
Jim
0
omg7742 (2)
6/20/2004 9:18:00 AM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

2 Replies
340 Views

Similar Articles

[PageSpeed] 26

Hi Jim

Create 3 queries, the first 2 create the count for the tblAreas and the
tblRates and the third query joins the first 2.

qryAreaCount
SELECT tblDest.DestID, tblDest.Destination, Count(tblArea.Area) AS AreaCount
FROM tblDest LEFT JOIN tblArea ON tblDest.DestID = tblArea.DestID
GROUP BY tblDest.DestID, tblDest.Destination;

qryRateCount
SELECT tblDest.DestID, tblDest.Destination, Count(tblRate.Rate) AS RateCount
FROM tblDest LEFT JOIN tblRate ON tblDest.DestID = tblRate.DestID
GROUP BY tblDest.DestID, tblDest.Destination;

Join the first 2 queries to the tblDest to create the third query
qryAreaAndRateCount
SELECT tblDest.Destination, qryAreaCount.AreaCount, qryRateCount.RateCount
FROM (tblDest INNER JOIN qryAreaCount ON tblDest.DestID =
qryAreaCount.DestID) INNER JOIN qryRateCount ON tblDest.DestID =
qryRateCount.DestID;

Stewart


"JimJim" <omg@wired.ie> wrote in message
news:da6f2902.0406200118.173a4685@posting.google.com...
> Wondering if someone here could help me out, I've tried a number of
> ways to do this, and while I have managed to get it to work, Im SURE
> im not doing the optimal way, and it seems to be causing problems with
> my form which is throwing up '#Name? ' in some fields(at seemingly
> random times...)
>
> Basicly I have 3 tables, one being the 'top' table, with only 2
> fields, a primary key (ID) and a second field Name, this table is
> called 'tblDests'
>
> The two other tables are called tblAreas and tblRates, and both have a
> field called DestID which is defined as a foreign key to the tblDests
> ID field. Both tables have a many to one relationship with the
> tblDests table (ie multiple entrys  in the two 'child' tables)
>
> What I want to do is get a result set with the total number of records
> in each child tables grouped by Name from the tblDests table, so my
> result set would look like:
>
> tblDests.Name | CountOfAreas | CountOfRates
> --------------------------------------------
> India         |     20       |     13
> England       |     12       |     13
>
> The way I'm doing it now involes first a Union query  like
> SELECT Name,Area, <fake rates> .... UNION SELECT Name ,<fake area>,
> Rates
> Then i do a totals query on top of that and get the First() from Area
> and Last() from Rates. It works how i want, but like I said, there
> surely must be a better way to do this??
>
> Its worth noting that both child tables have no 'ID' field, their
> primary key is a multiple field index so I use 'destid' in both cases
> (the foreign key)
>
> Thanks for any help
> Jim


0
sagasu4788 (35)
6/20/2004 11:08:19 AM
Thanks very much that works perfectly 

"Stewart Allen" <sagasu@NOT.wave.THIS.co.nz> wrote in message news:<cb3r7f$u53$1@news.wave.co.nz>...
> Hi Jim
> 
> Create 3 queries, the first 2 create the count for the tblAreas and the
> tblRates and the third query joins the first 2.
>
0
omg7742 (2)
6/20/2004 6:16:01 PM
Reply:

Similar Artilces:

2 * 2 * 2 * 2 or pow(2, 4)?
Hi , I hope this isn't too trivial to ask, but I'm evaluating a polynomial that involve lots of terms like x^2y^2z^3, and although I can use the power function pow(a, b), I wonder if just writing x*x*y*y*z*z*z for example wouldn't be more efficient than pow(x, 2)*pow(y, 2)*pow(z,3). Or is it all the same? Thanks, - Olumide Olumide wrote: > I hope this isn't too trivial to ask, but I'm evaluating a polynomial > that involve lots of terms like x^2y^2z^3, and although I can use the > power function pow(a, b), I wonder if just writing x*x*y*y*z*z*z for > exam...

linspace(2,2,2) returns [2 2] ??
May be the help for linspace should change? It says: "LINSPACE(X1, X2, N) generates N points between X1 and X2." Notice, it says BETWEEN. But when I ask for 2 points, between 2 and 2: ---------------- EDU>> linspace(2,2,2) ans = 2 2 --------------- May be mathematically this is ok? i.e. there are infinite points between the point and itself? (on the real line), but from programming point of view, it is confusing. no? --Nasser Maybe it should say "generates N points between X1 and X2, inclusive." What did you expect to get back as an answer from issuing linspace(2,2,2)? I didn't understand your statement "there are infinite points between the point and itself?" How do you have an infinite number of points between 2 and 2? I can see an infinite number of points between 2 and something that's not itself, like 2.00000000000000001, but if they are absolutely perfectly identical how are you getting an infinite number of points? On 10/10/2010 11:16 AM, Image Analyst wrote: > Maybe it should say "generates N points between X1 and X2, inclusive." > > What did you expect to get back as an answer from issuing linspace(2,2,2)? > I was just not expecting it to "work". may be an error message. That is not the point. The point is that it did work. > I didn't understand your statement "there are infinite points between the point and it...

Accessing 2 tables having same name but different databases ms-access
Hi, I have an application with MS-Access as backend and JSP/ tomcat as frontend/webserver. For getting better response time(as access is very slow compared to other RDBMS's), I have split the main DB into two DB's on different disks on my server for better performance.I have kept the table names same but changed the DB names. How do I access tables from both the DB's? This is what I have done; I made different connection to the respective DB's i.e. con and con2. When I want to access data, I am using 2 result sets for retriving data from the tables, but getting no result. Here is my code: rs2_1=stmt2_1.executeQuery("select * from Requests where Status='Released' AND LoginName='"+login+"' order by Req_No"); rs=stmt.executeQuery("select * from Requests where Status='Released' AND LoginName='"+login+"' AND Req_No> 5500 order by Req_No"); So, rs & stmt are for latest records and rs2_1 & stmt2_1 correspond the records from older DB. I think, I'm forgetting some small thing but cant get it. Please advice. What do you mean "no result"? Does "next()" return false for both rs2_1 an= d rs? If you're really trying to improve response time, you should probably be ru= nning the two queries in separate threads ... but as you acknowledge, Acces= s may not have as high performance as other RDBMSs. You would also see rel= iability adv...

Accessing a passworded MS Access 2000 database in Crystal Reports #2
Can anybody give me some code that I can put in to my VB6 app to open a passworded Access database in the Crystal Reports 8.5 RDC? I see so many examples - none of which seem to work for me. I am using a native connection in Crystal ( pdbdao.dll). My current code is : Dim Report As New crPrintPreview Dim crTable As CRAXDRT.DatabaseTable Private Sub Form_Load() Set crTable = Report.Database.Tables.Item(1) varpath = CurDir crTable.Location = varpath & "\OrderManager.mdb" CRViewer1.ReportSource = Report CRViewer1.ViewReport End Sub All I receive is the message "Error opening file xxxxxxx etc" and then "Open Database session failed" Appreciate any help! Peter Tyler ...

VIBE 2.2 #2
About: Visual Information Broker Enterprise (VIBE) is an IDE that enables application developers to automate the design and development of infrastructure components for complex distributed cross-platform, cross-language systems applications. You can define messages and generate infrastructure support code for the IBM MQ information bus, Sybase, Microsoft SQL Server, Oracle, and other JDBC compliant databases, XML with transformations that use tools like XSL, and J2EE. It also includes a fully functional Business Rules engine, along with a graphical programming environment. Changes: This relea...

OTM 2.2 #2
Hi all Just installed OTM on a cust pc (Win XP) Got a problem that when using the system terminal to access Meridian Mail, I am unable to exit from mail as the Cntrl ] function is not recognised. Now is this a problem with OTM or with the customers PC? Thanks Solved it. The term type was set to VT100 and not VT220 ...

Getting an error: Attempted to access u(2); index out of bounds because numel(u)=1. Error in ==> treat at 31 if (u(2)>1000) #2
HI, I am using an ode function having three differntial equations in u(1), u(2), (3) using function f=treat(t, u), Now when i use if function as if (u(2)>1000) c=1; else (u(1)<=100) c=0; end i am getting the following error Attempted to access u(2); index out of bounds because numel(u)=1. Error in ==> treat at 31 if (u(2)>1000) Please tell me. thanks "gati " <gatimishrano@yahoo.com> wrote in message news:kfdpbl$ke1$1@newscl01ah.mathworks.com... > HI, I am using an ode function having three differntial equations in u(1), > u(2), (3) using function f=treat(t, u), Now when i use if function as if > (u(2)>1000) c=1; else (u(1)<=100) c=0; end i am getting the following > error Attempted to access u(2); index out of bounds because numel(u)=1. > Error in ==> treat at 31 if (u(2)>1000) Please tell me. thanks My guess is that the initial condition vector you passed into the ODE solver has only 1 element. If you've got three ODEs in three variables, you need a 3-element initial condition vector. -- Steve Lord slord@mathworks.com To contact Technical Support use the Contact Us link on http://www.mathworks.com ...

Does 4/2/2= (4/2)/2 =1 OR = 4/(2/2)=4
I don't have matlab and I am trying to translate some simple matlab code. Does 4/2/2 = (4/2)/2 = 1 or 4/2/2 = 4/(2/2) = 4 On 9/11/2012 9:43 AM, sarah.englander@gmail.com wrote: > I don't have matlab and I am trying to translate some simple matlab code. > > Does 4/2/2 = (4/2)/2 = 1 yes. At a given precedence level (and obviously two of the same operators are the same level) evaluation proceeds from LtoR. ....snip... <http://www.mathworks.com/help/techdoc/matlab_prog/f0-40063.html#f0-38155> -- dpb <none@non.net> wrote in message <k2njh1$jes$1@speranza.aioe.org>... > On 9/11/2012 9:43 AM, sarah.englander@gmail.com wrote: > > I don't have matlab and I am trying to translate some simple matlab code. > > > > Does 4/2/2 = (4/2)/2 = 1 > > yes. At a given precedence level (and obviously two of the same > operators are the same level) evaluation proceeds from LtoR. > > ...snip... > > <http://www.mathworks.com/help/techdoc/matlab_prog/f0-40063.html#f0-38155> > > -- There are a few thing you need to understand: 1. You need to get matlab in order to translate the simple code 2. You should define your parameters as in what is the expected output of your search based on the equations used: Is it : a) 4/2/2 b) (4/2/2) On 9/11/2012 11:13 PM, Salvinder wrote: .. > There are a few thing you need to understand: > 1. ...

Printing 2^1, 2^2, 2^3, 2^4 and 2^5
Hi, When I use a while loop, the output is: 2 4 8 16 32, as desired: <?php $c=1; while($c<=5) { $d = newpow(2, $c); print "$d "; $c++; } function newpow($base, $power) { // statements... } ?> The But when I use a for loop, the output is: 64: <?php for($c=1; $c<=5; $c++); { $d = newpow(2, $c); print "$d "; } function newpow($base, $power) { // statements... } ?> Why is this?? Many thanks, Michael mejpark wrote: > When I use a while loop, the output is: 2 4 8 16 32, as desired: [...] > The But when I use a for loop, the output is: 64...

2 or not 2
is this a question! http://members.iinet.net.au/~moussa/Image029.jpg Should I? the in house auction is tomorrow :-/ Moussa -- The information contained in this post is copyright of the poster, and specifically may not be published in, or used by http://www.jlaforums.com Il Wed, 09 Sep 2009 16:13:08 +0800, Cheers ha scritto: > is this a question! > > http://members.iinet.net.au/~moussa/Image029.jpg > > Should I? the in house auction is tomorrow :-/ > > Moussa I don't know it but it's BIG ! Do you have free space ? How much, do you think, it will cost ? Could be a nice addiction, but let's listen to someone else... On Sep 9, 4:13 am, Cheers <moussa...@hotmail.com> wrote: > http://members.iinet.net.au/~moussa/Image029.jpg Personally, I'm not that fond of Sun equipment, so I haven't paid that much attention to their offerings. My guess is that the rack is a storage array, and it appears to be on the old side, so it's likely that the total storage capacity in the array could be met or exceeded by one large SATA drive (both 1.5TB and 2.0TB 3.5-inch drives are currently available) -- at much lower noise and power levels. Of course, if you already had one of these bad boys and were just looking for an inexpensive source of spare parts, I'd say go for it. Rick Ekblaw Hi Moussa ! > is this a question! > > http://members.iinet.net.au/~moussa/Image029.jp...

Getting access to a 2.0 database
I have an MS Access 2.0 database and a System.mda. And there is an application reading that DB and showing me the data. Now I want to read the data by myself. The user is "Mirko", but how do I find the password/SID? Can I trace the API calls of the application? On 7 Mar 2005 08:23:20 -0800, LspWvxEhvClk@spammotel.com (Martin) wrote: There is no documented way to do that. Ask the administrator for a username/password combination. Other sources may be the original developer, and the person documenting all mission-critical information (and security information certainly qualifies). -Tom. >I have an MS Access 2.0 database and a System.mda. >And there is an application reading that DB and showing me the data. > >Now I want to read the data by myself. The user is "Mirko", but how do >I find the password/SID? Can I trace the API calls of the application? Its a very old software on CD... JMS <LspWvxEhvClk@spammotel.com> wrote > Its a very old software on CD... _What_ is a "very old software on CD"? Its a very old software CD that contains that access database.... LspWvxEhvClk@spammotel.com wrote: > Its a very old software CD that contains that access database.... > ISTR some Oil & Gas supplier databases coming like this, aren't you suppose to pay a subscription to the vendor to gain access to _their_ data? If you do you'll get a very new software on a CD. -- This sig left intentionally b...

2 + 2 =
2 + 2 = 4. Period. The above was posted as a test. I am seeing if people in csma will start to debate the accuracy of my claims. After all, in base 3, there is no symbol "4". I am sure others can come up with ways to show I am wrong. Then, when I explain how 2 + 2 really is equal to 4, even in other bases or whatever other silliness is presented, I will be told that I am changing my claim. Someone will even go so far as to say that since I admit there is no "4" in base 3, that my argument has been refuted, and I should no longer state that 2 + 2 = 4. This has only been a test. Snit wrote: > 2 + 2 ...

Which to get, Disciples 2, Age of Wonders 2 or HOMM4 #2
Wait for AoW:SM or Dominions II. ...

OSK 2.0, 2.2 and 2.3
Does anyone here have any of the versions named in the subject? I am unravelling 68K Basic I-Code, and I need to compare 2.0, 2.2 and 2.3 modules with 2.1 and 2.4 modules. There are differences between 2.1 and 2.4, and I want to know how many other differences there are between versions. Thanks in advance. Wayne Campbell ...

Moving a single instance 9.2.0.7 database to a clustered 9.2.0.6 database environment #2
HI, I have the following situation: We have Veritas cluster setup , and there are 2 solaris servers on which VCS is set up. On this set up we have about 8 databases all of which are 9.2.0.6 version. Now we have one stand alone solaris server, and it has a 9.2.0.7 database. This database is for a third party application -Clear Case & Clear Quest. Now it is required to move the single instance database to clustered environment. Since in the clustered environment we have 9.2.0.6 version, is it AT ALL POSSIBLE to move this database, which is 9.2.0.7 to that enviroment? I guess not, and in that case, what are the options? - since this is a packaged application , so should be contact the vendor and ask them whether we can downgrade the database to 9.2.0.6? - is that a valid option? Are there other options? (upgrading the existing 9.2.0.6 database to make all of them 9.2.0.7 is not allowed.) With thanks, Nirav ...

Get Records from a 2.DB with MS-Access
Hello, I have a Ms-Access application. All data stand in a Oracle data base. The Oracle data base has a DatabaseLink to a second Oracle database. I would like to get records from a table in the 2. Oracle data base. Like: "Select * From Tabel@SecondDB " But it does not work Hello, Sam, near 16:50 13-Jul from samsung@gmx.de: > Hello, > > I have a Ms-Access application. > > All data stand in a Oracle data base. > > The Oracle data base has a DatabaseLink to a second Oracle database. > > I would like to get records from a table in the 2. Oracle data base. > > Like: > > "Select * From Tabel@SecondDB " > > But it does not work On the first database: create view v_tabel as select * from tabel@SecondDB; Then try to "select * from v_tabel". Other option is to configure tnsnames alias for second database on machine with application and configure datasource in your application directly from it (attach the table). -- wbr, Wit. ...

Commit : database changes in Ms access. #2
Hi all, For a ms access database is there any facility like 'commit' like activity to save or not to save records.. to modify/delete changing records. Any vbs code can we do in forms..? -kish ...

where to get 1GB DDR266 CAS2 2-2-2 1T timing?
Hi: Does anybody know if there are any manufacturer that makes 1GB DDR266 cas2 2-2-2 timing. I am putting together a dual xeon system that has room for four memory modules and i want to put 4GB of memory. I know you can have 512MB modules that are cas2 with 2-2-2 T1 timing, but i need more memory than just 2GB. I would really appreciate any help. thank you if you could email me i really appreciate it: pepelpue@mailcity.com what operating system will you be using? Do you need buffered/Registered or unbuffered/non-registered? ECC? how much money are you willing to spend? I have seen a lot of 1GB PC2100 DIMM's... but I have not seen ANY CL2-2-2-2. There is CL2.5-2-2-2 everywhere. NuTs "ali yousefi" <pepelpue00@earthlink.net> wrote in message news:_ZXUa.122136$Io.10434965@newsread2.prod.itd.earthlink.net... > Hi: > > Does anybody know if there are any manufacturer that makes 1GB DDR266 > cas2 2-2-2 timing. I am putting together a dual xeon system that has room > for four memory modules and i want to put 4GB of memory. I know you can > have 512MB modules that are cas2 with 2-2-2 T1 timing, but i need more > memory than just 2GB. I would really appreciate any help. > thank you > if you could email me i really appreciate it: > > pepelpue@mailcity.com > > > > > I have the iwill dp533 MB. it ...

RE: wxMac-2.6.2, CodeWarrior: no key responds #2
Hi > I'm investigating things, I've been able to reproduce the=20 > problems in the Carbon Builds, thanks. fixed in CVS HEAD and WX_2_6_BRANCH http://cvs.wxwidgets.org/viewcvs.cgi/wxWidgets/src/mac/carbon/window.cpp ..diff?r1=3D1.265&r2=3D1.265.2.1&only_with_tag=3DWX_2_6_BRANCH Thanks, Stefan --------------------------------------------------------------------- To unsubscribe, e-mail: wx-users-unsubscribe@lists.wxwidgets.org For additional commands, e-mail: wx-users-help@lists.wxwidgets.org ...

Who to Import PostGRESQL 7.2.2 database into Db2 7.2
Hi team Is there any easy way to Import PostGRESQL 7.2 which is on the Linux mechine to a DB2 UDB 7.2 on WIndows. What Options do i have. Thanks Ugra -- Message posted via http://www.dbmonster.com I think to use postgresql export (dump with create table), modify a tabel structure and type for create compatibily on db2 and import data with import tool! I not tray to import from postgresql to db/2 but viceversa! On Tue, 29 Mar 2005 17:40:04 UTC, "Ugrasena via DBMonster.com" <forum@DBMonster.com> wrote: > Hi team > Is there any easy way to Impor...

[ANN] gettext-2.0.2, gettext_activerecord-2.0.2, gettext_rails-2.0.2
Hi, Ruby-GetText-Package-2.0.2 and the families (gettext-2.0.2, gettext_activerecord-2.0.2 and gettext_rails-2.0.2) are now available. Ruby-GetText-Package is the library/tools for message localization. * gettext - Message localization libraries and tools for all kind of apps/libs. * gettext_activerecord - ActiveRecord Localization * gettext_rails - Rails support with gettext. Changes ------- == gettext-2.0.2 * Support ruby-1.9.1 style format string such as %<foo>d. * Apply new Locale.set_app_language_tags and Locale.candidates. [Suggested by Vladimir Dobriakov] * Enhance...

Get Rich #2 #2
Take advantage of a great deal ...

database security #2 #2
i a the user of sql server 2005 on window server 2003. i want to know that how can i prevent my database from other user logins because all of them are sysadmin type. and i am also looking for database concurrency control methods. if any one know about this plz mail me answer on this mail id mahendersingh_be@yahoo.co.in thanx in advance On 11 juin, 13:00, Mandy <mahendersing...@gmail.com> wrote: > i a the user of sql server 2005 on window server 2003. i want to know > that how can i prevent my database from other user logins because all > of them are sysadmin type. > an...

Access 2.0 #2
Does anyone have a copy of Microsoft Access 1.0 or 2 (original not upgrades) Steve ...

Web resources about - Getting 2 counts() of 2 foreign keys - comp.databases.ms-access

Live Coverage of Apple's 'Let Us Loop You In' iPhone SE and 9.7" iPad Pro Event
Apple's "Let Us Loop You In" event kicks off at 10:00 AM Pacific Time today, and we're expecting to see several product announcements including ...

George Clooney Slams Trump Talk In Hillary Clinton Event Letter
"If you listen to the loudest voices out there today, you'd think we're a country that hates Mexicans, hates Muslims, and thinks that committing ...

Supreme Court Takes Up Apple Vs Samsung Patent Suit
The Apple vs Samsung patent dispute has been going on for a few years now with no end in sight but that might be about to change soon. The United ...

Twitter turns 10 years old today and these baby chicks are ready to celebrate
It's Twitter's 10th birthday today. To celebrate the iconic social media platform, we brought in a few fuzzy friends of ours. Read more... More ...

Conflict between Apple and the FBI started 18 months ago with the release of iOS 8, says Bloomberg
While Apple and the FBI are about to fight in court over an iPhone 5c involved in the San Bernardino shootings, this isn’t a new issue for the ...

Manhunt launched for newly identified Paris attacks suspect Najim Laachraoui
CNN Manhunt launched for newly identified Paris attacks suspect Najim Laachraoui CNN Brussels, Belgium (CNN) French and Belgian police have ...

The early reaction to 'Batman v Superman' is here, and fans say this is the best part
Though we in the press can’t talk about “Batman v Superman: Dawn of Justice” just yet, fans who were at the movie’s premiere in New York City ...

Watch Justin Bieber Bring out Big Sean & Chance the Rapper in LA
Biebs kicked off his Purpose Tour in LA last night with a show at the Staples Center and he brought a couple of guests along to help get things ...

Novak Djokovic Stumbles Into Some Thoughts About Women's Tennis
Yesterday, the world was introduced to Indian Wells Tennis Garden CEO Raymond Moore , a dumb-ass man who thinks that female tennis players should ...

South Korea: Prepared for another North Korean nuclear test
A US think-tank said recent satellite images showed activity at the North's main nuclear test site that suggest a further underground detonation ...

Resources last updated: 3/21/2016 4:19:36 PM