Tables :: Auto-populate Table Field From Calculated Field In Another Table?
Jan 15, 2015
I am creating a database to keep up with the expenses for my art business. I have a table for Vendors with a field (Mileage) for one-way mileage that I enter manually (EX: 11.8 miles). Then I have a calculated field that basically just multiplies the Mileage field times two to get total mileage to and from my house to vendor location. (Total Miles) {The reason I want to do this this way right now is because I didn't keep up with my mileage this year and I am having to look up the distance from my home to each location)
Then I have a "supplies" table where I show the date, the vendor, the item, cost, etc, and I want to add a Total Mileage field based on the calculated Total Miles field in the Vendors table that will automatically populate based on the vendor of each transaction. I just can't remember how to make this work and I know just enough SQL to be dangerous.
Within my table if Field 1 has an answer of Self (from drop down), then, I would like Fields 6-12 to auto populate; however, if Field 1 does not have an answer of Self, then leave Fields 6-12 blank.
I am not quite sure how to lay this out. I am using Access 2010.
I have with my database. It's holds cost data including purchase made in foreign currencies which need to be converted to GBP using the correct exchange rate so a variety of reporting & stats can be performed.I have a table called Costs within which there are 2 fields
Purchase Currency Exchange Rate
I also have another table called Exchange Rates 13/14 within which there are 2 fields
Currency Exchange Rate
When a value is entered in the Purchase Currency field on the Costs table (this is a look up field linked to Exchange Rates 13/14 so it shows the listed currency in drop down) I need the database to automatically populate the Exchange Rate column in Costs i.e. match the value in the Purchase Currency field to the Currency field in Exchange Rate 13/14 and populate with corresponding Exchange Rate from Exchange Rate 13/14.I have tried the following and none work:
SQL Tried
1. UPDATE Costs SET ExchangeRate = [Exchange Rates 13/14].[Exchange Rate] WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
2. UPDATE Costs SET ExchangeRate = [Exchange Rate] FROM [Exchange Rates 13/14] INNER JOIN [Exchange Rates 13/14] ON Costs.[Purchase Currency] = [Exchange Rates 13/14].Currency
3. SELECT [Exchange Rate] FROM [Exchange Rates 13/14] WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
I know it is possible to have a drop down for Purchase Currency which shows 2 columns (both Currency and Exchange Rate) you can then use the exchange rate figure for a calculated field. The problem I have is that I am importing data into the costs table from excel. In Excel I can only have 1 value in the Purchase Currency column on the upload template. If I just have Euro in this column the database does not match it to the Euro in the Purchase Currency drop down and also store the correct exchange rate.
Or is the alternative to put this into the calculation of GBP Unit Cost where this somehow matches the Purchase Currency in the Costs table to the Currency field in Exchange Rates 13/14 tables and uses the appropriate exchange rate from Exchange Rates 13/14 to calculate GBP Unit Cost in Costs table.
I'm creating a database that keeps a track of questions and scores.
The questions in the database need to be dynamic and are changed frequently.
I have a scorecard table which keeps a record of scores and the applicable question at the time the record was saved. I need to do this because in 6 months time we may want to provide feedback. As the question may have changed we need to be able to refer back to what the question was.
I want the question field in my scorecard table to populate with the value in my question table.
I have tried a number of things including setting the question field in the scorecard table to a lookup based on the following query:
SELECT tblQ1.Q1 FROM tblQ1;
This works however only as a list or combo box. I don't want the user to have to select the question. I want it to auto populate, is this possible?
I need to populate a table with Grid reference values consisting of 6 figure integers, eastings and northings.
I am receiving/downloading the Grid Refs in a UK Grid Tile format, with 3, 4 or 5 figures preceded by letters.
E.g. SK 456 849, SP 6789 4356.
I have used some query functions and written a bit of VBA behind a form to convert the received coordinates into the full coords I want. The coordinates are now in unbound controls on a form, and I want to pass these back to a table so I can use the data for display purposes.
Ideally I want to link to the table from a GIS and autoupdate without having to open the database (my imported data is linked in to feed the database automatically).
I have done some searching and not found a method to pass the values into a table - What is my best way of doing this automatically?
I am new to Access and have started to move across some Excel based programs.
I have a simple example with two tables and a form where on the form a customer ID is entered which displays the name and below it is necessary to enter the total payments made by that customer for the day ( all records for the same day are entered at one time, which is why I would like to autofill the date).
I can enter the date directly on the form for each customer entry but I would like to automatically enter the required date in the payment table using the value of a datepicker on the form to ensure that no date errors are made.
I have been trying to use the expression builder to do this but the datepicker does not seem to appear as a valid option.
the table 2 is the source of a form that will let the user change the numbers. table 1 should change Date1 and Date2 Fields based on the two fields (3months) and (6months) if i want to make a lookup wizard it should be changed manually and if i make a calculated field i can't find other tables in the expression builder
I have a Table (tblFPAData) that is being populated with entries from a form I created. There are several fields but if I can get an answer for one relationship then I can repeat it for the others.
In tblFPAData there are the fields MaterialNo and Description.
I am creating another table called tblSchedule and using a form to enter the information. I need the user to select the MaterialNo from a combo (cmbMaterialNo) and the then table auto populates the Description from tblFPAData.
I have two forms - Main and StudentAdvisor (filtered by student ID#) SAdvisor.jpg
The main form originally had a combo box field to display advisor, the problem is that some students had multiple advisors during the course of their program. This problem was fixed by the creation of a StudentAdvisor (table/form) for recording ALL advisors. I set the form to display by newest term so the current advisor (or initial if no change) is the displayed record.
On the main form I then added "add" to open the StudentAdvisor form for entering information, and an unbound txtfield to display their current advisor (or initial advisor if no change).
The problem is I can not get the txtfield to display the information. I have managed to get the error ( #Name?)
Advisor Type 1=Initial, 2=Current, 3=Previous Changed 1=Yes, 2=N/A
so a record would record as: Initial and N/A Current and Yes Previous and Yes
I tried writing DLookup but could not get that to look up the combinations (above) and display either initial or current.
also tried pulling form SA = Advisor to form Main = txtAdvisor
For report purposes I need to know not only current advisor but how many were assigned during the program and also how many students each advisor was assigned during certain time frames.
I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?
I've learned the hard way about using calculated fields in a table. I used on while building in Access 2010 for users that will be using Access 2007. Once my testers pointed out the error message, I did some research and realized my calculated field was the problem.
So, I created a query to replicate the calculation, deleted the offending field from the original table and redirected everything to point at the new query vice the original table.
However, my testers are still receiving the error about needing version 14 to access that table. There isn't a calculated field there any longer, but they still can't access that table. Did that calculated field put some sort of taint on my database that can't be removed? I'm not sure what to do. Do I need to start over, so that a calculated field was never part of the database to begin with? If so, how much of my existing one can I reuse?
I am trying to add some numbers together for a number of fields, of which at least one field will either be 0 or empty. I am trying to use the following formula in a calculated field in a table:
I have a small database to keep track of some student information. Right now I have one table where a student is first entered with their contact information and then other tables that house specific information about other areas related to the student (if there's no longer with the school, paperwork is complete, etc.). The primary key for all of it is their student ID. The tables are linked parent/child with forms, etc., so that when I open a form having to deal with one of the related tables (student active status), and pull up the student's ID from the first table, it'll then populate that ID into the status table, even if something else isn't added.
What I need is really that when I create a new student on the first table with their contact information, that their ID is populated into all of the related tables, without waiting for additional information to be added. This is because I need to run reports to see who is missing information, etc., and without their ID showing up in that second table, I'm not getting accurate results. How would I set this up?
Sample of tables with smaller number of fields:
Table 1 - Contact Info (always done first for a new student) Student ID Name Address Email
Table 2 - Student Inactive Status (want the ID numbers to be the same quantity as table 1, even if Student ID is then the only completed field in this table) Student ID Inactive status type Date
Table 3 - Paperwork (same issue as table 2) Student ID Submission Date Approved By
Have a table with columns of data which I want to total at the end of each row. The Calculated Field does this simply enough but it only seems to be able to add whole integers? How do I get it to accept and add values to 2 decimal places?
i want to insert a calculated value of a e.g field6 (field4 * field5) in forms into the database table. by using expression builders in my forms. i have made that feild and on click it shows the calculated value but on filling in the rest of the form and clicking on Insert button does not update this calculated field but iserts all other fields.
Both tables are joined with 1 to 1 relations based on apm_id as both of the fields are same. The table [APM] is pre filled with data. The idea is to list data from [APM] and collect data from user to be saved in [DATA] based on the info from [APM].
I created a form which list all the fields from both tables. The fields from table [APM] are only for displaying data and created a combo box based on [APM].apm_id to display the fields by chosing an APM ID from the drop down box.
A user adds a record and moves on to second record, completes the second record. Now if the user wants to list the first record by chosing the APM ID from the drop down box it only populates the fields from table [APM] but not the fields from table [DATA] which the user just added. Infect the fields related to table [APM] changes, but the fields from table [DATA] stays the same.
How ever if I click on the small arrows on the bottom which lets you cycle through records than it populates all the fields from both tables correctly.
How can I fix the issue so that all the fields would populate based on APM ID selected from drop down box.
First hurdle in my database tables, I have a field called Date_Created which I want populated just once, whether from a form or an import.
I have another field called Date_Updated which I want to update any time the record is changed. I know I can do this by using the default value of Now(), but how can I get the Date_Created field just to update when the record is created and not any time after ?
I also want to show these values on the form, but have them as view only, hoping that is possible ?
- Assets - Shifts - Machine Offline Date - Machine Offline Time - Machine Online Date - Machine Online Time
In the Asset Table I have recorded on each asset the number of shifts it operates for as well as starting / finishing time of those shift. e.g. 2 shift asset will start at 7:00AM finish at 11:00PM or 3 shifts asset will start at 7:00AM finish at 6:59AM
Now I have 82 assets in the factory and 20% of those machines run 3 shifts. Each shift is 8 hrs.
What I have already done is allocate shifts per asset e.g.
when I pick Asset (a) in the Asset combo box, in the shift box it will automatically generate 2 or 3 dependant on what I have set.
If an asset runs for 2 shifts, it would mean that, that asset is operational/running from 0700 - 2300 or 7:00am - 11:PM
also if an asset runs for 3 shifts it would mean that, that asset is operational/running from 0700 - 0700 or 7:00AM - 7:00AM
These are the scenarios that is entered through a "Form"
Scenario A: Machine (a) breaks down at 1700/5:00PM on the 10/7/15 and was back online at 12:30 on 11/7/15, This machine runs for 3 shifts which would mean in the "Breakdown Downtime" the result should be 19.5 hrs
Scenario B: Machine (b) breaks down at 1900/7:00PM on the 10/7/15 and was back online at 10:00AM on 12/7/15, this machine runs for 2 shifts which would mean in the "Breakdown Downtime" the result would be 23 hrs.
I would like to make this an automatic calculation, Is this possible?
Please note I am not after a "Query", I need this calculation in the main Database table named "Tbl_MaintenanceDATA".
I understand this is not the norm and many have suggested against this, however for the purpose of what I need, it needs to be in the main table with all the other data.
I have the following tables: Inquiry (This is the main table I want to populate through the use of a form) Programs
My question refers to two columns in the Inquiry Table Program Group
The program column is populated by a drop down menu that is linked from a programname column in the Programs table.
I I want is the Group column in the Inquiry table to autopopulate based on the selected programname (There is a group column associated in the Programs table) so the form field will be autopopulated.
What I have tried is autopopulating the form (form name is Inquiries) Field called Group by using =[Program].[Column](1) in the source code of the text box. This works well, but it then does not populate the main table.
I need to either - Learn how to populate the main table column called Group based on the form autopopulating or - Learn how to autopopulate the column Group in in the table so the form will autopopulate
How to set up my tables as I'm just starting off with setting my database up.
I'm doing a simple database to track the purchase orders (PO) I am managing. Each PO has a PO Number and an Original Value. POs may have multiple amendments which would change the PO value. I would however like to keep the history of the PO original value and all different amendments.
So I created two tables:
tblPO: ID PO Number (Number) PO Original Value (Currency) PO Sum of Amendments (???????????) PO Current Value (Calculated = PO Original - PO Sum of Amendments)
tblPOAmendments: ID PO (Lookup from tblPO) PO Amended Value (Currency) Amendment Date (Date/Time) Amendment Desc (Text)
Now the two, million dollar questions are: 1.) Is this the right table structure to use. 2.) How do I go about calculating the Sum of Amendments field?
i have a user permission table.that consists of PermissionPK, UserFK, CompanyFK. I also want the username to be automatically filled in?So when a user ID is filled in on the table, it also fills in what that UserID's Username should be?As i need both the UserId and Username text for code that looks at the Environ username.
