SQL query with Many-To-Many relationship???

I am trying to write an SQL statement that is giving me some trouble, and I
was hoping that somebody here could lend me a hand. 

CONTEXT
I am working in Access 2007. The tables concerned are the Students, Classes
and StudentsAndClasses.

The relationship of Students to Classes is many-to-many, i.e. each student
can attend several classes and each class can have several students.
StudentsAndClasses table is used to help define this relationship and works
something like a class roster.

The Students table has StudentID as its primary key.
The Classes table has ClassID as its primary key.
The StudentsAndClasses table has StudentID and ClassID as foreign keys.

The relationship of Students to StudentsAndClasses is 1 to many.
The relationship of Classes to StudentsAndClasses is 1 to many.

There is a Students form which shows student details and a corresponding
subform which shows all the classes the student is in.

There is a Classes form which shows class details and a corresponding subform
which shows all the students in the class.


PROBLEM
The user is looking at a student on the Students form. In the Classes Subform,
the classes that the student is in are listed. There is a "Go To Class"
button that should pull up the Classes form and the details of the
corresponding classes (those appearing on the Students form when the "Go To
Button" was clicked), which includes a list of the students taking each class
(on display in the Students Subform). In other words, for any given student,
that a user might be looking at I want a record set that contains all the
classes that the student attends as well as the corresponding list of
students in each of those classes. 

I've been trying to do it by means of a query. 

This first query will get all of the classes that a student is in:

SELECT *
FROM Classes INNER JOIN [Students And Classes] 
ON [Students And Classes].ClassID = Classes.ClassID
WHERE [Students And Classes].StudentID=SomeStudentID

This next query will get all of the students that a particular class has:

SELECT *
FROM Students INNER JOIN [Students And Classes] 
ON [Students And Classes].StudentID = Students.StudentID
WHERE [Students And Classes].ClassID=SomeClassID

I can't figure out how to combine the two and then bind the record set to the
Classes form. 

The closest I have gotten is to have the Classes form appear with the correct
classes, but I can't figure out how to have the corresponding details (the
students in the class) to appear in the sub form automatically. If not an SQL
statement, is there a way to get the sub form to update based on the details
passed to the main form?

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200802/1

0
boyratchet
2/13/2008 3:38:54 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
610 Views

Similar Articles

[PageSpeed] 26

 SELECT *
 FROM  (Students INNER JOIN [Students And Classes]
     ON [Students And Classes].StudentID = Students.StudentID)
                                INNER JOIN Classes
    ON [Students And Classes].ClassID = Classes.ClassID


and you can add a WHERE clause to get only one studentID, or only one 
classID.  Basically, you can see the table [Students And Classes]  as your 
main table and the two other tables as small satellites around it, but 
linked (lookup) through the specified ON clauses.



Hoping it may help,
Vanderghast, Access MVP


"boyratchet via AccessMonster.com" <u41080@uwe> wrote in message 
news:7fad0bd61d6b3@uwe...
>I am trying to write an SQL statement that is giving me some trouble, and I
> was hoping that somebody here could lend me a hand.
>
> CONTEXT
> I am working in Access 2007. The tables concerned are the Students, 
> Classes
> and StudentsAndClasses.
>
> The relationship of Students to Classes is many-to-many, i.e. each student
> can attend several classes and each class can have several students.
> StudentsAndClasses table is used to help define this relationship and 
> works
> something like a class roster.
>
> The Students table has StudentID as its primary key.
> The Classes table has ClassID as its primary key.
> The StudentsAndClasses table has StudentID and ClassID as foreign keys.
>
> The relationship of Students to StudentsAndClasses is 1 to many.
> The relationship of Classes to StudentsAndClasses is 1 to many.
>
> There is a Students form which shows student details and a corresponding
> subform which shows all the classes the student is in.
>
> There is a Classes form which shows class details and a corresponding 
> subform
> which shows all the students in the class.
>
>
> PROBLEM
> The user is looking at a student on the Students form. In the Classes 
> Subform,
> the classes that the student is in are listed. There is a "Go To Class"
> button that should pull up the Classes form and the details of the
> corresponding classes (those appearing on the Students form when the "Go 
> To
> Button" was clicked), which includes a list of the students taking each 
> class
> (on display in the Students Subform). In other words, for any given 
> student,
> that a user might be looking at I want a record set that contains all the
> classes that the student attends as well as the corresponding list of
> students in each of those classes.
>
> I've been trying to do it by means of a query.
>
> This first query will get all of the classes that a student is in:
>
> SELECT *
> FROM Classes INNER JOIN [Students And Classes]
> ON [Students And Classes].ClassID = Classes.ClassID
> WHERE [Students And Classes].StudentID=SomeStudentID
>
> This next query will get all of the students that a particular class has:
>
> SELECT *
> FROM Students INNER JOIN [Students And Classes]
> ON [Students And Classes].StudentID = Students.StudentID
> WHERE [Students And Classes].ClassID=SomeClassID
>
> I can't figure out how to combine the two and then bind the record set to 
> the
> Classes form.
>
> The closest I have gotten is to have the Classes form appear with the 
> correct
> classes, but I can't figure out how to have the corresponding details (the
> students in the class) to appear in the sub form automatically. If not an 
> SQL
> statement, is there a way to get the sub form to update based on the 
> details
> passed to the main form?
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200802/1
> 


0
Michel
2/13/2008 3:50:12 PM
Thanks. This certainly helps.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200802/1

0
boyratchet
2/13/2008 10:01:48 PM
Reply:

Similar Artilces:

Opening Excel Opens Too Many Sheets
Don't know what I did recently but when I open Excel, FINANCIAL MANAGER is added to the main menu at the top and several sheets/files are opened that I do not want to open. FINANCIAL MANAGER wasn't there before and I can find no way to take it off the menu. I find no "startup" folder that lists these files that I could delete the shortcuts to - I've looked in the various folders relating to Excel but not found anything that might stop this activity whenever I open Excel. Opening the shortcut I'd been using to get to Excel, it's like a macro starts off and I fin...

Query Problem 03-27-10
Hi, I have 2 tables, tbBaby and tbVaccineInjection. tbBaby stores the babies' personal data like Name, BirthDate etc., tbVaccine stores VaccineName, DoseNumber (1st, 2nd, 3rd, etc.) and InjectionDate, etc. These 2 tables are one to many in relation, because each baby may have several vaccinations on different dates. I'd like to build a query to retrieve babies with their most recent injection date, i.e. only one record for each baby with latest injection date. my query is as follow SELECT tbBaby.*, tbVaccine.* FROM tbBaby INNER JOIN tbVaccine ON tbBaby.ID=tbVaccine.B...

Append query bloat
I have an access database where I use an append query quite often. I will use this same query over and over by changing the table I'm appending from, then I save the query. During the save process, the database bloats from ~50KB to ~150KB. Our company recently upgraded to 2007 and that is when I noticed the problem. Is there a way to stop this from happening? I can manage it by compacting and repairing after each save, but recently I added 4 tables in one day, so this was a pain. Thanks Don't even worry about it. An Access file can be up to 2 GB in size. 150 KB ...

SQL setup RMS 2.0
I am setting up a new system with 3 registers (Win XP SP2), two back office computers (Win XP SP2 and SQL 2005 Express), and Windows SBS 2003 with SQL Server 2005 Professional. The problem: Can't get the back office workstations to connect to the SQL Server. I installed RMS on the server, it connects to SQL and run perfectly. When I try from the workstation using RMS Admin, I get a time out error. I did everything in KB932078 without success. Must be something very minor with major consequences that I've missed. Can anyone help? Thanks Rich -firewall -sql surface area co...

selling ccv dumps banklogin bank transfer and many more
US: - Visa and Master 2$ per 1cvv/ buy >100cvv price 1.5$ per 1cvv - Dis and Amex 4.5$ per 1cvv/ buy >50cvv price 4$ per 1cvv - Full 30$ per 1cvv UK: - Master and Visa 5$ per 1 cvv/ buy >50cvv price 4.5$ per 1cvv - Dis and Amex 8$ per 1cvv/ buy >100cvv price 6.5$ per 1cvv - Full 55$ per 1cvv CA: - Randoom 9$ per 1cvv/ buy >100cvv price 8$ per 1cvv - Amex 20$ per 1cvv/ buy >100cvv price 18$ per 1cvv AU: - 10$ per 1cvv/ buy >100cvv price 9$ per 1cvv ITALIA: - 15$ per 1cvv/ buy >100cvv price 13.5$ per 1cvv GERMANY:- 15$ per 1cvv/ buy >100cvv price 13.5$ per ...

Does SQL Run Behind Queries?
Access 2007 When I run a query does Access, "go away" and run the SQL for what I see in the GUI? If the answer is, "yes" is there anything that I can switch on to see the SQL that it is running? What I would like to do, if it is possible, is to see the query in the GUI and, at the same time, see the SQL that is being used to select the data from the database; that way I can understand things (exactly what is happening) more clearly and more quickly. TIA for any replies. trip_to_tokyo wrote: > Access 2007 > > When I run a query does...

Database Query #4
I have created a data table in a worksheet and named it “data”. Then I created a separate worksheet, in the same workbook, and created a database query of the data table, by using Data…Import External Data…New Database Query…Excel Files* and defining the database name by browsing to the same workbook. The query worked fine. However, when I moved the workbook (which included the data source) to a different folder and tried to refresh the database query, I got the following error message: “[Microsoft] [ODBC Excel Driver] The Microsoft Jet database engine could not find the object ‘data’...

How can I NOT include relationship columns in child nodes?
I am trying to create an XML file with nested 'child' data. I have multiple columns that comprise the data relation(s). How can I NOT include the related columns in the child nodes? Code below (should be able to cut, paste, and run directly). ------------------------------------------------------------------------------ -------- 'Instantiate a new dataset. Dim MyTrx As New DataSet 'Name the dataset. MyTrx.DataSetName = "OUTPUT" 'Add a transaction table. Dim dtTransaction As New DataTable &...

i get a message too many fields defined on ACCESS
Trying to add columns, and it does not let me. What can I do Ibz�n wrote: > Trying to add columns, and it does not let me. > > What can I do I believe the limit is 255, but you may need to do a compact and repair (see top line menu) to free up previously deleted fields. If you are anywhere over about 30 or 40 fields, you likely need to revise your table design and "Normalize" it. -- Joseph Meehan Dia 's Muire duit ...

DNS clients Does not query the secondary DNS
Hi, I have 2 w2003 Dcs - both DNS servers Primary and secondary. I have Clients running XP static Ip and in both DNS servers IP is configured as Primary and Secondary DNS. My Primary DNS server went down, all clients are not quering the secondary! using NSLOOKUP it showed that clients are still querying the primary with a tomeout error !! all name resoultiond stoped in the organization! any idea? tx NsLookup does not automatically fall through to alternate DNS servers, it is not a good way to test server redundancy. If you ping a host, which will use the DNS Cl...

How many entries can Excel handle!?
I have close to 1000 entries in my Excel spreadsheet, and am trying to figure out why entries are coming up missing. I often do custom sorts. This may be when entries get tossed out? You are no where near the limit. Check out the help file article "Excel limits and specifications". There must be something else going on. -- Best Regards, Luke M "Rebecca Sage" <RebeccaSage@discussions.microsoft.com> wrote in message news:4241294E-484E-4A8A-9304-0E0C45E643A3@microsoft.com... >I have close to 1000 entries in my Excel spreadsheet, and am trying to ...

Calculate difference in rows in a query
I have a query that calculates how much revenue is claimed by month per job but then I have to take the new month less the prior month. The problem is the data is in row format. I don't know how to subtract February from January, March from but don't know how. I know how to do it in Excel but not Access. Please help... Example: Order Month JTD Clm Variance 101026521 January $511,525 $0 101026521 February $511,525 $0 101029438 January $1,238 $0 101029438 February $3,713 $2,475 101033168 January $21,465 $0 101033168 February $51,460 $29,995 101034011 Janu...

How to use colmn headings in paramter query
Sorry if this has been asked before, but I am not able to locate it if it has. But here is my delima: I need to be able to select the salesperson sales based on the month (1-12) from table 1. I* can create a parameter query, but am not sure hwo to query onlya certain month. table1 ID salesperson 1 2 3 4 5 6 7 8 9 10 11 12 1 joe 12 0 0 2 12 0 1 1 5 7 8 10 2 steve 2 5 6 18 0 0 2 3 4 11 12 1 select * from table1 where salesperson =joe and .... that is where I get stuck. I need to be able to select a column he...

sql report 3 tables
I am trying to write a report which will contain 4 tables, I need to display all 4 tables on one page. Unfortunatley because of the siz of the first 2 tables the 3rd table sits way down on the right hand side, how can i manipulate were the tables sit on the preview window? Regards Ian Are you writing your report in visual studio? in Report layout view, right click outside of the report area and select Properties. from the second tab you can change the layout of the report. By default it is set to 8.5 X 11, try chaning it to 11 X 8.5. Also, try changing the font size in your tab...

An open on table 'ME_Direct_Deposit_MSTR' failed accessing sql data.
Hi.. We are on Dynamics GP 9.0 and we recently updated our GP with 'Advanced Human Resource and Payroll suite 9.0'. The update on the server and client computers went through good but when one of our user is trying to print a payables check its giving the following error message. "An open on table 'ME_Direct_Deposit_MSTR' failed accessing sql data." We initialized the database for all our companies by logging in as SA and accessing all the companies on server as well as client computers. Any idea what could have gone wrong? Any help on this one will be much appre...

CRM Moving Sql Server 2000 to 2005 (Database and report) new Serve
Hi, I'm working to move sql server 2000 to sql server 2005 on a new server. CRM 3 is installed on one server and Sql 2005 and Reporting Service on an other server. I backuped and restored the database succefully. But I have an issue when I produce reports. On some reports, I have the graphic with fields where "#Error" is display. Any idea? Thanks David ...

Extracting invoice data from GP 9 using a SQL query (ODBC)
I'm working to extract invoice detail information from a client's GP 9 MSSQL database, with the goal of being able to mock up their invoices in external systems. Just to keep things interesting, they aren't using GP to it's maximum ability - quotes are generated from another software package, and when converted to a sale, minimal data is entered into GP and processed (such as shipping address, job name, job number, PO number, and occasionally a line it of what appears to be free-form text. I have hard copies of several invoices, various web resources, and access to t...

Query Does Not Count "2" Records
Good Evening Everyone... I have a bit of a logical puzzle that I need to solve. I have various queries which have various criteria for them. One query involves retirees or spouses being under the age of 65 and the other involves retirees or spouses being over the age of 65. Both the retiree and spouse are listen in the same records and on one line of the master table and queries. The situation that I am running into is that I can have a retiree who is over 65 and a spouse under 65 and the record comes up twice in the two different queries b/c it meets the criteria, which is perfect. What...

Purchase orders
Is there any way to have purchase orders print size matrixe horizontally instead of vertically? I can place an order for fiv style of shoes and the PO will be four pages long because of th large number of different sizes in the matrix. If the size matri could print horizontally, the PO would be about a third of a singl page. I'm talking about Quicksell. But what about RMS also? Do you need t buy a third party program for another $1,000 to make this happen ...

Web Query Help...
If I try and use a web query with the following site http://www.sportsline.com/mlb/stats/playersort/regularseason/yearly/MLB/P I get a message that says the web query returned no data. I click on the arrow right by player and it highlights all the players names and their stats. Anyone know why it doesn't import the data into excel? Is there too much data? Thanks Works fine for me, although one have to do some editing since it imports some trash as well (I would import it, then select the table itself from the import and copy and paste into a new sheet, then import the next 50 and...

Setting database location to SQL Server
Hello, I'm looking to move the POS system database from the local MSDE database to a SQL server. I've restored a recent backup (.bck) to a SQL server machine on our network and I'm looking for information on how to reconnect the POS to the new SQL server. Before we go live I will restore a new backup but I'm just testing the solution for now. The overall plan is to separate the database from the cash register and allow SQL server to perform backups and other normal database task. Is there a separate configuration tool for changing data connection information? ...

update many fields in a form from existing info.
I have 2 tables. One table stores various information about companies including company name, address, etc. The second table stores student names. In a form, I am able to enter mutiple student names into a memo field and, as of right now, type in the company name, address, etc. (it is not linked to the company name table at all). What I want to do is this: from the student add form, search the company table for a company based on partial name and/or zipcode, select one company from a possible 1 or more companies, and have the fields in the form automatically fill in - pulling the ...

Edit a word in many publisher files
> I need to change one word in a few hundred documents in Publisher. > I can: > open each file>Ctrl'M'>highlight and paste the text> save and open the next. > This is very lengthy, surely somone will have some software to do bulk > changes to a large numbr of files.Pleasehelp me if you can ...

Need Help with Delete SQL for Access
Hi, I have an MS Access Table that I use to store log information and it looks something like this: tblLog ID Job SN Message Date 1 script1 abc123 Failure 1/1/2007 2 script2 abc123 Failure 1/1/2007 3 script2 abc123 Success 1/2/2007 4 script2 def456 Success 1/2/2007 5 script1 def456 Success 1/9/2007 6 script1 def456 Failure 1/10/2007 7 script1 abc123 Success 1/10/2007 I would like to create a query that would delete all of the old information. In other words, I only want to keep the data from the last time the script was run. If I were going to select the data, I would use something like ...

SQL report as dashboard to filter for Current user cannot user CRMAF_ #2
First off I cannot use the default filters because the report is being generated for a dashboard and not a CRM Report, however it is being created via an SRS Report. I simply need a way to filter the report for the current user only, I found this query which gives the system user but this will not match up to the Account owner because of its format. select fullname from FilteredSystemUser where domainname = SUSER_SNAME() Gives: CRM Admin I need, Admin, CRM ...