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.
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).
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
I made a database that was working fine. It was only after I had finished the database, and data was being entered, that I was informed that the field I designated as the primary key would not work. Origanally, I used 'OffCit' as the single primary key. Now they tell me that one 'OffCit' can be entered several times for different reasons. To solve this, I thought about using autonumbers but have been told, and seen, that this may not be a good idea. So, I am trying to use multiple primary keys. So far I have had no luck. I have attached a screen shot of the tables and relationships. I have 'OffCit', 'AreaofLaw', and 'EffectiveDate' as the primary keys. The same 'OffCit' and 'AreaofLaw' will be used several times in different records. That is the reason for the third primary key. I am wondering if I have set this up right or if there is something I overlooked. Perhaps someone has an idea that may work better. Thanks for any input.
I have the following table: YieldID |ProductID | Year | Month| Yield
I want to make sure that it is not possible to insert the same date (so, year and month together) twice for each productID. But at the other hand, it should be possible to insert the same date but then for different productIDs. How can I make sure that Access does this? Can I use (multiple) primary keys to enforce this? Thank you, Stacey
I have tried searching the forums, but couldn't locate any relevant information. I have an assignment at the moment and am meant to have 8 tables in my data base, two of which are 'User' and 'Hardware' which have the following fields:
Now I am meant to have another table called 'Hardware Config' consisting of the Primary Key fields from the two tables above, and they are both meant to be Primary Keys in this table.
I have been able to make the 'Hardware Config' table, but I don't know how to make more than one Primary Key... any ideas?
I have a table that has the following fields: Symbol, DeliveryMonth, DDate, Close. This table has information for a large number of different commodities. I am hoping to run a query against the table and find breakouts. I am working on the actual query but my question is this: If I have only one table do I need to have a primary key? There will be no relationships established so what is the need for a primary key unless you plan to link tables together? If it is always a good idea to have a primary key then I will have to have the Symbol, Delievery Month and the DDate together be the key since without all three there would be duplicates. Each symbol has many different deliveryMonths, each DeliveryMonth has many symbols, and each date has many Symbols. SO it takes all three together to get a unique value. Will having a primary key in this scenario help me with writing queries? I appreciate all of your wisdom in explaining how this table should be organized. Thanks:)
Looking at a database that someone wlse has created I see that numerous tables have mulitple primary keys. How is this possible? If i try and allocate a primary key it will only let me do it for one field.
I think what I actually want to do is create composite keys but if I set the key then put it to duplicates allowed it says that the primary key is not allowed. Am I mixing up indexes and keys??? How do you assign a composite key
I have a table where each record is unique if at least one of three variables is different. The three variables in my case are each integers and are: YearRouteSegment.
Now each of these individual variables allows (and has) duplicates, but if the thing is working right, no pair of records in this table should have all three of these variables with identical values. Is this table a candidate for using multiple primary keys?
I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...
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.
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.
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.
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
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?
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
I have a tab control on a form, and I want to use "Hotkeys' to get from one page to another (or more specifically, to toggle the visibility of the pages).
So, I set up the tab control with the pages I want hidden set with visible=No. Then I enable the Form.KeyPreview, so that the form will get a chance to look at all the key presses.
Lastly I have a Form.KeyDown handler, that looks like this:
Code: Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer) MsgBox "Key pressed (1): Shift value is " & Format(Shift) ' Detect Hot-keys for the pages in the MultiPage wizard, and make them Visible or not visible ' P/D/X/S/R/H/C If Shift = acAltMask + acShiftMask Then
[Code] ....
This is early on in the design iteration - more will happen with the pages, but this is an easy way of reviewing various aspects of a project.
So what happens?
I put a breakpoint at the first If statement, and sure enough, it picks up the Ctrl key or the Alt key, whenever they are pressed. (I need to use the mouse to clear the msgbox, naturally!) When I press both of them (Ctrl/Alt) I get the required value of 6, but I never seem to get to the second msgbox. In addition, if I comment out the first message box, I also never seem to get to the second msgbox (ie the point where the combination has been detected.
KeyDown obviously has to fire for each component of a HotKey combination, and the Shift parameter has been shown to be cumulative. The only thing I can think of is that somehow I need to turn off keypress processing somewhere else (Used to be possible to use Cancel to do this, I seem to recall).
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.
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 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.
