Basic Data Access Question

[cabjork]

Using CR XI, I am currently reconciling vendor insurance invoices against our enrollment data by using an excel version of their montly spreadsheet and pulling in our SQL database enrollment data as a subreport to compare enrollment info between the two sources. Among other things, this report allows me to see if an enrollee on the invoice has no record in our database - returning an empty value via the subreport. My question though is - is there some way to do the reverse check with this same report? If we an employee is enrolled according to our database and has no record on the spreadsheet, is it possible to know that in this same report? My instincts tell me "no", that in order to get this info, I would have to create a new report, making our SQL database the main data source and bringing in the spreadsheet data as a subreport - essentially reversing my current "main" and subreport data in a new report. Is there some other way to compare both data sources in one report and get a list of any enrollees (join is via via SS#) who have no record in one data source or the other? I apologize if this is a really basic question.

 

[Madawc]

If you can make a 'left outer' link from table A to table B, then table A items will be shown when they have nothing in table B. Applying this to a mix of database and excel is outside my experience but should work.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[cabjork]

For some reason I was not able to get a basic left outer join to display table A values when they had no records in table B, so I made it work via subreports. However, to hopefully clarify my question - here is a very simplified version of sample data and the result I want to achieve (fyi, my ADP database actually contains 1300 employees each with approx 20 to 40 records in this table).

"Vendor" Spreadsheet
ID Name Cost
886644 John 1000
664422 Paul 1000
442200 George 300
997755 Horace 500


"ADP" Benefits Table (SQL)
ID Name Cost
664422 Paul 500
442200 George 300
997755 Horace 500
775533 Ringo 1000


Desired Report Results
ID Name Vendor Cost ADP Cost
886644 John 1000 0
664422 Paul 1000 500
442200 George 300 300
997755 Horace 500 500
775533 Ringo 0 1000

I know that I can create a report using Vendor data as the main report and get John to appear in my results with 0 ADP costs. I can also reverse my main and sub reports and get Ringo to appear with 0 vendor cost. But what type of join and or subreport can I use to get them both to show up in the same report?

 

[Madawc]

Are you doing a select on Table B? That will spoil the 'left outer', unless you also allow for null values in the Table B value.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10