hi
i need to create a table, i've figured out to make LastName the primary key. But im not so sure about the foreign key, and whether it is necessary to put it into the table in order for all my tables to relate? Sorry if this doesn't make much sense.
Also, to clarify.. 3NF primary keys in one table cannot be used in another table??
thanks
Hi there,
I am trying to model my database to create relationships between different tables in my database. During the process of doing so I ended up having one primary key in table A linked with to foreign keys in two different tables ( table B & table C) and both of those two table are junction tables to break out many to many between many other tables
Now for some reason!!! I feel that there is something wrong with my logic mapping and modeling of the relationships between tables due the fact of having one primary key linked to two foreign keys in two different tables :(
Is my intuition is right? should such case be considered as indicative of wrong modeling of relationships between tables in a single database
And if so what is the disadvantages of that link (talking down the road) when the database if fully populated? :confused:
By the way I am new member and new to database, so please take it easy on me :o
Many thanks
I've got a table - "Products" - in my database, with a text field - "Info" - which contains info about products.
Within this field I would like to have footnotes. To do this, I think the best way to do it is by putting numbers inside the text at the location of where I want the footnote to refer to. These numbers will actually be foreign keys to a table called 'Footnotes'.
I can then program the forms and reports to show any numbers as superscripts or whatever.
(Of course, if the user will actually want to insert a number into the text field which is NOT a reference to a footnote, I will have to make a workaround e.g. by making access put a symbol in front of the number, so access will know the number is just part of the text (and I will program the form to not show the symbol in front of the number)).
Is it wrong to have foreign keys within a text field? I think if executed correctly, it should work perfectly.
Right now, I have 4 related tables. There's a table with companies, one with people involved with companies, a table linking the two by having foreign keys of the company number and people names, and a table that indicates directors and their alternates.
Since there's a one-to-many relationship for companies/people to company-peopleID (A person can run multiple companies, a company has multiple directors, it's easier this way), a person's name can appear multiple times, as can a company, within that table.
In a company, a director may or may not have 1 and only 1 alternate director to him/herself. So, I thought the easiest way was to put an autonumber in the Company-personID table and have a table (alternates) that had two fields, "alternate" and "director", both using that autonumber to link them. However, it appears as though I can't link the same primary key twice to two foreign keys of the same table.
[URL]
Hi,
While designing a database, I came up with 3 different foreign keys in one table. I am afraid this is not right!
Is it possible to have this number of FK in one table or does that suggest a design flaw?
Any help will be very much appreciated.
B
I'm not new to access, and I understand normalization at pretty much all its levels, but right now I'm curious about a situation that I have just come across. The first time I've been in something like this, so I thought I would ask.
This is the setup.
I have employees. They are apart of a shift and a budget. Shifts and budgets are completely independent of each other.
The database needs to keep track of shifts and budgets over time. Therefore, 1 to many relationship to shift table and budget table.
But, the database also needs to keep track of attendance. And the user wants attendance tracked by Shift and Budget.
Shift and budget are completely independent of each other.
Currently, I have the relationships set up like this.
Employee (1) ---(Many) Budget (1)------(Many) Attendance
Employee (1) ---(Many) Shift (1)--------(Many) Attendance
When a new attendance record needs to be added, both the key to the budget and shift are added to the attendance table. The key chosen is dependant onthe Employee chosen, and whether the budget and shift are the CURRENT budget and shift that the employee is apart of.
I thought of running a query showing budget and shift by date descending, so that the latest budget / shift would be displayed, and thus the most current (SUPPOSEDLY) But, if a user put in a different date, or screwed up on the date, then the incorrect shift and/or budget would be displayed.
Date stamping an entry was an option, but there needed to also be a user entered date as well, to specify WHEN a user began working in that specific budget / shift. Therefore two date entries would be required. Duplicate entries in most cases.
I therefore decided to go with a true/false yes/no checkbox. Where the current budget or shift would be checked, and all non current ones would be unchecked (false).
Currently, this is how the systems works. And it works well. But it is dependent on some form code I created to set the yes/no checkbox to true/false depending on the situation.
I DON"T like doing this. Am I missing a way to do this "correctly" where by Access would do this "automagically" instead of via my trick.
The ONLY issue really, is that when a new attendance incident occurs, the user needs to put in the incident to the approbriate shift / budget. And if the current shift / budget could automatically be displayed without user intervention (IE user has to pick the shift / budget from drop down box after looking up info etc etc) since the current information SHOULD be known.
I've never done a table setup where two foreign keys are the many side of the relationship in a single table.
It is currently working fine, and seems to do well, but I wanted to make sure with others who might have had this experience. And also, any "advice" / "cautions" for this kind of situation so I don't step into it deep and have to fix it later.
More on the lot materials management program.
I have a series of related table:
Builder
Subdivision
Lot Number
Lot Option
Lot Materials Details
The [Lot Materials Details] table is populated by appending data from the [House Type Details] table tree as follows:
Builder
House Type
Elevation
Options Per Elevation
House Type Materials Details
The filtered Option Table in both branches determine which [House Type Materials Details] records get appended to the [Lot Materials Details] table.
The question is:
Is there any advantage, other than simpler queries later on, to adding the primary key from the [House Type Materials Details] table to the [Lot Materials Details] table.
It’s redundant because the [Lot Options Table] contains the [Options Per Elevation] primary key.
Any thoughts?
Wow, I've found some good stuff here already... Thanks.
PROBLEM:
I have a table(we'll call it shipping) that has two columns FK'ed to another table (locations). My problem is I have no idea how to pull the information from the locations table into the shipping table.
DESCRIPTION:
My shipping table has a source and a destination column. Now, I have a fixed location list that applies to both columns and I'd like to avoid having a source tbl and a destination tbl... I'm an Access Neophyte to say the least (for now at least) but I have managed to put together a querie that pulls out most of the information that I need (name, phone number, yadda, yadda, yadda) but I can't seem to correctly link the FK's for the sources to the locations table as well as the FK's for the destinations to the locations table. When I run my querie I get an error or my Source(destination) column will display the correct information but the Destination(source) column will display ALL of the locations in the location table. Here's what I mean:
Source destination
detroit toledo
detroit cleveland
detroit San Diego
detroit London
detroit Paris
detroit detroit
Paris toledo
Paris cleveland
Paris San Diego
Paris London
Paris Paris
Paris detroit
.
.
.
The first and second source was detroit and Paris but the Destinations list all of the locations I have in the table.
How can I link two FK's in one table to another table in a querie?
Thanks.
Can a table have two foreign keys?
View 1 Replies View RelatedHi,
I have to create a database for work and for my sins I have to use Access. We are running 2007 but I'm having some difficulty in creating foreign keys in my linking table.
I've searched their help guide and looked on the net but I can't seem to see how I physically create foreign keys.
My structure looks like this:
Customer
CustID (autonumber)
Cust Name
Cust contact
Cust email
cust version
no of users
other info
Modules
ModID (autonumber)
Module name
licensed module? (tick box)
MiscSoftware
MiscID (autonumber)
Software
Installed on live
CustMod
ID
CustID
ModID
MiscID
Thanks
I want to create a combo box filter - easy enough usually
However - the field I want to use is a foreign key field which, while it displays a meaninful description - it stores the id rather than the text
I want to create a combobox filter, but when I do all I see is the id's - which are meaningless to the user.
Is there a way to get these foreign key filters to display a meaningful text description??
I have what I think is a normalised database that uses foreign keys quite a bit.
For example if I had a 'contracts' table which refers to 'clients', then there would be a tblClients, where each client has a primary key. The tblContracts would then refer to the relevant client via that clients FK only which would be linked to the client's PK in tblClients via a one-to-many relationship.
If I need any user for any purpose to see contract related information that makes sense to a human being, I simply construct a query with the necessary relationships that will show client information alongside contract information by substituting tblContracts' client FK with required information from tblClients via the appropriate relationships.
I think that is reasonably basic stuff (hopefully correct practice!)
But what about when I come to import a block of new data that needs to go into tblContracts? I'm not going to be given a list of client keys (obviously) I'm going to given their real names.
MS Access has (in theory) all the information it needs (via the relationships) to substitute client IDs (keys) for their real names and thus slot these IDs into tblContracts with the new data as appropriate, but how do I make it do this? (I know it could kick out errors if there are any duplicate client names, but let's put that to one side for a moment).
I need some help creating a simple datebase to store my documents and other knowledge objects. There are two types of
knowledge objects I need to store: Documents and Questions/Answers. For this reason I have created three tables: Index,
Question, and Document.
The problems I am encountering is that the ID field in the Index Table is the Primary and relates to the ID field in the
other two tables. So basically when I am entering information it can either be a question or document. I want to create a
for so that when its a question that I am entering it records the information in the question table and when its a document
I am entering it records the information in the document table.
I'm a beginner at using access and don't know VB or SQL. Any help would be appreciated.
Table Structure
Index Table
ID (Primary Key)
Link
Type
Source
Reference
Question Table
ID (Foreign Key)
Question
Answer
Document Table
ID (Foreign Key)
Title
Summary
Notes
I'm developing a simple sporting records db. I have a 'Competitor' table listing competitors as follows: CompetitorPK, Name, Division, Club. I have a 'Contest' table listing contests as follows: ContestPK, Competitor1_FK, Competitor2_FK, Winner_FK, Score etc. My question is have I modelled tables correctly i.e. CompetitorPK will appear in three columns of Contest table. How do I define this relationship? What alternative is there to what I have done.I intend to use forms to populate both tables (independantly obviously).
View 4 Replies View RelatedI'm trying to understand how data is passed using foreign keys.
I'm using Allen Browne's 'Don't use Yes/No fields to store preferences' at: [URL] ....
I've also downloaded his sample DB, RelationBasics, to use as a guide.
Attached is my version of the Student / Sport DB as described on the webpage.
I use 2010 at home (saved as 2003 version) and 2003 at work (JPNSE OS). Both result in the same thing.
The problem I'm having is I cannot get the actual sports to display in the combo-box, only the Sport_ID number.
I've tried building both forms with and without actual data in the TBL_Student & TBL_Sports tables, but no mater what the result is the same.
I am using access 2007
i have a materialsUsed tbl that list all the materials used in a job and the quantity of each.
(ID
Material Code
Quantity
Unit of Measure)
I also have 2 look-up tables - 1 for the material codes and 1 for the units of measure
I have another table, priceperunitofMeasure
id
material Code (FK to material lookup tbl)
Unit of Measure (FK to unit of measure tbl)
Price
In my form Users will select material code from combo box - enter the quantity, and select a unit of measure from a combo box.
For example: they could enter the following :
Paint 2 gallons
Paint 3 tubes
Paint 1 pint
How do I lookup the price per unit of measure for each of these records in the price per unit of meassure table? The price per unit of measure table has 2 foreign keys.
I have two tables in my Db: tblMaster & tblAddresses. They are joined with a one-to-many relationship, with the addresses being the "one" side. Many entries in tblMaster have the same address.
I use a form, based on a query, to add records which creates the new vendor in tblMaster, and (if I have contact details), a record in tblAddresses. The problem is that while the PK is created in tblAddresses once I add the address info, the related FK field in tblMaster is not populated with that #.
I have two tables tblPatients (pkUMRN) and tblAdmissions (pkAdmissionID, fkUMRNAdmission). pkUMRN is not auto-number, but is an 8-digit alphanumeric code unique to each tblPatients record. I have set up a one-to-many relationship between tblPatients_pkUMRN and tblAdmissions_fkUMRNAdmissions (each patient can have multiple admissions).
Is it possible to allow the entry of a new tblAdmissions_fkUMRNAdmissions to create a new tblPatients_pkUMRN? IE can you create a new primary key from a new foreign key?
Along a similar line, if the primary key already exists, how would you autofill a form with data from tblPatients when the tblAdmissions_fkUMRNAdmissions is entered in to the table?
i have made two tables with data from an excel sheet. The excel sheet has many duplicates and im trying to eliminate this. The tables are:
tblTasks and tblTeam. Both have autoincrementing primary keys, and the tblTasks table has the TeamID (primary key from tblTeam) as a foreign key.
My question is, how do i populate the TeamID field via perhaps a query, as it is blank on all records. I have over 5000 tasks so a manual approach is what im trying to avoid. A sample of the fields in the tables is as follows:
tblTasks
TaskID - PK
Task Name
Team ID - FK
tblTeam
TeamID - PK
Team Name
there's also a Staff table. tblTeam has a one to many relationship with the Staff table. tblTeam also has a one to many relationship with the tblTasks table.
In a Related Tables, you need to enter a FK to link to the other table.You either key in the FG from memory..The standard procedure is to design the field as combo box AND use SQL to select the field that will input the ID of the other table. So, if you want to input foreign key 3 , the combo box will display what 3 is then when you selected Access will insert 3 for you.
My question is since I can later on, edit the table and change the the value to another value, and mess the whole thing up!!! HOW can I lock my first choice so it will stay unedited ? My second question is: Is this the only way to input the FK if you do not remember the exact ID number or there are thousands of records in the related table.?
Hello,
I'm designing a database that is used both in the office and field and it has two tables - an "office" table and a "field" table. The office table is a list of addresses, cities, and counties, each with a custom-designed key field. The field table contains equipment information at each location specified in the office table.
What I want to do is set up the database so that field personnel can copy an updated office table (with new locations added or old locations removed) into their databse and click something and have the tables "resync" with each other - the field table automatically adding new records to match those in the office table or delete records it has that it doesn't find in the office table...
Any thoughts or helpful links?
Hi,
I am trying to join 2 tables with composite keys - each of them have the same 4 columns as part of its key - date/person/problem/date sent.
Table 1 has 5 columns (4 key columns + 1), Table 2 has the same 5 columns plus 1 additional column (Comments).
I am trying to match the Comments column in Table 2 to Table 1 and show the output in another table.
Currently, I've related the 4 columns in each table to each other, each relationship defined as including all records from Table 1 and only the records from Table 2 where the fields are equal.
In the fields, I have 5 Columns from Table 1 and the last column (Comments) from Table 2. When I run the query, I get all the records from Table 1, but not all of the Comments that match from Table 2 are shown.
Any Ideas on how to improve this link?
Thanks!!!
Hi all, im hoping someone can help. This is the problem.
I have 3 tables that have tax payer information for 1 year broken up into 4 months, 1 table for each 4 month chunk. all 3 tables mostly have the same taxpayers, but each table has payers that are not in 1 or both of the other tables. each table has a 2 part primary key, the business number is the first part, and branch number for businesses with chains. the next 4 fields are each month of taxes paid. heres the design of each table.
Q1
ID 1ID2Data1 Data2 Data3
10
20
31
40
50
Q2
ID1ID2Data1 Data2 Data3
20
40
61
81
Q3
31Data1 Data2 Data3
40
61
81
90
100
is there an easy way to merge all of this data into 1 table easily. ive tried using make table or append queries, but it only addes the information where the IDs are in each table. this leaves out the ids that are in only 1 or 2 tables.
if you need me to clarify or explain anything else, please let me know. i can post screenies or the design, but obviously, not the data.
I am creating a bridge table to get rid of redundant data. I am doing it by making a composite of the primary keys from the two tables I am bridging.
The error message when I get to a certain point is "You cannot add or change a record because a related record is required in table tblMachCent." Both the numbers I am using to create this composite key are in the tables necessary, so I am not sure why I am getting this problem. Any suggestions??
So, I have two Primary Keys in my table, how then do I link another tables to it?
tblAdmissions
PatientID Number Primary Key
Visits Number Primary Key
tblRoomPhoneNumbers
PhoneID AutoNumber Primary Key
I've built a system with around 20 tables in it. All of these use the standard ID field offered by default by Access as the primary key. A friend with a background in database design and development for large corporates using Oracle has reviewed the database (he is going to do some VBA programming for me) and suggested that we use more appropriate primary keys based on data attributes.
He has read "somewhere" that there are problems with the standard ID fields and that occasionally the fields are renumbered/reindexed and that this can cause problems. There are a fair number of queries and some complex forms which will need to be updated for these changes but it would be a shame to go through all this work if it isn't really.
Should we use the standard Access IDs and is it worth a chunk of effort to change from where we are now?