Calculations Based Columns In Entry Form

Jun 8, 2012

I've got a data entry form that is used to enter incoming waste consignment data. (I've attached a screen grab of the form) Each screen represents one record and the data is recorded in a table called "IncomingWaste". The top of the form (light blue) refers to the customer/producer of the waste. Most of that detail is held in another table on the db. The bottom (Dark Blue) part is used to record the consignment data. It gives the user a grid to enter the individual waste components within the consignment (choosing from Drop down menus for the various descriptive elements) Most crucially, the weight of each component is recorded.

I have a calculated field at the bottom which gives a Total weight for that consignment. It simply adds the weight fields together and delivers a total in the box at the bottom of the screen which displays the total weight in that consignment using "Nz([weight1])+Nz([weight2)]+ ...etc - which works fine on the form. However, I can't seem to get this calculated total recorded and appended to each record in the table and I assume it must be re-calculated each time the screen loads (?)

The main issues is.... I need to create a report which adds the total weights from each customer over a monthly/quarterly period and I simply cannot seem to do it.

I have created the report which groups all the consignments from each customer and lists them in date order, but I can't identify or define the method I need to use to collect that Total Weight calculation from each consignment record and add them up to give a grand total for that customer for a particular period. I'm assuming if I can crack the first part and get the total recorded in the table, then I can call the value into the report.

View Replies


ADVERTISEMENT

Queries :: Adding Calculations To Queries Based On Columns In Query

Feb 18, 2014

I am trying to add calculations to queries based on columns in the query... it seems to randomly expect 'Expression' or 'Group by' as column types, and Im having to create 3 sets of queries following on from each other to de-dupe data and allow filters on calculated values.

Also I've got a function which turns a date into a quarterly cohort, e.g. Oct 2013 -> 20134. I use ot on a lot of dates. I created a VBA function, CohortQ used as follows in queries:

Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 CohortQ([InputDate]))

In the VBA, InputDate is defined as a date

Code:
Function CohortQ(InputDate As Date) As Integer
If InputDate = 0 Then
CohortQ = 0
Exit Function
End If

[Code] .....

But when I run it on a date field, it gives me a data mismatch error. I can't step through as it's working on 600K rows.
If I put the function into the query,

Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 Year([InputDate])*10+DatePart("q",[InputDate]))

it works.

View 3 Replies View Related

Modules & VBA :: Subform Calculations Based On Parent-form Data?

Jul 17, 2013

I have a form with a subform in a navigation menu. Something like:

Navigation Form
NavigationSubform
ParentForm (header data and some unbound calculation fields)
Subform (Multiple lines tied to query)

I have some fields in the ParentForm (i.e PF1, PF2) that would effect the values on the Subform (i.e. SF1, SF2). Also, there are some user editable values on the subform that will calculate the remaining fields on the subform (still using data from parentform).

My calculations work fine for the changes made on the subform. However I need to be able to calculate all the children lines on the subform when the form loads or a change is made to certain fields on the parentform. My On_Current event only wants to recalculate the first line.

Parent Form
PF1=10
PF2=3
Subform QTY ADJ SF1 SF2
Line 1 5 .05 10.6 112.89
Line 2 8 .14 10.38 105.15
...
Line N
SF1=PF1+(PF2/QTY)
SF2=SF1*(SF1+ADJ)

So if PF1 or PF2 were changed then all the lines is subform would recalc SF1 and SF2. If changes were made to QTY or ADJ, then that line would recalc SF1 and SF2.

View 3 Replies View Related

Calculations And Tests In Data Entry Forms

Feb 22, 2005

Hi all,

I have a data entry form with several values that could be calculated and entered if I knew how. The FAA requires logging and tracking values that result in the following fields:
TimeSEL
TimeXC
TimeNight
TimeDay
TimeDual
TimePIC
TimeTotalFlight

My last flight resulted in entering 3.4, 3.4, 0, 3.4, 0, 3.4, 3.4

My question is: is it possible to add some sort of test expression in the TimeXC field which would examine an associated check box (i.e. "Was flight XC?") and if it was checked then would automatically calculate the flight hours (TachStop-TachStart) and enter the value in the TimeXC field?

Thanks for any help.

View 2 Replies View Related

Calculations On Main Form Do Not Reflect Calculations On Subform

Sep 28, 2015

I have solved the problems getting values on the subform. I have not in getting values on the subform.For instance, on the one titled phone use the formula in the tutorial is:

=[sbfCustomerRoomUse].[Form]![txtTotalPhoneUse]

#Error results when the doc is put into a form mode.

Now when I input each value in the equation above separately.I still get no entry.

For " = sbfCustomerRoomUse" , I get #Error;
and for "= txtTotalPhoneUse", I get #Name.

CustomerRoomUse and txtTotalPhoneuse are from the subform that was dropped into the customer form in a previous step. It shows that explicitly when designed sbfCustomerRoomUse on the main that CustomerRoomUse come from a subform. This does not seem to need to be done with txtTotalPhoneuse, and I am not sure why. Neither one gives me a desired calculation result.

View 14 Replies View Related

Queries :: How To Trigger By Date To Create A New Entry In Database Based On Old Entry

Aug 6, 2014

I have a database that makes use of standing orders. That means that if a client has a standing order to receive products during for example 4 time as year (quartely at the end of the month). to automate the new entry by copying an old entry in the database.

Let's say I have a client where we will have to send a product at the end of June, it will look at a field where the next send date is, and when it reaches 2 weeks for that date, to create a new entry in the database based on that entry. This way, it will pop-up in our open cases and we are aware of it and also will be visible in our report.

View 1 Replies View Related

Grabbing Values From A Table Based On Form Entry

Jan 5, 2006

In my database I have 4 tables for a small painting and decorating business.

These are:
tblCustomers, tblOrders, tblStock, tblPayments.

I have a form based on tblOrders to add new orders when they come in from customers.

In this form there are 3 important fields: "CustomerID" (Lookup to tblCustomers), "StockID" (Lookup to tblStock) and Quantity (Entered by user).

However, I want a calculated field that will be worked out automatically when the user is entering these details. The calculated field should go to tblStock and grab the "Cost" field.

However the main problem is specifying what price it will grab from the table. I want it to get the "Cost" where "StockID" = (the stock ID selected in the lookup). I then want to multiply the price by the quantity which the user has entered.

The control source should be worded something like:
= ([tblStock]![Cost] where [StockID] = StockID)*Quantity

but that isn't valid.

Could anyone point me to a piece of VBA or some way to get around my problem?
Many thanks for any help,
Stephen

View 2 Replies View Related

Conditional Formatting - Form Based On Text Box Entry

Mar 24, 2006

Hello,
Could some kind person out there help me out please?
I have been 'dabbling in' the area of conditional formatting, particularly in the area of changing the colour of my active form based on the result of what’s output on a text box. I have had some success in this area but where I am getting confused is getting the colour of my form to change on the basis of what is entered into a text box control that does not match exactly. My forms background changes to the required colour when its value is ‘set exactly’ but what I am trying to achieve is to change it according to the first letter of entry regardless of what follows? It always starts with a letter, either a ‘D’ or ‘V’ to indicate whether its ‘Vital’ or ‘Desirable’ and is subsequently followed by no more than 6 numbers and finally a letter. Basically I need my form to change colour based on the whether it is either a ‘V’ or a ‘D’ irrespective of what follows???

What I have been using so far is:

Private Sub Model_Number_AfterUpdate()
If Me.Part_Number = "V111145K" Then
Me.Section(acDetail).BackColor = 12632256 'Light Red
Else
Me.Section(acDetail).BackColor = 8421631 'Blah Gray
End If
End Sub


Private Sub Form_Current()
If Me.Part_Number = "V111145K" Then
Me.Section(acDetail).BackColor = 12632256 'Light Red
Else
Me.Section(acDetail).BackColor = 8421631 'Blah Gray
End If
End Sub

Can anyone help me out of a ' sticky situation'..

Ta Very much

Carol

View 3 Replies View Related

Modules & VBA :: Change Query SQL Based On Form Entry?

May 30, 2014

I have a form where customer data is entered. one field serves as a criteria for a query. If, for example, A is selected in the form the query uses the criteria "A" in the X field f query. However, if B is selected in the form, the query uses the criteria "B" in Y field of query.

I can easily do this by setting up two queries and having criteria A run in X field of query and similarly for B. However, it seems as if there should be a way for me to have one query and simply use the criteria in a different field.So, is there VBA that will update a given query's SQL to use one criteria in one case and another critiera in another case?

View 1 Replies View Related

Tables :: Autofill Entry In Record Based On Form

Aug 15, 2015

I want to be able to pre-fill records with information based on a form every day for each client. This needs to display in a datasheet view showing all clients for each location.

Each day the subform needs to show the expected results of that day's activity with clients if all default conditions are met. (i.e. client receives a call that day based on expected conditions calculated in a form for that day).

View 3 Replies View Related

Forms :: Default Entry Into Subform Based On Data Entered In Main Form

Apr 23, 2014

I have a form that request the User to enter a Parcel ID number (99-9a-99-99aaa-aaa-aaaa). The sub form asks for the subdivision number, block number, and lot number. The subdivision number, block number and lot number are normally part of the Parcel ID number but sometimes they can be different. I want the sub form to fill in the Subdivision number, block number, and lot number as a default but allow the user to change the value if needed. currently the sub form will only show what is entered into the table itself if a change is made on the main form the sub form does not reflect the change.

View 3 Replies View Related

Queries :: If Function - Calculations Based On Field

Dec 15, 2014

I would like queries to do some calculations for me based on a field. The field in the form is a combo field and the user can choose either 1, 2 or 3. Based on the selection, I would like queries to generate some numbers for me.

This is what I have in mind but its not working.

Code : TEST RESULT: If([tlkpTEST].[RESULTID])=1, ([tlkpTEST].[RESULTID])*2, 100)

View 2 Replies View Related

Sum Query Based Off Two Columns

Apr 15, 2008

I have a problem trying to sum a numeric column based off another column in the same table.
What I am trying to accomplish is to sum a numeric column if a corresponding column is Not Null.

I have uploaded my sample DB that to illustrate my problem. The query I am testing with is called Query1 and I am looking at summing point values from the task table grouped by the test case those tasks are in. I can easily sum the total number of points for each test case. But when I add another field to the query to sum the point values of the task in the test case that have a date entered into its corresponding date field it will not sum them correctly.
The result I get is the same sum total is shown for all three fields in the query.

I have tried several attempts at making criteria for this to work but nothing seems to work the way I want it too.

Here are my attempts:
IIf(Not (Sum([Task].[Points]))=IsNull("Attempted_Actual"),Sum([Task].[Points]),0)

IIf(Not (Sum([Task].[Points]))=IsNull("Completed_Actual"),Sum([Task].[Points]),0)

Not IsNull("Attempted_Actual")

Not IsNull("Completed_Actual")

My question is:

Is it possible to do want I want all in one select query or would this be something to code in a VBA module and then just call that module in a report?

My plan was to do this all in a query then build a report to display this query.

I have attached my dummyDB file and you can look at the Test_Case Table and expand each test case to see the data I have populated.

Any guidance would be appreciated.

Thanks,

View 4 Replies View Related

Combo Box Based On 2 Columns

Feb 15, 2006

Hello everyone,
I have a combo box on a form tha displays information based on 2 columns from a table.What I want the combo to do is to allow the user to select the row he wants and for the field that is bound to the combo box to store the outcome of collumn1 + collumn2 (string values).
Is this at all possible and if so how can I do it cause I can't find a way.
Thanks for all your time and effort :)

View 3 Replies View Related

Combo Box Based On 2 Columns

Feb 15, 2006

Hello everyone,
I have a combo box on a form tha displays information based on 2 columns from a table.What I want the combo to do is to allow the user to select the row he wants and for the field that is bound to the combo box to store the outcome of collumn1 + collumn2 (string values).
Is this at all possible and if so how can I do it cause I can't find a way.
Thanks for all your time and effort :)

View 3 Replies View Related

Searching Database Based On 3 Columns

Aug 23, 2011

I have a requirement of searching the database based on 3 columns. I have attached the search form and the code.

Private Sub cmdSearch_Click()
Dim strSQLHead As String
Dim strSQLWhere As String
Dim strSQLOrderBy As String
Dim strSQL As String
Dim strJoin As String

[Code] .....

View 12 Replies View Related

How To Get An Automatic Entry Based On Criteria

May 9, 2007

My database tracks individuals qualifications. These qualifying scores relate to Unqualified, Expert, Advanced etc. What I want to do is add a entry into my roster table which will have the qualification score.
I then want to have the score and actual qualification (Advanced, Expert, etc) in a Report.
The qualification type is a range (less then 30, 31-40, 41-50), and I think that I will need a BETWEEN statement. What I am not sure of is how to put this together to make it equate the number with the equivalent word in a report.

Thanks
the brewmeister

View 3 Replies View Related

Input Value Based On Textbox Entry

Sep 28, 2006

basically i have a textbox called

'mytext'

when my user types t1 in that textbox, and tabs to the next field, i want the value LHR - T1 to be placed inside 'mytext'

how can i do this?

View 2 Replies View Related

How Do I Get The Average Speed Answered Based On The Sum Of Two Columns?

Oct 19, 2005

I am trying to get the Total average speed answered (ASA) based on two columns sums. (i.e. sum of calls handled and sum total speed answer). I need to get the total avg speed ans (formula is sum of total speed answer (tsa) divided by sum calls handled). I did an average query for asa but it gives me the avg for the rows(records) of the asa and not of the two columns. I need it for the two columns above. I was trying to change the formula in report design view, but I am not coming out with the correct answer. Are there any suggestions?

Please help!

View 5 Replies View Related

Modules & VBA :: Create Columns Based On Subgroupings

Nov 19, 2013

What I'm trying to do is create columns based on subgrouping. See attached example in excel.

It's almost like transposing but not it.

The names of the columns don't really matter. It's to be used in a dynamic excel workbook for looksups.

View 5 Replies View Related

General :: Calculated Columns Based On Another Table

Aug 18, 2012

Is it possible to have a calculated column based on another table. i have a 1 to many table. the table that is the parent hold a start time. all of the 'many' have an individual time length. i would like a calculated column in the parent table showing the full length. is it possible or will i have to use an update query?

View 1 Replies View Related

Finding And Removing Records That Sum To Zero Based On Two Other Columns?

Apr 3, 2013

I have a dataset of about 70 columns but would like to remove any two rows that sum to zero (based on two columns) if the column "Units" are the same for the same "Bar code" thus to say "for the same 'Units' (for any two lines), check to see if their respective 'Bar Codes’ are the same and if so remove if their amounts sums to zero".

I have the following code which seems to be working but for relatively large data ( about 5k), it does not select all the data that meets the criteria:

Code:
SELECT *
FROM datatable
WHERE ((([datatable].[Unit] & [datatable].[Bar Code] & Abs([datatable].[Amount]))
In (SELECT datatable.Unit&datatable.[Bar Code]&abs(datatable.Amount)
FROM datatable
GROUP BY datatable.Unit&datatable.[Bar Code]&abs(datatable.Amount)
HAVING count(*)>= 2 and sum(datatable.Amount)=0)))
ORDER BY [datatable].Unit & [datatable].[Bar Code] & Abs([datatable].Amount);

identifying the bug in this code that makes it not pull all the data meeting the criteria.

See sample data below:

# Unit bar Code Amount
1. AAB Mac1 2.75
2. AAB Mac1 -2.75
3. AAB Mac1 1.24
4. AAC Mac3 35.00
5. AAC Mac3 -20.00
6. AAC Mac3 20.00
7. AAD Mac3 16.11
8. AAC Mac2 11.00
9. AAC Mac2 -11.00
10 AAC Mac3 12.05
11 AAF Mac3 -12.05
12 AAD Mac3 -16.11
13 AAC Mac4 35.00

I want to select records # 1, 2, 5, 6,7,8,9 and 12 since they are same units having the same bar code and their sums total zero.

View 6 Replies View Related

Automatic Field Entry Based On Several Fields

Mar 16, 2008

Hi

I am an amature with databases and a bit rusty too. Can anyone help me with this problem?

basically I want to automatically enter a unique asset code for one table that is based on product and manuafacturer's codes from other tables

e.g. for audio visual eqipment

The first table would be a manufacturers table with the manufacturers code as the primary key e.g. JVC, HTC (hitachi), SHP (sharp) etc

The second table would be a product table with the product code as the primary key e.g. AMP, DVD, LCD (lcd tv) etc.

( i would also have a customer table and an asset repair table)

In my asset table I would like to enter an asset code as the primary key of the type JVC-DVD-001, JVC-DVD-002, HTC-LCD-001 etc by looking up codes from the other tables and then adding the last number.
(Ideally once the manufacturer's code has been selected only the product codes for products made by that manufacturer would be displayed).
(Also it would be ideal if referential integrity could be set up so that the asset code components can only contain valid manufacturer and product keys.)

The asset code cannot be a compound key as I want to have a repair records table in which the asset code is entered as a single field with referential integrity to the asset field in the asset table.

When entering repairs I would like the lookup field to display only the assets belonging to a particular customer.

View 4 Replies View Related

Creating A Table Based On The Latest Entry

Jun 14, 2005

I am working on a jobs database where employees enter information where the job is being handed off to. I want to create a table showing the latest job entry by date. The jobs are listed by "Job Number" and when I try to create a table and remove the duplicate "Job Number" it does not always remove the oldest entries.

Any help would be appreciated.

View 1 Replies View Related

Valildation Based On Previous Cell Entry

May 8, 2006

I'm new to this forum and I'm sorry if this is a repeat question...I looked in previous threads, but could not find my particular questions.

Here's the situation:
I enter a bird name in my form (I've set up a combo box of 200 choices). Each bird species has an max and min possible wing measurement. So when I enter a newly captured bird in my form, the wing measurement must fall between the minimum and maximum for that particular species. I've done similar things with cascading combo boxes, but can't figure out how to the do a between-type statement in this situation.

Thank you much,
Erik
LSU, Baton Rouge, LA

View 9 Replies View Related

Calculations On A Form

Mar 31, 2005

Hello,

I am trying to creating a database for attendance issues for employees. I have the following text boxes on my form:

First Date Absent, Last Date Absent, Reason for time off, Disiplinary action, point value, comments

Our attendance policy requires certain disciplinary action be taken when the point values total a certain amount in a specified amount of time.

With all that said, I am wondering if I can create a text box on this form to calculate the point value within a 30 day period and a second text box to calculate the point value within a 12 month period. I can sum the total points, but I am unsure how to sum the points within the date ranges needed. Any ideas?


Thanks so much!!!

View 1 Replies View Related







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