Add values from one table to another table?? - I know this is simp

[Guest]

I have two tables:

Table 1
T1Value1, T1Value2, T1Value3, T1Value4, etc

Table 2,
T2Value1 T2Value2, T2Value2, T2Value4, etc

Both tables are tied to forms where these values are entered.

I want to put a command buttom on Form1 that when clicked on will add the
current values of Table1 to the values of Table2. (e.g. T2Value1 now becomes
T2Value1 + T1Value1.)

Thanks for you help in advance.

 

[Guest]

Your tables must have some field in common that will allow them to be joined.
If you can do that, then you will create an update query for Table2 that
will update the fields in Table2 with the sums.

 

[Guest]

Bruce, thanks for your help!
The update query works (kind of.)

First, both tables have exactly the same fields so no problem finding a
common field.

The first table is called Stats. Stats are date driven (e.g.)

ID Date APEL APPCL ABPCL
1 06/01/04 6.0 6.25 5.75
2 06/15/04 updated updated updated
3 06/30/04 updated updated updated

The second table is Gains: same fields

ID Date APEL APPCL ABPCL
1 06/15/04 .25 .125 .5
2 06/30/04 .125 .1875 .125

Both tables are linked to individual forms. The Gains table has a button
"Update Stats" When I click on update stats button the current value of gains
should be added to the stats total.

Once a record has been added to the total I don't want the user to be able
to go back and accidentally add that record again. So once record ID 1 is
added updated is it possible to prevent this from reoccuring. Maybe some kind
of warning message?

I'm not sure my update query is even designed the best way, here it is:

Field: APEL
Table: Stats
Update To: [Stats]![APEL]+[Gains]![APEL]

Repeats of course with each field...

Is this correct and any ideas on how to limit the update to 1 time?

Sorry so wordy, thanks a bunch for your help!!!!

-Tim

 

[Guest]

I was just reading that an update query cannot add records and that you must
use an append query. Being that I want to update the values and then add them
as a new record in another table how would I accomplish this?

Thanks again!!!!

Bruce, thanks for your help!
The update query works (kind of.)

First, both tables have exactly the same fields so no problem finding a
common field.

The first table is called Stats. Stats are date driven (e.g.)

ID Date APEL APPCL ABPCL
1 06/01/04 6.0 6.25 5.75
2 06/15/04 updated updated updated
3 06/30/04 updated updated updated

The second table is Gains: same fields

ID Date APEL APPCL ABPCL
1 06/15/04 .25 .125 .5
2 06/30/04 .125 .1875 .125

Both tables are linked to individual forms. The Gains table has a button
"Update Stats" When I click on update stats button the current value of gains
should be added to the stats total.

Once a record has been added to the total I don't want the user to be able
to go back and accidentally add that record again. So once record ID 1 is
added updated is it possible to prevent this from reoccuring. Maybe some kind
of warning message?

I'm not sure my update query is even designed the best way, here it is:

Field: APEL
Table: Stats
Update To: [Stats]![APEL]+[Gains]![APEL]

Repeats of course with each field...

Is this correct and any ideas on how to limit the update to 1 time?

Sorry so wordy, thanks a bunch for your help!!!!

-Tim

Your tables must have some field in common that will allow them to be joined.
If you can do that, then you will create an update query for Table2 that
will update the fields in Table2 with the sums.

 

[Guest]

You'll need to add a Boolean field ("updated" in this example) to the Stats
table. This query should do it

UPDATE Stats RIGHT JOIN gains ON Stats.date = gains.date
SET Stats.apel = Stats!apel+gains!apel,
Stats.appcl = Stats!appcl+gains!appcl,
Stats.abpcl = Stats!abpcl+gains!abpcl,
Stats.updated = -1
WHERE (((Stats.updated)=0));

I am assuming that there is ONLY ONE record for any date in both tables. If
this is not true then adjustments must be made.

Bruce, thanks for your help!
The update query works (kind of.)

First, both tables have exactly the same fields so no problem finding a
common field.

The first table is called Stats. Stats are date driven (e.g.)

ID Date APEL APPCL ABPCL
1 06/01/04 6.0 6.25 5.75
2 06/15/04 updated updated updated
3 06/30/04 updated updated updated

The second table is Gains: same fields

ID Date APEL APPCL ABPCL
1 06/15/04 .25 .125 .5
2 06/30/04 .125 .1875 .125

Both tables are linked to individual forms. The Gains table has a button
"Update Stats" When I click on update stats button the current value of gains
should be added to the stats total.

Once a record has been added to the total I don't want the user to be able
to go back and accidentally add that record again. So once record ID 1 is
added updated is it possible to prevent this from reoccuring. Maybe some kind
of warning message?

I'm not sure my update query is even designed the best way, here it is:

Field: APEL
Table: Stats
Update To: [Stats]![APEL]+[Gains]![APEL]

Repeats of course with each field...

Is this correct and any ideas on how to limit the update to 1 time?

Sorry so wordy, thanks a bunch for your help!!!!

-Tim

Your tables must have some field in common that will allow them to be joined.
If you can do that, then you will create an update query for Table2 that
will update the fields in Table2 with the sums.

 

[Guest]

Bruce, first, thanks a ton for your help!!!

Well... the query runs with no errors but no records update. I think maybe
I'm making it too complicated: Let me try once more...

Basically all I'm trying to do is take the current values in the gains
table, add them to the current value in the stats table and write the new
value as a new record in the stats table. I think my statement about it being
"date driven" was inaccurate. The date doesn't really matter. Just as long as
two entries are not on the same date.So... something like this:

Stats Table

ID Date APEL APPCL ABPCL
1 06/01/04 6.0 6.25 5.75
2 06/15/04 6.25 6.375 6.25 <---- Each
new record
3 06/30/04 6.375 6.625 6.625 updates with
the values below

ID Date APEL APPCL ABPCL
1 06/15/04 .25 .125 .5
2 06/30/04 .125 .1875 .125


Thanks for your patience!!

You'll need to add a Boolean field ("updated" in this example) to the Stats
table. This query should do it

UPDATE Stats RIGHT JOIN gains ON Stats.date = gains.date
SET Stats.apel = Stats!apel+gains!apel,
Stats.appcl = Stats!appcl+gains!appcl,
Stats.abpcl = Stats!abpcl+gains!abpcl,
Stats.updated = -1
WHERE (((Stats.updated)=0));

I am assuming that there is ONLY ONE record for any date in both tables. If
this is not true then adjustments must be made.

Bruce, thanks for your help!
The update query works (kind of.)

First, both tables have exactly the same fields so no problem finding a
common field.

The first table is called Stats. Stats are date driven (e.g.)

ID Date APEL APPCL ABPCL
1 06/01/04 6.0 6.25 5.75
2 06/15/04 updated updated updated
3 06/30/04 updated updated updated

The second table is Gains: same fields

ID Date APEL APPCL ABPCL
1 06/15/04 .25 .125 .5
2 06/30/04 .125 .1875 .125

Both tables are linked to individual forms. The Gains table has a button
"Update Stats" When I click on update stats button the current value of gains
should be added to the stats total.

Once a record has been added to the total I don't want the user to be able
to go back and accidentally add that record again. So once record ID 1 is
added updated is it possible to prevent this from reoccuring. Maybe some kind
of warning message?

I'm not sure my update query is even designed the best way, here it is:

Field: APEL
Table: Stats
Update To: [Stats]![APEL]+[Gains]![APEL]

Repeats of course with each field...

Is this correct and any ideas on how to limit the update to 1 time?

Sorry so wordy, thanks a bunch for your help!!!!

-Tim

Your tables must have some field in common that will allow them to be joined.
If you can do that, then you will create an update query for Table2 that
will update the fields in Table2 with the sums.

:

I have two tables:

Table 1
T1Value1, T1Value2, T1Value3, T1Value4, etc

Table 2,
T2Value1 T2Value2, T2Value2, T2Value4, etc

Both tables are tied to forms where these values are entered.

I want to put a command buttom on Form1 that when clicked on will add the
current values of Table1 to the values of Table2. (e.g. T2Value1 now becomes
T2Value1 + T1Value1.)

Thanks for you help in advance.

 

[Guest]

Tim,
NOW I understand. If you are processing one Gains record at a time this
would be easy. Figuring out how to APPEND multiple records with a running
total will take a little turkey and sweet potatoes. I'll check in after the
Holiday.

Bruce, first, thanks a ton for your help!!!

Well... the query runs with no errors but no records update. I think maybe
I'm making it too complicated: Let me try once more...

Basically all I'm trying to do is take the current values in the gains
table, add them to the current value in the stats table and write the new
value as a new record in the stats table. I think my statement about it being
"date driven" was inaccurate. The date doesn't really matter. Just as long as
two entries are not on the same date.So... something like this:

Stats Table

ID Date APEL APPCL ABPCL
1 06/01/04 6.0 6.25 5.75
2 06/15/04 6.25 6.375 6.25 <---- Each
new record
3 06/30/04 6.375 6.625 6.625 updates with
the values below

ID Date APEL APPCL ABPCL
1 06/15/04 .25 .125 .5
2 06/30/04 .125 .1875 .125


Thanks for your patience!!

You'll need to add a Boolean field ("updated" in this example) to the Stats
table. This query should do it

UPDATE Stats RIGHT JOIN gains ON Stats.date = gains.date
SET Stats.apel = Stats!apel+gains!apel,
Stats.appcl = Stats!appcl+gains!appcl,
Stats.abpcl = Stats!abpcl+gains!abpcl,
Stats.updated = -1
WHERE (((Stats.updated)=0));

I am assuming that there is ONLY ONE record for any date in both tables. If
this is not true then adjustments must be made.

Bruce, thanks for your help!
The update query works (kind of.)

First, both tables have exactly the same fields so no problem finding a
common field.

The first table is called Stats. Stats are date driven (e.g.)

ID Date APEL APPCL ABPCL
1 06/01/04 6.0 6.25 5.75
2 06/15/04 updated updated updated
3 06/30/04 updated updated updated

The second table is Gains: same fields

ID Date APEL APPCL ABPCL
1 06/15/04 .25 .125 .5
2 06/30/04 .125 .1875 .125

Both tables are linked to individual forms. The Gains table has a button
"Update Stats" When I click on update stats button the current value of gains
should be added to the stats total.

Once a record has been added to the total I don't want the user to be able
to go back and accidentally add that record again. So once record ID 1 is
added updated is it possible to prevent this from reoccuring. Maybe some kind
of warning message?

I'm not sure my update query is even designed the best way, here it is:

Field: APEL
Table: Stats
Update To: [Stats]![APEL]+[Gains]![APEL]

Repeats of course with each field...

Is this correct and any ideas on how to limit the update to 1 time?

Sorry so wordy, thanks a bunch for your help!!!!

-Tim

:

Your tables must have some field in common that will allow them to be joined.
If you can do that, then you will create an update query for Table2 that
will update the fields in Table2 with the sums.

:

I have two tables:

Table 1
T1Value1, T1Value2, T1Value3, T1Value4, etc

Table 2,
T2Value1 T2Value2, T2Value2, T2Value4, etc

Both tables are tied to forms where these values are entered.

I want to put a command buttom on Form1 that when clicked on will add the
current values of Table1 to the values of Table2. (e.g. T2Value1 now becomes
T2Value1 + T1Value1.)

Thanks for you help in advance.

 

[Guest]

Bruce, yes, that is correct. I am only processing one gains record at a time.

Thanks again for your help!!!

-Tim

Tim,
NOW I understand. If you are processing one Gains record at a time this
would be easy. Figuring out how to APPEND multiple records with a running
total will take a little turkey and sweet potatoes. I'll check in after the
Holiday.

Bruce, first, thanks a ton for your help!!!

Well... the query runs with no errors but no records update. I think maybe
I'm making it too complicated: Let me try once more...

Basically all I'm trying to do is take the current values in the gains
table, add them to the current value in the stats table and write the new
value as a new record in the stats table. I think my statement about it being
"date driven" was inaccurate. The date doesn't really matter. Just as long as
two entries are not on the same date.So... something like this:

Stats Table

ID Date APEL APPCL ABPCL
1 06/01/04 6.0 6.25 5.75
2 06/15/04 6.25 6.375 6.25 <---- Each
new record
3 06/30/04 6.375 6.625 6.625 updates with
the values below

ID Date APEL APPCL ABPCL
1 06/15/04 .25 .125 .5
2 06/30/04 .125 .1875 .125


Thanks for your patience!!

You'll need to add a Boolean field ("updated" in this example) to the Stats
table. This query should do it

UPDATE Stats RIGHT JOIN gains ON Stats.date = gains.date
SET Stats.apel = Stats!apel+gains!apel,
Stats.appcl = Stats!appcl+gains!appcl,
Stats.abpcl = Stats!abpcl+gains!abpcl,
Stats.updated = -1
WHERE (((Stats.updated)=0));

I am assuming that there is ONLY ONE record for any date in both tables. If
this is not true then adjustments must be made.


:

Bruce, thanks for your help!
The update query works (kind of.)

First, both tables have exactly the same fields so no problem finding a
common field.

The first table is called Stats. Stats are date driven (e.g.)

ID Date APEL APPCL ABPCL
1 06/01/04 6.0 6.25 5.75
2 06/15/04 updated updated updated
3 06/30/04 updated updated updated

The second table is Gains: same fields

ID Date APEL APPCL ABPCL
1 06/15/04 .25 .125 .5
2 06/30/04 .125 .1875 .125

Both tables are linked to individual forms. The Gains table has a button
"Update Stats" When I click on update stats button the current value of gains
should be added to the stats total.

Once a record has been added to the total I don't want the user to be able
to go back and accidentally add that record again. So once record ID 1 is
added updated is it possible to prevent this from reoccuring. Maybe some kind
of warning message?

I'm not sure my update query is even designed the best way, here it is:

Field: APEL
Table: Stats
Update To: [Stats]![APEL]+[Gains]![APEL]

Repeats of course with each field...

Is this correct and any ideas on how to limit the update to 1 time?

Sorry so wordy, thanks a bunch for your help!!!!

-Tim

:

Your tables must have some field in common that will allow them to be joined.
If you can do that, then you will create an update query for Table2 that
will update the fields in Table2 with the sums.

:

I have two tables:

Table 1
T1Value1, T1Value2, T1Value3, T1Value4, etc

Table 2,
T2Value1 T2Value2, T2Value2, T2Value4, etc

Both tables are tied to forms where these values are entered.

I want to put a command buttom on Form1 that when clicked on will add the
current values of Table1 to the values of Table2. (e.g. T2Value1 now becomes
T2Value1 + T1Value1.)

Thanks for you help in advance.

 

[Guest]

Tim,
So, the way I am picturing it, you will be processing the gains from a form...
an Update Stats button could invoke some code that would query the Stats
table, check to see that the Gains date being processed has not already been
updated to the Stats table and if not, would append a single record to the
Stats table with a new total, based on the Gains record

Start with

SELECT TOP 1 Stats.apel, Stats.appcl, Stats.abpcl, Stats.date
FROM Stats
ORDER BY Stats.date DESC;

to get the most recent record in the Stats table.

if Stats.date > Gains.date then
issue message
else
calculate new totals for APEL, APPCL ABPCL
append new record to Stats with new date and totals

There is not much protection (or accomdation) here for Gains being processed
out of chronological order.

Overall, I would have to recommend removing Stats as a table, since it
represents a dynmaic summary of Gains transations. It would be better to
create it on demand from the Gains table.

Bruce, yes, that is correct. I am only processing one gains record at a time.

Thanks again for your help!!!

-Tim

Tim,
NOW I understand. If you are processing one Gains record at a time this
would be easy. Figuring out how to APPEND multiple records with a running
total will take a little turkey and sweet potatoes. I'll check in after the
Holiday.

Bruce, first, thanks a ton for your help!!!

Well... the query runs with no errors but no records update. I think maybe
I'm making it too complicated: Let me try once more...

Basically all I'm trying to do is take the current values in the gains
table, add them to the current value in the stats table and write the new
value as a new record in the stats table. I think my statement about it being
"date driven" was inaccurate. The date doesn't really matter. Just as long as
two entries are not on the same date.So... something like this:

Stats Table

ID Date APEL APPCL ABPCL
1 06/01/04 6.0 6.25 5.75
2 06/15/04 6.25 6.375 6.25 <---- Each
new record
3 06/30/04 6.375 6.625 6.625 updates with
the values below

ID Date APEL APPCL ABPCL
1 06/15/04 .25 .125 .5
2 06/30/04 .125 .1875 .125


Thanks for your patience!!



:

You'll need to add a Boolean field ("updated" in this example) to the Stats
table. This query should do it

UPDATE Stats RIGHT JOIN gains ON Stats.date = gains.date
SET Stats.apel = Stats!apel+gains!apel,
Stats.appcl = Stats!appcl+gains!appcl,
Stats.abpcl = Stats!abpcl+gains!abpcl,
Stats.updated = -1
WHERE (((Stats.updated)=0));

I am assuming that there is ONLY ONE record for any date in both tables. If
this is not true then adjustments must be made.


:

Bruce, thanks for your help!
The update query works (kind of.)

First, both tables have exactly the same fields so no problem finding a
common field.

The first table is called Stats. Stats are date driven (e.g.)

ID Date APEL APPCL ABPCL
1 06/01/04 6.0 6.25 5.75
2 06/15/04 updated updated updated
3 06/30/04 updated updated updated

The second table is Gains: same fields

ID Date APEL APPCL ABPCL
1 06/15/04 .25 .125 .5
2 06/30/04 .125 .1875 .125

Both tables are linked to individual forms. The Gains table has a button
"Update Stats" When I click on update stats button the current value of gains
should be added to the stats total.

Once a record has been added to the total I don't want the user to be able
to go back and accidentally add that record again. So once record ID 1 is
added updated is it possible to prevent this from reoccuring. Maybe some kind
of warning message?

I'm not sure my update query is even designed the best way, here it is:

Field: APEL
Table: Stats
Update To: [Stats]![APEL]+[Gains]![APEL]

Repeats of course with each field...

Is this correct and any ideas on how to limit the update to 1 time?

Sorry so wordy, thanks a bunch for your help!!!!

-Tim

:

Your tables must have some field in common that will allow them to be joined.
If you can do that, then you will create an update query for Table2 that
will update the fields in Table2 with the sums.

:

I have two tables:

Table 1
T1Value1, T1Value2, T1Value3, T1Value4, etc

Table 2,
T2Value1 T2Value2, T2Value2, T2Value4, etc

Both tables are tied to forms where these values are entered.

I want to put a command buttom on Form1 that when clicked on will add the
current values of Table1 to the values of Table2. (e.g. T2Value1 now becomes
T2Value1 + T1Value1.)

Thanks for you help in advance.

 

[Guest]

Bruce, you are right on the money! That's exactly how it should work. I
think I can figure out the individual steps from your outline with the
exception of appending a single record. I have searched unsucessfully. How
would I go about doing that? Also, I would like to follow your advice on
removing the stats table but there is a starting stats record (not a gain but
a beginning record) that must be entered by each user. If I remove the stats
table I'm not sure where I would put that information. And... I want each
user to be able to view the stats as a record set at any time with out having
to create a report. Given this do you think it's ok to keep the stats table?

Thanks again!!!!

Tim

Tim,
So, the way I am picturing it, you will be processing the gains from a form...
an Update Stats button could invoke some code that would query the Stats
table, check to see that the Gains date being processed has not already been
updated to the Stats table and if not, would append a single record to the
Stats table with a new total, based on the Gains record

Start with

SELECT TOP 1 Stats.apel, Stats.appcl, Stats.abpcl, Stats.date
FROM Stats
ORDER BY Stats.date DESC;

to get the most recent record in the Stats table.

if Stats.date > Gains.date then
issue message
else
calculate new totals for APEL, APPCL ABPCL
append new record to Stats with new date and totals

There is not much protection (or accomdation) here for Gains being processed
out of chronological order.

Overall, I would have to recommend removing Stats as a table, since it
represents a dynmaic summary of Gains transations. It would be better to
create it on demand from the Gains table.

Bruce, yes, that is correct. I am only processing one gains record at a time.

Thanks again for your help!!!

-Tim

Tim,
NOW I understand. If you are processing one Gains record at a time this
would be easy. Figuring out how to APPEND multiple records with a running
total will take a little turkey and sweet potatoes. I'll check in after the
Holiday.

:

Bruce, first, thanks a ton for your help!!!

Well... the query runs with no errors but no records update. I think maybe
I'm making it too complicated: Let me try once more...

Basically all I'm trying to do is take the current values in the gains
table, add them to the current value in the stats table and write the new
value as a new record in the stats table. I think my statement about it being
"date driven" was inaccurate. The date doesn't really matter. Just as long as
two entries are not on the same date.So... something like this:

Stats Table

ID Date APEL APPCL ABPCL
1 06/01/04 6.0 6.25 5.75
2 06/15/04 6.25 6.375 6.25 <---- Each
new record
3 06/30/04 6.375 6.625 6.625 updates with
the values below

ID Date APEL APPCL ABPCL
1 06/15/04 .25 .125 .5
2 06/30/04 .125 .1875 .125


Thanks for your patience!!



:

You'll need to add a Boolean field ("updated" in this example) to the Stats
table. This query should do it

UPDATE Stats RIGHT JOIN gains ON Stats.date = gains.date
SET Stats.apel = Stats!apel+gains!apel,
Stats.appcl = Stats!appcl+gains!appcl,
Stats.abpcl = Stats!abpcl+gains!abpcl,
Stats.updated = -1
WHERE (((Stats.updated)=0));

I am assuming that there is ONLY ONE record for any date in both tables. If
this is not true then adjustments must be made.


:

Bruce, thanks for your help!
The update query works (kind of.)

First, both tables have exactly the same fields so no problem finding a
common field.

The first table is called Stats. Stats are date driven (e.g.)

ID Date APEL APPCL ABPCL
1 06/01/04 6.0 6.25 5.75
2 06/15/04 updated updated updated
3 06/30/04 updated updated updated

The second table is Gains: same fields

ID Date APEL APPCL ABPCL
1 06/15/04 .25 .125 .5
2 06/30/04 .125 .1875 .125

Both tables are linked to individual forms. The Gains table has a button
"Update Stats" When I click on update stats button the current value of gains
should be added to the stats total.

Once a record has been added to the total I don't want the user to be able
to go back and accidentally add that record again. So once record ID 1 is
added updated is it possible to prevent this from reoccuring. Maybe some kind
of warning message?

I'm not sure my update query is even designed the best way, here it is:

Field: APEL
Table: Stats
Update To: [Stats]![APEL]+[Gains]![APEL]

Repeats of course with each field...

Is this correct and any ideas on how to limit the update to 1 time?

Sorry so wordy, thanks a bunch for your help!!!!

-Tim

:

Your tables must have some field in common that will allow them to be joined.
If you can do that, then you will create an update query for Table2 that
will update the fields in Table2 with the sums.

:

I have two tables:

Table 1
T1Value1, T1Value2, T1Value3, T1Value4, etc

Table 2,
T2Value1 T2Value2, T2Value2, T2Value4, etc

Both tables are tied to forms where these values are entered.

I want to put a command buttom on Form1 that when clicked on will add the
current values of Table1 to the values of Table2. (e.g. T2Value1 now becomes
T2Value1 + T1Value1.)

Thanks for you help in advance.

 

[Guest]

Tim,
This will be my last entry on this topic. I'm thinking that the single
record append will also be done with code as part of the form.
Look at the Recordset.AddNew method. Follow that with the setting of the
field values and finish whith Recordset.Update.

Bruce, you are right on the money! That's exactly how it should work. I
think I can figure out the individual steps from your outline with the
exception of appending a single record. I have searched unsucessfully. How
would I go about doing that? Also, I would like to follow your advice on
removing the stats table but there is a starting stats record (not a gain but
a beginning record) that must be entered by each user. If I remove the stats
table I'm not sure where I would put that information. And... I want each
user to be able to view the stats as a record set at any time with out having
to create a report. Given this do you think it's ok to keep the stats table?

Thanks again!!!!

Tim

Tim,
So, the way I am picturing it, you will be processing the gains from a form...
an Update Stats button could invoke some code that would query the Stats
table, check to see that the Gains date being processed has not already been
updated to the Stats table and if not, would append a single record to the
Stats table with a new total, based on the Gains record

Start with

SELECT TOP 1 Stats.apel, Stats.appcl, Stats.abpcl, Stats.date
FROM Stats
ORDER BY Stats.date DESC;

to get the most recent record in the Stats table.

if Stats.date > Gains.date then
issue message
else
calculate new totals for APEL, APPCL ABPCL
append new record to Stats with new date and totals

There is not much protection (or accomdation) here for Gains being processed
out of chronological order.

Overall, I would have to recommend removing Stats as a table, since it
represents a dynmaic summary of Gains transations. It would be better to
create it on demand from the Gains table.

Bruce, yes, that is correct. I am only processing one gains record at a time.

Thanks again for your help!!!

-Tim


:

Tim,
NOW I understand. If you are processing one Gains record at a time this
would be easy. Figuring out how to APPEND multiple records with a running
total will take a little turkey and sweet potatoes. I'll check in after the
Holiday.

:

Bruce, first, thanks a ton for your help!!!

Well... the query runs with no errors but no records update. I think maybe
I'm making it too complicated: Let me try once more...

Basically all I'm trying to do is take the current values in the gains
table, add them to the current value in the stats table and write the new
value as a new record in the stats table. I think my statement about it being
"date driven" was inaccurate. The date doesn't really matter. Just as long as
two entries are not on the same date.So... something like this:

Stats Table

ID Date APEL APPCL ABPCL
1 06/01/04 6.0 6.25 5.75
2 06/15/04 6.25 6.375 6.25 <---- Each
new record
3 06/30/04 6.375 6.625 6.625 updates with
the values below

ID Date APEL APPCL ABPCL
1 06/15/04 .25 .125 .5
2 06/30/04 .125 .1875 .125


Thanks for your patience!!



:

You'll need to add a Boolean field ("updated" in this example) to the Stats
table. This query should do it

UPDATE Stats RIGHT JOIN gains ON Stats.date = gains.date
SET Stats.apel = Stats!apel+gains!apel,
Stats.appcl = Stats!appcl+gains!appcl,
Stats.abpcl = Stats!abpcl+gains!abpcl,
Stats.updated = -1
WHERE (((Stats.updated)=0));

I am assuming that there is ONLY ONE record for any date in both tables. If
this is not true then adjustments must be made.


:

Bruce, thanks for your help!
The update query works (kind of.)

First, both tables have exactly the same fields so no problem finding a
common field.

The first table is called Stats. Stats are date driven (e.g.)

ID Date APEL APPCL ABPCL
1 06/01/04 6.0 6.25 5.75
2 06/15/04 updated updated updated
3 06/30/04 updated updated updated

The second table is Gains: same fields

ID Date APEL APPCL ABPCL
1 06/15/04 .25 .125 .5
2 06/30/04 .125 .1875 .125

Both tables are linked to individual forms. The Gains table has a button
"Update Stats" When I click on update stats button the current value of gains
should be added to the stats total.

Once a record has been added to the total I don't want the user to be able
to go back and accidentally add that record again. So once record ID 1 is
added updated is it possible to prevent this from reoccuring. Maybe some kind
of warning message?

I'm not sure my update query is even designed the best way, here it is:

Field: APEL
Table: Stats
Update To: [Stats]![APEL]+[Gains]![APEL]

Repeats of course with each field...

Is this correct and any ideas on how to limit the update to 1 time?

Sorry so wordy, thanks a bunch for your help!!!!

-Tim

:

Your tables must have some field in common that will allow them to be joined.
If you can do that, then you will create an update query for Table2 that
will update the fields in Table2 with the sums.

:

I have two tables:

Table 1
T1Value1, T1Value2, T1Value3, T1Value4, etc

Table 2,
T2Value1 T2Value2, T2Value2, T2Value4, etc

Both tables are tied to forms where these values are entered.

I want to put a command buttom on Form1 that when clicked on will add the
current values of Table1 to the values of Table2. (e.g. T2Value1 now becomes
T2Value1 + T1Value1.)

Thanks for you help in advance.

 

[Guest]

Thanks for your help!!!

Tim,
This will be my last entry on this topic. I'm thinking that the single
record append will also be done with code as part of the form.
Look at the Recordset.AddNew method. Follow that with the setting of the
field values and finish whith Recordset.Update.

Bruce, you are right on the money! That's exactly how it should work. I
think I can figure out the individual steps from your outline with the
exception of appending a single record. I have searched unsucessfully. How
would I go about doing that? Also, I would like to follow your advice on
removing the stats table but there is a starting stats record (not a gain but
a beginning record) that must be entered by each user. If I remove the stats
table I'm not sure where I would put that information. And... I want each
user to be able to view the stats as a record set at any time with out having
to create a report. Given this do you think it's ok to keep the stats table?

Thanks again!!!!

Tim

Tim,
So, the way I am picturing it, you will be processing the gains from a form...
an Update Stats button could invoke some code that would query the Stats
table, check to see that the Gains date being processed has not already been
updated to the Stats table and if not, would append a single record to the
Stats table with a new total, based on the Gains record

Start with

SELECT TOP 1 Stats.apel, Stats.appcl, Stats.abpcl, Stats.date
FROM Stats
ORDER BY Stats.date DESC;

to get the most recent record in the Stats table.

if Stats.date > Gains.date then
issue message
else
calculate new totals for APEL, APPCL ABPCL
append new record to Stats with new date and totals

There is not much protection (or accomdation) here for Gains being processed
out of chronological order.

Overall, I would have to recommend removing Stats as a table, since it
represents a dynmaic summary of Gains transations. It would be better to
create it on demand from the Gains table.
:

Bruce, yes, that is correct. I am only processing one gains record at a time.

Thanks again for your help!!!

-Tim


:

Tim,
NOW I understand. If you are processing one Gains record at a time this
would be easy. Figuring out how to APPEND multiple records with a running
total will take a little turkey and sweet potatoes. I'll check in after the
Holiday.

:

Bruce, first, thanks a ton for your help!!!

Well... the query runs with no errors but no records update. I think maybe
I'm making it too complicated: Let me try once more...

Basically all I'm trying to do is take the current values in the gains
table, add them to the current value in the stats table and write the new
value as a new record in the stats table. I think my statement about it being
"date driven" was inaccurate. The date doesn't really matter. Just as long as
two entries are not on the same date.So... something like this:

Stats Table

ID Date APEL APPCL ABPCL
1 06/01/04 6.0 6.25 5.75
2 06/15/04 6.25 6.375 6.25 <---- Each
new record
3 06/30/04 6.375 6.625 6.625 updates with
the values below

ID Date APEL APPCL ABPCL
1 06/15/04 .25 .125 .5
2 06/30/04 .125 .1875 .125


Thanks for your patience!!



:

You'll need to add a Boolean field ("updated" in this example) to the Stats
table. This query should do it

UPDATE Stats RIGHT JOIN gains ON Stats.date = gains.date
SET Stats.apel = Stats!apel+gains!apel,
Stats.appcl = Stats!appcl+gains!appcl,
Stats.abpcl = Stats!abpcl+gains!abpcl,
Stats.updated = -1
WHERE (((Stats.updated)=0));

I am assuming that there is ONLY ONE record for any date in both tables. If
this is not true then adjustments must be made.


:

Bruce, thanks for your help!
The update query works (kind of.)

First, both tables have exactly the same fields so no problem finding a
common field.

The first table is called Stats. Stats are date driven (e.g.)

ID Date APEL APPCL ABPCL
1 06/01/04 6.0 6.25 5.75
2 06/15/04 updated updated updated
3 06/30/04 updated updated updated

The second table is Gains: same fields

ID Date APEL APPCL ABPCL
1 06/15/04 .25 .125 .5
2 06/30/04 .125 .1875 .125

Both tables are linked to individual forms. The Gains table has a button
"Update Stats" When I click on update stats button the current value of gains
should be added to the stats total.

Once a record has been added to the total I don't want the user to be able
to go back and accidentally add that record again. So once record ID 1 is
added updated is it possible to prevent this from reoccuring. Maybe some kind
of warning message?

I'm not sure my update query is even designed the best way, here it is:

Field: APEL
Table: Stats
Update To: [Stats]![APEL]+[Gains]![APEL]

Repeats of course with each field...

Is this correct and any ideas on how to limit the update to 1 time?

Sorry so wordy, thanks a bunch for your help!!!!

-Tim

:

Your tables must have some field in common that will allow them to be joined.
If you can do that, then you will create an update query for Table2 that
will update the fields in Table2 with the sums.

:

I have two tables:

Table 1
T1Value1, T1Value2, T1Value3, T1Value4, etc

Table 2,
T2Value1 T2Value2, T2Value2, T2Value4, etc

Both tables are tied to forms where these values are entered.

I want to put a command buttom on Form1 that when clicked on will add the
current values of Table1 to the values of Table2. (e.g. T2Value1 now becomes
T2Value1 + T1Value1.)

Thanks for you help in advance.