By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,846 Members | 2,146 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,846 IT Pros & Developers. It's quick & easy.

Multiple Rows Into One - Revisted

P: n/a
I was searching the threads about combing multiple rows into one and
found some good stuff, but need your help to expand on it.

I have a table as follows

Policy Number DateOfTrans TransType BenefitAmt
1234 12/1/2006 Received $12,000
1234 12/3/2006 Approved $5,000
1234 12/5/2006 Paid $5,000

I want to get this on one line to look like:

PolicyNumber DateReceived DateApproved DatePaid BenefitAmount

The benefit amount shoudl be the Approve's benefit amount. If approved
is null, then use the received amount.

Possible?

Thanks,
Brian

Dec 21 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Use the Nz function.

If that does not completely meet your needs (it should), consider the IIF
function, but if and only if you do not have a large number of records to
return.

--
Darryl Kerkeslager

"BerkshireGuy" <be*************************@yahoo.comwrote
The benefit amount shoudl be the Approve's benefit amount. If approved
is null, then use the received amount.

Dec 21 '06 #2

P: n/a
I tried this, but its only returning one date. Each status has a date,
so I'd like to return the status of each date.

SELECT PolicyNumber, RDLastTransaction,
MAX(IIF(StatusDisplay="Received",StatusDisplay,Nul l)) AS ['Received'],
MAX(IIF(StatusDisplay="Paid",StatusDisplay,Null)) AS ['Paid'],
MAX(IIF(StatusDisplay="Not Taken",StatusDisplay,Null)) AS ['Not Taken']
FROM tblMyInfo
GROUP BY PolicyNumber, RDLastTransaction;

Darryl Kerkeslager wrote:
Use the Nz function.

If that does not completely meet your needs (it should), consider the IIF
function, but if and only if you do not have a large number of records to
return.

--
Darryl Kerkeslager

"BerkshireGuy" <be*************************@yahoo.comwrote
The benefit amount shoudl be the Approve's benefit amount. If approved
is null, then use the received amount.
Dec 21 '06 #3

P: n/a
3 queries, one for each transtype, including the benefitAmt
a 4th query, combining the first 3 queries,
and with a field like nz(qryApproved.benefitAmt,
qryReceived.benefitAmt)
BerkshireGuy wrote:
I was searching the threads about combing multiple rows into one and
found some good stuff, but need your help to expand on it.

I have a table as follows

Policy Number DateOfTrans TransType BenefitAmt
1234 12/1/2006 Received $12,000
1234 12/3/2006 Approved $5,000
1234 12/5/2006 Paid $5,000

I want to get this on one line to look like:

PolicyNumber DateReceived DateApproved DatePaid BenefitAmount

The benefit amount shoudl be the Approve's benefit amount. If approved
is null, then use the received amount.

Possible?

Thanks,
Brian
Dec 21 '06 #4

P: n/a
Ben
I agree with the last entry, you need a query to turn each element into
a row and then another query to bring it all together (assuming your
table name is policy, and I've added an '_' to the id).

Something like this should work great:

SELECT policy.policy_number, [a].DateReceived, [b].DateApproved,
[c].DatePaid, IIf([b].[benefitamt]>0,[b].[benefitamt],[a].[benefitamt])
AS totalbenefitamt
FROM ((policy INNER JOIN
(SELECT policy.policy_number, Max(policy.dateoftrans) AS DateReceived,
policy.transtype, policy.benefitamt
FROM policy
GROUP BY policy.policy_number, policy.transtype, policy.benefitamt
HAVING (((policy.transtype)="received"))) a ON policy.policy_number =
[a].policy_number) INNER JOIN
(SELECT policy.policy_number, Max(policy.dateoftrans) AS DateApproved,
policy.transtype, policy.benefitamt
FROM policy
GROUP BY policy.policy_number, policy.transtype, policy.benefitamt
HAVING (((policy.transtype)="approved"))) b ON policy.policy_number =
[b].policy_number) INNER JOIN
(SELECT policy.policy_number, Max(policy.dateoftrans) AS DatePaid,
policy.transtype, policy.benefitamt
FROM policy
GROUP BY policy.policy_number, policy.transtype, policy.benefitamt
HAVING (((policy.transtype)="paid")))
c ON policy.policy_number = [c].policy_number
GROUP BY policy.policy_number, [a].DateReceived, [b].DateApproved,
[c].DatePaid,
IIf([b].[benefitamt]>0,[b].[benefitamt],[a].[benefitamt]);


BerkshireGuy wrote:
I was searching the threads about combing multiple rows into one and
found some good stuff, but need your help to expand on it.

I have a table as follows

Policy Number DateOfTrans TransType BenefitAmt
1234 12/1/2006 Received $12,000
1234 12/3/2006 Approved $5,000
1234 12/5/2006 Paid $5,000

I want to get this on one line to look like:

PolicyNumber DateReceived DateApproved DatePaid BenefitAmount

The benefit amount shoudl be the Approve's benefit amount. If approved
is null, then use the received amount.

Possible?

Thanks,
Brian
Dec 21 '06 #5

P: n/a
Oh....

How would this be against a table with 300,000 records?
Ben wrote:
I agree with the last entry, you need a query to turn each element into
a row and then another query to bring it all together (assuming your
table name is policy, and I've added an '_' to the id).

Something like this should work great:

SELECT policy.policy_number, [a].DateReceived, [b].DateApproved,
[c].DatePaid, IIf([b].[benefitamt]>0,[b].[benefitamt],[a].[benefitamt])
AS totalbenefitamt
FROM ((policy INNER JOIN
(SELECT policy.policy_number, Max(policy.dateoftrans) AS DateReceived,
policy.transtype, policy.benefitamt
FROM policy
GROUP BY policy.policy_number, policy.transtype, policy.benefitamt
HAVING (((policy.transtype)="received"))) a ON policy.policy_number =
[a].policy_number) INNER JOIN
(SELECT policy.policy_number, Max(policy.dateoftrans) AS DateApproved,
policy.transtype, policy.benefitamt
FROM policy
GROUP BY policy.policy_number, policy.transtype, policy.benefitamt
HAVING (((policy.transtype)="approved"))) b ON policy.policy_number =
[b].policy_number) INNER JOIN
(SELECT policy.policy_number, Max(policy.dateoftrans) AS DatePaid,
policy.transtype, policy.benefitamt
FROM policy
GROUP BY policy.policy_number, policy.transtype, policy.benefitamt
HAVING (((policy.transtype)="paid")))
c ON policy.policy_number = [c].policy_number
GROUP BY policy.policy_number, [a].DateReceived, [b].DateApproved,
[c].DatePaid,
IIf([b].[benefitamt]>0,[b].[benefitamt],[a].[benefitamt]);


BerkshireGuy wrote:
I was searching the threads about combing multiple rows into one and
found some good stuff, but need your help to expand on it.

I have a table as follows

Policy Number DateOfTrans TransType BenefitAmt
1234 12/1/2006 Received $12,000
1234 12/3/2006 Approved $5,000
1234 12/5/2006 Paid $5,000

I want to get this on one line to look like:

PolicyNumber DateReceived DateApproved DatePaid BenefitAmount

The benefit amount shoudl be the Approve's benefit amount. If approved
is null, then use the received amount.

Possible?

Thanks,
Brian
Dec 21 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.