Autopopulate Data Based On Relationship
Feb 21, 2013
I am trying to create a 2-table database where we can enter our survey data. I want them to be linked by "head"# so that the "Temp" "Date" "island" and "site" are autofilled into the "time dep"table from the data I entered in the first "perm"table.
I would also like if the lookups were filtered so that, for example, when I choose "Cat Island" my only two options are "fishbowl" and "Cuda" then from there depending on which of those I choose, The "head"# can be chosen from a dropdown as well. There are about 22 coral-heads per site, and two sites per island. I don't want to have a giant dropdown of 80+"head"#s, but at the same time, if we are typing them in, it increases chance of error. limiting the options of head number to be site specific would be ideal.
I feel like I may be going about this all wrong, and need a form? But I cannot figure out how to make the form data fill the datasheets that I have created.
Metapop.accdbMetapop.accdb
View Replies
ADVERTISEMENT
Sep 12, 2013
I am in the process of revamping an old database. I have a main table that currently has two fields "Name" and "Job" but will have more. I have another table with the same fields. I want to use as a lookup to populate the main table with a user form from the second table. I can populate the "Name" field using a combo box, but my problem is how to autopopulate the "Job" field. E.g. If the user selects Mr Smith from the "Name" drop down list, the corresponding "Job" populates the second field.
View 4 Replies
View Related
Jun 24, 2015
I know the way to SELECTDISTECT on a combobox referring to a table so the value will autopopulate in the combobox. But is that doable in a subform?
I have a table with 3 columns. First Name, Last Name, Relationship.
I want to let the user adding data directly to the subform, but when typing RICHARD in the first record of First Name, then on the second record, when they type R, it will suggest RICHARD, a kinda auto fill if desire.
View 11 Replies
View Related
Mar 24, 2005
Hello everyone. How do I go about automatically inserting a date from one table into another table. This are my tables:
tblPAF (Personnel Action Form)
PAFNo
EmployeeNo
Date
Department
Division
Salary
Comments
tblBenefits
BenefitsNo
EmployeeNo
Date
BenefitType
CoverageDate
Every time an employee’s salary or benefits changes we fill-out a PAF (paper format) on it we write all the changes. In my PAF db form I’m thinking adding a command button to open my Benefits Form. Once my Benefits form is open I don’t want to renter the date that was entered in the PAF form I wanted to automatically insert that same date. Hopefully I made myself clear.
Thank you for your assistance.
HR
View 1 Replies
View Related
Nov 25, 2013
I attached a screenshot with notes that describes my problem.
View 2 Replies
View Related
Jan 27, 2014
Each each record in table1 has a unique four character (alpha-numeric) code to identify it. The first two numbers of this code represent the group it is in. (Ie. 15AB and 1502 are both grouped together) The second table stores values that apply to the entire group. I need to create a relationship between these two tables based on the first two characters in the ID field.
Things I've tried:
* Making a calculated field with left$() formula - Access doesn't allow relationships on calculated fields
* Create a new field for just the first two characters and create a data macro for after update and after insert to update that field with the expression - cannot edit the field the user is on
View 4 Replies
View Related
Nov 4, 2005
Got a problem I can't figure out. I will try and explain in my newbie language.
I have a list of companies and a very long list of employees who work in those companies. On my form, would want the user to be able to select a company from a combo box. Afterwards, I would want another combo box be populated with only the employees from the selected company so that they could select the employee without scrolling down for an hour....
How can I make this happen?
View 2 Replies
View Related
Mar 31, 2005
1. how do i auto populate a form from another table. like a repair form pulling up information from the vendor table.
2. I have been trying to pull information into my form 'check stock' from a table called 'inventory'.
i want to be able to just enter the serial number and then autopopulate the rest of the data from the inventory table.
the quantity that is displayed in the check stock table must be the difference of the quantity in the inventory table and the requisition table.
3. Also, every time i enter something in the inventory table, i want it to be able to iteratively check for any avaliable stock and update the quantity instead of making a new entry every time.
please help
View 1 Replies
View Related
Apr 4, 2008
I have a table that I'm storing Horsepower and Watt in. Is there anyway to set it up so that when I provide Horsepower it automatically populates Watts? (just a simple multiplication)
View 1 Replies
View Related
Jun 21, 2007
Have form as follows.
Can # (Lookup/dropdown)
Movement Date (date/time)
Current Location (lookup/Dropdown)
Current Address (lookup/Dropdown)
Prior Location
Prior Address
When I drop the can # down in the form, I want the Prior Location and Address to auto Populate with the last known current Location and address of that can.
Any ideas on the best way to do that?? I assume there is code and a query, but I am an absolute database illiterate so exact directions would be nice.
To explain again, whenever I put a new record in, as soon as I select the Can# from the drop down tab, I want the prior location and address to be populated with the last known current address for that Can#. And if I change the can # I want the prior location and address to change with it.
HELP PLEASE.
View 4 Replies
View Related
Mar 18, 2013
I have a Subform/Subreport that shows in a datasheet view. I hosts two (2) fields on it: "Job Performed" & "Details". The "Job Performed" field is a drop-down list. The "Details" field is a memo field.
What I am trying to do is, well, one of the selections in the drop-down list will ALWAYS have the same details - "Changed Oil & Filter". But all of the rest will vary constantly.
So my question is, can I make it so if "Monthly" is selected from the first (drop-down) field, it will ALWAYS populate the Details field with the words "Changed Oil & Filter" without affecting the other selections?
View 1 Replies
View Related
Dec 1, 2005
:D I am trying to make an accounting database that includes 3 (problematic) tables.
1)
tblAccounts
keyAccountID
AccountName
OpeningBalance...
2)
tblLoanAccountsSupplemental
AccountID (key)
LoanName
InterestRate...
3)
tblRealEstateAccountsSupplemental
AccountID (key)
PropertyName
Address...
The idea is that ALL ACCOUNTS have the information in table 1, and specific SETS of accounts have the ADDITIONAL information in each supplemental table (at the same group level). A one-to-many relationship would not be correct here, and I've tried to use a 1-to-1 relationship.
PROBLEM: When I make a new item in tblAccounts or (for example) tblLoanAccountsSupplemental, I want it to autocreate the KEY and make it the same for both tables.
My intuition may be wrong with the 1-to-1 relationship. What should I do? :(
View 1 Replies
View Related
Jun 16, 2014
I have created a many-to-many relationship but am now having trouble figuring out how to add new data.
I have a table PEOPLE:
ID First Name Last Name
1 Participant A
2 Participant B
3 Participant C
And I have a table BEHAVIORS:
ID Behavior
1 Yelling
2 Grabbing
3 Smearing feces
And I linked these together in a many-to-many relationship in a table PEOPLE_BEHAVIORS:
ID Participant Behavior
1 A Yelling
2 B Yelling
3 C Yelling
4 A Grabbing
5 B Grabbing
6 A Smearing feces
7 C Smearing feces
All that's good to go. But now I need a table so I can count how often these behaviors occur every day. I need to link PEOPLE_BEHAVIORS to a new data table that will count how often I see the behaviors linked to the people every day. So my table should look like:
ID Participant Behavior Date Frequency
But I should only document the frequency of the behaviors linked to that participant (so for Participant A, I should have a space for yelling, grabbing, and smearing feces whereas for Participant B, I,d only have a space for yelling and grabbing). Is it possible to create a table that will basically partially create records for me? So if I open the table to add data today, I'd see the following and fill in the frequency blanks?
ID Participant Behavior Date Frequency
1 A Yelling 6/16/14
2 B Yelling 6/16/14
3 C Yelling 6/16/14
4 A Grabbing 6/16/14
5 B Grabbing 6/16/14
6 A Smearing feces 6/16/14
7 C Smearing feces 6/16/14
View 1 Replies
View Related
Apr 17, 2014
I have about 600,000 records in an excel sheet and tried using the table analyzer but I run out of memory. Instead i have broken up my giant table into a few other tables with their own primary keys and have populated from a maketable query. I related the tables together in a 1 to many and many to many table and one of the subforms is working great. The problem i am having now is with the many to many table (WorkedJunctionTbl). I don't want to have to manually input thousands of lines of data, so is there a way i can populate the data from the original data correctly using the new autonumber primary keys?I can't seem to get an append query to work correctly.
View 8 Replies
View Related
Mar 1, 2005
I'm stuck on something that should be simple, but maybe it isn't:
Problem 1:
To speed data entry in a contact table, I want an entry in the Zipcode (postal code) field to automatically populate the City and State fields from the data in a Zipcode table (Zipcode-PK, City, ST (2 letter code)). The Zipcode table has only one city /st for each zipcode, so it can be the primary key (always a five digit number, sometimes with leading zero(s)).
Sometimes there are several names for a City associated with a particular Zipcode, so I want the City field to be able to be overwritten with a different city name if the data entry person needs to do that. Alternatively, I could establish a separate primary key for the zipcode table, and allow several choices for the rare zipcode that has more than one city name in use.
If I did that I wouldn't have to store the city and st in the contact table (which is better database design, I realize), but I really do want to be able to store the city and state in the contact table because there will be times when the right zipcode isn't known, and the city and state are known, and forcing the input of a possibly incorrect zipcode would be undesirable.
Problem 2: I've got a table with 4,000 records where I have the zipcode, but the city and state information were lost, and I want to be able to re-populate those fields by writing, I guess, an update query that taps the info in the zipcode table.
Problem 3: When entering addresses, sometimes there won't be info about a certain zipcode, and I need some automatically generated dialog that allows adding city and state info for a new zipcode that won't slow down the entry of contact records.
Any help would be greatly appreciated. Thanks in advance. Send an email if you prefer.
HenryTD
View 3 Replies
View Related
Jan 27, 2012
I would like to have a field autopopulate (date field) when a checkbox is selected 'true'
Fields are:
FolderBCCheck-In (checkbox field)
Check-InDate (date field)
I need the check-inDate field to populate Date() when the FolderBCCheck-In field is checked.
I assume it is VBA code to the AfterUpdate (Event procedure). Just having trouble getting the right code.
View 10 Replies
View Related
Apr 28, 2005
First of all, I want to say Thank you to everyone in this forum I have been reading just about every question in each Topic and I have pick up a lot more than I tough I knew. [Thank you]! I not sure how to ask this question or better yet write it. Here is my Situation I work for the NAVY as an Enlisted Personnel station in (New Orleans, LA) I have created a few databases for my workcenter and were simple enough that I managed. Now, I been task to help create a Call Center DataBase to keep track of Phone Calls and Issues the Agents(Employer received Daily) As well run other reports.
I am Including a copy of what I done so far. Can someone advise me about my Database Design? Does it makes sense? Are the Relationship seen good?
my frmCustomer is based on query (qSupport) I would like to know if the form and the SubForm are set up ok where I can have the Agents input data.
And one more thing on the frmMenu I have a few Text Boxes where I would like to display the total Amouth of Phone Calls received - as well the ones pending and Close? Any Criticism or Suggestion are more than welcome.
I hope my Question / Request makes sense. Thank you ....
V/R,
MrDix
View 3 Replies
View Related
Mar 10, 2015
I am using Access 2007, I need to achieve some data from my database, what is the best way to do this.
I have a one to many relationship database, so 1 customer could have many orders, how would you achieve this data?
View 1 Replies
View Related
Apr 15, 2008
Hi...I'm having issues.
I have a project trying to manage some patients.
I have a table with all patient information ~1000 patients. However, not all patients will be case managed. I have another table where I will add all the patients that need to be case managed ~ 100-300 patients. I'd like to be able to autopopulate the 2nd form (the case managed patients) by selecting the Patient ID.
I'd like the patient ID to pull the patient info from the 1st table and populate it into the 2nd table.
Same for physicians. I have a physician file with around 2000 physicians. I'd like to type in the physician ID in my form and have it pull all the demographic information for the physician and populate those fields...
Any thoughts? I am sick of cut/paste and retyping all this info that could easily be pulled for seperate existing tables.
View 5 Replies
View Related
Jan 10, 2014
I am trying to get two different fields to autopopulate when I select a choice from a combo-box on my form.
I have a form called IncomeRcvdFrom, which takes its fields from the table "IncomeRcvd"
In the form, I have three fields that need to relate to each other and auto populate.
The first field is called "IncmType", which comes from a different table called "CategoriesList". There is another field called "IncmDescr" which also comes from the same table. The third field, called "Category" is a list box in that table that comes from another table called "CatType".
All three of these fields also are list boxes in the table "IncomeRcvd", where the source of these fields come from the "CategoriesList" and the "CatType" tables.
What I am trying to accomplish is so when someone selects an Account, or "IncmType", it auto populates the "IncmDescr" field, as well as the "CatType" field on the form.
So far, I have gotten the Account or IncmType and the IncmDescr fields to autopopulate each other, but the Category field either says "Name?", or I get the "type mismatch expression" error message. I also accidentally got the field to work one time, but it would only display the ID Key field and not the actual wording in the rows (i.e. Misc. Payment, Royalty, Rebilling, etc.)
I have checked my relationships, my control sources, and still can't get it to work.
View 7 Replies
View Related
Oct 22, 2013
My database has comboboxes to autopopulate fields. These comboboxes have parametered queries for row sources so that the user doesn't have to go through a long list of IDs and to make the database more efficient in case it has to hold hundreds of records. They work the first time round but not after, when the comboboxes are clicked for a second time the query parameter is not popping up.
View 1 Replies
View Related
May 29, 2014
I have two tables of data, each relating to three business branches (branches A, B and C).
Table 1 shows the expenditure of each branch (by fuel, premises and wages).
Table 2 shows a number of units for each branch (mileage, floorspace and sales).
What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.
View 6 Replies
View Related
Apr 27, 2013
I'm currently using Access 2010 and I'm working on a database project. My question is related to table relationships. Within that project I do have a table that is related to other three tables where that table is the parent. The problem here is that once that table is updated or have a new value it never cascades it down to the other tables.
1- How to have multi relationship to the same field on the same table from different tables?
2- how to cascade the updates to the related fields?
3- Is there any way to force the data update to other tables?
View 5 Replies
View Related
Dec 16, 2013
I'm trying to create a timesheet database to keep track of employee hours worked. I have my tables set up as follows...
Employee_T
PK -EmployeeID
PositionTitle
Last Name
First Name
Employer
Projects_T
PK-ProjectID
ProjectNumber
ProjectName
WBS
Timesheet_T
PK-TimesheetID
FK - EmployeeID
FK - ProjectID
Sat
Sun
Mon
Tues
Wedn
Thurs
Fri
WeekEnding
My question is on my input form I want to have the user/employee pick his name once in a header combo box as-well as the week ending date and have it populate to every new record that user input (each employee can charge to multiple projects in a week so I want to eliminate the need for them to pick their name and week ending date everytime they select a different job charge.)
John Doe Week Ending 12-20-2013
M T W TH F
Landscaping 8 4 3 2 6
Roofing 3 5 2 1
Plumbing 1 4 1
View 6 Replies
View Related
May 20, 2015
I have created 2 tables: Client and Routing.
Each client record is unique. A client can have 'several' routing records.
Key field in Client is 'Client ID'.
Routing table has foreign key of: Client IDFK
I created a relationship of one to many from Client table 'Client ID' to Routing table 'Client IDFK'.
I created a form for the Client table and works.
Where I am having issue is: Client data is not populating into the following Routing form.
* I want a 'Routing form' that you can lookup client info and place it into that form.
* The bottom of the form will be all the routing table fields. The new routing info will be entered into it.
My client needs that form printed for the driver. Client will have many routing forms(records) but only one client record. The driver will have one completed form for each time he picks up client.
View 3 Replies
View Related
Jul 17, 2014
I created a table in MS Access using a Lookup & Relationship data-type. This means that my record cells call upon a particular table for values. It creates a LIST of values for that cell.When I query a particular value in this table I get every possible combination of the value. My query and code are below:
OR,
SELECT MainT.Content.Value, MainT.Source, MainT.[Entities Impacted].Value, MainT.Update,
MainT.[Divisions Impacted].Value, MainT.Announced, MainT.Effective, MainT.Stakeholders.Value,
MainT.[Ref#ID], MainT.[Issuing Agencies].Value
FROM MainT
WHERE (((MainT.Content.Value) Like "*" & [Forms]![SearchF]![ContentCB] & "*" Or (MainT.Content.Value) Is Null)
[Code] .....
This means that if I query STATES: "California", I'll get back every possible combination that exists with the name California in that particular field.The issue with this is that I'll get MULTIPLE primary key values rather than just 1. So, if one record, under STATES has California in it, but the other fields in that record have the Lookup & Relationship data-type, then every possible combination of that record will query instead of the multiple field-values form that you'll see in the table I am querying.
notice that my primary key (REF#ID) is repeated numerous times! This obviously causes problems with generating records and forms concerning information for 1 particular primary key. Is there a way around this problem other than specifying search criteria down as much as possible?! Perhaps there is some SQL code.
View 1 Replies
View Related
