Linear Interpolation Headache

Feb 7, 2006

I've been searching in vain for a solution to a relatively simple bit of mathematics that I’d like to Access to undertake.

It involves interpolating between data, as illustrated in the following example. Basically have several items, and let's say different "prices" dependent on the number of units purchased. The relationship between the “prices” is not a straightforward formula, therefore I need a query to return an interpolated price for a given number of "units".

DATA
ItemID Price10Price20Price35Price50Price100Price 1000
1£70£135£200£315£800£6500
2£23£31£61£89£210£1500

OUTPUT
ItemIDUnitsPrice(int)
117?
231?

So, for example
if Units <10, Then Price(int) = Price10
if Units = 17, Price(int) = Price10 + (17-10)*(Price20-Price10)/(20/10)
if Units = 32, Price(int) = Price20 + (32-20)*(Price35-Price20)/(35/20)
if Units >1000, Then Price(int) = Price1000

I'm aware that IIF statements might do the job, but I have 15 different 'Price' points, and don't particularly want to nest them all.

This problem was solved previously in Excel using a clumsy set of VLOOKUP/HLOOKUP functions in conjunction with If statements.

View Replies


ADVERTISEMENT

General :: Effect Size In Multiple Linear Regression

Jul 4, 2012

I have (from SPSS) many multiple regression analyses, and each one of them has about 7-8 independent variables. I need to find the effect size-in particular I want to have effect size above 1% and statistical significance p<0.001. I am familiar with p, but I cannot understand whether effect size is b, beta or R square and how to interpret it.

View 1 Replies View Related

Simple Linear Regression On Multiple Data Fields

Mar 25, 2013

I have a Table that looks like the following:

A B C D E
10 12 8 4 14
9 10 7 5 12
12 13 10 3 17
10 11 6 6 15

I have figured out how to calculate the regression statistics for A vs B on a query. So it looks like this...

Predicted B Difference between
A B m b via regression Predicted and Actual
10 12 .9474 1.7895 11.263 -.737
9 10 .9474 1.7895 10.316 .316
12 13 .9474 1.7895 13.158 .158
10 11 .9474 1.7895 11.263 .263

Question: What I am looking to do is to duplicate this for A vs C, A vs D, A vs E, B vs C, B vs D, and so on until all of the pairs are calculated and put into one table/query. I know I can just create more fields to the right with the A vs C data and continue on that way, but this does not seem very efficient and I am limited in the number of fields that can be created. So it seems that "going down" rather than across makes more sense however I do not know how to move on to the next pair (A vs C and so on).

I thought of running the regression query over and over again and "recording" the data generated into a table. Once again I have the same problem. How do I tell the regression query to run the next pair? If my data is setup incorrectly, I am willing to change that as well.

View 1 Replies View Related

A Big Headache Please Help

Dec 4, 2007

Hi all,

ok i have a bit of a problem here and this is it.

I have set up a quesry based on 2 forms 1 orders and 2 order details.
i have the set a criteria on the report to print a order number

i have then set up a report based on the query.
Then i have created a command button on my form up untill here it is fine the problem is when i create the order and send it to print the criterai box pops up which is ok but because i am using a touch screen it will not be viable for the user to enter the number each time he prints i would want it to pick it up from the current order id.

Is there a way to do this as i am sure all invoice systems and everything should be working like this or is there a better way to do this or am i doing something wrong i have attached a screen shot of what si going on

Thanks

View 6 Replies View Related

UK Date Headache

Jun 1, 2005

Hi guys

I'm having issues with getting UK dates to show in access.

I created a table and then undertook an insert with the following:

docmd.RunSQL "INSERT into tbl_test([dates]) VALUES (#01/06/2005#);"

Note that my machine is in UK local and the format of the field is short date. The date in access is 06/01/2005. ie 6th Jan , not 1st June as it should be

If I set up a query via the query wizard to look at this field then I see that the date is shown in US format.

I tried to use the following in the field section:

result: format(dates, "dd/mm/yyyy")

and the date still showed up at US ie 06/01/2005. argghhhh. (assume due to the fact that format returns a string and I'm looking at a date field)

Any ideas how to overcome?

View 3 Replies View Related

Causing A Headache

Sep 28, 2006

I have a main form [mainform] with several subforms on it, two of which are on a tab.

On the second tab [page79] there is a subform [subformlist] which has like 25 checkboxes on it, any combonation of which can be picked by the user.

I need there to be at least one pick required of this subform, else the record won't show in a general query I have, since the table the subform is based on is linked to the record.

Is there anyway to make making a choice of one of at least one of these checkboxes checked a manditory thing before the user can close or move on to the next record?

I've tried making an "Other" box that on the Table has a default value of "True" which I thought would cause it to show in the query record...to no avail.

The only true way to make this work is to have it mandatory that the user pick from AT LEAST one, usually more before the record is complete.

Any help? Other than writing a MASSIVE If...Then statement...?

Or is there a way to make it so the choice isn't required for the record to show up in the query?

I'd post...but my db is too massive, even zipped.
:(

View 2 Replies View Related

2 Tables + 1 Query = 1 Headache

Jul 28, 2006

I am trying build what I thought was a super simple data base, but this is driving me crazy.

I have a group of building maintenance manuals (12) each of which is subdivided into sections. I have created a list of all the section headings and respective page numbers and have imported them into a table (tblMASTER ) with these fields:

MasterIndexpkf
SectionNumber (unique sequential number 1-311)
SectionName
PageNumber

I have a second table (tblCROSSREF) with these fields:

CrossRefIndexpkf
MasterIndexfkf
SectionNumber
SectionName
PageNumber

The two tables are linked in a one-to-many relationship on the respective MasterIndex fields.

I also have a query (qryMaster) which includes ALL the fields in tblMaster which serves as a lookup to the SectionName field in tblCROSSREF.


What I want to do is to select a section number in tblMaster and enter all the possible cross references in tblCROSSREF by selecting the available section names from qryMaster.

This seems to me to be sooo simple, but I am having a real headache!

My primary problem is that when I use the lookup function in the SectionName field (in tblCROSSREF) it returns not the Section Name, but the Primary Key Field number.

This thing is making me feel like the AFLAC duck. Can anyone give me a clue about where I have gone wrong?

Thanks,

Rick

View 4 Replies View Related

Headache With Changing The Filename... ...

Oct 19, 2006

Hi all,

I got a headache to changing my form's filename... i am totally a newbie in Access while in the few months ago... so i just create all those form or table name's for the way i like. But now i had already know how important of management of the filename because when u r using the module to write the code u will be headache to look for the filename that u want in the code. So that is important to add 'tbl', 'frm', 'Qry' infront of the filename.

So do anyone have a good way to edit all the file name with all match to the other in the module with a quick way rather then one by one editing (really tired for one by one) Thanks!!!

View 4 Replies View Related

Queries & Calculated Fields - Headache! Help!

Dec 16, 2006

I need some help. My brain just can't grasp how to do this.

I have an Access DB. I sell vinyl records. I have an order form. When a customer orders a record, each vinyl record has a unique ID#. No two vinyl records are alike. I have four tables: Customers, Orders, Inventory, & Order Details. I have an "Order Form" form where I input all the info. It's an all-in-one form where you can view and data entry in one screen. The form has two subforms in it.
Here's the problem:
If a customer orders more than one item (i.e. a vinyl record), each Inventory_ID (representing one vinyl record) is associated with one Order_ID which is, in turn, related to one Customer_ID. If I have one Order_ID that has multiple Inventory_IDs (i.e. has ordered two or more vinyl records) that belongs to one customer, how can I calculate that total cost?
Currently, I have a calculated field within my all-in-one form that calculates only the price of the Inventory_ID that one currently sees on the subform. The Inventory_ID item has a set price, for instance $5.00. If I scroll through the ordered Inventory_IDs, the calculated field will only display the price of the product that you currently see. It's not really calculating anything, I guess. It's just bound to that field, but I need to sum up all the price fields for all the Inventory_IDs that are associated with one Order_ID & Customer_ID whether it's a query or calculated field...I just need it done so that it'll display on this form.
Any help would be much appreciated. I can show you whatever you need to see. Thanks in advance.
David

View 10 Replies View Related

ComboBox/Forms Synching Headache

Jul 26, 2005

Ok, here's something I think I've done dozens of times, successfully. Now I can't get it to work.

I have a Parent Form with three Subforms (based on 3 separate, linked, tables).

I have a combo box on the parent form which looks up a piece of EQUIPMENT by its unique ID and the other two forms display equipment DETAIL and SERVICE HISTORY related to that equipment. It works just great until I use the ParentForm's navigation buttons. The two Subforms change to display the information relevant to other pieces of equipment, but the combo box continues to show the original look up.

Whaaaat have I done or not done here?

Thanks,

Rick

View 2 Replies View Related

Append Query .v. Table Query .v. Headache!

Mar 20, 2007

Ok, this is what I want to do :

I want to 'append' individual records from 2 tables and place in an archive table or within another database, whichever is the best option.
I then want to be able to 'delete' the relevant records from one table.

The tables are tproperty and trents. This property paid rents but has since been sold. Therefore it no longer belongs in the database, however client wishes to keep details of the property/person/and rents paid in past, for any future ref. These tables are linked in relationships to tlessee and tbilling.
I've read books/notes/looked on here for inspiration and the right direction! If I choose 'append' which seems pretty straight forward then a 'delete' query, how do I choose only one record? Is a make-table onto a different database a better option? It would appear that the whole table is copied over? Can't understand the issue about auto-numbers being copied over? Do I have to use an append/delete query for each individual property that's ever removed?
I'm at a loss! :) Thanks

View 1 Replies View Related







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