Hello,
I'm quite new at this and would appreciate some advice. I have a table of football stats I am keeping. Here's what I am trying to accomplish with three columns (points_for, points_against and wins):
if points_for > points_against then 1 otherwise 0. I can do this in Excel but I'm not familiar with Access.
Thanks!
I have a query that calculates input information into a value that then needs to be compared to another query values and will be used to output a % change in a third query. Is there any way to make this happen? Thank you in advance!
View 2 Replies View RelatedHi All:running Access 2000.I have a qry with a formula field that performs some calculation (Extension: CCur(([OnHand]*[Price])/[UnitsPer]))and it works fine. How can I update the actual table with the result of the calculation for every record? The problem I have is that there's some null values in the results therefore when I try to get totals on that field, it errors out saying "invalid null use"thanks
View 7 Replies View RelatedI am new to the access database world- I have a database in which I have projects that each have a priority- to get the priority # there are about 10 categories that need to total up to give me that priority #
Every formula I have tried, has given me an error message of some sort! PLEASE HELP, and please let me know if I am not explaining this enough!:confused:
Hello,
Im hoping someone can help me. Im not sure if its possible to do what I want...
I have a query with a column Ive created called Indicator. This field looks up the values of 2 other columns and returns a result based on whether they match or not. The formula is:
Indicator: +IIf(Table!EST=Table!ACT,"Match","No Match")
I need to tweek this formula so that it takes into account a variable. For example, if Est is 5 and ACT is 5.001, I want it to be a match.
Any help is much appreciated.
Thanks
First off can someone link me to a helpful page if there is one so that i can learn all these formulas, i know excel v.well but not access..
My query is:
i have the fields:
Product Quantity Unit Price Discount Discount Total Price
prod1 2 222.50 10.0% 44.50 400.50
now i have a whole number of products being listed, and 1 price for each row.. then below at the bottom of the page i have a Sub-Total / Shipping Charge / Total + VAT.
What is the formula i must use to calculate all the cells listed in the Price column (so i can work out the sub-total automatically)?
See Attached image.
i am using a query view in an access project, using the following sql
SELECT comturbineLifeID, cycles_used, cycle_limit, cycles_used / cycle_limit AS rata
FROM dbo.tblcomturbineRata
the fields cycles_used and cycle_limit can be a null value
where cycles_used and cycle_limit are positive values, the formula cycles_used / cycle_limit just returns 0
can anybody explain what i am doing wrong, please
how do i write if null then equal to 0, the query does not accept the nz function
Hi guys,
Please would some be able to help me?
I have created a query (its a holiday chart). The table the query is created from shows dates taken in each of the 12 months under the 'month name' column and then in the next column it has the total for each of the individual month.
For example,
Field 1 (Jan)
Field 2 (total Jan)
Field 3 (Feb)
Field 4 (Total Feb)
Field 5 (March)
Field 6 (Total March)
and so on.
Then at the end I have a field for amount left. So what I need the query to do is add all the numbers in each individuals record in all the 'total month' column and then show this in another column which is my 'amount taken' column.
Thank you in advance for your help.
Hi does any body no the access syntax to create this excel formula
=POWER(SQRT(L5/10)*47.1,2)/3769.9
Into access
Thanks
John
How to apply the formula, IF (COUNTIF (O:O;O3)> 1; K3/SUMIF (O: O;O3; K: K), 1)from an Excel spreadsheet into an Access query.
View 2 Replies View RelatedI am trying to make a query using fields from multiple tables. I used the Expression builder to create it and it is a formula field where multiple table fields are involved. But whenever i run the query Access asks for a parameter value in a modal dialogue for the value of the fields. Is it because the field names has Square brackets around it? But access puts that automatically. How to make it work properly?
View 11 Replies View RelatedHi
I would like to count number of items witin a text field,
but breaking it down identifying the different items within the
text field.
e.g.
Got a field with fruits listed. Now I am identifying the fruit but want
to know how many rotten fruit there was for each specified fruit.
At the moment my query is by date and the fruit including the column
specifying if the fruit is rotten or not. But there is 3 options in the last
column. How can I count this last column to count how many of these
3 options there are for each fruit in my report?
Please let me know if this makes any sense or not otherwise I will try to explain
it a little bit better.
Need a formula which can capture filename for me. For example, I have a db saved at "C:DlocationofficeChina.accdb". Is there a way to capture "China" in a query?
View 2 Replies View RelatedAccess 2003
Excel 2003
I have a routine that exports the results of a query to an Excel file. Is it possible to input the formula into the query so that the Excel values calculate?
This is the formula I am trying to pass to the "AZ" column of the Data tab
Code:
MyCalc::"IF(T2="","0",TODAY()-T2)"
=TEXT(TRIM(MID($A2,5,2) &" "&LEFT($A2,3)&" "&MID($A2,8,10)&" "&RIGHT($A2,2))+0,"dd/mm/yyyy hh:mm:ss ")
Is it possible to replicate the above formula from Excel into criteria in an Access Query.
Assume the cell reference $A2 is a text field which has customized date info in it but is not a recognized date format.
I got a table salaries master I want to extract some info out of and calculate some values. The formula below doesn't work, I'm pretty sure it has to do with [pay period] being a text field. Is there no way to make it work?
'Gross monthly Salary: IIf([Salaries Master]![Pay Period] = "weekly";[Salaries Master]![Daily Wage]*5*52/12,IIf([Salaries Master]![Pay Period] = "monthly";[Salaries Master]![Daily Wage]*20))'
I have a table linked to SQL Server 2014. As SQL Server 2014 does not support calculated fields I created a query to use formulas. Now I want to write formulas on the existing fields ( TotalMarks ) of table Not to create new fields.
View 1 Replies View Relatedi want to write a ms access query for date difference.i have the excel formula for that.in attachment i have shown wot i really want as output.i want to make a IIf query for these conditions.
in excel i did this like:
=IF(J13="",IF(INT(B13)=INT(AE13),C13-DAY(I13)+1,C13),IF(INT(AA13)=INT(B13),IF(MONTH(J13 )=MONTH(I13),J13-I13+1,DAY(J13)),IF(INT(AA13)=INT(D13),DAY(J13)-E13,0)))
how to do it in access query.
Below is a formula that I have in my query. The problem I now have come up with is sometimes I will not want the allocated qty to be summed back onto the qty outstanding.
I have more fields in my form ( allocation)of which one is called " consumable" what I need the formula below to do is the consumable is not empty then don't add the " qty allocated" to the "qty outstanding"
Code:
qty required: IIf(DLookUp("[material]","Allocation","material= '" & Stocklist.material & "'") Is Null,[qty outstanding],[qty outstanding ]+DSum("[qty allocated]","Allocation","material= '" & Stocklist.material & "'"))
i have a list of check boxes which state whether a ticket has been sold or not. what formula can i use to find the percentage of tickets that have been sold (by counting the number of checked boxes). also how can i implement that onto a report to display it next to a pie chart of the information. if it cannot be put on a report how can i put it onto a form?
thanks in advance
I have a nice database which is working well apart from one part
I have 2 date fields, you enter in any date in the first and i need a formaula which will generate a date 6 weeks in the future
can anybody help or suggest what to do?
Hi
I am trying to create a formula that will update a field in my table - I have created an update query and want to update the field GPA.
I have tried using the iif formula without success. Here is what I want to calculate.
I have two fields one is Score the other is Baseline. The formula needs to place the Score of 4.0, 3.5, 3.0, 2.5, 2.0, or 1.5 in the GPA field given the following.
If the difference between the field score and baseline is .00 then the score 4.0 goes in the GPA field, if the difference between the field score and baseline is .05 then the score is 3.5 in the GPA field, if the difference between the fields score and baseline is 1.0 then the GPA is 3.0....
If both fields are equal then score is 4.0
.05 score is 3.5
1.0 score is 3.0
1.5 score is 2.5
2.0 score is 2.0
2.5 score is 1.5
Can anyone help me with this formula.
I have a customers table, products table, and a transaction table that brings them together fields in tblTransaction:
Transaction ID
Order Number
Customer ID
Title ID
Date
Status
I want to create a query so that you can type a name of a customer in, and it will bring up all the items they have bought, and will add up all the prices (from tblTitle) and show it at the bottom but im not sure how to get this calculated field into the query? Whats the sum formula that you put into a query?
I have a form that people fill in where they can choose from 4 boxes (and they can tick as many as they want - it is a yes/no box)
I have tried to write a formula for a queryto show where none of the boxes have been ticked.....this doesn't work:
=(IIf(IsNull([Q3(i)]) And iif(IsNull([Q3(ii)]) And iif (IsNull([Q3(iii)]) And iif(IsNull([Q3(iv)]),"1","0"))
???? what should I write?
Because I am stupid at math, can some please help me out with the formula to get the percent of something that has changed.
Say I have October usage of 2685 and November usage of 6294. I need to know what the percentage of the change is from Oct to Nov Basically the formula to make this calculation.. The calculation will be done in Excel. I know this is an access help area, but you guys have really helped me in the past.
I need a formula that will give me the count of the number of files I scored 3.0, 2.5,2.0,1.5, and 1.0. These scores are in one field called Claim Quality Index.
What I am trying to do in a query is to count the number of files I scored a 3.0 and in the next column of the query give me the count of the number of files I scored a 2.5 - same for score 2.0, 1.5, and 1.0.
I have chosen the count in the query but I cannot get the criteria to only give me the above. I believe I need to use the expression and name the field CQI3: ????(formula that I cannot get right.
Can anyone help - I have tried various ver. of the IIf formula but to no avail.