Queries :: Junction Table In A Ternary Relationship?
Nov 14, 2013
I explain , i have 5 tables :
Class ( classID , classname,... )
Student (studid , firstname, lastname , classID ,....)
Course( courseid , coursename )
Term ( termid , termname , begindate , enddate )
Score ( scoreid , studid , courseid , termid , score)
Note :
1) A class can contain one or more students (one-to-many between Class and Student tables)
The table "Score" is a junction table between three tables : Student , Course and Term because it contains three foreign keys ( I could use a combination of 3 foreign keys to make a primary key ! ) .
The tables ; "Class", "Student", " Course ", "Term " already contain data for each table I created a data entry form .
My biggest problem is how to create a form to enter students' grades for each class.
On the form I would like to use ComboBox to select a class that displays students from this class then another ComboBox to select course and another ComboBox to choose the term and then enter grades or scores for each student.
View Replies
ADVERTISEMENT
Jan 6, 2005
Ok, I have a problem. I am creating a customer database, with the tables tblRep, tblAgent, and tblOffice. Each agent is assigned to one rep and one office. However, each office can have more then one rep and each rep may have more then one office. Now after saying that, in general an office will only have one rep. However, in some cases an agent from a particular rep will be related to an office that belongs to another rep, hence the many-to-many relationship. When this happens, this is considered an exception and I need to track this. So, I tried to create a junction table called tblException that included all the primary keys from the three main tables, and an extra field ‘exception’. However, when I try and create a new office I am unable to relate this to the rep table, because I do not have the agentID to create a record in the table tblException. The agent may or may not be added at a later date. So I have a problem. It seems to me that I have this set up wrong. Anybody have some advice? I’ve included a screen shot of the relationships. And if you are wondering officecode is part of the primary key in both the tblagent and tbloffice because of updatability between separate instances of the database.
View 7 Replies
View Related
May 29, 2005
I am trying to set up a Real Estate database, and can't get the Relationships to work. I have three tables:
House Listings
Farm Listings
Clients
Clients can have properties in both House and Farm Listings Tables, so I need a Junction Table (or do I need more than one?)
The Primary Key fields of House Listings Table (HouseID) and Farm Listings Table (FarmID), become a composite Primary key in the Junction table. Is this correct?
Both the House Listings Table and the Farm Listings Tables have a field for Client ID. Is this causing me problems, as I can get the Farm and Clients Queries to work, but not any queries with the House Listings.
As this is my first attempt at creating a database with many-to-many relationships, please explain in simple terms what I am doing wrong.
I would appreciate any help.
Thanks
View 2 Replies
View Related
Oct 21, 2013
I have the following tables:
tblInvoice
tblInvoicePrePayments
They are related with a one-to-many relationship. The related field is "InvoiceId"
I want to create a query that shows selected fields from tblInvoice and the sum of field "PrepaymentValue" from table tblInvoicePrePayments.
I want to be able to edit the fields from tblInvoice in the query.
If I create a totals query (qryInvoicePrePaymentsSum) on tblInvoicePrePayments, using the fields InvoiceId and PrepaymentValue, and then create another query with tblInvoice & qryInvoicePrePaymentsSum, the fields in tblInvoice cannot be updated!
View 3 Replies
View Related
Apr 13, 2007
I'm working in the petro-chem and industrial service industries now, and am finding there are relationships I haven't had much exposure to in the past.
Can someone please help me with constructing the tables and relationships needed here?
This is something I should probably know, yet I've never created junction tables which must take into account nested data. I have created a relationship which works, but I feel it could be done in a simpler fashion.
By the way, I cannot find Pat Hartman's many-to-many sample db. It is either missing, or the restrictions on word length in Search just aren't letting me find it.
Table: WorkOrder is the primary table I'm working with. I need to store 4
pieces of information for every WorkOrderID, two of which have subs.
Here's a picture of the data I need to collect:
View 4 Replies
View Related
Apr 24, 2006
I’m having trouble defining Relationships I’m thinking I need a “Junction” Table and I have tried looking at the Orders.mdb but it hasn’t helped (I’m sure I’m just missing something) I just don’t see how it works. If at all possible please don’t just give the info try to help me understand so I can get the answer myself.
Here is what I have:
Far table:
FarNumID (PK) > autonumber
FarNumber > Text “224-10C”
FarTitle> Text
FarParagraph table:
FarParaID (PK) > autonumber
FarNumID > Number
FarParaTitle > Text
FarParaText > Text
AC table:
ACNumID (PK) > autonumber
ACNumber > Text
ACTitle> Text
ACParagraph table:
ACParaID (PK) > autonumber
ACNumID > Number
ACParaTitle > Text
ACParaText > Text
1. Each FarNumber can have only 1 FarTitle 1:1
Each FarNumber can have many FarParaTitles 1:Many
Each FarNumber can have many FarParaText 1:Many
2. Each FarTitle can have many FarParaTitle 1:Many
Each FarTitle can have many FarParaText 1:Many
3. Each FarParaTitle and have only 1 FarparaText 1:1
Thanks so much.
View 4 Replies
View Related
Dec 8, 2005
All I ever seem to do is sit about trying to figure this out and then give up and ask for help :(
I have three tables.
One for contacts
One for groups (groups like people attending meeting a, b c)
and a junction table as the top two create a many to many relationship.. one person can be part of multiple groups, and a group can have multiple members.
In the contacts table my primary key is an auto number, and is contactsID
I also have some contact details, and a groupID field
In the group table my primary key is GroupID and is an auto number,
it also have group name, and description
in the junction table I have a primary key, then GroupID, and ContactsID which are also set at primary keys and are set with the same values as the same named primary keys in the relavant tables (I beleive this defines them as foreign keys... i hope so anyway!)
I have the relationships set up as a one to may relationship from contacts to junction and groups to junction, contacts linking from ContactID to ContactID in each table, likewise with the Group table.
However, when I try to put everything into a main form for contacts,with a subform for groups, all I am getting is the autonumber... which isn't much good for my end user..... :( How do I solve this?
Thanks anyone that can help.......
View 3 Replies
View Related
Dec 5, 2007
I'm trying to add records to a junction table based on additional record added to one table via form in the many to many relationship.
Junction table = ClientAssociation
Main table = ClientMain (qry_NewClientNullAssociation identifies new record added)
Many to many table = Associations
I'm trying to do this upon clicking a command button that then opens ClientAssociation based on Client_ID. Then allows user to select mutliple associations (Asoc_ID) for Client_ID.
Here's what I have - I'm new at this...
Private Sub ClickAssociations_Click()
On Error GoTo Err_ClickAssociations_Click
'Open recordset with new client records
Dim db As Database
Set db = CurrentDb
Dim recClient As DAO.Recordset
Dim strClientID As String
Set recClient = db.OpenRecordset("qry_NewClientNullAssociation", dbOpenDynaset)
'Loop through new client ids
Do While Not recClient.EOF
strClientID = ClientMain.Client_ID
Dim recAsoc As DAO.Recordset
Dim strAsocID As String
Set recAsoc = db.OpenRecordset("Association", dbOpenDynaset)
'Loop through asoc ids
Do While Not recAsoc.EOF
strAsocID = Asoc_ID
'Open junction table and update records
Dim recClientAsoc As DAO.Recordset
Set recClientAsoc = db.OpenRecordset("ClientAssociation", dbOpenDynaset)
With recClientAsoc
.AddNew
!Client_ID = strClientID
!Asoc_ID = strAsocID
!CheckBox = False
.Update
End With
recAsoc.MoveNext
Loop
recClient.MoveNext
Loop
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frm_Associations2"
stLinkCriteria = "[Client_ID]=" & Me![Client_ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_ClickAssociations_Click:
Exit Sub
Err_ClickAssociations_Click:
MsgBox Err.Description
Resume Exit_ClickAssociations_Click
End Sub
View 1 Replies
View Related
Dec 18, 2007
Hello all,
I just wanted to run this by some experts before I go mucking around in my db. Basically, I am creating a data entry form to populate with information about car parts, and the vehicles they apply to. This is what I am working with:
tblParts tblModelsParts tblModels
PartID ModelPartID ModelID
PartNumber PartID ModelNameID
PartDesc ModelID ModelYearID
I am trying to build a form that will allow me to enter new part numbers and descriptions into the table, and then have the user select (through a listbox?) all the vehicles the parts apply to. Is there a way to populate the junction table with each combination of partID and ModelID automatically? NOTE: tblModels is already populated with data.
I am assuming there would be a way to do this... A query of the junction table (and tblModels?) as the recordsource for the form, and then a subform with the tblParts as its recordsource?
I would have posted this in the massive thread I have going already... but I thought it might be nice to give Craig and Adam a break from helping me :o
View 14 Replies
View Related
Dec 21, 2007
Hi :)
I have solved a m:n relationship by adding a junction table. The composite primary key in the junction table is made up of primary keys from the other two tables, and both of theses primary keys are set to autonumber.
My question is this: Is it correct to set both parts f the composite key in he junction table to number rather than autonumber?
I was thinking that since autonumber should be set only once, the junction table should just take on what ever value there is in the primary keys of the other two tables.
Kind Regards
Marie
View 3 Replies
View Related
Apr 8, 2008
Hello,
I have attached a screenshot of the relationships in (a section of) a database I am working on. Would anyone please be willing to view it and offer any suggestions as to whether is fully normalized or not? Here is the description:
An account [table, Account] consists of some money. That money is split-up and invested into several different places.
The funds that are available as choices are in Fund; the funds that are chosen are in InvestmentInstance. Different, unaffiliated accounts can invest in the same fund. I would like to keep track of when an account begins to invest in a fund and when it stops investing there (“InceptionDate” and “CloseDate”).
The generic, account-independent performance of a fund is kept in Price, where the values come from Yahoo! Finance or something like that.
Similar to Price, MarketValue stores the dollar-amount of an investment, specific to an account.
I think I have most of it set OK, but my main concern is about the relationship between InvestmentInstance and MarketValue. As the picture shows, I use InvestmentInstance’s “Id”* as the “one” in the one-to-many relationship, but “Id” is not a primary key. To get it to work, it was necessary to set the Index option to, “Yes (No Duplicates),” so that it was a unique index. Is this a bad practice? Should I just include “Id” as part of the compound primary key? I didn’t think making it part of the compound key was a good idea because then it would defeat the purpose of having “FundId” and “AccountId” control the uniqueness of records. Am I missing something important?
In other words, how can I correctly create a one-to-many relationship that begins in a juntion table (InvestmentInstance) and connects to a new table that was not involved in the normalized many-to-many relationship (MarketValue)?
Thank you!
* I know many like to include the table name in the ID, like, “InvestmentInstanceId,” and it is only my preference to keep it just as “Id” for a local key for simplicity, but add the table name when it becomes a foreign key. I feel “Id” is good enough for my purposes and it’s table location is usually clear from the context.
View 8 Replies
View Related
Aug 31, 2011
I am trying to create a main form that selects a series of values allowing the user to enter new data or edit current data (using other forms). I have a series of cascading combo boxes that populate accordingly and become visible after update. I can successfully create the series and add new data and edit data as in my code.
How to populate the junction table from the forms so for example when editing or adding a new site it not only populates the tblSites and the tblSitesContacts it also populates the tblClientSitesJunction with values from the frmSites and also cmbClients. If I use the wizard and try and add values from all 3 tables to form it doesn't work and I am not sure how to add the ClientID and SiteID manually to frmSites.
I have attached a copy of my database :
Attachment 4334
View 14 Replies
View Related
Nov 20, 2004
Hi, I have the following structure:
Products 1-M ORDER DETAILS M-1 ORDERS M-1 CUSTOMERS
I have ORDER DETAILS set as a junction table so that many products can be recorded within one order. All is good apart from when i go into ORDERS and create a new order. I click the subform which links to the ORDERDETAILS. I then pick a product number(look up from products table). The problem is this: In the ORDER DETAILS I want to display the unit price of this product simply by picking the product id.
Eventually this would form the basis of an order form where I can pick Product Id and have it display unit price.
Any ideas on this one, I'm sure its quite simple!!
Thanks in advance
View 1 Replies
View Related
Dec 14, 2007
Hi there.
I'm trying to archive records by using append and delete queries that span accross 12 tables.
the VBA will execute them all. However, I have a junction table near the end; It will be the Junction table, then a parent then a child table. I can get the junction table keys deleted but after that I can't get the Parent and the child table records deleted.
Does anyone know how to do this. the parent table can't delete any records because I've deleted the junction table keys first but if I delete the parent table keys access obviously wont let me because it's leaving orphan records.
I'm sure there's a way to do this. hope someone can help me.
Thanks.
View 2 Replies
View Related
Feb 3, 2014
trying to understand how to create a form with a Junction table. This design will allow a book to have more than 1 author.
Author table
Author ID
AuthorName
AuthorID table (Junction table)
AuthorFK
BookFK
Book Table
BookID
Book Name
Author table has a 1 to many relationship with AuthorID table and Book table has a 1 to many relationship with AuthorID table. Now how do you create the forms? Do I need a main form FrmAuthor , subform FrmBook and a frmAuthorID
View 8 Replies
View Related
Jun 11, 2015
New to access (and this forum) and working on a small db that includes the following tables:
tbl_Orders
tbl_Junction (Order Details)
tbl_Products
While the form itself is bound to tbl_Orders I decided to use unbound text boxes to enter the data (knowing I was in for some work!). Reason is that if textboxes were bound would need to use a subform with multiple combo boxes and that UI did not seem suitable for the application.
I’ve completed the code to add new records to tbl_Orders and tbl_Junction from the unbound textboxes. I'm using the VBA .AddNew method and is working well. Now I need to integrate code that will allow users to edit existing records while they are viewing them on the form.
I have the code to look up the record set I want to edit in tbl_Junction and am familiar with the .Edit method but the issue is that the required changes MAY include not just editing existing records but also deleting and adding new records. For example, the initial order may have been for Apples and Oranges and the revised order may change the number of Apples, eliminate Oranges entirely, and add some Bananas. So, it seems I need to .Edit Apples, .Delete Oranges, and .AddNew Bananas?
Should I try to write code to determine where I need to .Edit/.AddNew/.Delete? -- not looking forward to that. Thought maybe could delete the entire junction record set and then just .AddNew for everything per the revised order. That should work as the display on the form would be correct for the whole order. But maybe would cause a problem as would remove the FK in tbl_Junction that corresponds to the PK in tbl_Orders?
View 14 Replies
View Related
Feb 6, 2014
Attached is my many to many relationship setup. I would like to prevent the possibility of entering the same person more than once for the same training event. I am assuming that I do this by setting an index setting for a field the junction table?
View 3 Replies
View Related
Dec 8, 2013
I explain , I have 5 tables :
Class ( classID , classname,... )
Student (studid , firstname, lastname , classID ,....)
Course( courseid , coursename )
Term ( termid , termname , begindate , enddate )
Score ( scoreid , studid , courseid , termid , score)
Note :
1) A class can contain one or more students (one-to-many between Class and Student tables)
The table "Score" is a junction table between three tables : Student , Course and Term because it contains three foreign keys ( I could use a combination of 3 foreign keys to make a primary key ! ) .
The tables ; "Class", "Student", " Course ", "Term " already contain data for each table I created a data entry form .
My biggest problem is how to create a form to enter students' grades or scores for each student that belong to his class.
I do not know how to do it especially since the idea is that :
On a form I would like to use ComboBox to select a class that displays student's list from class selected and a ComboBox in same form to select course and another ComboBox to select a term and then enter grades or scores for each student
This is my general idea to enter students' grades. The rules are :
A class contains one or more students
Each student takes one or more subjects.
Each student gets scores for each subject and each term(quarter)
View 5 Replies
View Related
Feb 3, 2014
I've established a many to many relationship using a junction table.
So I have 3 tables (A for "materials", B for "batches", and J for "junction")
Form A is linked to table A, and contains a subtable linked to a query from table J. This allows me to input materials into table A and then list all of the batches it may be used in that are in table B. I successfully got this to input all the batches and materials combinations in table J.
Now on form B, which is linked to table B, displays the batch information, with the subtable J.
My problem, is that only the materials primary key is showing, not the other information that should be linked from table A.
View 14 Replies
View Related
Sep 26, 2013
So I have a form that has a sub form on it (based off of a junction table), indicating which category a particular project belongs to. It can belong to multiple categories:
ProjectID
100
Category
Admin
Finance
But I want to run a query to find all of the projects that are both admin and finance. When I do that now, it comes up as two separate records in the query, which is fine... but I have to know the projects before hand to find if it's in both, because it returns heaps of records, anything that says admin or finance... and that defeats the whole purpose. I want the query to show me JUST the projects that have both of those in their subform..
View 14 Replies
View Related
Jul 5, 2013
I'm jumping to the forms development and going to try what I need via queries and SQL. I'm trying to do a series of cascading combo boxes which have worked out fine, right till I hit the first junction table.
I will include the VBA code below as well as a screenshot but here's how it goes.
REGION cascades down to COUNTRY cascades down to PORT and then to CARRIER.
This is for a shipping program.
When it gets to PORT and are trying to cascade to the various CARRIER's thats where it hits a junction table of PORT_CARRIER.
Here is REGION to COUNTRY:
Me.cboCountry.RowSource = "SELECT COUNTRY.CNTRY_ID, COUNTRY.COUNTRY FROM COUNTRY " & _ " WHERE REG_ID = " & Nz(Me.cboRegion) & _
" ORDER BY COUNTRY"
Me.cboCountry = Null
COUNTRY to PORT :
Me.cboPort.RowSource = "SELECT PORT.PORT_ID, PORT.PORT FROM PORT " & _
" WHERE CNTRY_ID = " & Nz(Me.cboCountry) & _
" ORDER BY PORT"
Me.cboPort = Null
PORT to CARRIER:
Me.cboCarrier.RowSource = "SELECT PORT_CARR.PORT_ID, PORT_CARR.CARR_ID FROM PORT_CARR " &_ " WHERE PORT_ID = " & Nz(Me.cboPort) & _
" ORDER BY CARR_ID"
Me.cboCarrier = Null
PORT to CARRIER is where the problem is.
It populates the combo box, but with the ID numbers instead of actual CARRIER names.
(the Junction table are two PK fields and are lookups to PORT in PORT table and CARRIER in CARRIER table.)
Is there a magic spell for cascading combo boxes when you hit a junction table?
View 14 Replies
View Related
Apr 21, 2014
I would like to use a listbox set to multiselect to add records to a junction table. I've been using code to accomplish this with checkboxes (love how it looks and works) but after moving my tables to Office 365 as the backend, linkedto a local frontend, sql does not like this particular set up, and I do not have the time or knowledge to sort out why. So what I need is a step by step to look at the many, in this case possible roles a contact can have, and choose one or more, which then creates a record in the junction table with the contact id and role id.
I would prefer to not use a combobox on a continuous form because every time a user goes to select roles he would have to scroll through all the choices for each separate role.
View 5 Replies
View Related
Jun 24, 2014
Is there an easy way to auto-populate a Junction table [in access 2010] given the following two tables with a many-to-many relationship for Tasks? The two tables are
Table 1) tblTasks (TaskID (PK), Description), and
Table 2) tblMeasures (MeasureID (PK), Description, Tasks)
If JCTN table is JCTN_Tasks_Measures (TaskID, MeasureID), is there a way to populate when tblTasks(TaskID) == tblMeasures (Tasks)?
View 1 Replies
View Related
May 31, 2005
I've got a problem with a make-table query that creates a new table which is then populated with new values that relate back to the original table the trouble is the make-table won't run with that relationship in place.
Is there a way to delete the relationship then reinstate it after the query? or any other way round that someone can think of.
Thank You
View 1 Replies
View Related
Dec 4, 2013
I know my way around Access reasonably well but am by no means an expert. I have created a system that I use in my business for hardware/software requests, and was told to do it in Access. I did ask that we do it on SQL Server with a Web Front End, but we are where we are.
Now, I have been on a SQL query training course so I know the basics, but am a bit confused on this one since Access has been added to the mix.To make matters more frightening, this is Access 2003!
My tables are as follows:
[T_Request]
AT Reference (primary key, autonumber)
Forename
Surname
UserID, etc etc
[T_Hardwarelist]
ID (primary key, autonumber)
Make
Model
Description
[T_UserHardware]
AT Reference
ID
[Request] is inner joined to [UserHardware] on the AT Reference column.
[UserHardware] is inner joined to [Hardwarelist] on the ID column.
The select query I have is basic and does just what it says on the tin; it shows who has what hardware.The query:
Code:
SELECT T_UserHardware.[AT Reference], T_UserHardware.ID, T_HardwareList.Make, T_HardwareList.Model, T_HardwareList.Type, T_HardwareList.Description
FROM (T_Requests INNER JOIN T_UserHardware ON T_Requests.[AT Reference] = T_UserHardware.[AT Reference]) INNER JOIN T_HardwareList ON T_UserHardware.ID = T_HardwareList.ID
WHERE (((T_UserHardware.[AT Reference])=[Forms]![F_Request]![AT Reference]));
However, when it comes to adding NEW hardware to the requestI have a form called F_Request.I have a sub form called SF_Hardware.The SF_ Hardware subform runs the aforementioned query, and shows what hardware is assigned to the parent request.If I add new hardware via a dropdown on the form, it adds it in to [T_UserHardware], but it also adds another value on the [T_Hardwarelist] table. E.g. if I add a "HP Compaq 8200 Elite" (which is stored in [T_Hardwarelist]) it adds it to the [T_UserHardware] table correctly, but creates a second (third, fourth, fifth) entry on the [T_Hardwarelist] table for the same kit.
I think its confusing because of using a select query and might have to run a 2nd query on save or something like that, I had a working version before that had the make, model etc in both tables and didn't have a 3-way relationship. It'd be nice to have the request table with the high level info, a hardwarelist table with our catalog of kit, and a userhardware table just containing the ID and Reference for the hardware/request rather than duplicating the information.
View 2 Replies
View Related
Nov 25, 2014
I have a table with name, club members details ID etc. This is linked by a 1 to many link (ID) to a table containing details of membership subscription payments. One entry/row per membership period. This second table has DatePaid, Paid (Yes/No), Period. Period contains 2013-14, 2014-15 etc.
I can do a query for those that have Paid (Yes) but when I try one for those that have not Paid (No) or <>Yes I get no result. I only enter members when they have paid. I need a query to display those who have not paid for the 2014-15 period.
I can do it a long winded way copying 2 lists into Excel. One all members. One those who have paid. Then remove duplicates and those paid in 2013-14 leaves those not paid in 2014-15.
View 2 Replies
View Related
