Normalization: One-to-many Out Of A Junction Table Without A Primary Key
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 Replies
ADVERTISEMENT
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
Nov 20, 2007
I'm not a programmer and know very little about setting up access databases other than the very basics...however...due to my politics background, I'm setting them up for the local government....just my little disclaimer about why I'm asking stupid questions.
I am creating a database for multiple users who are of two sorts. The users deal with either the setting up of a contract, or with the payment of a contract. Both of these groups need to be able to access different information about the same contracts. What seems most logical to me is to create two tables in the same database, one for the contract, and one for the payment information and have both update when one or the other is worked on.
My basic set up for now is this:
Table One: contract information
Table Two: Payment information
Link Key: Serial Number from contracts
These are my questions:
1) is there a way to set this up so that the only way someone from the payments group can create a linked record is if a record with the same serial number already exists in the contracts table?
2)Is there a way I can set it up so that when someone in the payments group starts to input their information, they can find the serial number they want, and have certain fields show up? For example: I want them to be able to search for a serial number and have the contract name show up with all of the previously entered payment information, then have a new field each time they go into it for their next payment (if that makes sense).
3) When linking, hopefully in such a way as to do the above, do I need to have the same field name in both tables, or...? I'm a bit confused as to how this works as initially i created a field in both tables that would have the same serial number and tried to link those..and it didn't work.
Sorry if this is so confused...I'm sort of learning as I go along.
View 6 Replies
View Related
May 16, 2005
Hey buddays,
I have a contract-tracking database that has been in use for about a year. It used to be that every contract had the same number of periodical "applications" with it - the companies we worked with would send in 4 applications over time.
So, I had this set up:
tblCompanies (the companies with whom we work)
- CompanyID (Primary Key autonumber)
- CompanyName
- Address, contact info, etc.
tblContracts (the contracts we issue)
- ContractID (Primary Key autonumber)
- CompanyID (foreign Key)
- DateIssued
tblApplications (things companies send back to us over time)
- AppID (Primary Key autonumber)
- AppDesc (description of the application - something like "Application 1", "Application 2" , etc.
tblAppsRecvd (Join table/log of apps received)
- ContractID (foreign key to tblContracts, so we know which contract this applies to) (also a composite primary key, so we don't duplicate an app to a contract)
- AppID (foreign key to tblApplications, so we know which application we rcvd) (also a composite primary key, so we don't duplicate an app to a contract)
- DateRcvd
Pretty self explanatory. Everything was keen, and I was reaping the rewards of a job well done.
But now we've expanded out contract offerings. He have introduced 2 new types of contracts, and each contract has a different amount of applications associated with it.
Contract 1 has 4 apps.
Contract 2 has 2 apps.
Contract 3 can have 1 to 8 apps. (The last app before a contract closure is always considered "Final" - so if we get 5 apps, it would be apps 1, 2, 3, 4, and Final.)
I've tried implementing a couple different schemes, but none seem to be fitting the bill. The type of contract will dictate how many apps we receive - i.e. If we are using Contract 1, then we KNOW we'll recieve 4 apps over time. So, is this as simple as adding a "ContractTypeID" to the tblContracts with an appropriate tblContractType table, and expanding tblApplication to more apps, or is there a smarter scheme?
Thanks, buddays!
View 4 Replies
View Related
Sep 15, 2006
Im setting up a database that contains information about printed circuit boards that are being developed for our current project. The information that i need includes the following:
Board # - 3 digit number
Board Name -
Module # - 9-digit number
Module Name -
Project Leader - engineer responsible for design
In/Out/Oem - inhouse, outsourced, or purchased OEM
Designer - engineer or outsource contractor who designed the board
MFG/Assembler - company responsible for building the boards
Directory - file on network drive the drawings are located in
There can be mutiple boards in a single module. So i have 3 tables right now.
pcb_tbl - this includes all the info above minus the module info.
Module_tbl - this table includes the module number and name and responsible engineer, with the module number being the key.
module_pcb_tbl - this table lists the module number in the first field linked to the module table, and the pcb number in the second field linked to the PCB table.
Am i doing this right?
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 4, 2005
Greetings-
I am having trouble with the table design of my db.:confused:
What I am is a human resource supervisor trying to develope a database to
track the hundreds of actions that come in and out if my office on a weekly basis.
I am almost there, in regards to table setup and relationship setup, except I am stuck on something and any advive I can get would help tremendously.
Here is what is going on:
I need to be able to relate my customers to the actions they turn in..and track them, the actions, until complete.
I have attached my db and will answer any questions needed. Will someone please take a look at it and evaluate what you may think is needed to get the "ball" rolling.
Thank you for your help
DW
View 1 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
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
Apr 25, 2005
I am having trouble visualizing how to break out the following info into functional Tables
I have a table to hold Items
tblListG1Items
IDItemList
ITemNumber
ItemName
I then have a detail table
tblDetailG1Items
IDItemDetail
FKToItemList
ItemMFG
ItemMSP
ItemLead
Here is where I get lost.
Each item has a variable for each month
So item 1
January = .01
February = .03
ECT.
Once set the Variables rarely change, at most once per year.
I will need to be able to pull an item and based on what month it is pulled uses the variable to multiply several other factures.
My first Thought was,
tblVarItemList
IDVarList
FKToItemList
VarJanF
VarFebF
VarMarF
--And so on--
It just does not look right.
Any Suggestions on what might work Better. :confused:
View 6 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
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 3 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
Jun 16, 2006
Im planning a db client's dinner questionaire that will comprise of client's favorites (meals, games, transportation). Each client is allowed to bring a guest. So the client's will also be filling out "the same set" of questionaire for his/her guest.
Meaning, a user will need to fill out the same form twice, one for himself and one for his guest.
Now, my question is, should i have two tables. Client's questionaire table and Guest questionaire tables with foreign key in the guest's table showing who's guest the question belongs to?
Or should i have one table for everyone (since they are the same set of questions). Having one table seem like a wise idea since it's the same set of questions (don't want to have 2 duplicate tables with only one field different). Now, if i go with one table. How do i go about handling this? So that client's and guest's info are linked. and i can run a query to tell who's guest it is? I have never tried thsi before. Care to share your thoughts?
View 3 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
Sep 29, 2014
I am using this query to cluster a group of customers by monthly revenue. The issue is that I get duplicates. The customers appear more than once because the condition is valid more than once. How do I get access to select only once a field from Normalization sheet table??
SELECT [Cust Profile This Year].MSISDN, [Cust Profile This Year].[Total Revenue], [Normalization sheet].[Arpu Range] FROM [Normalization sheet], [Cust Profile This Year] WHERE ((([Cust Profile This Year].[Total Revenue])>=[Normalization sheet].[Arpu]));
View 3 Replies
View Related
