Queries :: Importing Data Where Foreign Keys Are Involved
Jul 10, 2014
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).
View Replies
ADVERTISEMENT
Jan 25, 2015
I'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.
View 2 Replies
View Related
Aug 13, 2013
I know that i can edit contents in a single table query and it will update the data in the underlying table, but how do i gain the ability to change data in a query when 3 tables are involved?
View 4 Replies
View Related
Jan 26, 2007
:confused: I have a database of historical baseball data.
It contains 5 tables:
Players: (Full Name, Last Name, First Name, Primary position)
Year: (Single field, w/autonumber)
Data: (many fields of specific data)
Position: (C thru DH)
Team: (1-30)
Each table has a unique key, (auto-generated)
The database contains 4 years worth of specific data, (2003-2006)
The main data table has lookups to the other tables built in to the current table
First run through shows the DB to operating pretty efficiently, (although more time will tell more, NOTE: I am a level 2 rookie).
PROBLEM:
I now need to import/update/append the tables with 2007 projected data, and then at the end of this year coming up, I will need to do the same with actual data, and so on.......
The data that is coming into the DB does not contain MATCHING PRIMARY KEY:
The Team Name, (with age as an added field)
Player Names, (all full,last,first,primary position)
The data, (all fields match name and data type)
Year, (2007, + I will be adding a field that denotes actual or projected).
HOW do I update the tables and assign the primary keys to all the table's matching names while doing so and assign the correct/matching Pirmary Key to the updated records.
HOW do I verify that the update was correct without scrolling through 2500 data rows and 937 players?
Any and all help would be greatly appreciated
Thanks
Doug
View 1 Replies
View Related
Aug 25, 2006
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
View 7 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
Jul 8, 2005
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?
View 2 Replies
View Related
Sep 25, 2006
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
View 2 Replies
View Related
Aug 5, 2007
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.
View 5 Replies
View Related
Oct 5, 2015
Can a table have two foreign keys?
View 1 Replies
View Related
Jun 14, 2007
Hi,
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
View 10 Replies
View Related
May 30, 2012
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??
View 5 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
Aug 28, 2013
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.
View 8 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
May 4, 2006
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
View 1 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
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
Mar 4, 2015
I am getting "Truncation Error" while importing data from Excel to access 2007. I have tried changing the format to short text but the result is the same.
View 3 Replies
View Related
Apr 21, 2013
I created a database. exported some Excel data into Access tables, created a number of lookups, default values etc. and created a pretty simple query.
My problem is that after importing and tweaking the data, when I add new records, my queries do not pick them up!
I have tried:
1. saving, closing, opening and re-running the queries.
2. putting an Nz expression for each field in the query as I read that null values may cause a problem.
3. wrote the query again, field by field to see if all records were received.
4. Exported the table back to Excel and imported to a new Access table in my database
None of this works.
View 7 Replies
View Related
Sep 29, 2014
I have an access file in which have a table name dataupdated
I have an excel 2007 file(Datanew) which have a sheet name data
I want to update table dataupdated at regular period
How can I create a query to import data from excel while file path and sheet name is always same and other steps to import data is same every time...
View 5 Replies
View Related
Jun 16, 2006
I have an autolookup that changes the numbers 2, 4 and 6 into P, M, and D so they look easier for my purpose. However, when I use a select query to find the max number for each criteria it shows a number not the letter. Do lookups stop working when you apply maths or can i change this.
View 6 Replies
View Related
Jul 5, 2013
I have been tasked with importing data from a clocking on system to analyse worked hours.
There is no option to amend the format of the export which is not great
surnameforenameField2Field3xxx
xxx
11/06/201319.18
xxx
xxx
11/06/20135.37+
xxx
xxx
12/06/20135.41+
xxx
xxx
12/06/201319.21
The plus indicates a night shift worker
eg started at 19.18 and finished at 5.37 on the 12th
so for every record I have staff id , date, and clock time ( I have stripped out the + ) and created a yes/no field to identify the records where field2 should actually be field2+1
I have sat in query design screen for ages and cant think how on earth I am going to calculate hours worked for a given staff member and date combination.
for every combination of staff id and date there should be 2 records - a clock in and a clock out
I thought about creating new fields clockin and clockout but struggling to see how I can link the 2 "paired" records together
vb script with dlookup for every record ?
View 10 Replies
View Related
Jul 1, 2007
I've sent a post about this before and got some of the answer from 'Dennisk'- thanks.
I want to replace composite keys with a primary. I've just made an autonumber field on one table for my new primary key and already populated it. On the foreign table ive made a new field with 'number' datatype; this field is empty at the moment.
I'm not sure how to update the foriegn tables new field with the primary tables data. I've saw it before and remember it being easy, just can't remember how to do it.
I obviously want the numbers in the primary key field to relate to the correct records in the foriegn table.
Anyone know what the query is or if there's another way?
Thanks:confused:
View 1 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
Jun 10, 2014
I noticed that when I made a few changes to one of my queries, the results in my data sheet view were in some sort of Asian looking language. See below. Why is it doing this? I've only been using English with this program and have not changed any of my settings so I'm very confused. Also the other fields are in English. This one field is the only one I'm having issues with.
椋
椌
æ¤
æ¤
è„ž
踖
è¼™
è¼¥
[code]...
View 8 Replies
View Related
