(1)Let's say I have numeric fields A and B.I want, whether with a query or with an Acess functionality if such exists,to show the user a C field which shows the sum: "A+B" of each registry. (Not the totals, I mean a new field).
(2)This is something i need to know. There is something else. Let's say i want, from the past example, the same table A,B and C. Let's say theres another field called "FORMULA". I want now Access to use that formula, user inputed, apply it to A and B and show the result in C. But the user must define what calculations must be done.
ok guys i need help figuring out something here.. i am trying to incorporate this into a formula in a form
in the retail industry we all determine profitability based on gross margin % and $..
example
if an item cost $250 and you sell it for $599
you can take 250/599 = .417
which tells you that the reverse of that is the actual percentage margin you made.
now look at the logic behind it.
(((250/599)*100)*-1)+100 =58.26% ideally thats a formula that we dont think of but in our heads thats how we calculate that.
now this formula that we all use (without realizing it) only works up to certain #s. If you start selling items extremely under cost your Gross Margin goes nuts.
Here is an example..
cost = 250 selling price=1
(((250/1)*100)*-1)+100=-24900%
now i am trying to analyze this and make more sense of it. a -24900% margin does not make much sense and this is why.
item A cost 250, sold for 599 item B cost 250, sold for 1
your profit amount for item A =$349 your profit amount for item B= -$249
therefore your Gross Margin $ profit is $100
now when you figure out you Gross Margin % it wont work using the formula since you are averaging (-24900 and 58.26).. it will show a negative margin when i fact you made money on this so its not negative..
I have solved the problems getting values on the subform. I have not in getting values on the subform.For instance, on the one titled phone use the formula in the tutorial is:
=[sbfCustomerRoomUse].[Form]![txtTotalPhoneUse]
#Error results when the doc is put into a form mode.
Now when I input each value in the equation above separately.I still get no entry.
For " = sbfCustomerRoomUse" , I get #Error; and for "= txtTotalPhoneUse", I get #Name.
CustomerRoomUse and txtTotalPhoneuse are from the subform that was dropped into the customer form in a previous step. It shows that explicitly when designed sbfCustomerRoomUse on the main that CustomerRoomUse come from a subform. This does not seem to need to be done with txtTotalPhoneuse, and I am not sure why. Neither one gives me a desired calculation result.
I have an odd problem that has stumped me for several days. I'm working on a form that contains a chart. The chart is based on a query, which is based on another query, which is based on a table. In the top query, I need to put some calculated fields that operate on other fields. But when I try to multiply two fields together, I get Null. I'm pretty confident that the fields I'm operating on are numerical.
I'm hoping this is a no brainer...I am a complete newbie and self taught so apologies for not knowing the correct techie terms!!
I have a db with products...
Each product has a price (to customer) and cost (to me the seller)
What I want to work out is the margin i.e. price minus cost divided by proce multiplied by 100 to give a margin in percentage terms.
The data is held in a table with a record per product with the price and cost..
I want to build in another fieled per record that calculates the margin...
Thanks very much
Jessica
(I would also like to be able to work out decreasing margins if I was to extend say a 10% or 20% discount to the customer but I guess I shoudl walk before I run!!)
Hi, I have a database and on one table I have dates stored for certain jobs. I was wondering if anyone knew how to automatically calculate an end date if the start date and duration is entered!
Ok I'm really new at using access so go easy on me. I am trying to write a database to keep 30 measurements of a part grouped by lot number and then calculate the standard deviation, mean and a few other calculations, however I am unsure about how to do the calculations in access.
I would like the database operator to be able to enter the data then print out a report per lot, with the entries and the calculated answers.
I'm just unsure where and how to do the calculations... any help or suggestions?
I'm trying to a create a report to list results for a running club based on series-to-date. To do this, I allocate points to the first 100 runners to cross the line in each race. The winner gets 100, second place 99, and so on with the 100th person getting 1.
In the database i have 1 table holding member details, then one table holding races. I also have a table linking the 2 called race_entry so i know what members have entered for which races. I have one other table called race_result that links the runner to a race and holds the position they finished in the race.
How could i use the data in the race results table to add up all the points they earned by looking at their finishing position in the races they run and then listing the points in order in a report.
I assumed it wouldnt be necessary to have a point field in the table as its deriveable.
This is more than likely simple for a lot of people out there but it has me stumped. I have a form that has a two combo boxes with dates in both ( so the user to pick from and to dates) now I am always going to be adding more dates as time progresses. I need the next form to add the info with the dates no mater what the user picks. For example I am going to (once a week) be adding new info to the table.The end user is going to pick 06/24 (to indicate the end of the week) in both forms or 06/24 in the from box and 07/01 in the to box. In all situations I need the next form to add the data together. Sorry for the long post but didn't know how to state it any simpler.
I am a beginner with Access and was wondering if someone would be so kind to give me some pointers
I have created a form where the user can select which dates they require a hotel for the evening. I was looking for some advice on how I can go about putting an area on the same form which calculates a sub price for the days they have selected. If I am not being clear then ask for more details.
I need to be able to use some functions available in excel in an access 2000 database. These include SUM and IF functions. If any one can help me figure out how to do this any help help wouold be greatly appreciated
Hi I would like to manipulate some date information. I have a start date field and an end date field and I would like to draw up a schedule for payment where the Acess takes the finished date and the start date to get total days appart then devides this by a value set by the operator. say If the operator wanted 6 paymes then my total days would be devided by 6 giving me the number of days to add on to the start date. Then with these dates a schedule is made up and the code I have tried looks something like this.
Dim valuation1 As Date Dim valuation2 As Date Dim valuation3 As Date Dim valuation4 As Date Dim valuation5 As Date Dim valuation6 As Date Dim valuation7 As Date Dim valuation8 As Date Dim valuation9 As Date Dim valuation10 As Date Dim valuation11 As Date Dim valuation12 As Date Dim startdate As Double Dim findate As Double
Dim valcounter As Double
'retrieve start date and end date startdate = CDbl(Me.Start_Date) findate = CDbl(Me.Completion_Date)
'calculate evaluation periods Dim valCount As Double Dim valDays As Double
Select Case valcounter Case "1" Me.valuation1 = startdate Case "2" Me.valuation2 = startdate Case "3" Me.valuation3 = startdate Case "4" Me.valuation4 = startdate Case "5" Me.valuation5 = startdate Case "6" Me.valuation6 = startdate Case "7" Me.valuation7 = startdate Case "8" Me.valuation8 = startdate Case "9" Me.valuation9 = startdate Case "10" Me.valuation10 = startdate Case "11" Me.valuation11 = startdate Case "12" Me.valuation12 = startdate
I have a question that I can't seem to figure out. I am trying to create a form in which there are three major fields. 1) Value 1 2) Value 3) Sum of Value 1 & 2. I want it so that two fields are added up and then the sum, which will populate in the third field, will be stored in a table.
Does anyone have any suggestions on what I can do? I am fairly new to access and haven't had much luck finding anything on the net.
The fields include both date and time (date and time are in the same field) i.e. “6/26/2006 4:23pm”. The next field I need to calculate the duration, basically one field minus another…anything I’ve tried will only calculate the time difference will not factor in the days. For example, if it has been 52 hours (2 days, 4 hours) it will only say 4 hours. A few of what I’ve already tried are below. And I’ve also tried an elapsed time expression.
I am currently setting up a data base for my work and was wondering if there is any way to do the following calculation:
I have a database of automatic doors that are contantly being repaired and i want to keep a record of the accumulated costs involved with this.
I have set up a form to update each door and its accociated repair cost, the problem is when i enter the new repair cost access replaces the current cost with the new and i want it to be added to the current cost so the accumulated cost can be bulit up over time.
I've read the posts regarding not store calculations. However, I was told by IS to store the mileage IRS deduction as it changed 3 times last year.
Any idea's how to handle this? I need the table to remember how much $$ was paid out in mileage expense. I had set up a field called mexpense and mileage, where =[Mileage]*0.375.
Just like to thank you all for all the previous help I have recieved from this forum.
I have reached another snag :eek:
I am trying to show a field in a form which will be the sum of some data in a sub form ( Which hols data in a seperate table )
Anyway,
The Relationshiop to the subform table is a 1-many from my main table. This has a ref number which is what makes the 2 link together.
Im trying to find the money value of feild X all of the records related to the reference number, then this needs to be displayed in a query so I can show this value in a form.
This is what I`ve tried and it just displayer #error =Sum([Cost £]) I would have though this would work, as i have used this before to calculate the running total of another field. Does this not work because its a subform im trying to get the data from ?
I`ve attached a copy of the database with some sample data in it just incase I have not made myself clear. (A2003)
Thanks in advance for any help/advice.
Max
[update]
Quote: In the report, do the SUM in the subreport, set it to 'Invisible' (in the properties box), and reference it in the the main report by adding an unbound text field and setting its Control Source to: =Name_Of_Your_Sub_Report.Report!Name_of_the_Invisi ble_Totals_Textbox
I have done the above, but made changes like below: =[Materials & Labour subform]!Total
Hi, firstly I just want to put I'm a beginner in SQL so don't laugh too loud when you read this.
I have a query that takes two field sfrom seperate tables. Lets call the fields X and Y.
The X field contains an ID number (there are 6 possible instances) which can be duplicated many times. The Y field holds individual pieces of data(sample data below)
I was wondering if somebody could help with a problem I have come across. I am currently developing a database for a small charity that I work for. The aim of the database is to record when staff undertake counselling.
I have created a query that calculates the current age of a client in the database uing the following expresion:
Age: Date()-[Date of Birth]
The Date of Birth is formated as a Short Date
In the query I have set the format of the column in the query to 'yy'. This then just displays the age of a client. This work fine apart from ages which are less than 30. If the age is less than 30 e.g. 29 it displays the date as 1929.
Does anybody know how I might be able to solve this or an alternative way in which to calculate dates and display it as 'yy'. (Its been a few years since I last work with access so skills a little rusty)
The reason behind the yy is that the monthly & yearly stats have to group by age ranges.
I have a database that records all procurements and requisitions of our medical supplies.
I have set up queries that sum all requisitions in a set time frame for each item that should be in the store (i.e. this month, this year etc) and similar query for the procurements.
A further query then subtracts the requisitions from the procurements and (should) give me an inventory of all medical supplies.
BUT.....
If no items have been procured or requisitioned, they are excluded from the sums. We have a standard list of items (which is a table in the DBase) and I need to know if we have no stock (there are certain things we can't get all the time in Tanzania, so procurements and requisitions are zero/nul for a while and I need the Dbase to highlight the lack of items).
Please help!
Note, if there is away of doing this with out VB I would be very happy as I have never used VB and it terrifies me.
