UPDATE QUERY

[vladk]

Can somebody help me with update query?

I have two tables: T and S. Both tables have fields F1,F2,F3,F4. These fields identify common records on the tables. Table S has [Calendar Year] and [Calendar Month]fields. Also, for each (F1,F2,F3,F4) record, table S has some numeric parameter in field F5. This parameter is available for 12 Calendar Months within Calendar Year. As such, table S can have many (F1,F2,F3,F4) records.

Table S:

F1,F2,F3,F4, CY,CM,F5
______________________

A,B,C,D,2009,1,0.12345
A,B,C,D,2009,2,0.27138
.....
A,B,C,D,2009,11,0.7438
A,B,C,D,2009,12,0.3456
....
....
A,B,C,D,2010,1,0.62315
A,B,C,D,2010,2,0.12905
.....
A,B,C,D,2010,11,0.3561
A,B,C,D,2010,12,0.7343


Also, Table T has five quater-end fields, for example:

Table T

F1,F2,F3,F4, QD1,QD2,QD3,QD4,QD5
______________________

A,B,C,D,9/30/2009,12/31/2009,3/31/2010,6/30/2010,9/30/2010


I need:

1. For each record on Table T, find corresponding records on Table S (based on first four common fields).
2. Extract year from T.QD1 (2009 in the example).
3. For this year, I need to calculate SUM(S.F5) for calendar months 7,8,9 and calendar year = 2009 on Table S, and save it in, say, field, Calc1 on Table 5
4. Repeat it for all QD# fields. For example, for QD2, I need to calculate SUM(S.F5) for calendar months 10,11,12 and calendar year = 2009 on Table S, and save it in field Calc2 on Table 5.

I know, it sounds like to much to ask, but I will greatly appretiate any help!

Thank you!

vladk

 

[dhookom]

Without attempting to dig into this too far, it looks like you want to take an aggregated value from one table and use it to update a field in another table. If this is the case it might be easiest to create a totals or crosstab query as the source to a make table or append query. This would place your summed values into a separate table.

You could then make sure the appropriate fields create a primary key in the "made" table so you can join it to your target table in an update query.

Duane
Hook'D on Access
MS Access MVP

 

[vladk]

dhookom, thank you for response!

Fortunately, client does not want it: it would be too much time for query to run!

Thank you again!

vladk

 

[dhookom]

What takes to much time? Did you try it? Do you have a better alternative or are you still looking? Why doesn't your client want this?

Duane
Hook'D on Access
MS Access MVP

 

[vladk]

Hi, dhookom!

Thank you for your interest!

It would take too much time to run the query (they have to run ten of them). They tried not even a query, a subquery to build the table for major one (Table S) and decided not too continue; it was not my call. I conserved the project and suggested to return to it sometime later.

Thank you again! If they decide to resume it, I will certainly try to contact you...

vladk