Store Calculations

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 Replies


ADVERTISEMENT

Calculations On Main Form Do Not Reflect Calculations On Subform

Sep 28, 2015

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.

View 14 Replies View Related

Calculations On Calculations--too Much For Access?

Jun 8, 2005

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.

The fields of the query should be:

MaxOfEverInSchool
MaxOfSurvivalToGrade
MaxOfEverInSchool

With these being the calculated fields:

MaxOfSurvivalToGradeCum: MaxOfEverInSchool*MaxOfSurvivalToGrade/100
MaxOfCanReadCum: MaxOfEverInSchool*MaxOfSurvivalToGrade*MaxOfCanRea dCum/10000

Any insight would be greatly appreciated--I just can't understand why Access would suddenly refuse to do math.

View 1 Replies View Related

Store Month Only

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

Store A Calculated Value?

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

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 1 Replies View Related

What Is The Best Way To Store A Picture??

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

Store Pdf Drawings In Mdb

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

Calculations

May 3, 2005

Hi ive made a query but instead of calculating the total at the end of each line I want it to add up the total quantity at the end.
For example:

ID Description Quantity
ID Description Quantity
ID Description Quantity
ID Description Quantity
ID Description Quantity

How do i get the query to add up all of those quantities?

View 5 Replies View Related

Calculations Need Help!!!

Oct 30, 2005

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!!)

View 3 Replies View Related

Calculations

Nov 15, 2005

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!

Do I use a certain query?

Hope you can help, Tobi :)

View 2 Replies View Related

Calculations

Jul 26, 2006

Hello all,

I'm after some advice. Access is not one of my strong points (even though I use it alot in work!)

Anyway, is it possible to create calculations in access similar to those in excel????

View 2 Replies View Related

Help With Calculations

Mar 18, 2008

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?

Thanks in advance!

View 14 Replies View Related

Calculations

Mar 30, 2007

Hi,

I've read plenty of advice warning against performing calculations in tables. What is the best way for me to achieve this.

I need to calculate the product of a number of fields.

Field1 * Field2 * Field3 * Field 4 = Value

Field1 and Field 2 change periodically

Field3 and Field4 change less frequently

I want to be able to perform filter queries using the resulting value

i.e return all items with a value greater than 100

Thanks in advance.

Al

View 2 Replies View Related

Age Calculations

Apr 13, 2008

I'm having a problem with this age formula in the query :

AGE: DateDiff("yyyy",[birth_date],Date()) & " " & " years" & " " & "and" & " " & DateDiff("m",[birth_date],Date()) Mod 12 & " " & "months"

Need your help guys in 2 probs :
1. age calculated is not right.
2. need to compute what w'd be age on the sep 1st 2008 - how do I do it?

Appreciate your help.
Thanks.

View 2 Replies View Related

Sql And Calculations!

Apr 26, 2005

Hi,

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.

Any help urgently needed!!!

View 1 Replies View Related

Need Help With Calculations

Jul 13, 2006

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.

View 10 Replies View Related

Calculations

Dec 6, 2007

Hi everyone!

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.

Thanks

View 2 Replies View Related

Best Ways To Store Data

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

Where To Store Misc. Data?

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

Pictures : Store And Display

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

How To Store Selected Songs Ids In Db?

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

Store Calculation Question

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

Store Value To (TABLE) From (FORM)

Mar 22, 2005

Hi

I want to know how can i Store Value from FORM to Table.

Ex: I made count in FORM and i want to store that data in TABLE?

View 2 Replies View Related

Store Parameter Value For Requery

Sep 29, 2005

I have a form based on a query. The query uses a parameter to filter the data to be displayed on the form. The user then enters additional data. I'm using a requery macro to refresh the data after the user completes the data entry. The problem is the user has to enter the parameter value twice. Once for the original parameter and once for the requery.

Is there a way for the requery to use the values from the original parameter instead of having to re-enter the parameter values? If so, how do you store the values from the parameter and pass these values to the requery?

Thanks for you help,

Jeff

View 3 Replies View Related

Store Calculate Values

Sep 29, 2005

I searched the archive for how to store a calculated value and found a lot of controversial dialogue about the pros/cons but not really a solution on how to do it.

I have a form based on a query. The database behind the form and query is our ERP database and is connected though an odbc connection. The form allows the user to enter some shipping dimensions and freight rates. The data is automatically stored in the ERP database and any user can view the data from the ERP software.

Here is the problem. There are also some freight calculations that occur on the form that our business teams want the results stored in the ERP database. They can view the results from the calculations using the ERP software instead having to open a 2nd application (Access form) to view the calculated data.

I have determined which fields in the ERP database will hold the data. I only need the vb code or other suggestions on how to update the calculated values into the database.

I apologize for the long message. Thanks for your help,

Jeff

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved