add column

G

Guest

hi,

I have the following query:
TRANSFORM Sum(Calls_by_activity_date.[SumOfCOUNT( * )]) AS [SumOfSumOfCOUNT(
* )]
SELECT Calls_by_activity_date.ResolvedStatus,
Calls_by_activity_date.ResolvedDt
FROM Calls_by_activity_date
WHERE (((Calls_by_activity_date.ResolvedStatus) Like "curr_pd*") AND
((Calls_by_activity_date.ResolvedDt) Between #6/1/2006# And #6/30/2006#) AND
((Calls_by_activity_date.activity_date) Between #6/1/2006# And #6/30/2006#))
GROUP BY Calls_by_activity_date.ResolvedStatus,
Calls_by_activity_date.ResolvedDt
PIVOT Calls_by_activity_date.activity_date;


I would like to add the following for each resolveddt, in parentheses beside
each resolveddt. but when I do, I get thousands of rows more returned in the
query.

tbl_CACS_activity_by_resolvedstatusandcontactcode.[CountOfLoan Acct #]

HOW?

Thanks in advance,
geebee
 
G

Guest

Try this --
TRANSFORM Sum(Calls_by_activity_date.[SumOfCOUNT( * )]) AS [SumOfSumOfCOUNT(
* )]
SELECT Calls_by_activity_date.ResolvedStatus,
[Calls_by_activity_date].[ResolvedDt] & " (" &
[tbl_CACS_activity_by_resolvedstatusandcontactcode].[CountOfLoan Acct] & ")"
AS [Resolved Date]
FROM Calls_by_activity_date LEFT JOIN
tbl_CACS_activity_by_resolvedstatusandcontactcode ON
Calls_by_activity_date.ResolvedDt =
tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedDt
WHERE (((Calls_by_activity_date.ResolvedStatus) Like "curr_pd*") AND
((Calls_by_activity_date.ResolvedDt) Between #6/1/2006# And #6/30/2006#) AND
((Calls_by_activity_date.activity_date) Between #6/1/2006# And #6/30/2006#))
GROUP BY Calls_by_activity_date.ResolvedStatus,
[Calls_by_activity_date].[ResolvedDt] & " (" &
[tbl_CACS_activity_by_resolvedstatusandcontactcode].[CountOfLoan Acct] & ")"
PIVOT Calls_by_activity_date.activity_date;
 
G

Guest

Thanks...
I now have:
TRANSFORM Sum([1tbl_sumcalldata].[SumOfCOUNT( * )]) AS [SumOfSumOfCOUNT( * )]
SELECT [1tbl_sumcalldata].ResolvedStatus, [1tbl_sumcalldata].[ResolvedDt] &
" (" & [tbl_CACS_activity_by_resolvedstatusandcontactcode].[CountOfLoan Acct
#] & ")" AS Dater
FROM 1tbl_sumcalldata LEFT JOIN
tbl_CACS_activity_by_resolvedstatusandcontactcode ON
[1tbl_sumcalldata].ResolvedDt =
tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedDt
WHERE ((([1tbl_sumcalldata].ResolvedStatus) Like "curr_pd*") AND
(([1tbl_sumcalldata].ResolvedDt) Between #6/1/2006# And #6/30/2006#))
GROUP BY [1tbl_sumcalldata].ResolvedStatus, [1tbl_sumcalldata].[ResolvedDt]
& " (" & [tbl_CACS_activity_by_resolvedstatusandcontactcode].[CountOfLoan
Acct #] & ")"
PIVOT [1tbl_sumcalldata].activity_date;

BUT here is some sample data:
ResolvedStatus Dater 6/1/2006 6/2/2006 6/3/2006
CURR_PD 6/13/2006 (1) 642865 517370 624815
CURR_PD 6/13/2006 (10) 40602 32676 39462
CURR_PD 6/13/2006 (11) 13534 10892 13154
CURR_PD 6/13/2006 (111) 6767 5446 6577
CURR_PD 6/13/2006 (12) 6767 5446 6577
CURR_PD 6/13/2006 (128) 13534 10892 13154
CURR_PD 6/13/2006 (129) 6767 5446 6577

The only problem is that I only want the number in () to appear for the
whole date, but in this case it is grouped by the different numbers in ().
For example, for 6/13, there is a total of 9999. So I want that 9999 to
appear in () for all 6/13 rows.

Is this possible?

Thanks in advance,
geebee


KARL DEWEY said:
Try this --
TRANSFORM Sum(Calls_by_activity_date.[SumOfCOUNT( * )]) AS [SumOfSumOfCOUNT(
* )]
SELECT Calls_by_activity_date.ResolvedStatus,
[Calls_by_activity_date].[ResolvedDt] & " (" &
[tbl_CACS_activity_by_resolvedstatusandcontactcode].[CountOfLoan Acct] & ")"
AS [Resolved Date]
FROM Calls_by_activity_date LEFT JOIN
tbl_CACS_activity_by_resolvedstatusandcontactcode ON
Calls_by_activity_date.ResolvedDt =
tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedDt
WHERE (((Calls_by_activity_date.ResolvedStatus) Like "curr_pd*") AND
((Calls_by_activity_date.ResolvedDt) Between #6/1/2006# And #6/30/2006#) AND
((Calls_by_activity_date.activity_date) Between #6/1/2006# And #6/30/2006#))
GROUP BY Calls_by_activity_date.ResolvedStatus,
[Calls_by_activity_date].[ResolvedDt] & " (" &
[tbl_CACS_activity_by_resolvedstatusandcontactcode].[CountOfLoan Acct] & ")"
PIVOT Calls_by_activity_date.activity_date;


geebee said:
hi,

I have the following query:
TRANSFORM Sum(Calls_by_activity_date.[SumOfCOUNT( * )]) AS [SumOfSumOfCOUNT(
* )]
SELECT Calls_by_activity_date.ResolvedStatus,
Calls_by_activity_date.ResolvedDt
FROM Calls_by_activity_date
WHERE (((Calls_by_activity_date.ResolvedStatus) Like "curr_pd*") AND
((Calls_by_activity_date.ResolvedDt) Between #6/1/2006# And #6/30/2006#) AND
((Calls_by_activity_date.activity_date) Between #6/1/2006# And #6/30/2006#))
GROUP BY Calls_by_activity_date.ResolvedStatus,
Calls_by_activity_date.ResolvedDt
PIVOT Calls_by_activity_date.activity_date;


I would like to add the following for each resolveddt, in parentheses beside
each resolveddt. but when I do, I get thousands of rows more returned in the
query.

tbl_CACS_activity_by_resolvedstatusandcontactcode.[CountOfLoan Acct #]

HOW?

Thanks in advance,
geebee
Click to expand...
Click to expand...
 
G

Guest

Create another query - ResolvedDt_Count --
SELECT tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedDt,
Sum(tbl_CACS_activity_by_resolvedstatusandcontactcode.[CountOfLoan Acct #])
AS [SumOfCountOfLoan Acct #]
FROM tbl_CACS_activity_by_resolvedstatusandcontactcode
GROUP BY tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedDt;

Then this --
TRANSFORM Sum(Calls_by_activity_date.[SumOfCOUNT( * )]) AS [SumOfSumOfCOUNT(
* )]
SELECT Calls_by_activity_date.ResolvedStatus,
[Calls_by_activity_date].[ResolvedDt] & " (" & [SumOfCountOfLoan Acct #] &
")" AS [Resolved Date]
FROM Calls_by_activity_date INNER JOIN ResolvedDt_Count ON
Calls_by_activity_date.ResolvedDt = ResolvedDt_Count.ResolvedDt
WHERE (((Calls_by_activity_date.ResolvedStatus) Like "curr_pd*") AND
((Calls_by_activity_date.ResolvedDt) Between #6/1/2006# And #6/30/2006#) AND
((Calls_by_activity_date.activity_date) Between #6/1/2006# And #6/30/2006#))
GROUP BY Calls_by_activity_date.ResolvedStatus,
[Calls_by_activity_date].[ResolvedDt] & " (" & [SumOfCountOfLoan Acct #] & ")"
PIVOT Calls_by_activity_date.activity_date;


geebee said:
Thanks...
I now have:
TRANSFORM Sum([1tbl_sumcalldata].[SumOfCOUNT( * )]) AS [SumOfSumOfCOUNT( * )]
SELECT [1tbl_sumcalldata].ResolvedStatus, [1tbl_sumcalldata].[ResolvedDt] &
" (" & [tbl_CACS_activity_by_resolvedstatusandcontactcode].[CountOfLoan Acct
#] & ")" AS Dater
FROM 1tbl_sumcalldata LEFT JOIN
tbl_CACS_activity_by_resolvedstatusandcontactcode ON
[1tbl_sumcalldata].ResolvedDt =
tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedDt
WHERE ((([1tbl_sumcalldata].ResolvedStatus) Like "curr_pd*") AND
(([1tbl_sumcalldata].ResolvedDt) Between #6/1/2006# And #6/30/2006#))
GROUP BY [1tbl_sumcalldata].ResolvedStatus, [1tbl_sumcalldata].[ResolvedDt]
& " (" & [tbl_CACS_activity_by_resolvedstatusandcontactcode].[CountOfLoan
Acct #] & ")"
PIVOT [1tbl_sumcalldata].activity_date;

BUT here is some sample data:
ResolvedStatus Dater 6/1/2006 6/2/2006 6/3/2006
CURR_PD 6/13/2006 (1) 642865 517370 624815
CURR_PD 6/13/2006 (10) 40602 32676 39462
CURR_PD 6/13/2006 (11) 13534 10892 13154
CURR_PD 6/13/2006 (111) 6767 5446 6577
CURR_PD 6/13/2006 (12) 6767 5446 6577
CURR_PD 6/13/2006 (128) 13534 10892 13154
CURR_PD 6/13/2006 (129) 6767 5446 6577

The only problem is that I only want the number in () to appear for the
whole date, but in this case it is grouped by the different numbers in ().
For example, for 6/13, there is a total of 9999. So I want that 9999 to
appear in () for all 6/13 rows.

Is this possible?

Thanks in advance,
geebee


KARL DEWEY said:
Try this --
TRANSFORM Sum(Calls_by_activity_date.[SumOfCOUNT( * )]) AS [SumOfSumOfCOUNT(
* )]
SELECT Calls_by_activity_date.ResolvedStatus,
[Calls_by_activity_date].[ResolvedDt] & " (" &
[tbl_CACS_activity_by_resolvedstatusandcontactcode].[CountOfLoan Acct] & ")"
AS [Resolved Date]
FROM Calls_by_activity_date LEFT JOIN
tbl_CACS_activity_by_resolvedstatusandcontactcode ON
Calls_by_activity_date.ResolvedDt =
tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedDt
WHERE (((Calls_by_activity_date.ResolvedStatus) Like "curr_pd*") AND
((Calls_by_activity_date.ResolvedDt) Between #6/1/2006# And #6/30/2006#) AND
((Calls_by_activity_date.activity_date) Between #6/1/2006# And #6/30/2006#))
GROUP BY Calls_by_activity_date.ResolvedStatus,
[Calls_by_activity_date].[ResolvedDt] & " (" &
[tbl_CACS_activity_by_resolvedstatusandcontactcode].[CountOfLoan Acct] & ")"
PIVOT Calls_by_activity_date.activity_date;


geebee said:
hi,

I have the following query:
TRANSFORM Sum(Calls_by_activity_date.[SumOfCOUNT( * )]) AS [SumOfSumOfCOUNT(
* )]
SELECT Calls_by_activity_date.ResolvedStatus,
Calls_by_activity_date.ResolvedDt
FROM Calls_by_activity_date
WHERE (((Calls_by_activity_date.ResolvedStatus) Like "curr_pd*") AND
((Calls_by_activity_date.ResolvedDt) Between #6/1/2006# And #6/30/2006#) AND
((Calls_by_activity_date.activity_date) Between #6/1/2006# And #6/30/2006#))
GROUP BY Calls_by_activity_date.ResolvedStatus,
Calls_by_activity_date.ResolvedDt
PIVOT Calls_by_activity_date.activity_date;


I would like to add the following for each resolveddt, in parentheses beside
each resolveddt. but when I do, I get thousands of rows more returned in the
query.

tbl_CACS_activity_by_resolvedstatusandcontactcode.[CountOfLoan Acct #]

HOW?

Thanks in advance,
geebee
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

UNION QUERY 1
format number 1
query prompts 2
change underlying query 2
day before 1
query from form 8
data type mismatch in criteria expression 2
query addition 1

Top