Update Dates from Form

[cwilson30341]

Hi all,

I have a table that contains seven dates that are used to update another table (didn't know how else to to it). On my form, I would like to have the user enter a begin_date, click a button, and update the table with the begin_date and the next six days.

I have tried to do this with a simple update query, but it hasn't been working correctly.

If someone could point me in the right direction, I would be very greatful!

Chris

 

[RuralGuy]

If six of the fields are *always* the next six days from the first field then you do not need the fields. What are you using them for?

 

[cwilson30341]

The dates are used in a query to add dates to a standard set of criteria (in another table). If there is a better way to do this, I have no issues changing it.

 

[RuralGuy]

The dates can be created dynamically with a query but I would question your table structure that has fields with 7 associated dates. What is the "standard set of criteria" table structure?

 

[cwilson30341]

Basically, the COF_TABLE_BASE data will not change, has 44,506 records, and has the following fields:

Product
Min Rate
Max Rate
Avg Rate
Min Term
Max Term
Avg Term
COF
Base
RateLock
Prepayment

Using the base table and the date table, I create a COF_TABLE with:

INSERT INTO COF_Table ( Product, [Date], [Min Rate], [Max Rate], [Avg Rate], [Min Term], [Max Term], [Avg Term], COF, Base, RateLock, Prepayment )
SELECT COF_Table_Base.Product, tblRun_Dates.Run_Dates AS [Date], COF_Table_Base.[Min Rate], COF_Table_Base.[Max Rate], COF_Table_Base.[Avg Rate], COF_Table_Base.[Min Term], COF_Table_Base.[Max Term], COF_Table_Base.[Avg Term], COF_Table_Base.COF, COF_Table_Base.Base, COF_Table_Base.RateLock, COF_Table_Base.Prepayment
FROM COF_Table_Base, tblRun_Dates;

The COF_TABLE is used to process daily Cost of Funds.

As you can see, this is very confusing.

 

[RuralGuy]

Your description of the table does not include the Date field and *Date* is a reserved word in Access and should not be used for a name of anything. Is the tblRun_Dates table the table of 7 dates? It looks like you are only using one of them.

 

[cwilson30341]

Your description of the table does not include the Date field and *Date* is a reserved word in Access and should not be used for a name of anything. Is the tblRun_Dates table the table of 7 dates? It looks like you are only using one of them.

Unfortunately, the final table must have Date as the field name as that is how the Ab Initio Developers want to see it when it is loaded. The query above is a (i think) Cartesian join.

It takes the COF data and adds a date to each record. then it kind of loops and does it for each date. The result is a table with 311,542 records. The data in this table is used to calculate Cost of Funds for each record.

 

[RuralGuy]

As you can see, this is very confusing.

I'll have to second that thought. I'm afraid I have no further suggestions to offer here.

 

[cwilson30341]

I really appreciate your trying to help. I thought this would be pretty simple and was just something that I was overlooking.

Chris

 

[RuralGuy]

It will take someone with better query skills than mine. Sorry.