Calculated Records

Mar 14, 2006

Hi

I have read a number of these threads now where it says you should not generate new records based on calculated fields. So can some one help with my following problem.

I am creating a customer/contract database where when an order is created, a record set is generated to store dates for invoice periods. I have done this by using the start date of the contract and then adding onto this a value of x days that updates a table giving y records based until the end date of the contract.

Is this the best way to do this or can some one tell me a better way.
Thanks

View Replies


ADVERTISEMENT

3 Queries, Need Records From Both Even When The Calculated Value = 0

Feb 19, 2008

(Phone usage report) I have 3 queries, Query 1 counts number of calls in for a phone extension. Query 2 Counts the number of calls out (dialed calls). The information is keyed on the phone extension. Query 3 runs the first 2 queries (By date via parameter). Each query is keyed on Extension, I only want to see the number of calls in and out for each extension. Problem, when a phone extension being counted has no calls (In or Out), it leaves the phone extension out of the report. I want to see all the phone extensions info regardless of the count (if it = 0). Does anyone know a way around this? Thanks in advance for your help.;)

View 1 Replies View Related

Top X Records Using Calculated Field?

Dec 18, 2012

I am looking for a way to query to top 10 or 25 records, and can you do this using a calculated field as the basis for the ranking?

View 1 Replies View Related

Prevent Duplicate Records For Calculated Field

May 3, 2013

I have a table for storing vehicles. The table contains the following fields:

VehicleID
Make
Model
Trim
Build Date Begin
Build Date End

Vehicle Name - a concatenation of 'Make' 'Model' 'Trim' 'Build Date Begin' and 'Build Date End'

The 'Vehicle ID' is the primary key, the rest of the fields can't be indexed with no duplicates. Is there a way of preventing one from saving a duplicate VehicleName on the table since it is a calculated field.

View 3 Replies View Related

Having A Records Calculated Field Get Info From Previous Record

Jan 20, 2007

I have a table (tblStats). The user enters his current weight in a field named Wt. There are 2 other fields in the table named WkLoss and TTDLoss. These two fields are intended to be calculated. What I am trying to do is when the user enters his current weekly weight figure into the Wt field, I want the field WkLoss to get the user's weight from the previous record and place the difference in the field WkLoss. Also I would like to keep a total to date in the TTDLoss field.

So far this has been my attempt.
Code:Public Sub setWeight()Dim rs As DAO.RecordsetDim Startval As DoubleStartval = 190Set rs = CurrentDb.OpenRecordset("tblStats")'Select records to be updatedSet rs = CurrentDb.OpenRecordset _("SELECT * FROM tblStats ") rs.MoveFirst Do rs.Edit'field to update rs.Fields("WkLoss") = Startval rs.Update rs.MoveNext'increment step value Startval = WkLoss - Wt Loop Until rs.EOF rs.Close Set rs = Nothing End Sub
All I get is the value 190 placed in the first record and all zeros in the subsequent records. Also I don't have a clue as to how to get the Total To Date field to calculate.

Any help would be appreciated
Thanks for your time.

View 1 Replies View Related

Queries :: Put Sum Of All Records In Calculated Field And Divide It By Predefined Value

Feb 6, 2014

I want to take the sum of all records 'cost' i.e.
record 1 - 2
record 2 - 4
record 3 - 3

I want to take the sum and put it in a calculated field and use it to divide it by a predefined value 'no of responses.'

So I make the field in the query -> Calcfield: (Sum([cost])/[responses])

I am pretty new to this and i don't understand why this won't work? Is it something to do with the row Group by (total.)

View 3 Replies View Related

General :: Calculated Fields - Reading And Modifying Records

Aug 2, 2012

I am new to Access and this is likely a very elementary question. I recently created a database to track status of projects including progress and funding. The table contains several fields that record various aspects of funding. These fields are added (very simply) and create a calculated field that is a summation of those fields. The table currently contains approx 60 records. The calculated field functions for 2 or 3 records and will not calculate further. I have tried this in a form as well as a query. The form will change the same number, 2 or 3 records. When the query is run the result shows only the 2 or 3 records. What I am doing that is only reading and modifying those records?

View 3 Replies View Related

Queries :: Calculated Field Is Blank For Records With Even Primary Key

Jun 17, 2013

I have a calculated field in a query. The field name is TotatPt (this is to calculate the total points students have earned during the term). The expression is as follows:

Code:

TotalPt: [Att1Pt]+[Att2Pt]+[Att3Pt]+[Att4Pt]+[Att5Pt]+[Att6Pt]+[Att7Pt]+[Att8Pt]+[Att9Pt]+[Att10Pt]+[Att11Pt]+[Att12Pt]+[Att13Pt]+[Att14Pt]+[Att15Pt]+[Att16Pt]+[Att17Pt]+[Att18Pt]+[Att19Pt]+[Att20Pt]+[Att21Pt]+[Quiz1Pt]+[Quiz2Pt]+[Quiz3Pt]+[Quiz4Pt]+[Quiz5Pt]+[Quiz6Pt]+[Quiz7Pt]+[Quiz8Pt]+[Quiz9Pt]+[Quiz10Pt]+[MidtermWritPt]+[FinalWritPt]+[Proc1Pt]+[Proc2Pt]+[Proc3Pt]+[Proc4Pt]+[Proc5Pt]+[Proc6Pt]+[Proc7Pt]+[Proc8Pt]+[Proc9Pt]+[Proc10Pt]+[Proc11Pt]+[Proc12Pt]+[Proc13Pt]+[Proc14Pt]+[ProcPracPt]

I think this should be no complicated expression (though a bit long) and should just add the fields together. But what I get is that the calculated field appears as expected for records with an odd primary key (1, 3, 5, 7, ...) and turns out blank for records with an even primary key (2, 4, 6, 8, ...)!

I've attached a screenshot.

View 6 Replies View Related

Reports :: Sort Records By A Calculated Percentage Using Text Box Values

Jan 27, 2015

So I have a report with the following text box controls:

[Surname] & ", " & [Firstname]
=Sum([Quarter1_A]) - Named "Quarter_Total"
=Sum([Quarter1_T]) - Named "Quarter_Target"
=Val([Quarter_Total])/Val([Quarter_Target]) - Named "%Target" (Percent Format)

The report is grouped by the expression '[Surname] & ", " & [Firstname]'.I am trying to sort the records by the %Target text box. I tried entering the expression into the sort function but it still sorts by the grouped expression. I also tried sorting by the name of the text box but got the same results. How can I sort by the desired control?

View 14 Replies View Related

Basing A Calculated Field On Another Calculated Field

Apr 12, 2006

My query contains two calculated fields [TaxSavings1] and [TaxSavings2], which are based on some currency and number-type fields in one of my underlying tables.

I just created another field in my query which looks like: [TaxSavings1]+[TaxSavings2]. Instead of adding the two fields, it actually lumps the two numbers together. For example, if [TaxSavings1] =135 and [TaxSavings2]=30.25, it will give me: 13530.25. I need it just to simply add, i.e. answer of 165.25.

Does anyone know how to correct this? Thanks in advance.

:confused:

View 1 Replies View Related

Calculated Value

Apr 6, 2006

I have a table that contains 2 columns:

1 = Deduction Reference Code
2 = The Value associated with each Code.

for example

Deduction Reference Value

999997 10000
666 6000

Code 999997 duplicates the value within code 666 so I wish to set up a caluclated value to say:

If deduction reference = 999997 then the value of 999997 minus the value of deduction reference 666.

I have tried several statements but none of which seem to work.

Can anyone help?

Thanks

View 1 Replies View Related

Help With Calculated Data

May 9, 2005

I know it is said that you should not store calculated data in tables so I need some input on how to go about a problem I am having.

I am creating a DBase with some aircraft info in it. The DBase will be around for awhile so I plan to implement so sort of archiving later on.

The problem is one piece of info I am tracking is flight hours for the aircraft along with hours on certain components. I have a table with info pertaining to each aircraft by number (1 record per aircraft) one of the fields is total current flying hours. I have another table that has individual flight info including number of hours per flight. I want to add the individual flight hours with the total flight hours to get a new total flight hours. Easy with a query but if I get rid of old flights then how will you add them in the query. The only thing I can think of now is to save the newly summed number in the table which is supposedly taboo. Same with the components if it is on the aircraft during the flight it needs to get the new flight hours on it also .

Sorry if this is trivial but any ideas on how this should be done would be great.

Thanks in advance.
Frank

View 7 Replies View Related

Calculated Field

Feb 9, 2006

Hi,
I have a calculated field in a query that concatinates an order number prefix and the part number to give the order number:
OrderNum: [OrderPrefix] & [PartNum]

There are 2 tables:
tblPartsList
PartNum
PartDesc

tblPartsListCategories
OrderPrefix
OrderNumCategory

This works fine except for one of the categories. For the third category there are no entries in the tblPartsList - because they are miscellaneous.

How do I change the calculation so that it works as above where there are corresponding Part numbers in tblPartsList and otherwise gives me the order number and any 2 digits e.g. something like [OrderPrefix]*

Having been trying to do this for ages but having trouble with syntax.

View 10 Replies View Related

Calculated Field

Apr 20, 2006

hi. i wanted to kno how to have a field in a table that is calculated from other fields if this is possible. for example i have a order table which has a quantity field and a price field and i want to add a total field that will alutomatically calculate the total (quantity*price) and add it to the field. i know how to do this in a query but don know how to get that field from the query into the table. thanks for any help

View 2 Replies View Related

Calculated Fields

Sep 13, 2006

Hi,

I have read that it's not recommended to put calculated fields on a table and they should instead be calculated using a query.

How about if I need to store that calculated value in a table (e.g. field1 + field2 = value in field3)

How can I do that please?

Thanks,
B

View 5 Replies View Related

Store A Calculated Value?

Jul 9, 2007

I was once told that Access does not like to store calculated values but I would like to.

Here is my expression: =([Shift Lenght]-[Total idle time]-[Total down time])/([Shift Lenght]-[Total idle time])*(0.33*[Total pc])/([Shift Lenght]-[Total idle time]-[Total down time])*([Total pc]-[Total scrap])/([Total pc]+0.1)*100

This gives me the value that I desire, now I need to store it in the table with the rest of the records data.
I am planing on storing 3 -4 expressions like this and then averaging them.
Then building a query to the averages for the day,week,month, and year.

Please be gentle I am not very familiar with access but learning a lot:)

View 7 Replies View Related

Calculated Field

Aug 11, 2005

I am new to Access so please be gentle.

I heve started a new table and one of the fields is Date of Birth.

I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.

Any ideas on how to make this work? does it have to be a querie?

Mant Thanks

View 3 Replies View Related

Calculated Field

Aug 11, 2005

I am new to Access so please be gentle.

I heve started a new table and one of the fields is Date of Birth.

I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.

Any ideas on how to make this work? does it have to be a querie?

Many Thanks

View 1 Replies View Related

Calculated Field

Aug 11, 2005

I am new to Access so please be gentle.

I heve started a new table and one of the fields is Date of Birth.

I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.

Any ideas on how to make this work? does it have to be a querie?

Many Thanks

View 12 Replies View Related

Calculated Field

Apr 2, 2006

I have a table with customers, one with products and one transaction table. How can i produce a report, based on a query that will add up the total any one particular customer has spent to date from the transaction table?

View 1 Replies View Related

Calculated Field

Apr 20, 2006

hi. i wanted to kno how to have a field in a table that is calculated from other fields if this is possible. for example i have a order table which has a quantity field and a price field and i want to add a total field that will alutomatically calculate the total (quantity*price) and add it to the field. i know how to do this in a query but don know how to get that field from the query into the table. thanks for any help

View 1 Replies View Related

Calculated Field

Oct 26, 2006

i have opened a field (which was created in a table) in a form as a calculated fieled
the calculations are ok but only in the for
the same field in the table remainded empty

how can it be set so the table's field will get the clcaulation ?

** here is a sample file

View 2 Replies View Related

Calculated Field

Oct 15, 2007

I have this query which selects two fields and calculates it in another field.
But the calculated field has more than 1 expression.Eg
If field A < Field B,Field B + Field C AS X,If field A < Field C,Field A + Field C AS Y.The problem is that when I show this on the Form I want only 1 of the fields to be shown ie either X or Y .if any of the expressions is satisfied then only that field should be shown as a textbox control on the form and not both.How do I do this?? Its really complicated for me..Pls help..

View 14 Replies View Related

Calculated Expression

Aug 16, 2005

I have two email addresses in a table one personal and one work and a question asking which to use, I have a query running from the table and have an IIF expression creating a field which looks to see which one to use (that all works fine !) anyway my client has now asked for an extra field in the query which combines the two email addresses, as in aaa@homedomain.com;bbb@workdomain.com so that I can run an email to both addresses at the same time. fraid I just can't figure it out ! I'm sure it's obvious, I can't find a similar question posted either, I've tried the expression builder using AND for the two email addressses but just end up getting -1 in field when I run the query, not the two email addresses separated by a ;

Anyone any ideas

many tks

:)

View 3 Replies View Related

Calculated Value In Where Clause

Sep 30, 2005

In one of my queries, I'd like to reuse a calculated field as
criteria in the Where clause. SQL seems reluctant to want to
do this. You can RESTATE the calculation in the Where clause,
but can't refer to it by Name. You can Order by it, but it
can't be in the Where clause.

If the query result set consists on a set of Named columns, why
can you refer to a calculated column by Name in the "Order By"
clause but not in the Where clause?


Why is this OK?

Select FldA As FirstField,
FldB As SecondField,
FldA - FldB As TheDifference <-- Just a calculated field
From SomeTable

And this is OK?

Select FldA As FirstField,
FldB As SecondField,
FldA - FldB As TheDifference
From SomeTable
Order By TheDifference <-- Order By is OK

And this in not OK?

Select FldA As FirstField,
FldB As SecondField,
FldA - FldB As TheDifference
From SomeTable
Where TheDifference > 100 <-- Can't use TheDifference in Where clause

Don't want to repeat the calculation.

Select FldA As FirstField,
FldB As SecondField,
FldA - FldB As TheDifference
From SomeTable
Where FldA - FldB > 100 <-- I know this works, but ...
Order By TheDifference



Wayne

View 3 Replies View Related

Calculated Date ....Help Please

Aug 13, 2006

Hi Folks

I have a simple table that has a date field.

This is called dateopened and is in the format ddmmyy hh:m

I have a query based on this table.

What I'm trying to do is workout the day that a case was opened from the date entered.

i.e I have an entry that was opened on 10/08/06

My calculated filed works out that this was raised on a Thursday ?

I can then do dcounts etc on how many cases were opened on a Thursday or a Friday etc

I cant really get my head around the syntax in the query fileld

Can anyone advise me how to do this ?

Many thanks

Jimmy

View 3 Replies View Related







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