Using Foreign Key To Get Related Values From One Table Onto Another Table?
Sep 25, 2013
I created 2 table that are linked to a link table so to get a many to many relationship.I have a table for locations and a table for parts.One location can have many parts and One part can belong to many locations, that is way the many to many relationship.
how to get values from the parts table to the Location table using the linked Primary Key in the foreign key linked field.The values also have to be interactive, if I change the value in the part table it needs to update in the location table.
What can I put in the field of the location table to get a specific value from the parts table?
I tried setting the field to calculated field and put the Dlookup function but that didn't work. The lookup wizard seems to be the solution, but I can only create combobox or list box and I need to select the value manually. I just need a value to be put automatically by looking at the foreign key.
If I put the Dlookup function on the default value, those it update if a value is changed in the part table?
View Replies
ADVERTISEMENT
Mar 27, 2014
I have two related tables. (related with AcqID)
tblAcq and tblAcqDetail
In tblAcq I have information about the delivery and in tblAcqDetail delivered articles, price and quantity.
tblAcq
[AcqID] ; [Contractor]; [TransportCosts]; [CustomDuties]; [SumWithoutVAT]; [LimSUM](this total cost of shipment Goods+other cost)
[Code].....
View 4 Replies
View Related
Nov 7, 2012
Is it possible for the primary key of one table to act as the foreign key in another table for more than one columns? What I'm trying to do is create a table for a Committee which will have 1 student and 5 professors! So Can I import the faculty ID for each of the 5 faculty members?When I try creating the second relationship, access automatically creates a new Faculty table for the relationship!
View 6 Replies
View Related
May 5, 2013
I have a table TO-det and another table DO-DET.The table DO-det will have details about all DO for each TOID record.Both have a common field name TOID The tables are related under ONE-MANY relationship.One TO-DET record can have many DO-DET record
Now I wanted to create a form where if i add a new record to TOID i must also be able to add data for DO-DET for that corresponding TOID.
View 1 Replies
View Related
Jul 10, 2012
I previously created 2 tables:
One lists all the stock symbols and company names = SYMBOL
The second table lists the purchase information for each stock = PURCHASE
I then created many queries, etc. using this data. Symbol is the key link between the various tables, queries, etc.
NOW that I understand the lookup wizard in the data type, I would like to change the symbol field in the purchase table to a Lookup field. I, of course, receive a message. I am told to delete the relationship with the other tables. If I remove the relationships and change the data type, can I then replace the relationship with out damaging all the queries and forms?
View 1 Replies
View Related
Jan 22, 2013
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.
View 4 Replies
View Related
Apr 20, 2006
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.
View 14 Replies
View Related
Oct 5, 2015
Can a table have two foreign keys?
View 1 Replies
View Related
Oct 2, 2014
How would one link a replication ID as a foreign key within a different table as they aren't numbers and not quite text?!
View 8 Replies
View Related
May 30, 2005
I`m designing a database in MS Access '97.
A lot of the tables I`m using have multiple primary key fields.
How is it posible to create one relationship between 2 fields in table A and 2 primary key fields in table B and force integrity?
thx in advance
View 4 Replies
View Related
Jan 14, 2008
okay, last question. I promise. You all have been so helpful, though...
I have been given the assignment of splitting down a table into two more tables(three in total). My question is this: Could I use the Table analyzer? If so, what are the shortcomings? I would like to have the other tables referenced by the key of the original table instead of a lookup field, whioch I'm not sure if I can do in the analyzer. What attracts me to the analyzer is that it would make the needed queries to join the new tables. If I don't use the analyzer, would I use a make table query to accomplish it? If so, would I have to change every existing query in the database that used that old table?
I know it's a lot, but any help in pointing me in the right direction would be much appreciated
View 1 Replies
View Related
Sep 22, 2007
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
View 2 Replies
View Related
Jun 17, 2014
Trying to use a form based on a table to choose a recordset of values, and pick the ID of that recordset to include in another table as a foreign key.
I am not sure how to even search for what I'm trying to do, though I've tried all the variants I can think of anyway.
The idea being that there are a set of values that are associated with each other, and generally found in conjunction with another set of data. So Table 1 contains data such as this, though this is greatly simplified from the numerics that are actually stored in these fields:
FooID Field 1 Field 2 Field 3 etc, etc...
1 x y z
2 d y z
3 x y q
4 x r P
.
.
.
This data is then associated with the information in table 2, and rather than repeat fields 1-whatever in table 2 I want to use the FooID in table 2 to store the link to all of the subsequent field data. Normalization as I understand it in other words.
I cannot figure out how to pull the table 1 data into a datasheet form and allow someone to choose one of the lines of data, store the FooID into an unbound text box, or some other method, and then run an update query on table 2 to update a field with the FooID for the 12-72 records that are in that table.
Would this make more sense as a combo box in a single form? I've been trying to do this with a datasheet, but I think I'm too limited in appending a check box to an individual recordset this way, or at least I've not managed to get it to work so far anyway.
View 2 Replies
View Related
Aug 29, 2013
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]
View 3 Replies
View Related
Mar 26, 2013
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 Related
Apr 25, 2015
I used to import excel data into access successfully, many times but now I have to import excel data into an existing Access table with foreign key fields, which makes me problems.
Its just doesn't work...and Im sure the forien key fields are the prob cause, the other fields are going well ...
View 1 Replies
View Related
Mar 4, 2015
I am looking for a query that will return records from a table that have related records in another table. Opposite to the Unmatched Query Wizard.I have two tables: tblSupplier and tblSupplierProducts.The two tables are related by the field "SupplierId".I need the query to only return Suppliers that have Products.
View 3 Replies
View Related
Nov 21, 2006
Hello
Could you help me ?
My problem is :
I'm developing a ms database for the a "repair service" .
I created a table witch contains basic data from customer, including its unique ID number (key field), name and contacts ...
Now I need to store the "repair service" data from each costumer ( its time consumption, replaced materials...) in a separate table but, If I'm thinking well, I must have a table for each costumer so, I will need to link each individual "repair service" data table from each costumer to each costumer itself.
Do you know how to do this ?
Lots of thanks in advance,
Miguel
View 4 Replies
View Related
Jun 26, 2007
I have two databases, one containing the tables and one containing the forms and queries. The forms/query database is built from linked tables from the tables database. I would like to expand one of the fields from 7 characters to 9 characters. I planned on doing the in the table database, then updating the linked table. However, the table I need to alter has relationships with about 10 other tables and I get the (you cannot make changes to a related table error message). Is there a way I can make this change without deleting all the relationships?
For future reference, should the relationships have been created in the form/query database?
Thanks, N
View 3 Replies
View Related
Mar 13, 2008
Greetings to everyone,
I'm building a database which will generate work orders, and those have a opening date and closing date, besides that it has a work order number that is of the following format:
YYYY/ NNN (being the NNN the work order number in that year)
to create the number of the work order I'm trying to fill the WOYear field
with the year of the WODate field...but I'm not being able to do it...
How should I do it?
Thanks.
View 5 Replies
View Related
May 29, 2005
Hi :confused:
I am trying to set up a query. i have to work out the cost of a phone call with the use of three fields--
i have: lenght of call, country call code-e.g england is +44(these are fields in my query) and then the cost per minute values for each country call code(but these are in a table)
e.g.Country CodeRegion Cost per minute
+1 United States, Canada, Caribbean£0.20
+20Egypt £0.30
+212Morocco £0.30
How would i set up a query to solve this problem?#Can someone please help me???
View 1 Replies
View Related
Dec 9, 2007
I have a very simple database with two tables. One stores Schools that I work for and the other holds people that work at that school.
I have a form that displays information from the schools table and I have a button that I want have open a form that displays the people that are related to that school.
THe problem is I can't figure out how to limit the recordset to just the people from the school record I was just viewing.
For example. I am looking at a record with School ID 1. I want to run a query on the People table that will return just the people that work for school id 1. I just can't figure out the criteria part.
Any help is appreciated.
Thanks
View 3 Replies
View Related
Nov 11, 2005
Hi
I want to be able to invoke a form from a main form that allows me to add, delete or amend records in a table related to the one updated by the main form.
As many details records are being updated, I want to retain the value of a couple of foreign keys (but be able to amend them if required) based on the value of the previous record.
I have looked at the Dlookup function and wonder if this is what I should be using. If it is, I am having trouble trying to understand how you could differentiate between the first invocation of the pop up form and any subsequent table entries. The way I understand this function is that to get the value of the previous record in the table you use "ID-1", but this would almost certainly lead to an error for records being updated for a new master record, or if the table happended to be empty then there would be no ID-1 record.
Any help would be greatly appreciated.
Thanks
View 3 Replies
View Related
Aug 30, 2007
Hi, I'm a total newbie at Access, and know nothing about scripts. I've been operating at the level of using the Wizards and drop-down menus. I am trying to create a report that does two things:
1. Displays the results of queries that sum data from a table (I think I have this figured) and
2. Displays those sums as a percentage of a number that is input each time the report is run. (This is only one number that comes from a totally different place and has no prior relation to the data.)
Any help/advice that anyone could offer would be much appreciated!
Thanks!
View 5 Replies
View Related
Dec 14, 2004
Hello,
I have two related table: table_Company and table_contact. Now i have:
Table_Contact Table_Company
James Bolton -----------> MacDonalds
Hellen Kidman ----------> MacDonalds
Now, i wanna have one "MacDonalds" in my Company table, and delete the others "MacDonalds" and still have the relationship between my contacts and their company.
View 4 Replies
View Related
Sep 5, 2006
Hello,
I have a form based on 1 table and I am trying to find out if I can display data from another table if I have a relationship between the 2 tables?
Thanks
View 1 Replies
View Related
