Update query using a 'group by' tbl

J

Jennifer

I am trying to run an update query... I have two tables- CB_Final and
CB_Groupby$
CB_Final contains-reason codes, descriptions, & periods 1-12
CB_Groupby$ contains- Reason codes, descriptions, SumOfInvoiceAmts and the
Period. the table is grouped by reason code and period and sums the invoice
amts.
Some reason codes have no $figures for the period, which is fine.
I am running an update query linking my reason codes from the two tables,
and linking the #periods 1-12 from my CB_Final to the 'PD' field in my
CB_Groupby$.
This should work, but it is not.
Please Help,
Jennifer
 
J

Jerry Whittle

Hi Jennifer,

I was about to get on my soapbox and spout off about you even having such a
table as CB_Groupby$. But then I noticed the SumOfInvoiceAmts field.
Sometimes things like Invoices are OK to store as they are data at a point in
time and not very easy to recompute.

First off are you sure that you need something like the CB_Groupby$ table?
Couldn't you retrieve this data with a query as needed?

Secondly are you sre that CB_Groupby$ is a table? I hate to ask but the
SumOfInvoiceAmts field is names exactly like something from a Totals query.

Lastly post the SQL statement so that we can see what you are doing. Open
the query in design view. Next go to View, SQL View and copy and past it
here. Information on primary keys and relationships would be a nice touch too.
 
J

Jennifer

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
 
J

Jerry Whittle

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.


Jennifer said:
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
 
J

Jennifer

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.


Jennifer said:
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
 
J

Jerry Whittle

What you really, really need is a crosstab query! It will display the data
like you want to see in CB_Final_tester. Well the Desc field could be a
problem unless it is linked to the Reason Code. The basic SQL statement
would look something like:

TRANSFORM Sum([CB_Groupby$].[SumOfInvAmt]) AS SumOfSumOfInvAmt
SELECT [CB_Groupby$].[Reason Code]
FROM [CB_Groupby$]
GROUP BY [CB_Groupby$].[Reason Code]
PIVOT [CB_Groupby$].[PD];
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Jennifer said:
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.


Jennifer said:
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
 
J

Jennifer

I already have a crosstab query,which creates problems because it will not
display all the reason codes, just the ones used. And I cannot use the
crosstab query to update the CB_Final because it does not have the "PD" as a
field, it's the actual 01,02,03 etc... Which if the period is already ran,
than I can link the 01 from one table to the 01 of another table; but if the
period isn't ran this doesn't work. Can I do update query?

Jerry Whittle said:
What you really, really need is a crosstab query! It will display the data
like you want to see in CB_Final_tester. Well the Desc field could be a
problem unless it is linked to the Reason Code. The basic SQL statement
would look something like:

TRANSFORM Sum([CB_Groupby$].[SumOfInvAmt]) AS SumOfSumOfInvAmt
SELECT [CB_Groupby$].[Reason Code]
FROM [CB_Groupby$]
GROUP BY [CB_Groupby$].[Reason Code]
PIVOT [CB_Groupby$].[PD];
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Jennifer said:
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
 
L

Lord Kelvan

then in your crosstab query use

CB_FE3348_Final_tester_tbl LEFTR JOIN [CB_FE3348_Groupby$_tbl]

instend of a INNER JOIN then it should give you all records not just
the used ones
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top