General :: Update Total Based On Another Field

Oct 30, 2012

After testing it, the database got corrupted. I had backed it up just prior to using this.

It was something like = Sum(Abs[AmountPaid], [Paid] = "X")

I have a continuous subform with an "AmountPaid" column. The total is displayed in the subform footer. I need it to display the total for only the fields with an X in them denoting that they were paid. This total should match the statement we receive.

After clicking the button to put the X in the Paid field, then I used the formula to update the AmountPaid field.

Joe..........10..... X
Al..............5.....X
Flo.......... 25

.....Total = 15

View Replies


ADVERTISEMENT

General :: Update Field Based On Date Range

Sep 7, 2012

I have 2 tables like this

TableA
FromDate
ToDate
BatchType
TotalBatches

TableB
Date
BatchType
NumBatches

Is their a way to update the TotalBatches in TableA with the sum of NumBatches from tableB that have the same BatchType and falls between the FromDate and ToDate of TableA?

View 14 Replies View Related

General :: UPDATE Field In Table Based On DISTINCT Values

Mar 12, 2013

Below is a sample of the table with the data. I manually added the 1 and 0 to the hc_Year field. However, I would like to create an Update query that will add a 1 to the hc_Year if its the first instance of PIDM & regsYear and add a 0 to the records that are not the first instance.

PIDM | regsYear | hc_Year
52 | 2009 | 1
52 | 2010 | 1
201 | 2007 | 1
201 | 2007 | 0
201 | 2007 | 0
201 | 2008 | 1

View 6 Replies View Related

General :: Automatically Update Field Based On Entry In Another Table

Jan 14, 2013

I have two tables, one called 'Company' and one called 'Person'. Both tables have several fields, but they both have the same primary key, i.e. 'Naam'.

When I type in a name in the 'Person' table, I'd like the 'Company' table to automatically display the name too. So for example if I type in 'John Doe' in the person table, I want to be able to switch to the 'Company' table and have the same name displayed there, automatically.

View 1 Replies View Related

General :: How To Position Class Students On Report Based On Their Total Marks

Sep 2, 2012

I am working a database that manages student's Records including marks for various subjects, but i would like to do the following on each of the students' report cards:

a) Display the marks for all the 12 subjects .This one i have done.

b) Display a total for all marks of each student.Done as well.

c) The problem is, i don't know how to display the position of each student on the individual report cards.I want to base this grading on the total marks obtained by each student in the same class.

View 3 Replies View Related

Queries :: Query To Total Based On Value Of A Field?

Jul 31, 2013

the relevant fields look something like this:

TellerNum - Date - Field1 - Field2 - Field2

I need a way for my query to take all rows with the same TellerNum and add Field1, Field2, and Field3 together for those records. So, there may be 20 records with the same TellerNum, and I need Field 1, 2, and 3 to be added together and then totaled for all 20 records to give me 1 grand total. I need this to be done for each TellerNum, so that the results will be a grand total for each TellerNum. So, the results may look something like this:

TellerNum 1: 486
TellerNum 2: 300
TellerNum 3: 240
etc..

View 3 Replies View Related

Total Noob Can't Update/append Form Field

Jan 14, 2004

Hi guys- totally in the frying pan here...I can't seem to get past this initial stumbling block with Access

i've made a simple form that "should" create a primary key from the first two digits of a clients last name and last four digits of their phone number...i've tried an update query button and a refresh data button...the key field gets filled in on the form, but it will not transfer to the underlying table...I keep getting a null value error for the key....any help would be greatly appreciated...

heath

View 9 Replies View Related

Forms :: Update A Field Based On Prior Field

Jul 13, 2013

I have a table that has been converted into a form for an input. Now suppose I have two columns. Say I enter value in first column. Now the second column is a lookup to a data from another table.

What I want is to see only those data in 2nd Column which have the value filled in Column 1. This is should be in run time mode. I fill in the value and then in the 2nd column I directly get to see only common values.

View 2 Replies View Related

Update A Field Based On Data Entered In Another Field?

Jan 9, 2015

I am wanting to update data in one field which is being pulled in from another table based on an entry in another field in a form

Attached is the database. In the sales form I want to enter a customer ID which will then pull in the customer name from the customer table and put it in the Customer Field in the sales table.

I know I am duplicating the data by having customer name in both tables which is bad database design!

View 3 Replies View Related

General :: Update Table Based On Form - Create Multiple Rows

Apr 15, 2014

I have a table with the following columns: Task, Visa type, time it takes to perform the task. There are several taks that are performed for all visa types. I want to create a form to enter data to the table in which for the field visa type I have a list box that can allow multiple values, however, I do not to create a single line with the task and on visa type all the types of visas selected. I want to create a line for each type of visa with the information introduced.

I don't know if this is possible, the reason for which I want for the form to create several rows depending on the visas types is because then I have a query that sums all the types of visas. Can this be possible? I don't want the people to introduce manually directly to the table the data and also that for the same taks they have to enter manually 50 rows with values. I want it to be more simple and easier.

View 3 Replies View Related

Update Table Field Based On Another Field.

Mar 3, 2008

Ok. I've got a table named SIPATable and some of the fields contain summed values. Let's say for instance I have a field named "GaDRaw", which represents a raw score. now...I need to update a field called "GaDPer", based upon the value in "GaDRaw". So...If "GaDRaw" <=16, then I would want "GaDPer" to be updated to = 35. Further, if "GaDRaw" = 17. then I want "GaDPer" to = 37. and so on....

I think it's an If Then Else statement, but not sure how or where to place it. Id like it to be as code under a command button, and update the table all at once.
Any help would be appreciated.
Thanks in advance,
Freud52

View 7 Replies View Related

Forms :: Update Field Value Based On Value From Another Field

Aug 24, 2014

I have a form used to track attendance of employees:

I want to update a value in Field B to a default value if a specific criteria is selected in Field A. How? I cannot seem to find a answer...

Specifically: If the combo box value "Not Present" is selected from Field A, I want to value in Field B to automatically update to "Not Available".

View 11 Replies View Related

Modules & VBA :: Update Field Value Based On Another Field

May 11, 2015

I have the following fields in an A2010 form

PaymentMethod
Total
InstalmentAmounts
OurFee

InstalmentAmounts field is populated based on the results of PaymentMethod which is a combo box

Here is the current code

Select Case Me.Payment_Method.Value
Case "Three post dated Cheques"
'If three payments by cheque
Me.InstalmentAmounts.Value = Round(Me.Total.Value / 3, 2)

In some cases there will be a few pennies that the client "overpays" because the instalment amounts will not add up to the Total. I need to add up the TotalInstalmentPayments to find out what the client actually pays and then add the difference between TotalInstalmentPayments and Total to the OurFee field

So

Total = 500.00
Payment Method = 3 instalments so
IstalmentAmounts = 166.67 (rounded) so client pays 500.01 so the 0.01 needs to add to the OurFee field.

View 4 Replies View Related

Update Field Based On Other Other Fields

Jan 5, 2006

drvRegion either contains "EUROPE", "AMERICA" , "ASIA" or is NULL.

if drvOrderSource="Whatever" THEN update drvRegion with "EUROPE", "AMERICA" , "ASIA"

Right now I'm getting a circular reference error :/

please advise. Thank you.

View 5 Replies View Related

Trying To Update Field Based On Other Table

Jul 25, 2006

I have the following situation.
Table 1:
------------------------
Primary Key
Name
Secondary key

Table 2:
------------------------
Primary Key
Age
Secondary Key

*****************************
This is just an example. The first table is totally populated. The second table does not have the secondary key assigned, but otherwise is populated. In additon, some of the records in Table 2 do not have a matching entry in table 2.
I would like to be able to update table2, populating the Secondary Key field in order to allow me to delete the Primary Key fields. The Primary key was assigned by somone else who created the db, and makes no logical sense, i'm trying to replace it with a key that can be used more easily. Any help would be greatly appreciated.

Kevin S. Jones

View 1 Replies View Related

Update Field Based On Other Form

Oct 9, 2006

I have two tables:

Table1 - show details of customer payment (full payment / with credit terms up to a maximum of six (6) months)

ColID - (PK/Autonumber)
Col1
Col2
Col3
Col4
Col5
Col6


Table2 - shows insurance agent's prorata commission (based on cleared payment).
ComID - (PK/Autonumber)
ColID
Com1
Com2
Com3
Com4
Com5
Com6

Scenario: If a customer was given 6-month-credit term to pay for his insurance premium, then the insurance agent's commission will also be given in six monthly terms.

Example Computation:
Amount of premium is 12,000 (payable in 6 months = 2,000/monthly)
Agents Commission is 10% of Premium (1,200 = 200/monthly)

What i need is a code to automatically update Table2 if Table1 is updated. If the dbase user updates Col2 based on the amount the customer pays, the field Com2 must also be updated.

I hope this is feasible.

Thanks!


Sheila

View 4 Replies View Related

Auto Update Of 1 Field Based On Another.

Jul 6, 2006

i have set up a database to help track the vehicles my company owns. However on the form i have created when i type in the license plate number want the work area that is responsible for the vehicle to pop up so i can inspect it.the form also has the different things i would look at but i don't want to continue to change the work area that has the possession of the vehicle or have to look it up every time i have an inspection come due. Any help would be appreciated

View 1 Replies View Related

Update Query Based On Length Of Value In Field

Jul 12, 2007

I want to create an query that will update a field value for records where the length of the field value is less than a given number (if LEN of UniqueID is less than 6 for example), but I can't figure out how to write a SELECT query, much less an UPDATE query for this condition....can someone help me, please?

View 1 Replies View Related

Need To Update A Field Based On Selected ControlSource

Mar 31, 2008

I have a combobox that selects the customer and shows related information on that customer such as phone number, cc#, etc..

now the trick is i need to allow the selected member to be added to the order information.

For instance, the user selects the customer "Bob" and bobs information is displayed to check for accuracy. After the info is approved the user will continue to process his order. In order to do this I need the customer ID to be the same as the selected customer in the combobox.

Then the user will proceed to fill out the order information, location, date, time, etc.

How can I go about doing this?

Thanks in advance

View 2 Replies View Related

Update Field Based On A Reference Table

Mar 15, 2006

I would like to update two fields [Category] and [ProdType] in tblAccum based on a reference table.

The reference table is tblReference and contains the fields [Code], [Category] and [ProdType].

tblReference example of field values:

Code Category ProdType
A Blank Accessory
BS Blank Blank Stock
O Printed Offset

So if the Code field in tblAccum has a value of O then based on the tblReference table the Category value would be Printed and the ProdType value would be Offset.

Any help is greatly appreciated.



Thanks,

View 7 Replies View Related

Forms :: Update Value In A Field Based On A Condition

Jan 25, 2015

I have a calculated field in the form footer which adds up the number of boxes that have been ticked for the received field

=Sum(IIf([Recieved]=Yes,1,0))

If the ticks equal to 3 then I want to update the status field in another table to "Active".I am trying this VBA code but it won't work.

Code:

If Text9 = "3" And custNumber = tblCustomers.custNumber Then
tblCustomers.Status = "Active"

View 3 Replies View Related

General :: Update Main Form Field Value After Updating Field Value In SubForm?

Feb 12, 2015

I have a main form and a subform.

Both forms have the field called JobID in common.

Both forms have a field called JobStatus.

Any easiest solution so that After I Update the field called JobStatus in the subform, it changes the field called JobStatus in the main form to the value which was selected from the subform?.

View 3 Replies View Related

General :: Autopopulation Of Field In Table Based On Entry In Second Field

Jul 22, 2014

Please see attachments.

POST.zip (384.0 KB)
database.zip (58.8 KB)

View 4 Replies View Related

I'm Sure This Is Very Easy: Update Multiple Fields Based On One Field...

Jul 26, 2006

I promise I have searched, but I hav spent 10 minutes reading through posts that are unrelated...

What is the code to have multiple fields updaterd based on what is input into a field?

---
Example:

A ZIP Code Field, which updates City & State on the form when entered. (I have a table that has over 39,000 ZIPs w/ City & State already there)
---



I have several applications for this, but if someone could explain this use to me, I will be able to figure it out.

Thanks a million!!!

View 1 Replies View Related

Modules & VBA :: Update Table Field Based On A Query

Nov 16, 2013

I have a table 'table1' which has various field including an ID field and a yes/no field. I then have a form based on a query originally derived from data in the original table. The form provides a list with some ID's with a yes and some with a no.

I'm trying to write a button code to convert all the no's into yes' for those ID's picked out by the query.

I've searched lots of sites and get that I need to set recordsets for both table1 and the forms' query but all my efforts crash or give an error.

I am looking for the right way to say .." for each ID in form set the yes/no field corresponding to the same ID in table1 to yes"

I'd attach my version ....

View 2 Replies View Related

Tables :: Update Table Based On Concatenated Field

Aug 8, 2013

I have a field in a table that is to be concatenated from two other fields, PolPrefix and PolNum. On a form I got it to show the full field 'PolicyNumber' by making the control source =[PolPrefix] & [PolNum].

But this doesn't update the field PolicyNumber in the respective table, and only shows it on the form. How do I get a concatenated field defined by the user into a table so that I can call that field other places in the database?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved