Conditional subtotaling 1

[scooterny]

Hi folks.

I am using CR10 against MS Access. I have 2 tables with Service Code and Volume. Some times the Service Codes are the same, most of the time, they aren't. All of the Service Codes are unique. For example,

Service Code 1 Volume Service Code 2 Volume

1001 100 1547 50
2500 250 2500 100
3333 75 9999 47

Here is my issue. I want to combine the 2 Service Code columns into 1. Where they match, I must add the volumes. Where they don't match, I just want to take the unique volume from that associated Service Code. I want my end result to look like this:

Service Code Volume
1001 100
1547 50
2500 350
3333 75
9999 47

Any ideas? Thanks.

Scott

 

[lbass]

Create a command with a union all, as in:

Select table1.`service code`, table1.`volume`
from table1
union all
Select table2.`service code`, table2.`volume`
from table2

Then you can insert a group on code and then insert a summary on volume and then suppress the detail level.

-LB

 

[scooterny]

LB,

Thanks for the response. I tried what you said, but I get an error on the DAO DB. Too few parameters. Expected 2. I then tried the command on Access and it is expecting me to pass it a parameter. Am I doing something wrong here.

 

[scooterny]

I am sorry, I typed it wrong. When I type the command properly into CR, it too asks for a parameter.

 

[lbass]

I'm not sure why that is happening. You are entering this in database->database expert->your datasource->add command, right?

Please paste your query into the thread.

-LB

 

[scooterny]

yes, that is what I am doing. Here is the query:

SELECT [CVR to CAP volume recon summary by svc cd v1].[Service Code 1], [CVR to CAP volume recon summary by svc cd v1].[Volume 1]
FROM [CVR to CAP volume recon summary by svc cd v1]
union all
SELECT [CVR to CAP volume recon summary by svc cd v2].[Service Code2], [CVR to CAP volume recon summary by svc cd v1].[CAP Volume]

 

[lbass]

Why are you using brackets around everything? Is this how your queries look if you go to "show SQL query"? If this is Access-based, I would think you would use:

SELECT `CVR to CAP volume recon summary by svc cd v1`.`Service Code 1`, `CVR to CAP volume recon summary by svc cd v1`.`Volume 1`
FROM `CVR to CAP volume recon summary by svc cd v1`
union all
SELECT `CVR to CAP volume recon summary by svc cd v2`.`Service Code2`, `CVR to CAP volume recon summary by svc cd v1`.`CAP Volume`

-LB

 

[scooterny]

LB,

I am using brackets around everything because I copied the query from Access. Yikes. Once I made that change, everything worked like a champ. I guess that brackets must have some other signifigance. I really appreciate your help. Thank you very much!!

Scott