Show differences of Union Query

[retygh45]

I had this in a different post, but that post had a lot of other issues in it.

I have a union ALL query of 2 tables that list records sent from one db and received in another. So I have 3 fields in the results: filename, date, and a 3rd column which contains either "sent" or "received" so I know from which side it came.

I have the report grouped on this field with a total, so my report looks like this:

sent: 50
Received: 60

If sent count doesn't equal the received count, how can I display the details section to only show the records, from either side, that have no corresponding record? So in my above example, I'd want to show just the 10 files under "received" that have no match in the "sent" group?

I've tried group selections, and trying to suppress the details of the report based on a formula, but can't seem to get it to work. Any help is appreciated, thanks!

 

[lbass]

Remove the group and instead group on the filename. Then use a group selection like this:

distinctcount({table.sentrecd},{table.filename}) = 1

-LB

 

[retygh45]

Got it, thanks! But how can I show the original totals? I have the list correct of files where the count = 1, but in my report header, I'd like to show the totals, so something like this:

Total sent: 60
Total recvd: 50

missing files:

[group headers]

I've tried using conditional formulas and running total fields, but cannot seem to get the totals correct. Any help is appreciated, thanks!

 

[lbass]

Create two formulas:

//{@Sent}:
if {command.type} = "Sent" then 1

//{@Recd}:
if {command.type} = "Received" then 1

Then you can use a formulas like the following in the report header:

//{@Total Sent}:
sum({@Sent})

Repeat for Received. If you didn't set up a name for your field that returns "sent" and "received", go into the command and do so. If you were using Access, it would be:

'Sent' as type

In Oracle, it would be:

'Sent' "Type"

Not sure what the correct syntax would be for you.

-LB

 

[retygh45]

got it, thanks!

 

[retygh45]

I had the group selection formula as above, however when the counts do match, the formulas show 0 because it's suppressing all the records.

So I took that out and just listed them all in the report, but put a suppression forumla on the details:

DistinctCount ({Command.Sent}, {Command.wdFileName_Full}) = 2

so when the sent and received files match, they will be suppressed, if there's only 1 of them, it would show. This seems to work ok, unless you see any problems with it?

Thanks!

 

[lbass]

If you used group selection and you used the conditional formulas I showed you, NOT running totals, they should have appeared in the report header, showing the counts of all records, not just those displayed.

-LB

 

[retygh45]

I have the report grouped on filename, and the group conditional formula: DistinctCount ({Command.Sent}, {Command.wdFileName_Full}) = 1

In my header I have 1 formula:

//{@Total Sent}:
sum({@total_sent})

where the formula {@total_sent} is:

//{@Sent}:
if {Command.Sent} = "Sent" then 1

And I have the same 2 formulas for "received"

When there is a discrepancy in the counts, the report formulas show, but when the 2 counts are equal, the formula is blank.

Any ideas? THanks again for all your help!

 

[lbass]

It sounds like you are placing these formulas in the group header instead of the report header. Can you verify the section in which you are placing them?

-LB

 

[retygh45]

RHc Report Header C.

 

[lbass]

Neither section suppression nor group selection would affect summary formulas like these, so there must be something you've done that you are not relating. I think you will have to describe step by step what you've done.

-LB

 

[retygh45]

very strange. I actually created a new blank report just to make sure there wasn't something hiding in it.

I created a new report, added my command line data source.

2) grouped on file name
3) created my formulas
4) at this point, when I look at the data, it's correct: Count of send: 1342, count of receivd: 1343 (so there should be one header). When I look at the details, they're correct: I see a filename for each header, then 2 detail records for each, one "sent" and one "received"
5) added a group selection formula (report>selection formulas>group): DistinctCount ({Command.Sent}, {@filename}) = 1

At this point, once I add a the group record selection, I lose all the records and the formulas in my header go blank, there not there. The total record count is the same, but something happens to the formulas. It's almost as if adding the group selection formula does something to block the formula "if {Command.Sent} = "Sent" then 1" from reading the records.

Anyway, thanks so much for all your help!!!

 

[lbass]

The formulas in the report header should be using sums, NOT distinctcounts:

sum({@sent})

The group selection formula should use the field (not a formula) that results in sent or received.

distinctcount({command.sentrecd},{@filename}) = 1

-LB