473,325 Members | 2,785 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,325 software developers and data experts.

Using And in a query using two tables linked by ID

I need to run a query showing what families have a student in both grade 5 and 7. I can run the query showing if a family either has a student in either of those grades but I need to know which family has a student in both of these grades. Is there a way to find this out. The parent table and student table is linked by family ID. Any ideas?
Oct 23 '06 #1
5 1455
AricC
1,892 Expert 1GB
Post table schema and the query you're trying.
Oct 23 '06 #2
MMcCarthy
14,534 Expert Mod 8TB
SELECT ParentName, Grade
FROM Parents INNER JOIN Students
ON Parents.ParentID=Students.ParentID
WHERE Grade IN (5,7);
Oct 23 '06 #3
NeoPa
32,556 Expert Mod 16PB
I think this one's a little more complicated than it first appears.
This should work.
Twins mean a simple count of records matching the '5 or 7' criteria is not good enough.
I'm afraid it does have two levels of subqueries but, hey, what's a subquery or two between friends ;-).

Expand|Select|Wrap|Line Numbers
  1. SELECT tblParent.Family
  2. FROM tblParent INNER JOIN (SELECT Family, Count(Grade) AS 5And7
  3. FROM (SELECT DISTINCT Family, Grade
  4. FROM tblStudent
  5. WHERE (Grade In (5,7))
  6. GROUP BY Family, Grade) AS subStudent
  7. GROUP BY Family) AS subStudent ON tblParent.Family = subStudent.Family
  8. WHERE (((subStudent.[5And7])=2));
Oct 24 '06 #4
MMcCarthy
14,534 Expert Mod 8TB
Not bad

Down to interpretation again. I took the question to be if any child in grade rather than count of children in grade.


I think this one's a little more complicated than it first appears.
This should work.
Twins mean a simple count of records matching the '5 or 7' criteria is not good enough.
I'm afraid it does have two levels of subqueries but, hey, what's a subquery or two between friends ;-).

Expand|Select|Wrap|Line Numbers
  1. SELECT tblParent.Family
  2. FROM tblParent INNER JOIN (SELECT Family, Count(Grade) AS 5And7
  3. FROM (SELECT DISTINCT Family, Grade
  4. FROM tblStudent
  5. WHERE (Grade In (5,7))
  6. GROUP BY Family, Grade) AS subStudent
  7. GROUP BY Family) AS subStudent ON tblParent.Family = subStudent.Family
  8. WHERE (((subStudent.[5And7])=2));
Oct 24 '06 #5
NeoPa
32,556 Expert Mod 16PB
MMcCarthy,

Thanks for the grats - I've seen a lot of your work so that means a lot.
As I said though, this isn't straightforward. The count is after grouping then filtering so it really means :-

If the total number of different grades found in ( the set of children containing either 5 or 7 ) is equal to 2.

So, for instance, if a family had 2 children (twins), both in grade 5, they would only count as 1 and would therefore not get selected.

It's always a lot easier to formulate complicated code than it is to interpret it I find and that lot was more concise than explicit.

I'm assuming of course, that the code is correct and wasn't lost in translation from my brain to the paper. That wouldn't be a first.
Oct 24 '06 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: dk | last post by:
Hi all, Would appreciate some advice on the following: I am trying to speed up an Access database connected to a SQL Server back-end. I know I can use a pass-through query to pass the sql...
5
by: deko | last post by:
How to run action query against linked table? I have an Access 2003 mdb with an Excel 2003 Workbook as a linked table. When I attempt to run an action query against the linked table I get this...
1
by: annie | last post by:
Hi all, I have recently ported my Access 2000 app to SQL Server, keeping the Access client as the front end using linked tables. I am also using triggers on my SQL tables to trap orphan...
0
by: Mike Knight | last post by:
(I've also posted this problem on microsoft.public.excel.programming) I have a MS Access 2003 Database named "AS400 Fields.mdb". This database contains links to tables on an AS400. In MS...
1
by: David | last post by:
I am hopeing someone can help me with this as I have struggled over the last few days with it. I have a make table query which I want to use to split the table up into seperate tables which are...
2
by: me321701 | last post by:
I have an Access 2003 MDE with a SQL Server 2000 back-end using ODBC linked tables. The MDE is shared among approximately 20 users. This is the environment; I don't have the ability to change it...
10
by: Robert | last post by:
How do you get an accurate count of the number of records returned from a query when using linked tables. I have an access 2003 database as a front end to another access 2003 database that...
3
by: AP | last post by:
Hello I have a department full of Access databases. I am starting to think about moving some of the larger ones to use a sql server backend. I do not want to go throught the technical upsizing. I...
1
by: jasonlakewhitney | last post by:
I have an MDB on my PC with 3 linked tables to a SQL database. I only have permissions on the SQL db to insert and delete records. The linked tables are LnktblA, LnktblB, LnktblC and the main MDB...
0
by: Chandan Ahuja | last post by:
Hello , I use MFC DAO classes to access my MS access database . I am using some linked tables(Linked to Oracle) in my MS Access database . Also i have some queries in my databse , these...
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: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.