Yea Jerry tell me about it! It's a beast, at least for me.. But I will try to
show you the best I can.
Lets start with the data in CB_Groupby$:
Reason Code SumOfInvAmt PD
12 50000 01
52 5413 01
75 45411 01
12 65871 02
68 87412 02
75 9999 02
This is the data I want updated to my CB_Final_tester tbl. Below is what I
would like the end result to be--- right now the entire table except for the
reason codes(which I manually entered) is blank.
Now CB_Final_tester tbl:
Reason Code Desc. 01 02 03 04
05 etc....
12 blabla 50000 65871
52 blabla 5413 0
68 blabla 0 87412
75 blabla 45411 9999
Now you can see why I have the 01-12 linked to the PD. It should be able to
look under the PD column and find the 01 to update that data into the
CB_Final_tester_tbl. But it's not working. If looking at the table in design
view- all my data types match correctly. I don't know what to do at this
point, any help you can provide is appreciated.
Thanks
Jerry Whittle said:
Oh my! It looks like the join must match 12 fields in on table to just one
field in the other table. Also you are updating all 12 of those fields with
data from just one field in the other table. Do you really need those 12
fields then as they all will have the same data?
Could you provide sample data of the tables and what you expect to outcome
to look like?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
I pull all my data in which includes CB & PB- I need to separate btwn those
two plus two different acct# as well. So yes, I like to keep it all separate
and created the groupby table. I made my groupby$ table through a make-table
query, and changed it to an append query before I ran the database again.
There are no primary keys- we usually don't name keys. My relationships are
Reason code to reason code, description to description, and 01-12 to period.
Field PD is a column in my groupby table that displays the period 01-12
based on Inv.$amts to the reason codes.
FYI- what you are about to see is my SQL statement: table
CB_FE3348_Groupby$_tbl is the CB_Groupby$ and CB_FE3348_Final_tester_tbl is
the CB_Final I referenced.
UPDATE CB_FE3348_Final_tester_tbl INNER JOIN [CB_FE3348_Groupby$_tbl] ON
(CB_FE3348_Final_tester_tbl.[Reason Code] =
[CB_FE3348_Groupby$_tbl].APCRE_RSN_CDE) AND (CB_FE3348_Final_tester_tbl.[12]
= [CB_FE3348_Groupby$_tbl].PD) AND (CB_FE3348_Final_tester_tbl.[11] =
[CB_FE3348_Groupby$_tbl].PD) AND (CB_FE3348_Final_tester_tbl.[10] =
[CB_FE3348_Groupby$_tbl].PD) AND (CB_FE3348_Final_tester_tbl.[09] =
[CB_FE3348_Groupby$_tbl].PD) AND (CB_FE3348_Final_tester_tbl.[08] =
[CB_FE3348_Groupby$_tbl].PD) AND (CB_FE3348_Final_tester_tbl.[07] =
[CB_FE3348_Groupby$_tbl].PD) AND (CB_FE3348_Final_tester_tbl.[06] =
[CB_FE3348_Groupby$_tbl].PD) AND (CB_FE3348_Final_tester_tbl.[05] =
[CB_FE3348_Groupby$_tbl].PD) AND (CB_FE3348_Final_tester_tbl.[04] =
[CB_FE3348_Groupby$_tbl].PD) AND (CB_FE3348_Final_tester_tbl.[03] =
[CB_FE3348_Groupby$_tbl].PD) AND (CB_FE3348_Final_tester_tbl.[02] =
[CB_FE3348_Groupby$_tbl].PD) AND (CB_FE3348_Final_tester_tbl.[01] =
[CB_FE3348_Groupby$_tbl].PD) SET CB_FE3348_Final_tester_tbl.[01] =
[CB_FE3348_Groupby$_tbl]!SumOfAPGLD_INVC_AMT, CB_FE3348_Final_tester_tbl.[02]
= [CB_FE3348_Groupby$_tbl]!SumOfAPGLD_INVC_AMT,
CB_FE3348_Final_tester_tbl.[03] =
[CB_FE3348_Groupby$_tbl]!SumOfAPGLD_INVC_AMT, CB_FE3348_Final_tester_tbl.[04]
= [CB_FE3348_Groupby$_tbl]!SumOfAPGLD_INVC_AMT,
CB_FE3348_Final_tester_tbl.[05] =
[CB_FE3348_Groupby$_tbl]!SumOfAPGLD_INVC_AMT, CB_FE3348_Final_tester_tbl.[06]
= [CB_FE3348_Groupby$_tbl]!SumOfAPGLD_INVC_AMT,
CB_FE3348_Final_tester_tbl.[07] =
[CB_FE3348_Groupby$_tbl]!SumOfAPGLD_INVC_AMT, CB_FE3348_Final_tester_tbl.[08]
= [CB_FE3348_Groupby$_tbl]!SumOfAPGLD_INVC_AMT,
CB_FE3348_Final_tester_tbl.[09] =
[CB_FE3348_Groupby$_tbl]!SumOfAPGLD_INVC_AMT, CB_FE3348_Final_tester_tbl.[10]
= [CB_FE3348_Groupby$_tbl]!SumOfAPGLD_INVC_AMT,
CB_FE3348_Final_tester_tbl.[11] =
[CB_FE3348_Groupby$_tbl]!SumOfAPGLD_INVC_AMT, CB_FE3348_Final_tester_tbl.[12]
= [CB_FE3348_Groupby$_tbl]!SumOfAPGLD_INVC_AMT;
Hopefully you can help!!! Thanks