Divide And Store
Mar 17, 2005
Hi hope someone can help,
What I’m trying to do is work out price per quantity, at the mo I have a database that has products and price so lets take paint for an example.
If I have a 5 litre pot of paint and I’m selling it for £10 I would like a field that tells me the price per litre, so what I’m think I need to do is have a field called ”price” and divide it by a second field called “quantity” and output the sum to a third field called “price per litre” which populates the database.
Sadly I haven’t got a clue how to do this with access and if anyone can help it would be much appreciated.
Thanks
View Replies
ADVERTISEMENT
Mar 13, 2006
Just wondering, if this is possible and if so how?
I got query results and would like to divide a figure from the number of results shown..
so if 4 results are shown from the query then i would like to divide 100 by 4, and if 5 results showed then divide it by 5?
View 5 Replies
View Related
Sep 12, 2006
How can I avoid a divide by zero error in an MS Access Query?
View 2 Replies
View Related
Oct 24, 2006
HiIn Excel, I believe you can format a number and automatically divide it by 1,000 without messing up rounding.For example, 10,000.06 after you apply the formatting becomes 10 as in 10k (for some reason accounting people find this easier to look at/work with). However, if you try to sum the value Excel will actually use the decimal figure rather than the number 10.Is there a similar way in Access?
View 2 Replies
View Related
Dec 8, 2006
Hi,
I have a text box which contains a formula. When the text box is divided by zero, it appears #ERROR in the text box. I would like it to be invisible when it divided by zero and to be visible when it has a number.
Any ideas?
Thanks,
Le
View 3 Replies
View Related
Aug 25, 2005
Hi all.
I have three subforms on my main form. They all display salary details from different sources, and each subform has 2 calculated fields in the form footer to show the average salary and the number of records on which the average has been calculated.
I then have a field on the main form to calculate the average of the averages which works OK unless one of the subforms has null or zero in it, in which case it returns no information.
Can anyone suggest a way of bypassing this divide by zero error? The code I'm using to calculate the average of averages is as follows:
Me!Text44 = ((Me![Frm1 subform].Form!text4 * Me![Frm1subform].Form!Text6) + (Me![Frm2 subform].Form!text4 * Me![Frm2 subform].Form!Text6) + (Me![Frm3 subform].Form!Text6 * Me![Frm3 subform].Form!Text8)) / (Me![Frm1 subform].Form!Text6 + Me![Frm2 subform].Form!Text6 + Me![Frm3 subform].Form!Text8)
Thanks for any help you can provide...
View 3 Replies
View Related
Aug 9, 2015
I am trying to divide each unique value in a column by the total column; so sales by total sales.
View 4 Replies
View Related
May 26, 2015
I have an alias named [Net Income]
[URL]...
I want to divide it's value by the sum of its values through another alias [%]. How to do it?
%:=[Net Income]/Sum([Net Income]) Not working
getting an error saying:
"You tried to execute a query that does not include the specified expression 'SR_NO' as part of an aggregate function."
View 4 Replies
View Related
Mar 22, 2014
I have a database which contains a table called "orders" that has a field called "quantity" in it. I also have a query called "defective parts supplied" which has a field called "defective parts".How would I use a new query called "% failure/defect rate per supplier" to calculate the following:100 * [defective parts]/[quantity].
View 14 Replies
View Related
Nov 12, 2013
I have an order table
order nr quantity.
100 110
and a pack table
pack quantity
1 1
1 2
...
1 25
2 1...
I want to make a query that returns this :
order nr pack quantity
100 1 25
100 2 25
100 3 25
100 4 25
100 5 10
View 8 Replies
View Related
Apr 18, 2015
I have a list that shows how many books we have for each PO No. (Sorted by PO No.) Like below:
LIST 1:
Code:
PO NoTOTAL BOOKS
PO-0001 12
PO-0002 15
PO-0003 26
Now I would like to distribute/divide these different books to boxes and each box gets maximum 10 books. Like below list:
LIST 2:
Code:
BOX NOBOOKSPO NO
0001 BOOK 1~10 PO-0001
0002 BOOK 11~12 PO-0001
0002 BOOK 1~8 PO-0002
0003 BOOK 9~15 PO-0002
0003 BOOK 1~3 PO-0003
0004 BOOK 4~13 PO-0003
0005 BOOK 14~23 PO-0003
Format is not important in the list2, for example in Box 0002 we have book 11~12 means (book 11 of 12 +book 12 of 12) in po-0001
+ book 1 of 15 until book 8 of 15) in po-0002
books have bookno, for example for po-0001 we have 12 different books it means we have bookno (1 of 12) until (12 of 12) for this po no.
How I can produce automatically list 2 from list 1 by query or coding.
View 2 Replies
View Related
Jul 31, 2014
Simple division, I thought! I'm dividing a Sum of hours by Pay Periods to determine the average of missing hours for year-to-date. Most of the division works perfectly, and some doesn't and appears to be related to values of 1 or less than one.The formula: [Sum]/[Pay Periods].Sum relates to total missing hours and pay periods are total pay periods for the year so far.What comes out wrong:
1 (hr) / 12 (pay periods) = 8.33, when it should be .0833
0.5 / 12 = 4.166 when it should be .0416
What is right is everything else, starting with 1.5 hrs
1.5 / 12 = 0.125
What do I need to do to make all of the values divide correctly?
View 3 Replies
View Related
Mar 17, 2013
I'm struggling with the correct syntax for this, but ideally what I want to achieve is:
SELECT (SELECT Count(Status) FROM P_T WHERE 'criteria are met') / (SELECT Count(Status) FROM P_T WHERE 'different criteria are met')
They are both fairly simplistic select statements, so I imagine this is not that difficult to achieve, but I'm not sure how to structure the query.
View 6 Replies
View Related
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
Aug 9, 2013
I am with an educational organisation. Now we need to allocate various fields to students depending upon their merit, where the difference between the average percentages between any two groups cannot be more than 2% and that also implies equal distribution of talent. However, First few have to be given choice of field and also students recommended have to be given choice. The number of fields varies from 2 to 8. . Further the vacancies are also not equal in all fields. E.g. for 100 students to be divided in four fields the vacancies can be 35 for ZZSS, 24 for UYTE, 29 for OIUI and 12 for HGFT.
I need a solution preferably in MS Excel or MS access for the same with nil/ least usage of VBA.
Table examples
Name REg. No. Percentage
Aaa 986544a 89.3%
Bbb 547521j 87.2%
Ddae v44887y 95%
View 8 Replies
View Related
Apr 10, 2013
I am running a very simple query that divides profit over turnover to show the margin. I have many queries throughout my database that do this, here is my little formula;
Margin: Round(([profit]/[Turnover])*100,2)
Now, this runs just fine but if I put in a criteria of <10 I get the division by zero error. The reason I am confused is that there are no zeros or error values or even negative values in either of the profit or turnover columns?
Also I have an almost identical query in another database that has a <5 criteria in it and it works a peach.
I have searched but all I am getting is the usual definition of the division by zero error.
View 5 Replies
View Related
Jan 9, 2006
if you are asked to store only the month, or only the month and year, how do you do it? tnx.
View 3 Replies
View Related
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
Jan 12, 2005
In everyones opinion what is the best way to handle picture uploads in an Access database?
One picture for one employee, where should I store the pictures. I know that keeping pictures in the database eats up space and really can slow ya down. Make a new folder just for pictures??
Thanks fellas and ladies.
View 2 Replies
View Related
Jan 15, 2005
Ok I know everyone is going to say you should not store calculations, but I have no choice!!!!!!!!!! I have the form ORDERFORM (recordsource ORDERS table) with a subform ORDERFORMsubPROD (recordsource ORDERSPROD table). Essently theses 2 forms togather allow me to place an order for multiple products and store them in the ORDERS (key: ORDERID) and ORDERSPROD (foregin key: ORDERID) tables. The ORDERFORM includes information such as an ORDERDT and the ORDERFORMsubPROD includes a sum of cost the products ordered which is shown in ORDERFORM with a boundcontrol = to the ORDERFORMsubPROD control that does the actual calculations.
Next I need to be able to pull specific informtaion from the ORDERS table and group it togather by the SALESMANID. To this end I have a form SALESMAN with a subform SALESMANsubORDERS that displayes the ORDERID, ORDERDT, and the calculated sum of the products ordered on that ORDERID based on the SALESMANID of the order.
So far everything stated above is correct and should not require that I store calculations. My problem comes in when I am trying to make a form that will filter this information based on user supplied criteria (my user does not want to have to use the filer buttions supplied by Access). I'm using DoCmd.OpenForm and setting my WHERE criteria based on a bunch of If .. Then statements that check the various filter text/combo boxes to determine if they are populated and then admend the WHERE statement accordingly. At least that is how it works on a very similar form that filters ORDERFORM. I have discovered that because the subform, SALESMANsubORDERS, is based on a query that includes calculations the WHERE statement needs to be phrased as HAVING. I can not figure out how to get Access to comply with this so all I can think of is to store the calculated values (and be able to allow them to be updated later) then pull them directly from the ORDERS table.
Anyone who can write precise/generic code and most importantly tell me where (what event) that code should be placed on please let me know. This is the first time I have run into this problem and it is really my last problem before completing a DB application that needs to be completed by Wed 1/19/2005. Thanks in advance for any advise/help I receive.
View 1 Replies
View Related
Oct 7, 2007
NE1 help me with this?
I want to store some pdf drawings in an mdb.
I want to be able to search just by the file name of these drawings.
I want to be able to click on any one of the file names I find and have adobe display the file.
I want to compile this into an mde to make it a stand alone app.
I can do this using asp on a web server. But I can't seem to figure out how to do it just using access without asp.
View 2 Replies
View Related
Apr 12, 2006
Hi
I am creating a contact management database.
I have set it up and now need to create an invoice section. An invoice needs to be issued every month and is worked out on a percentage of works complete. For example in one job there may be 10 items of work and for 8 of these iems 25% of the work is complete and the other 2 no work has started.
Therefore my invoice needs to be able to calculate 25% of the value of the 8 items and work out a total price to be invoiced. Each time an invoice is created I need to be able to store the invoice value and the percent value of each item complete.
Does any one have any ideas as to a good way of doing this. That is if you can undersatnd what I am asking for. Thanks for any help.
View 2 Replies
View Related
Jun 30, 2005
I have a handful of misc. data that is unique an really doesn't fit anywhere... I need it to be easily updateable, as it will and needs to change from time to time, What should I do with it?
Some of the data is a "Common Footer" that prints on ALL reports and DOES change periodically. I decided to create a common footer table with a single value and can access it from all of my reports... it works great!
However, I now have a bunch of other misc. data that I need to store, like the "Working Year" (I am going to use the field to dictate which year of info to pull the data from) "Common Header", etc. Additionally, I am considering storing formatting data such as font size (still considering this).
So with all this explained, to sum it all up, what is the best method for handling all of this misc. data? I didn't think creating a separate table for each piece was the best idea.
Thanks for your suggestions!
View 1 Replies
View Related
Dec 17, 2005
Lets say an employee form withs the name, the position, the birthdate, ... and a picture of the person.
There are several ways to store pictures in a database, each having pros and cons.
Experienced developpers in dealing with pictures would be kind to tell me the way they recommend to store the picture, and the best way to simply display it on the form.
Thanks,
View 7 Replies
View Related
Feb 18, 2006
Hi all i have a db for my music collection. It has one table with the following feilds on it:
ID|filename|artist|album|title|track|comments|genr e|seconds|filesize|bitrate
I already created pages that with checkboxes that user select indvidual songs and by click of play button it goes and plays those songs for me.
Now i created another button next to play that it supposed to write the selected songs to db and save the ids of slected songs.Note: i use song ids to pull indvidual songs from db to be played by my song player!
My problem is i do not how to design a few tables that keep track of selected songs for each user along with the name of playlist for each user!! In processs i do not want to modify my current table i just want to add more tables to help me achive what i want. I be happy if an expert tell me how i can create tables that store play list(selected songs)info for each user and later i be able to query them easily in order to pull out playlist of each user.Thanks
View 3 Replies
View Related
Jan 22, 2008
Hi,
I know this goes against typicall database rules however I really need to do this.
I have a table in it are three fields
Rate, Amount, and Pay
The Rate is fixed, the amount is fixed per record however the pay varies for each record.
I am calling this table for a word merge and I cannot get the calculation to run on the word document.
So, how can I populate the table field Pay with the calculation? Is there some kind of query or something?
Thanks.
Fen How
View 2 Replies
View Related
