Linking tables

[jbhsuman]

CR9

This may sound like a silly question but I am getting some strange results while trying to link a table to an existing report. The tables in question are:

Invoice Header
Invoice Detail
Invoice History

Currently I have the following links established:

InvoiceHistory.invoice----->InvoiceHeader.Invoice

With this simple link I get the results I am looking for. However once I try to link the InvoiceDetail table also by the InvoiceDetail.Invoice. However, when I link this table the report comes back blank. Regardless of how I link the table's together the addition of this table comes back blank.

Each table should have a valid record associated with the other tables in the link. For example, there is definately a detail record associated with the header table and there should be a history record associated with both the header and detail tables.

Any suggestions would be appreciated.

Thanks

 

[dtempleton]

Are you linking Header to Detail? Make sure that Header.Invoice is the same type ans length as Detail.Invoice.

 

[synapsevampire]

You didn't state what you were linking InvoiceDetail to, just that you were.

Right click the joins and select Left Outer join, that should also help, but if there should always be a link, then it sounds like something wrong in your joins.

-k

 

[lbass]

I would use a left join FROM Invoice Header to Invoice History, and a left join FROM Invoice Header to Invoice Detail.

-LB

 

[jbhsuman]

Thank you all for your input. synapsev, I am linking all talbles to the field Invoice. As far as the question from dtemp, the field lengths are all the same.

I used the left join option and now the report does not return a blank report, however it still does not disply the value I m looking for from the InvoiceDetail table. Perhaps I have a problem with the way I am linking the tables. The main purpose of this report is to pull the invoice history. So in this instance I am using the InvoiceHistory as the primary table. so my links are as follows:

InvoiceHistory.Invoice left join to InvoiceHeader.Invoice and InvoiceHistory.Invoice left join to InvoiceDetail.Invoice. InvoiceHeader.PatientNo to Patient.PatientNo. and InvoiceHeader.InsNo to InsCo.InsNo.

If a record exists in the InvoiceHistory table then there is, without question, an associated record in both the InvoiceHeader and InvoiceDetail table. The records in the InvoiceHeader table are unique (only one invoice record per invoice number. Both the InvoiceHistory and InvoiceDetail can have many records associated to the one record in the header. As I previously stated, the primary purpose of the report is to report on the transaction history. My report is grouped as follows

GH1 Patient.Patient.No
{Display} Patient.Name
GH2 InvoiceHistory.Invoice
{Display} InvoiceHistory.Invoice
{Display} InvoiceHeader.Date
{Display} InvoiceHeader.Charges
{Display} Invoicedetail.Date
Detail
{Display} InvoiceHistory.Date
{Display} InvoiceHistory.Methode
{Display} InvoiceHistory.Description
GF2
{Display} Running Total
GF1 Surpress


When I used the outer join option, all the data is displayed properly with the exception of the InvoiceDetail.Date in GH2.

Does anyone see where I may be going a wrong?

 

[kskid]

With {Display} Invoicedetail.Date in GH2, you will only get the first one. You can do one fo the following.

1. Insert Group 3 for {Display} Invoicedetail.Date
2. Create a subreport for Invoicedetail.Date
3. Move {Display} Invoicedetail.Date to the detail section