Calculating And Storing New Values From Existing Ones

Jan 30, 2006

I am pretty new to access, I will get right to it.

I am scoring rating scales. I want to be able to enter just the responses from a person, say on 80 questions (preferably into a form), where all the answers are 0 to 3 and have Access add the questions into the right groups to calculate all the sub-scores of the rating scale.
For example, Scale A may consist of Questions 1, 6, 15, 29, 35, and 70
I need access to add those up and save them so I can use them in a report.
I also need access to add the frequency of certain questions that were responded to with the answer 3.
I have gotten Access to calculate the scores in a form by adding extra variables to the form, erasing the variable name out of the white box and putting in the syntax =[Q1]+[Q6]+[Q15], etc. in place of a variable name but it just sits there on the form and doesn't store it anywhere.
Thanks

View Replies


ADVERTISEMENT

Calculating Vs Storing Data?

Aug 9, 2007

Dear Access Expert.

I have a choice of either calculating a field or storing it.

Specifically, I have two pieces of data ... departure time and arrival time which I have taken from another source. This other source also has the difference between these times. Currently, I am not storing the difference in a field as I have opted to calculate it in my database.

However, I am thinking that maybe it would be better to store it vs. calculating it because I have 35, 000 records and storage space is not going to be an issue.

What do you suggest if I my main concern is speed and effeciency?

Thank you very much.

View 6 Replies View Related

Storing Values?

Mar 10, 2006

Howdy

I know that you shouldn't store totals in Access, but I can't think of a way around what I want to achieve and wondered if anyone had any ideas.

An item on stock will move from one location to another, I will need to list the 'balances' of this stock at each location, easy enough to do, but there are hundreds of item movements per day and the users do not want a history of movements to be kept but do require an ongoing balance.

e.g.
100 of the item moves from A to B, this movement record gets deleted, how would I know that the item stock at A has been reduced by 100 and B raised by 100?

View 1 Replies View Related

Storing Array Values In Access

Aug 2, 2007

Hello,

I wanted to catch a little advice if I may be so bold. I have a HTML form with a series of checkbox values.

Code:<form method=post action=form-test.asp><input type=checkbox name=drinks value='cocal-cola'>cocal-cola<input type=checkbox name=drinks value='sprite'>sprite'<input type=checkbox name=drinks value='lucozade'>lucozade<input type=checkbox name=drinks value='oasis'>oasis<input type=submit value='Submit'></form>

Normally I would gather these up into an array using ASP and email the split up comma separated values out to the client via an email.

Code:drinks=Request("drinks")drinks_split=split(drinks,",")'email drinks to client

However, this time I need to store those values in an access database. I am assuming that the client may want to run queries on the different array values, for example, how may people chose coca cola and sprite.

Is it better to store the value separately - one Ms access field for coca cola, one for sprite (which is what I thought I would have to do to create queries on this) - or can they be stored under one field heading "drinks" and queries be performed on different values within that field?

Could someone explain a quick way of doing the latter i possible in MS access and the SQL for querying?

Cheers,


trufla

View 2 Replies View Related

Tables :: Storing Calculated Values?

Mar 7, 2014

I understand the conventional wisdom of not storing calculated values on the table, but I have a need to do do so. On my Input Screen I have a generated Certificate # derived from 5 single value fields keyed onto the screen. This certificate # is unique to those 5 single-value fields which should not be repeated. I want to store it on my Table as the Primary key with "no duplicates" so that if a keyer keys the same 5 single-digit values , thus creating the same Certificate #, he will get a MsgBox back telling him he cannot add a duplicate entry.

attached is my table and Input Screen.

View 3 Replies View Related

Forms :: Storing Multiple Values

Apr 29, 2014

In my database (attached), I need to be able to store multiple notes for tasks performed in various cities -- each note has a small description (more on that at the bottom of this post). For the "San Antonio, TX" task, notes "Heartbleed" and "BAT Scan" apply. From what I understand, this is a many-to-many relationship which will require the use of a junction box. My structure is like this:

tbTasks
TaskID (PK - autonumber)
Task

tbNotes
NotesID (PK - autonumber)
Notes
Details

jcttbTaskNotes
TaskID (FK from tbTasks)
NotesID ( FK from tbNotes)

My mainform (fmTasks) is based off the tbTasks table; my subform (subfmTaskNotes -- continous form) is based off the jcttbTaskNotes junction table. The Master/Child link for the subform control is TaskID. In the subform I have a combo box bound to the NotesID field in the junction table that allows me to choose a note.

My problem: I also have a "Details" text box in the subform that I want to be populated with the description of the Note selected in the combobox mentioned above. For example: if I choose the "Heartbleed" note, then I want that "Details" field to be populated with "Mitigate vulnerability associated with CVE-2014-0160". I assume it has to be somehow tied to the "Details" field of the tbNotes table, but how to implement that. How do I get the Details text box to display the details for each note?

View 1 Replies View Related

Tables :: Storing Calculated Values In Table

Jan 8, 2014

I know that as a general rule one should not store calculated values in a table, but if I need to include such a value on a number of forms, and calculating that value involves manipulating an ADO Recordset, does there come a point where the performance hit outweighs the design "quality"?

View 1 Replies View Related

Forms :: Subform Storing Duplicate Values

Jan 20, 2015

I am fairly new to using access and am currently creating a database for a small bakery. They have new legislation whereby they have to list all their product ingredients and any allergens in the ingredients must be shown in bold.

So far I have 3 tables as follows:

Products (*ProductID, ProductName, Photo)
Ingredients (*IngredientID, Ingredient, Allergen)
Product_Ingredients (ProductID, IngredientID)

I have 2 forms set up:

one to input ingredients and a checkbox if it is an allergen

The other has a main form and subform to get ProductName, Photo and then a combo box in the subform to add ingredients.

Everything appears to be working ok but I have an issue with my subform. The combo box has no duplicates and lists the ingredients in alphabetical order as I require. However, if I view the subform separately from the main form I see that it is storing all the ingredients selected for each product. Therefore it is storing a lot of duplicate information.

View 3 Replies View Related

Adding Feild Values And Storing In Another Field In Same Table

Mar 19, 2005

I have been struggling with this and would appreciate any help. It sounds so simple!!! I have 4 fields (Unit Price 1, Unit Price 2, Unit Price 3, Unit Price 4) in a Orders table that I want to add together and store in a 5th field (Sub Total) in the same table using a form. I am having problems with the sum and I am unsure how to get it to show in the Sub Total box in the form as well as the table. Thanks for your help!!

View 2 Replies View Related

Lookup Values In Existing Database

Dec 13, 2007

I've been racking my brain the last couple of days trying to figure out how to solve this problem, and I believe I've reached the end of my rope. I have a feeling that this isn't very difficult to a more savvy Access person, but I am at a loss.

Here's the situation. I have received a fairly large DB containing CCTV data for sanitary sewers. There are primarily two tables I'm dealing with, one lists a number (auto-number) for each pipe that was televised. Simple enough. The other uses this legacy number to show all deformities or service leads within a particular length of pipe. For example, for run X, there may be 7 rows in the table with X as the ID, one for each service lead along that length of pipe (I hope this is making sense :()

Ultimately, we need to tie this database into our GIS theme. To do this, I will need to add to the PipeID number from our GIS theme to the access table. What I've done so far is to create a new table in the DB with the number for each pipe televised, and I've manually added the corresponding PipeID number from the GIS in the second column. What I'm hoping to do is add a new column to my occurrence table so that for each occurrence X, I can add the GIS PipeID number. Perhaps this would make more sense:

Run # PipeID
1 S143
1 S143
1 S143
1 S143
2 S231
2 S231
2 S231
2 S231
3 S543
3 S543
3 S543

I've gone ahead and created the relationship between the newly created table and the existing table based on that auto-number field, and I've made the new PipeID column a combo box. This shows all of my PipeID numbers, which is a good thing. I'm hoping there's a way for it to automatically recognize the auto-number field and populate the PipeID field accordingly.

Have you ever known what you want to say, but not quite understood how to say it? That's kind of how I feel about this question, and I do apologize if I've made no sense. But if I have, and anyone has any suggestions for me, they would be greatly appreciated.

Cheers,

Azimuth

View 3 Replies View Related

Calculating Values

May 2, 2006

I am not sure whether I posted in the right place but here's the deal: I have a form and a subform based on two tables and on the form I create a mathematical expression concerning a field which is based on another. The results are correct but of course the values in the table remain the same (null). Is there a way to have a formula in the table and then when I create the form based on that table to have the formula ready? BTW where are the data stored if they are not stored in that table?

View 1 Replies View Related

Queries :: Combo Box Won't Display Existing Values

Nov 28, 2013

I had a text box in a contacts DB form for the State field (Named txtState). It worked fine. I decided to replace it with a combo box. Now not only will it not display the existing values, but to rub salt in the wound it won't allow me to select from the drop down list. The error message that says that the field is too small for the value. I tried changing each of the yes/no properties one at a time but none of them change the outcome.

I have attached a couple of screenshots:

ErrorMessage.jpg
Properties.jpg

View 3 Replies View Related

Tables :: How To Not Allow A New Record To Have Similar Values As Existing One

Jul 29, 2015

I am having an issue with duplicates in my table. I have a table, called "Part Mods", that has about 12 fields in it. the first 2 of them are "Mod" and "Part Number". I have a form where someone can insert data into this table and what i want is for them to NOT be able to insert a record with the same Mod/Part Number combination of an already existing record. I have "indexed" turned on but I am pretty sure that is for every field.

View 3 Replies View Related

Calculating Values In A Text Box

Nov 24, 2005

Hi everyone

i have been struggling on this database that i am developing for a while and i haven't found a solution yet.

i have a Two tables

Table 1
"Attendance" with fields
Attendance ID,
Hours,
Group ID.

table 2
"Days" fields,
Day ID
Week beginning
Monday
Tuesday
Wednesday
Thursday
Friday
Hours

now i have created a form from these two tables, and what i wanted was each day of the week i will input a value under the day and i wanted it to be added up and then the result outputted to the "Hours" Box.

My dilema is that although i have figured out how to add up the fields in the form view by changing the Control source for the Hours box to something like
=[Monday]+[Tuesday]....[Friday]
this will only display the value in form view, the table will not update. i guess this is because the control source is not relating to te table anymore.

so if someone has some other ideas i would appreciate it.
regards
Yusef

View 3 Replies View Related

Calculating Values Vis-à-vis A Report

Aug 30, 2004

Just wondering if anybody could help me on this. I have encountered a problem in producing a report based on my main form (recordsource: tblStudents) and subform (recordsource:tblStudentsClasses), which are joined through their respective StudentID fields. My problem pertains to the fact that I just couldn’t seem to find a way to make my report produce a calculated value using an unbound control. I need it to show the average grade a student gets every semester by multiplying his individual class grades against its corresponding credit units and then add them together, and divide them against the sum total of that particular semester’s credit units. For example, a grade of 1.0 for psychology 101 multiplied by that subject’s 3 credit units + a grade of 2.0 for chemistry 101 multiplied by that subject’s 5 credit units and then divided by the sum total of those two subject’s credit units of 8.

I kinda find it complicated because each semester is made up of between 1 to 10 classes, so it would be inappropriate to just put the one average computation on a footer since each semester has to have an average grade of its own. Tried lots of procedure already and couldnt make anyone of them work. Another fact is that a credit unit of –3 and grade of 5.0 shouldn’t be included in the computation at all. And some of my data are supplied by combo boxes in the subform – cboCreditUnits and cboGrades (rowsources: tblClasses and tblGrades). How should I go about this all? Any ideas will be highly appreciated.

Thanks!

View 1 Replies View Related

AfterUpdate - Values Not Calculating

Apr 15, 2008

Below is the code I have created to calculate the values "CalcValue" and "CalcWork". The values for AssignValue, AssignPriority, Complex, Effort, Goal are all assigned by the selection made in the referred to/related combo boxes. However, the caluclated values return the concatonation of the intergers and not the sum of the integers. Additionally, if I am to use the CDec() function how do I specify the number of decimal places?

what am I missing here?

thanks!

Private Sub cboAssignPriority_AfterUpdate()

Dim CalcValue, AssignValue, AssignPriority, Complex, Effort, Goal, CalcWork As Integer

Value = 0
AssignValue = 0
AssignPriority = 0
CalcValue = 0
CalcWork = 0

AssignValue = Me!cboAssignValue.Column(3)
AssignPriority = Me!cboAssignPriority.Column(2)

Complex = cboDBObjectID.Column(2)
Effort = cboTaskTypeID.Column(3)
Goal = cboAgencyGoalID.Column(2)

Value = AssignValue + AssignPriority
CalcWork = Complex + Effort + Goal
CalcValue = CDec(Value)

CalcValue = Me!lngCalPriority

Debug.Print "Complex="; cboDBObjectID.Column(2)
Debug.Print "Effort="; cboTaskTypeID.Column(3)
Debug.Print "Goal="; cboAgencyGoalID.Column(2)
Debug.Print "AssignValue.Column(3)="; cboAssignValue.Column(3)
Debug.Print "AssignPriority.Column(2)="; cboAssignPriority.Column(2)
Debug.Print "Value ="; Value
Debug.Print "CalcValue ="; CalcValue
Debug.Print "CalcWork ="; CalcWork

End Sub

View 14 Replies View Related

Calculating Column Values

Mar 15, 2006

I have a table with numberous records with Values in a number of fields. In a report I am showing all these records but at the end I want to tally the values.

Whats the best way to do this?

I thought of placing a text box in the form that will run some code to calculated the total....

What kind of code would I use to accomplish this?

Is there an easier way?

View 2 Replies View Related

Calculating Values From Subreports

Jun 16, 2006

I have a report that contains 3 subreports with a calculated total text box in each. I am trying to calculate the total of these text boxes from the subreports in the main report but I keep getting an error message (#Name?). Is it possible to calculate calculated sums from subreports in a main report?

View 1 Replies View Related

Queries :: DateAdd Function Using Existing Field Values

Nov 26, 2013

I have a query in design view that consists of two calculated fields at the moment. One of the calculated fields is supposed to add one year onto a value from another date/time field called "Last_Date."

The expression I've written in the "Field:" box in Query design view is

Expr 2: DateAdd('yyyy',1,[Last_Date])

When I try to run the Query it doesn't work! Access just prompts me to enter a parameter value for Last_Date.

View 1 Replies View Related

Calculating Daily Values For A Month

Jul 7, 2006

Hello all. I hope someone can lend a helping hand here. I am trying to create a query that will calculate a 24 hour value for every day of a month. For a month a go out a read meters. I need to get the difference of two readings by subtracting the first day from the second day and so on until I get to the end of the month. So, Day2-Day1=24 hour value, Day3-Day2, Day4- Day3... I have this in a spreadsheet but can't seem to grasp it for a query in a database. I would appreciate any help. Thanks.:confused: :D

View 1 Replies View Related

Calculating The Difference Between 2 'Time' Values

Nov 12, 2007

Hi, I wonder if someone can help me with the following problem:

I have a table with 2 columns,start time and end time, both containing time values in a four digit format, eg 0930 being 9:30am etc.

I have made a query to convert these into the standard format (eg 0930 becomes 09:30), using left/right and & functions.

Now I use the timevalue function to convert the string , eg 09:30m into a recongnised time value.

The problem is now I want to find the difference between the start and end times- but when i try a simple end_time - start_time formule it returns a long number which i dont understand, rather than just giving me the difference betwen the two times.

Im sure there is a simple way to do this which I dont know, can anyone help me out? Thanks in advance!

View 1 Replies View Related

Calculating The Difference Between 2 'Time' Values

Nov 12, 2007

Hi, I wonder if someone can help me with the following problem:

I have a table with 2 columns,start time and end time, both containing time values in a four digit format, eg 0930 being 9:30am etc.

I have made a query to convert these into the standard format (eg 0930 becomes 09:30), using left/right and & functions.

Now I use the timevalue function to convert the string , eg 09:30m into a recongnised time value.

The problem is now I want to find the difference between the start and end times- but when i try a simple end_time - start_time formule it returns a long number which i dont understand, rather than just giving me the difference betwen the two times.

Im sure there is a simple way to do this which I dont know, can anyone help me out? Thanks in advance!

View 1 Replies View Related

Reports :: Calculating Values In A Report

Jul 29, 2015

we have a field in a report, and currently, there is a text box with a control source of

=IIf([Flag1]="Capital",[Reserve]-Sum([Text52]),"")

They want me to add to it, where if [Text106]="0",[Reserve]-[Text52], otherwise it's [Text106]="0",[Reserve]-[Text106].I am able to do this second part OK with

=IIf([Text106]="0",[Reserve]-[Text52],[Reserve]-[Text106])

But then I lose the [Flag1]="Capital" part.

View 4 Replies View Related

Queries :: Append Query To Check For Existing Values And Add Only Those Not Present

Oct 23, 2013

I have a database used to manage teaching assignments (which kid is assigned to which teacher so to speak). I have this relationship defined through three tables, a teacher table, a student table, both with unique ID's. The third table is used to define the assignment. Also, the kid table has an extra GroupID. The group ID is what is used to define. So in the definition table, Teacher 1 is assigned to Group 1, and so on (though their may be 20 kids in group 1). When a new teacher is added to the teacher table, I need it to add it to the corresponding field in the definition table. The groupID is in the table as an Autonumber so that will populate automatically.

I need to appendTeacherID to tblassignment (TeacherassignmentID) and have only one occurrence of the TeacherID. So, if I have teachers 1-8 listed, each assigned to a group# in the tblassignment, and I add Teacher 9, I need it to add Teacher 9's unique ID to the TeacherassignmentID field without adding 1-8 again. I can't figure out how to "check" for ID's 1-9 and add only those I've added to the teacher list that aren't already assigned to a group.

I've tried a few different SQL queries append/select queries but nothing seems to do what I need it to do....

View 1 Replies View Related

Queries :: Calculating The Difference In Values In Two Different Records?

Aug 12, 2015

I have a query that must calculate the different between values in two different records, based on the date.

The underlying ORIGVALUES table contains:

Date; Value; Diff
1/10/15; 100; (this is what I am trying to calc)
1/11/15; 101; (this is what I am trying to calc)
1/14/15; 102; (this is what I am trying to calc)

What is the best way to have each record calculate the selected date value divided by the prior date value)? E.g.

For 1/11 the math is 101 / 100 (so the 1/11/15 record would show the result of 1.01)
For 1/14 the math is 102 / 101 (1/11 to 1/14 represents a weekend)

I could copy the prior day values to the current day records and do that math easily but that is very redundant.

View 5 Replies View Related

Queries :: Calculating And Returning Values From External Database?

Jan 2, 2014

I have created a link to a CRM system that we have, and am using access as an interface for a specific task with the data contained within this CRM.

I have written a query that pulls to fields of data together but cannot get the two to calculate.

Within SQL server management studio I use the following query to give me a numerical response.

SELECTPRCPRICE,SUM(CAST(VL2BENEFICIALASNUMERIC (20,6))*CAST(PRCPRICEAS MONEY)))ASHOLDINGVALUE

Access doesn't appear to accept the cast functionality / how I can perform a similar functionality with access?

View 9 Replies View Related







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