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.
Out of this sale we have to pay a bank commision so what i required is that when commission is Rs. 30 then it must be add in sales and another coloumn is created which is Salewithcomm.
Commision Calculated on basis
When sale upto 9999 then Commision is Rs. 30, when sale is between 10000 to 14999 then commision is Rs. 50 and above 15000 we have to charge Rs. 3.50 per thousand
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!
Hi 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
I 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:
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:
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)?
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.
I 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?
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?
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
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?
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.
i 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)))
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"
HelloBascially I am trying to make a query to check if certain Parameters are met over all in the table then add up records.For example i have a record set up likeBob Brown|Access Programmers|18/3/08|1Bob Brown|Access Programmers|17/3/08|5I would want to make a query compound those records up in a report (e.g the compounded values don't need to be saved just displayed)If the records name field is the same and the dates fall in a certain range then the records will be compounded together e.g the numbers being added upSo the end result for that example would beBob Brown|Access Programmers|6Because the records name and "Access Programmers" is the same and they fall within a certain date range the records are compounded togetherMy question is, Would you make a query to do that or can it be done with a report. Bascially what would be the best way to do it.:)Thankyou In Advance!
I have a form (frmTraveler) in which a user is given a selection of levels for a type of trip from a drop down box (TripLevel). Basically, if a user picks level A, then I would like a value to be written to the corresponding record in another table (tblHotel). The PK is an autonumber (TravelID) and the tables have a one to many relationship. Hope that's enough information for some tips! :)
I am trying to create a parameter query to be able to access from 5 different tables data on a product that has been delivered to retail stores between a range of dates.
I need the query to have the ability for the user to enter the product number and the range of dates to activate the selection.
