Access :: Queries & Calculated Fields

Sum Queries And Storing Calculated Fields

Aug 26, 2005

Hello All,

Probably trying to do the impossible.

Normally I would never (that�s a long time) have the results of a calculated field in a table, but I�m stumped.

Table1
TestID: Autonumber
Quantity: Number
Selected: Yes/No

Table2
SumID: Autonumber
TestID: Number, Foreign Key
Quantity Delivered: Number

Query1-Sum Query
Table2
TestID: Group
Quantity Delivered: Sum

Query2- Select Query
Record Sets

Query1 Table1
TestID-----------------------------------TestID
Quantity Deliverd Quantity
Select

Fields in Query2
TestID from Table1
Select from Table 1

Run Query
Can�t update �Select� field

So I�m thinking that I should create a 3rd query to update the summed Quantity Delivered into a new field in table 1. Which would in effect create a stored calculated field.

View 2 Replies View Related

Creating Non-Calculated Fields In Select Queries

Nov 14, 2005

Hi All!

I am trying to create an updateable non-calculated field in a select query. Specifically, I want to create a Yes/No data type called "Fail".

This is a multiple-user application so I can't solve the problem by using append and delete queries to modify records in a table.

This has turned into a much stickier problem than I anticipated. I would appreciate any insight or advice you have.

View 2 Replies View Related

Queries :: Give All Null Values 0 In Calculated Fields?

Jun 12, 2013

I have a query that performs some calculations, these calculations feed into another query and produce a final value. If there are no null values everything works perfectly. But when there are null values, I get errors.

What I have tried:

1) I tried to implement the Nz function for each field of the formula that is not calculated but from user input.

2) I tried to implement the Nz function for just the field that calculated the last result before feeding into the next query, but it didn't show the 0 value I gave.

3) I tried to use UPDATE TABLE SET in SQL, but I don't think I was using it right. I tried to use it for all records but always got an error when it ran.

The first two above result in showing no values at all, even if there are some. The third didn't work. How to easily update null values in the query to 0? All I could think of was to somehow use Criteria or SQL.

View 14 Replies View Related

Queries :: Filter Error With Calculated Fields - Missing Operator In Expression

Jan 9, 2014

I tend to work with large amounts of data (extracts from company systems) and I create a lot of Access/VBA based tools to automate processes.

I have an annoying error which has always appeared but I don't understand the root cause of it.

When viewing a query, if I filter, I get an error message pop up (though after clicking through the error I can still use the filter function):

"syntax error missing operator in expression 'name of field'".

This seems to happen when I add several calculated fields. Here are some examples of the conditions and calculated field formulas I'm using in this current one:

Conditions:
<>"CINEMATIC" And <>"SFX"
Not Like "*_ZZ*" And Not Like "*test*" And Not Like "EP_*"
[Forms]![FRMscriptPrintReview]![selectLangCombo]

Calculated Field:
Audio Ref Guide: IIf(Left$(Right$([TBLdata]![Script Resource],2),1)<>"_","?",Right$([TBLdata]![Script Resource],2))

I get the impression that its more of a bug with Access as the formulas aren't complicated really but need confirmation on this and if there is a way I can avoid it.

View 2 Replies View Related

A Big Headache Please Help

Dec 4, 2007

Hi all,

ok i have a bit of a problem here and this is it.

I have set up a quesry based on 2 forms 1 orders and 2 order details.
i have the set a criteria on the report to print a order number

i have then set up a report based on the query.
Then i have created a command button on my form up untill here it is fine the problem is when i create the order and send it to print the criterai box pops up which is ok but because i am using a touch screen it will not be viable for the user to enter the number each time he prints i would want it to pick it up from the current order id.

Is there a way to do this as i am sure all invoice systems and everything should be working like this or is there a better way to do this or am i doing something wrong i have attached a screen shot of what si going on

Thanks

View 6 Replies View Related

UK Date Headache

Jun 1, 2005

Hi guys

I'm having issues with getting UK dates to show in access.

I created a table and then undertook an insert with the following:

docmd.RunSQL "INSERT into tbl_test([dates]) VALUES (#01/06/2005#);"

Note that my machine is in UK local and the format of the field is short date. The date in access is 06/01/2005. ie 6th Jan , not 1st June as it should be

If I set up a query via the query wizard to look at this field then I see that the date is shown in US format.

I tried to use the following in the field section:

result: format(dates, "dd/mm/yyyy")

and the date still showed up at US ie 06/01/2005. argghhhh. (assume due to the fact that format returns a string and I'm looking at a date field)

Any ideas how to overcome?

View 3 Replies View Related

Causing A Headache

Sep 28, 2006

I have a main form [mainform] with several subforms on it, two of which are on a tab.

On the second tab [page79] there is a subform [subformlist] which has like 25 checkboxes on it, any combonation of which can be picked by the user.

I need there to be at least one pick required of this subform, else the record won't show in a general query I have, since the table the subform is based on is linked to the record.

Is there anyway to make making a choice of one of at least one of these checkboxes checked a manditory thing before the user can close or move on to the next record?

I've tried making an "Other" box that on the Table has a default value of "True" which I thought would cause it to show in the query record...to no avail.

The only true way to make this work is to have it mandatory that the user pick from AT LEAST one, usually more before the record is complete.

Any help? Other than writing a MASSIVE If...Then statement...?

Or is there a way to make it so the choice isn't required for the record to show up in the query?

I'd post...but my db is too massive, even zipped.
:(

View 2 Replies View Related

2 Tables + 1 Query = 1 Headache

Jul 28, 2006

I am trying build what I thought was a super simple data base, but this is driving me crazy.

I have a group of building maintenance manuals (12) each of which is subdivided into sections. I have created a list of all the section headings and respective page numbers and have imported them into a table (tblMASTER ) with these fields:

MasterIndexpkf
SectionNumber (unique sequential number 1-311)
SectionName
PageNumber

I have a second table (tblCROSSREF) with these fields:

CrossRefIndexpkf
MasterIndexfkf
SectionNumber
SectionName
PageNumber

The two tables are linked in a one-to-many relationship on the respective MasterIndex fields.

I also have a query (qryMaster) which includes ALL the fields in tblMaster which serves as a lookup to the SectionName field in tblCROSSREF.

What I want to do is to select a section number in tblMaster and enter all the possible cross references in tblCROSSREF by selecting the available section names from qryMaster.

This seems to me to be sooo simple, but I am having a real headache!

My primary problem is that when I use the lookup function in the SectionName field (in tblCROSSREF) it returns not the Section Name, but the Primary Key Field number.

This thing is making me feel like the AFLAC duck. Can anyone give me a clue about where I have gone wrong?

Thanks,

Rick

View 4 Replies View Related

Linear Interpolation Headache

Feb 7, 2006

I've been searching in vain for a solution to a relatively simple bit of mathematics that I�d like to Access to undertake.

It involves interpolating between data, as illustrated in the following example. Basically have several items, and let's say different "prices" dependent on the number of units purchased. The relationship between the �prices� is not a straightforward formula, therefore I need a query to return an interpolated price for a given number of "units".

DATA
ItemID Price10Price20Price35Price50Price100Price 1000
1�70�135�200�315�800�6500
2�23�31�61�89�210�1500

OUTPUT
ItemIDUnitsPrice(int)
117?
231?

So, for example
if Units <10, Then Price(int) = Price10
if Units = 17, Price(int) = Price10 + (17-10)*(Price20-Price10)/(20/10)
if Units = 32, Price(int) = Price20 + (32-20)*(Price35-Price20)/(35/20)
if Units >1000, Then Price(int) = Price1000

I'm aware that IIF statements might do the job, but I have 15 different 'Price' points, and don't particularly want to nest them all.

This problem was solved previously in Excel using a clumsy set of VLOOKUP/HLOOKUP functions in conjunction with If statements.

View 2 Replies View Related

Headache With Changing The Filename... ...

Oct 19, 2006

Hi all,

I got a headache to changing my form's filename... i am totally a newbie in Access while in the few months ago... so i just create all those form or table name's for the way i like. But now i had already know how important of management of the filename because when u r using the module to write the code u will be headache to look for the filename that u want in the code. So that is important to add 'tbl', 'frm', 'Qry' infront of the filename.

So do anyone have a good way to edit all the file name with all match to the other in the module with a quick way rather then one by one editing (really tired for one by one) Thanks!!!

View 4 Replies View Related

ComboBox/Forms Synching Headache

Jul 26, 2005

Ok, here's something I think I've done dozens of times, successfully. Now I can't get it to work.

I have a Parent Form with three Subforms (based on 3 separate, linked, tables).

I have a combo box on the parent form which looks up a piece of EQUIPMENT by its unique ID and the other two forms display equipment DETAIL and SERVICE HISTORY related to that equipment. It works just great until I use the ParentForm's navigation buttons. The two Subforms change to display the information relevant to other pieces of equipment, but the combo box continues to show the original look up.

Whaaaat have I done or not done here?

Thanks,

Rick

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

Calculated Fields

Mar 26, 2008

I am trying to create a calculated field in a query, that shows money owed from customers. I have 4 different job codes that have their own prices. I also have customers with the amount of hours they owe for, for each job. There are two customers that owe for two different jobs. I know how to write in : Money Owed: [price per hour]*[number of hours]. but i want to have only one line for each customer when i run the query. How do i combine??

View 1 Replies View Related

Sum Calculated Fields

Aug 8, 2006

I have a detail form, which has controls populated from a query.

For example,
Name Number
xxxx xxxx

This can be any length depending upon the amount of database records, so there might be 5 names, or 4 names, etc.

I then need to sum these in the footer. Is the possible?
I just seem to get #Error displayed when doing SUM([ControlBoxName])

View 9 Replies View Related

Calculated Fields

Sep 30, 2004

I have a calculated text box on a form:-

=[AnnualSavings]/[SKUAnnualSpendInPounds]*100

But, if both the Annual Savings and SKUAnnualSpendInPounds text boxes have a value of 0 the result of the calulation comes out as #Num!. I assume this is the error because I am doing 0 divided by 0.

Does anyone know if this is the reason for my error and if so is there anyway of changing the #Num! back into a 0 so that I can submit it to my tables.

Any help would be greatly appreciated

View 1 Replies View Related

Calculated Fields

Apr 27, 2005

Here is my dilemma

I have a table called inventory that stores my different products and the quanitities i have in stock. I also have an orders table that handles a customer palcing an order for a certain product and the amount they want. What i want to happen is this: If a customer orders for example 5 widgets, I want my inventory table to automatically reflect the 5 widget deduction from the quantity in stock. Anyone know how to do this?

View 1 Replies View Related

Calculated Fields

Mar 13, 2008

Im wondering how to make calculated feilds in a query?
Do i have to make the feild in a table the go to query?

View 1 Replies View Related

Manipulating Calculated Fields

May 18, 2005

I developed a query that calulates when a contract is reviewed i.e 5 weeks and 9 weeks after the commencement date. These are called CDE1 and CDE2. This works well and the CDE1 and CDE2 dates are displayed ok.

The query above is:
SELECT Contract.ContractNo, Contract.APSNo, Contract.Firstname, Contract.Surname, Contract.AgencyDesc, Contract.FacilityName, Contract.Designation, Contract.WeeklyHrs, Contract.PositionNo, Contract.ComDate, Contract.EndDate, IIf(DateAdd("ww",5,[comdate])<[enddate],DateAdd("ww",5,[comdate])) AS 1stCDE, IIf(DateAdd("ww",9,[comdate])<[enddate],DateAdd("ww",9,[comdate])) AS 2ndCDE
FROM Contract;

The problem is that I now want to access contracts under these CDE dates between two dates that is user defined (startdate) and (enddate).

As the above are calculated fields, how can I develop a new query based on the above as CDE1 and CDE2 are already calculated fields?

View 2 Replies View Related

Storing Calculated Fields

Aug 9, 2005

New to Access and learning the hard way. Im sure this is an easy one, but sure would appreciate the help. I am trying to sum a group of cells and display the total, which i have done. But I also want to store this total in my database. So my question is, how do i move my calculated total to my field that has the "record source" bound back to my field in the database? Hope that makes sense. Thanks in advance.

View 3 Replies View Related

Refreshing Calculated Fields

Sep 10, 2004

I have an Access 97 database with a form and a subform for invoice and invoice details. When I change the qty or price in the subform I have a calculated field on the main form that updates. I also have a button that when clicked brings up the payment screen with the totals from the previous screen. The problem lies when I change the qty or price and click the button right away, the next form pops up but does not take the new totals, it grabs the old ones. The calculated fields do not refresh fast enough. I have tried requery, refresh, and even a loop to make it wait before opening the payment form, but nothing seems to work. I'm at a loss. Any ideas would be appreciated

Thanks
Ken Reid

View 1 Replies View Related

Calculated Fields In Forms?

Sep 9, 2006

Now I can create a Query that adds, subtracts a specific column, but im having a hard time trying to get the information into a form since it don't seem to allow me to pick the query field which is calculated as a datasource. Can someone tell me how to make a calculated query column appear on a form as a field? Such if I type in monday in the regular table, the form will add a few days to the next available game? I have the information/code already in a query that displays the query right in a new table using the Expr method but I can't get the form to just simply display the field. Someone please help!

I see a few people asked close to the same question but nobody never answered so I am thinking it is impossible

View 3 Replies View Related

Saving Calculated Fields ( I KNOW BAD)

Nov 30, 2006

I keep getting caught up in the same point over and over again. I need to save a calculated field's calculation to a table field. The problem is how do I do this? I already know about the rules but I need to do this and store that specific information. Can someone post a sample code? The problem is the code and codes I been using dont' seem to work any more. I was putting

fieldname = textbox2.txt
in the form before update box. The problem with that is it would give me an error if the data source the calculation pulls from is not in the table/form.

View 1 Replies View Related

Storing Calculated Fields In A Table

Apr 20, 2007

I know that it is bad form to store a calculated field in a table - normalization and all that - but I can see no other way around a need that I have.

I'm trying to make a database to store injury time for employees. If an employee is injured, he can do light work for a certain amount of time, but this light work need not be contiguous. But, the total time on light work must be known so as to see when it is used up.

For example:

Bob is injured on 4/1/06 and goes on light work on 4/2/06 until 4/5/06 (4 days). Bob is then taken off light work until 4/10/06 when he reinjures the same injury. This does not count as a new injury, because it is an aggravation of the old one. So, Bob goes back on light work on 4/10/06 until 4/15/06 (6 days).

So, I need to store Bob's total time on light work for this injury (10 days) so that I can keep a running total to check against the maximum for a single injury. All I can think of is to store the sum of days on light work as a field in Bob's injury record, but that means storing a calculated field in a table.

Am I missing an easy way to do this, or is there a method to do what I want that I am just not aware of?

Thanx for any help!

View 2 Replies View Related

Can't Edit Query Which Has Calculated Fields

Jan 7, 2008

Hi!

I have done a query which has calculated fields, but I can't edit the stored fields directly from the query, instead I have to go to the table so as to edit it. Is this the way it works or is something wrong?

SELECT [INSTRUCCIONES DE EMBARQUE].N_OP, [INSTRUCCIONES DE EMBARQUE].[N� FAC EXP], [INSTRUCCIONES DE EMBARQUE].[FECHA CUMP], [INSTRUCCIONES DE EMBARQUE].[DERECHO PGC], [INSTRUCCIONES DE EMBARQUE].[VTO DER EXP], [INSTRUCCIONES DE EMBARQUE].[IMPORTE REINTEGRO], [DETALLE CONTENEDOR].CANTIDAD, [DETALLE CONTENEDOR].[VALOR FOB], [INSTRUCCIONES DE EMBARQUE].[TIPO DE CAMBIO], [TIPO DE CAMBIO]*[VALOR FOB] AS [PRECIO VTA (ARS)], [PRECIO VTA (ARS)]*[CANTIDAD] AS [TOTAL(ARS)], [TOTAL(ARS)]/[TIPO DE CAMBIO] AS [TOTAL(USD)]
FROM [INSTRUCCIONES DE EMBARQUE], [DETALLE CONTENEDOR];

View 2 Replies View Related

Calculated Fields On Load Of Form

May 4, 2005

Could someone tell me what Event I need to put this code in to calculate my fields when the form first opens?

I have this code on both the After Update and On Change events which is working fine, but, when I first open the form, those calculated fields are blank and I have to perform a change or update before the calculations will appear each time.

If Not IsNull(Me.fcstloc) Then
Me.prodvolume = DSum("[volume]", "qryMonthlyEngineVolumes", "[product] = '" & Forms!Projects!product & "'" & " And [MonthID] Between " & Forms!Projects.MonthID.Column(0) & " And 12")
ElseIf Not IsNull(Me.prodvolume) Then
Me.prodvolume = DLookup("[2004volume]", "tblProjectDetails", "[projectid] = " & Forms!Projects!projectid & "")
End If

Thanks!
Toni

View 1 Replies View Related

Queries & Calculated Fields - Headache! Help!