Append Query help needed

[jannie]

I have a table that has several fields of data for gas wells. The data is
normalized and each wells forecasts begin on the date of 1st production. Not
all wells start on the same day but the new forecast will be the same for all
the wells that exist and will be drilled this year. I know this is a easy
thing to do but I have never done this before.
I added a new field in my existing table called 2010_Forecast. I need to
populate this field with the new forecast I have in Excel. There are 9,329
days of data that I will need to populate for every well in the database -
the forecast is going out about 25.56 years into the future. I tried to just
copy and paste it in but access won't let me. I tried to append but I can't
do that either as it will create duplicate records. I just want to find the
existing well, start at the first production date and populate the forecast
in the new field.

I know this should be easy to do but I need some help doing it. Thanks.
Jannie

 

[merry_fay]

Do an update query.

Drag in your new field '2010_forecast' & put your value into the 'Update
To:' section & run it.

 

[jannie]

Merry,
I made a new update query using the table with the new forecast field. There
isn't just one value to go into the 'Update To' section there are over 9,000
values.

 

[jannie]

Also, I forgot. When the query runs to update the forecast it has to begin
updating on the first day of production for each well. The forecast is the
same for every well. It is a daily forecast that goes out over 25 years
(daily volumes) not yearly. Each daily can be a similiar or different volume.
I have the forecast in Excel it is 9340 lines long. Just a list of forecast
volumes. This list will need to start on the day of first production for each
well. (This is normalized data.)

 

[merry_fay]

Hi Jannie,

Do you have the dates in a field in your existing table?

If you do, then you can add a column to your excel spreadsheet with the
dates next to the forecasts, import it into your database then in the update
query, link the dates so it only updates a particular date to the forecast
against that date.
If the forecasts were different for different wells, you can add a column
for that & link it too so it's specific to well & date.

I hope this solves your problem!

merry_fay

 

[jannie]

Merry,
That won't work because the start dates are all different for each well. I'd
have to change the dates for each well. And how do I import the data from
excel to access?

 

[John Spencer]

What is on the the spreadsheet?

You would need to have a column with the numbers 1 to 9340 (or better 0 to
9339), plus the forecast value.

Next, are you going to ADD records (9340 for each well) or UPDATE EXISTING
records (9340) for each well.

Personally I would add a NEW Table to contain the forecast values if you
really need to store them.
Forecast (Table)
TheYear
WellID
TheDate
Forecast

Now link to the Excel Spreadsheet and then run a query like the following.
INSERT INTO Forecast(TheYear,WellId,TheDate,Forecast)
SELECT 2010 as TheYear
, WellID
, DateAdd("d",LinkedSpreadsheet.ColumnCounter,Wells.StartDate) as TheDate
, LinkedSpreadsheet.ColumnForecast
FROM Wells, LinkedSpreadsheet

Or import the data from the spreadsheet into a table named forecasts and use a
query to get the value for any well and any date.

Parameters [TargetDate] DateTime;
SELECT WellID, Wells.StartDate, [TargetDate] Forecasts.Forecast
FROM Wells INNER JOIN Forecasts
ON DateDiff("d",Wells.StartDate,[TargetDate]) = Forecasts.ColumnCounter


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[jannie]

My excel worksheet has days 1 through 9340 in col A. Header is Day. Column B
header is Forecast_2010. Then column 2 lists the forecast for each day
beginning with 1340.241625 on row 2 and ends on line 9330 with 30.001479.

In Access I have an existing Forecast table that has several of our
forecasts, and type curves. We have many. All I want to do is add this new
forecast to the other forecasts. They all start on the same date of 1st
production for each well.

Some wells started 12/1/2007 and some started a couple weeks ago, just
depends. But there are about 42 flowing wells right now that have to have
this forecast in the table so the forecast curves will plot on the graphs.

The table in access that already exists has many fields of data already.
Because of the keys I can't append data because it will cause a violation
error and won't append because of duplicate data so I have to find a way to
update only this forecast so that it lines up with each wells start date.

I'm not a computer savvy as you are but I'm trying to figure this out.
My fields in access are: PID (key field) list unique ID for each well - is
listed multiple times for each day of every forecast.
Well_Name - the name of the well
PType - another key field - is the type of well it is - bore, boregroup, etc.
Date - date field - is also a key field - this is the date the well first
came on line then goes out for about 30 years or so (daily)
Field_Forecast_Gross_2010 - This is the field I need to populate with the
new data in the Excel spreadsheet
Then there are a whole bunch more forecasts that start on the 1st day of
prod for each well. This table has about 490,000 records in it right now.
Each well is listed in wellname order by date.
Jannie

What is on the the spreadsheet?

You would need to have a column with the numbers 1 to 9340 (or better 0 to
9339), plus the forecast value.

Next, are you going to ADD records (9340 for each well) or UPDATE EXISTING
records (9340) for each well.

Personally I would add a NEW Table to contain the forecast values if you
really need to store them.
Forecast (Table)
TheYear
WellID
TheDate
Forecast

Now link to the Excel Spreadsheet and then run a query like the following.
INSERT INTO Forecast(TheYear,WellId,TheDate,Forecast)
SELECT 2010 as TheYear
, WellID
, DateAdd("d",LinkedSpreadsheet.ColumnCounter,Wells.StartDate) as TheDate
, LinkedSpreadsheet.ColumnForecast
FROM Wells, LinkedSpreadsheet

Or import the data from the spreadsheet into a table named forecasts and use a
query to get the value for any well and any date.

Parameters [TargetDate] DateTime;
SELECT WellID, Wells.StartDate, [TargetDate] Forecasts.Forecast
FROM Wells INNER JOIN Forecasts
ON DateDiff("d",Wells.StartDate,[TargetDate]) = Forecasts.ColumnCounter


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Merry,
That won't work because the start dates are all different for each well. I'd
have to change the dates for each well. And how do I import the data from
excel to access?

.