Look up table help needed

[jannie]

I have never done this before so I need hand holding for this one. I have
been thinking about how to do this effectively. I use a program DSS which
works with Access and other Engineering programs feed data into it
dynamically. I also have static tables (manual) that I input data into such a
forecasts. We have several forecasts we use and refer to that plot type
curves on graphs so we can monitor production (anticipated gas well
production). I have a 2010 forecast in Excel. There are 2 columns, one is
numbered 1 - 9329, and column 2 is the daily forecast for that day.

My table is in Access. It is a manual table with several other forecasts as
well. I created a new field in the table 2010_CPC_Forecast. I want to
populate this field with the new CPC daily forecast data. What I need to have
happen is I currently have 42 flowing gas wells that will need to have this
new daily forecast applied to them. That is basically 9330 records per well.
That is a lot of data. And the more wells we drill the bigger this monster
will become.

How do I create a lookup table where Access can go out get the forecast
data, show it in a graph or report, whatever - on the fly - not actually
store the data for each well but however reference it as it is needed from
the forecast table.

I could use some serious step by step help on this. If I need to create a
single table for each forecast that is fine I can do that. It is the lookup I
need help with and making it work. Thanks for your help on this.
Jannie

 

[John W. Vinson]

How do I create a lookup table where Access can go out get the forecast
data, show it in a graph or report, whatever - on the fly - not actually
store the data for each well but however reference it as it is needed from
the forecast table.

Create a Query joining the two tables.

That's what Access is designed to do - it's a relational database!

You don't need a "lookup field" - you just need a Query, which just needs a
common field between the two tables.

I'd give you detailed step by step instructions if you'll post the actual
structure (fieldnames, datatypes) of your two tables.

 

[jannie]

Excel Worksheet:
Column A Column B
Days MCFD
1 1340.241625
2 1330.640539
3 1321.192833
4 1311.894729
5 1302.742574
and so on... the forecast goes out 25.56 years (9329 lines of data).
Column one is General column two is a Number.

Access table: (Manual_Input_Forecast)
Field Name Data Type
PID Text
Well_Name Text
PTYPE Text
Date Date/Time
Field_Forecast_Gross_CPC_2010 Number - This is where the new
forecast goes
PD_MCFPD Daily Gas Volume - Number
The PID, PTYPE, and Date fields are Key because DSS requires those 3 fields
in any table.

The forecast in Excel is the same for every well. The forecast must start on
the the day of first production for every well. There will be volume in the
PC_MCFPD until the well came on production.

The access table has many other forecasts in it as well.
What my concern is that right now we have 42 wells flowing to sales. And we
are forecasting out 25.56 years for each well. that is a lot of data stored.
We will bring on a bunch of new wells this year. The forecast will have to be
entered for all of those wells as well. And that data will be stored as well.
The table will grow very quickly.

That is why I wondered if there is a way to do some kind of lookup where the
database goes and gets the forecast, creates like a temp table on the fly.
Where we can still create and use the graphs we need and generate reports as
well.

You are the expert so let me know what you think.

 

[jannie]

John,
I was able to join the data on the first well but I had to add the field
days to my original table and join on days. I had to copy down the over 9000
days in order to get the data to join. I don't know how to number the days in
the existing records other than to copy them down (arrow key dn). Reason is I
have to start day 1 on the 1st day of production for each well. Maybe an
update query would work for that but I've never done that either.

 

[John W. Vinson]

There's a fair bit to go over here, and I'm pretty busy right now. You'll need
to import the data from Excel into Access, and I'll need to think about your
data model and your business model to be able to answer; I'll try to do so
over the weekend. I'm sure I'll have more questions, but it WILL be possible
to do this, and it won't involve any "lookup fields" I can assure you!

 

[jannie]

Thks. Oh yeh I had a small typo - There is NO production showing for wells
until they come on line. So in my Access data base when there is the first
gas volume PD_MDFPD for a given well, then that is the day the forecast
begins. Also the PID is my access tbl is the well ID#.

Jannie

 

[ryguy7272]

See if this helps:

Lots more like that on on YouTube!!

 

[RD]

See if this helps:

Lots more like that on on YouTube!!

Wow! Who knew? Well, I guess you did.

Thanks for this post. I have a new place to point when people ask for
help.

RD

 

[John W. Vinson]

John,
I was able to join the data on the first well but I had to add the field
days to my original table and join on days. I had to copy down the over 9000
days in order to get the data to join. I don't know how to number the days in
the existing records other than to copy them down (arrow key dn). Reason is I
have to start day 1 on the 1st day of production for each well. Maybe an
update query would work for that but I've never done that either.

I'm having some health problems and finding it hard to concentrate... it may
be a while before I'll be able to dig through your logic here. I DO know that
if your tables were properly normalized, that you would not need to "copy
down" anything at all, much less 9000 rows!