Storing Data In Access for Use in Excel/Graphing

[Sprinks]

I have prototyped an Excel macro using a Userform that, based on the best
curve fit of price vs. size, lets a user enter an arbitrary size (x) to
return its price. The data itself, however, is buried in my code, and I'd
prefer to put it in Access.

In my macro, I've actually stored just a code for the curve type (Linear,
Quadratic, or Logarithmic), the function coefficients, and the correlation,
but if I were to use Access for the data, it seems to make more sense to
calculate on the fly from the data itself.

Two tables are required:

Items
-------------
ItemID AutoNumber (PK)
ItemName Text
Unit Text (SF, TONS, LF, etc.)
DataYear (The calculation adds escalation to prices published at the
beginning of the year)

ItemPricing
-------------------
ItemPricingID AutoNumber (PK)
ItemID (FK)
Size Integer
Price Currency

I need help with:

- Reading the Access data from the Items table when the macro is invoked
from Excel to populate a userform, creating textboxes and command buttons
on-the-fly as required.

Item Size Unit Result
Condensers, Air-Cooled TB1 TN CB1 TB2 CB2
Generators, Dieset TB1 KW CB1 TB2 CB2

TB1 is a textbox where the user enters the test value.
CB1 is the command button that performs the regression.
CB2 enters the value in the active cell.

- When the regression command button is pressed, read the appropriate data
from the ItemPrice table, using the foreign key from step 1.

- From within Access, as a quality control measure on each year's data
entry, I would like to program a button that would:

- Perform the 3 types of regression on the data
- Select the one with the highest correlation
- Display a graph of the data, showing the regression function, correlation,
and the data points

Thanks in advance.

Sprinks

 

[dch3]

Yes you are correct. One of the key reasons for using a database is just that
- a BASE of DATA to do with what you will... however grasshopper, you must
first pluck the fly from the air before you can skin the elephant. (Sorry I'm
just in that mood)

Before you go off and just start building something in Access. Buy a book on
Access (The Access Developer's Handbook by Sybex is a good one), and read,
read, read. You'll save yourself a lot of time and aggrivation. Also, plan
out how its going to work FIRST before you even start Access.

 

[Sprinks]

Thank you for your reply.

Actually, I have created many multi-user database applications in Access, so
I am conversant in VBA and I agree with you--The Developer's Handbook is the
Tao Te Ching of the Access world--I could not have completed any of my
projects without it or the generous assistance of people on this forum.

I do not, however, yet have these skills in my quiver. Can you suggest a
starting point?

Thank you.

Sprinks

 

[dch3]

Do you know enough about DAO to loop through records manually by typing the
commands directly in the Immediate Window?

 

[Sprinks]

Not yet.

Sprinks

Do you know enough about DAO to loop through records manually by typing the
commands directly in the Immediate Window?

 

[dch3]

Most Access Developer's book will at some point cover VBA and discuss DAO.
You'll probably need to read up on it and understand how DAO can be used to
go record by record through a recordset.