Avoid Multiple Entry For Foreign Key?
Sep 8, 2014
I have a master to child table relation which is one to many. Each one is represented by a different form.
The problem is, the user enters the primary key "JobID" on the master form, then he needs to enter a few records on the child list with the same "JobID" which is the foreign key in that case. I don't want the user to enter the "JobID" every time he enters a new record in the child form.
Is there a way I can copy the primary key to the foreign key each time the user enters a new record in the child form?
View Replies
ADVERTISEMENT
Sep 8, 2014
I have a master to child table relation which is one to many. Each one is represented by a different form.
The problem is, the user enters the primary key "JobID" on the master form, then he needs to enter a few records on the child list with the same "JobID" which is the foreign key in that case. I don't want the user to enter the "JobID" every time he enters a new record in the child form.
Is there a way I can copy the primary key to the foreign key each time the user enters a new record in the child form?
View 8 Replies
View Related
Jun 14, 2013
I have an Access 2007 mydb.accdb file. If I click on it, it starts up fine. If I click on it again, then focus goes to the already open db.
If I do the same but with the .accde of the same db, for each click a new instance opens. This is not good, in that a single user then can have multiple instances running without noticing it, and that might mess things up. What Windows setting to use, to convince Win XP that only one instance of mydb.accde should run, similar to the .accdb?
View 2 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
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
Sep 23, 2004
I am trying to create a movie database that uses a table and data entry/view form for films (title, year, director, and actors, etc), and another table that uses a data entry/ record view form for each actor including bio information and films that have appeared in.
The problem I am trying to solve is how I can relate the two tables by entering the film/actor information on the movie form, and have each film an actor has appeared in show up as a list on the actor form. Every time an actor’s name is added to the film table, I want the title of that film to be added to the list of films the actor has appeared in when viewing their bio form.
If I have a single field for “actors” on the film form, it creates an inexact match for relational purposes, due to multiple names being listed. If I use multiple fields (“actor 1, actor 2, actor 3”, etc) I have both the relationship problem and the inevitability that I will not have enough fields on the form for the data entry person to add all actors.
Is there a way to create a relationship between the tables using an inexact match? Is there some way to have a combo or list box for each actor nested in a single field? Would the tables still relate correctly?
Any help you can provide would be greatly appreciated.
View 2 Replies
View Related
Feb 3, 2008
Im doing a late note record entry for my school.
Basically a student is allowed to be late three times before they get detention.
I want to record
1. Student Name
2. Late Date (a maximum of three late dates need to be entered)
3. What time they arrived on the corresponding date
4. Whether they have a note or note
6. Their reason for being late.
7. A checkbox for detention.
It would be great if someone could help me with this, I understand it needs to go into seperate tables but I run into difficulty when I want to record the dates.
For example I created 3 tables, LateDate1, LateDate2 and LateDate3 all with the relevant info (time, note reason) but I think there must be a clearer and simpler way to do this?!
View 1 Replies
View Related
May 4, 2005
Hi
I have a form and sub form, the sub form is populated from a query and has a column of checkboxes. Basically these checkboxes get checked as part of the form, but only a maximum of five are allowed to be checked. Is there a way of achieving this?
Cheers
Dan
View 1 Replies
View Related
Jun 20, 2007
I have a very simple database that keeps a track of line performance figures, i have attached a part of it as an example:
In my lines table are 8 production lines
in my log table i need to keep a track of the data,
what i would like to happen:
i need a form that when i open it all the production lines i have are displayed and next to each line are 2 data entry fields so i can enter a performance and an output figure for each line in one go, so in this instance i would like 8 lines and 2 entries per line.
at the moment i have to do each line individually.
i would also like this to grow should i add more lines
how can i best sort this please.
Help would be much appreciated
Andy
View 1 Replies
View Related
Sep 25, 2013
The below isn't the actual use but it is easier to explain like this...
I have a system set up on access and I have a front page (form) where I can enter the reference number (1141#2) of a building (YELDR for example).
This then fills in information in the other fields on the front page. For example address, number of doors and entry codes.
My problem is that this only displays one door and one entry code. I need it to display all doors and all entry codes so that I can just type in my reference number and have something that looks like the below but for all of them. It works fine at the moment but only for one of the results when there can be up to 10.
Reference No. YEDJR <---------------- I ENTER THIS
Address - high Street - London - UK <-------------- All of these fields are populated using the query
Doors - Green - front <-------------- All of these fields are populated using the query
- Red - side <-------------- All of these fields are populated using the query
- Orange - back <-------------- All of these fields are populated using the query
Codes - 10111 <-------------- All of these fields are populated using the query
- 10221 <-------------- All of these fields are populated using the query
- 10256 <-------------- All of these fields are populated using the query
I know this is probably nowhere near enough information but is this possible? I don't know SQL so i would have to use the designer.
This is the SQL which I have at the moment though (I didn't write it i just copied this from design view on the query)
Code:
SELECT [Site Info Data].[1141 #2], [Site Info Data].[1141 #2], [Site Info Data].[Site 2 Name],
[Site Info Data].freq, [Site Info Data].status, [Site Info Data].[Site 2 Owner], [Site Info Data].[NGR #2],
[Site Info Data].[Address #2], [Site Info Data].[Postcode S2], [Site Info Data].[Dish size #2],
[Site Info Data].[Dish height #2], [Site Info Data].[Dish Bearing #2], [Site Info Data].RCLO
FROM [Site Info Data]
WHERE ((([Site Info Data].[1141 #2]) Like [Forms]![Front Page]![txt1141] And ([Site Info Data].[1141 #2])
Like [Forms]![Front Page]![txt1141] And ([Site Info Data].[1141 #2]) Like [Forms]![Front Page]![txt1141]));
View 5 Replies
View Related
Oct 1, 2012
I have a Comments field on a form. I would like to set it up so that multiple comments can be added and logged at different times by different users. I have attached a picture of what I am looking to do.
View 4 Replies
View Related
Jul 17, 2006
I have a form with a "WorkerID" field. Once the worker enters his ID once, I want the same ID to show up for all the subsequent records so that he doesn't have to type it over and over again. Is there any way to do this? Sorry if I sound totally new to this (I am)!
View 1 Replies
View Related
Aug 18, 2006
I have been trying to figure this one out for a while and I can't seem to find anything helpful. I'm going to present a simplified version of my problem to help keep it simple.
I have an "Activities" table that contains fields:
ActivityID
ActivityName
I have a table of 7 Risk areas which contains fields:
RiskID
RiskName
What I need to do is to create a form that will allow the user to select an activity and then, for each of the 7 risk areas, input a risk measurement.
This involves the creation of a 3rd table to collect the user input which would contain fields:
MeasurementID
RiskMeasurement
ActivityID
RiskID
I can do a dropdown of the Activities and have a subform that links to it but the problem I'm having is trying to get that subform to list the 7 Risk Areas AND collect the RiskMeasurement information from the user. How can I have a listing of these 7 areas from one table that are tied to inputs that go into another table? This seems so basic and simple yet I have tried numerous things and all have failed.
I hope this was clear. Any help is appreciated.
View 14 Replies
View Related
Nov 9, 2004
Hi All,
I am coding in VBA and use Microsoft Access as the front end.
My question is : I have a table(tblStudent) which has two fields Student Name and Gender.
I want to add multiple records in this table through a form at a button click....(Say I want to add least 5 students at a time)
So, I have 5 textboxes for student name, 5 textboxes for Gender and an Add button.
How do I so this programmatically?
Any help will be appreciated.
Thanks.
View 1 Replies
View Related
May 25, 2005
I am trying to customize one of my query table, so that it shows the latest review date of an employee (with multiple entries). I have gotten the SQL statement to work so that it shows the latest employee review date which is greater than the current date. But if the employee has 2 records after the current date and I want it to show the latest entry of the 2. I don't know to put add a sub-Select statement or whether to add another criteria in the Where criteria.
e.g. Current Date = May 25, 2005If Employee A's next review date is set for June 1, 2005 but has already been reviewed (the next review date is May 31, 2006). The query will show the June 1, 2005 and not the latest entry of May 31, 2006.
Because there are 2 review dates that are after the current date. What should I do to make only the latest entry appear?
Below is the my VBA code so far, which produces the above result.
SELECT tblEmp.fname, tblEmp.lname, tblEmpWorkHistory.[current store], tblEmp.position, tblEmpSalaryHistory.salary, tblEmpSalaryHistory.next_review_date
FROM (tblEmp INNER JOIN tblEmpSalaryHistory ON tblEmp.ssn=tblEmpSalaryHistory.ssn) LEFT JOIN tblEmpWorkHistory ON tblEmp.ssn=tblEmpWorkHistory.ssn
WHERE (((tblEmpSalaryHistory.next_review_date)>Now() And (tblEmpSalaryHistory.next_review_date)<=[Please enter the Next Review Date]))
ORDER BY tblEmpSalaryHistory.next_review_date;
My brain is stuck and can't figure it out. I hope any fresh mind could help me out. Thanks in advance.
View 5 Replies
View Related
Mar 29, 2008
The way my tables are organized:
Clients --> OrderInfo --> Ordered Items
I'm trying to do an order form with the following :
select the user < drop down >
upon selection, their information such as cc #, experation date, etc will be visible in the associated data boxes.
if they are not in the drop down list, there will be a button to add a new user
enter the info for location, date, time, etc
enter the purchased items.
submit form either by clicking create new order, or finish which brings the person back to the switchboard.
Thanks in advance.
View 3 Replies
View Related
Mar 26, 2014
I am having an issue with a database I'm creating. It is a root cause analysis database. One of the fields is Target_Date. I need to set up queries that alert us if the target date is coming up, (in a week or so), or if it is past due. The issue is that some target dates are simply, "Next Run", which is to say that the corrective action must happen on the next run of the particular item which, at that point, has not been scheduled.
In order to do the above query, the dates must be formatted as Date/Time as <date() will bring up all dates regardless of whether they are in fact less than today if they are in text format. However, this restricts the ability to put in the target_date of "Next Run". Next Run will be required for another query in which we will specify the products coming up and it will let us know which "Next Run" items associated with those products we will do.
Essentially, I have taken the long route to ask, is it possible to create a field format that measures dates as dates and text as text?
View 3 Replies
View Related
Jul 19, 2013
I am currently working on a form that involves several linked tables. Since our personnel got so used to a certain form format, I have scanned the form and imported into an access form as a background and have laid corresponding fields on top.(I have attached a snapshot of the form). This works great for a view only form, but I can't seem to add/edit records using this form because of the table relationship. I have done some research, and found that one way to get around this problem is to use subforms, but that would kill the form format. Another way I have seen (which I am not sure how to do) is to have a temporary field and once you hit the submit button it will then fill out the tables.
View 1 Replies
View Related
Jul 27, 2005
I'm having trouble finding an entry anywhere about this, although I'm sure I'm not the first person to come across it...
When I am typing my entry into a combobox it begins to autosearch, highlighting the part of the entry that it finds that matches the beginning that I typed in (as a normal combobox does in access).
However, when the entry it finds has multiple periods in it (i.e.: S.T.A.R) it stops searching and does not highlight the remainder of the entry it moved to. I'm not sure that searching is the correct term, which may be why I can't find any information on this, but is there a way to correct this problem?
View 2 Replies
View Related
Oct 21, 2012
I'm pretty new to making databases outside of a basic access class..Is it possible to make a record in one table that makes a new record in 5 different tables using different bits of the initial record?I want to use the data entered in an evaluation form to create a new entry with the basic identifying information in 4 different tables.
View 12 Replies
View Related
Feb 18, 2014
I would like update a table column values separated by ; For example
1. ColumA has no data, I want to do an update and place a comma at the end
2. If ColumA has already value do not delete add new valued at the end where ; if found.
My start attempt as follow:
Code:
qry_test= "UPDATE Table1 SET ColumA =" & "Enter Entered ;" & & _
" WHERE ID=" & 1
Set RS = cnnDB.Execute(qry_test)
What do I need to do to add more stuff where ; is found without delete existing value if values exist.
View 2 Replies
View Related
Oct 24, 2014
Right now i have a qry that takes ID - Name - Order - RepeatOrder - Month - Department.
Now when i run the qry what i want is a grand total of Order/Repeat Order by Name for the Month, what is happening is the qry is creating multiple entries for different departments. I even have Department "Group by" but still causing the issue, For example:
For APR:
4/1/14 Dep A 12 2
4/1/14 Dep B 10 2
However i do not want them separated, how to i merge the data into 1 entry? 4/1/14 22 4?
View 1 Replies
View Related
Sep 17, 2014
I have a query that returns numerous rows of data per person per day.
Simplified Example ( ';' used to show columns):
LastName ; Date ; DrillNumber ; Score
Smith ; 18/9/14 ; 0 ; 100
Smith ; 18/9/14 ; 1 ; 150
Smith ; 18/9/14 ; 2 ; 120
Doe ; 18/9/14 ; 0 ; 150
Doe ; 18/9/14 ; 1 ; 150
Doe ; 18/9/14 ; 2 ; 100
etc.
I'm trying to create a report that shows each of the scores per drill in the report per person (Dont worry about the formatting):
Name: Smith
Date: 18/9/14
Drill & Score:
0 - 100
1 - 150
2 - 120
However, when I drag the field lists into the report Im getting a new report entry for each drill eg.
Smith
18/9/14
0 - 100
Smith
18/9/14
1 - 150
etc.
Any way to get all drills to show in the one report entry per person? So all data for Smith is in the one report or report section, and then all the data for Doe.
View 5 Replies
View Related
Jun 2, 2013
I am in the of designing an access 2010 database for data entry. Is it possible to create a button on a form in which a prompt asks a user for which records to export. Then depending on the entry export specific fields (First Name, Last Name, DOB) to a specific excel 2010 workbook (setup) and worksheet (template)?
For example, if the user entry is 1, only record 1: First Name, Last Name, DOB is exported to the setup.xlsx (more specifically the template worksheet of the setup.xlsx). However if 2,3,4 are entered then records,2,3,4: First Name, Last Name, DOB are exported to the setup.xlsx (more specifically the template worksheet of the setup.xlsx).
View 1 Replies
View Related
