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.
I have a temporary table with Yes/No fields (F101,F102,F103,....etc). In another table i have a field called ProductCode with values (101,102,103,....etc) Is it possible to append(or update) the records of the temp table with criteria on "F" &ProductCode field? (For example if ProductCode=101 then F101 sets to yes)
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’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
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?
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
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:
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
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.
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.
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.
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!!
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.
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.
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
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?
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?
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)
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.
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..
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?
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.
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
how do i use conditional statement in sql? im using access 2003 as frontend and backend.
i would like to count the number of records in a table (ex. ENROLL) where there is only 1 instance of any value in a field (ex. STUDENT_NO) plus another condition where a given value (ex. 1) should exist in another field (ex. SECTION).
I am trying to set the following variable that will be used to create a PDF file in an already existing folder and name the file WorkorderIDddmmyyyy-hhnnss.pdf
The following variable setting creates the filenameddmmyyyy-hhnnssWorkorderID.pdf.
I just can't figure out how to construct it correctly.
Also, is there a way to dynamically create a folder if the folder doesn't already exist? I currently hard code the folder name but would really like to create the folder name based upon some other variable.
I have a form with several fields and combo boxes on which the user will select different criteria to generate a report.The default value for each of these fields/cbo's is "*", so if the user changes nothing from the default, I execute a basic "SELECT *" query.
If however the user enters some value (selects a specific date range, customer number, salesman,etc), I would like to 'easily' modify my query to accommodate the entered search criteria.Trying to construct SQL when there are many search options like this is difficult. I took a stab at using QueryDefs but I'm struggling to find example VBA code as an example.
I am trying to construct a query with a number of DCounts for a summary report, and each of those counts is to have more than one criteria.So far, the code below is an example of the code, which returns #Error in five rows
Code: DCount("[ID]","Attendance","[Absent]= True & " And [Date]=" Between [Forms]![DateSelect]![txtStartDate] And [Forms]![DateSelect]![txtEndDate]")
I have a feeling that it is the criteria for the date that I have got wrong, but I cannot see the wood for the trees at the moment.If it makes any difference, all of the DCounts will refer to one table and will all be within the same date range.
