Query Structure Help Needed

[CharlesWhiteman]

In my Db I have

TblInvoices.InvoiceID (PK)
Tblinvoices.InvoiceNumber
Tblinvoices.InvoiceDate

Etc... then also

TblFinanceReceipts.ReceiptID (PK)
TblFinanceReceipts.ReceiptDate
TblFinanceReceipts.InvoiceNumber

Etc

At the moment one of my accounts has two invoices outstanding, I want to produce a report to show outstanding invoices, amount paid (receipts) and can then produce a balance.

I've been trying to produce this in QBE by adding both tables to the query but don't seem to ge the right results. Any advice welcomed and thanks.

 

[MSAccessRookie]

In my Db I have

TblInvoices.InvoiceID (PK)
Tblinvoices.InvoiceNumber
Tblinvoices.InvoiceDate

Etc... then also

TblFinanceReceipts.ReceiptID (PK)
TblFinanceReceipts.ReceiptDate
TblFinanceReceipts.InvoiceNumber

Etc

At the moment one of my accounts has two invoices outstanding, I want to produce a report to show outstanding invoices, amount paid (receipts) and can then produce a balance.

I've been trying to produce this in QBE by adding both tables to the query but don't seem to ge the right results. Any advice welcomed and thanks.

The Basic structure for a query that gets results from two tables would be something like this:

[B]Select[/B] {[COLOR=red][B]Whatever information that you want[/B][/COLOR]}
[B]From[/B] Table1 {[B][COLOR=red]Left[/COLOR]/[COLOR=red]Right[/COLOR]/[COLOR=red]Inner[/COLOR][/B]} [B]Join[/B] Table2 [B]On[/B] {[COLOR=red][B]Whatever they join on[/B][/COLOR]};

Fill in the blanks with your information and see if this gets you started.

 

[Alansidman]

How are your tables joined. I would think that you would want to have a Left join whereby you show all invoices and only those remittances affecting invoices. Does this help?

Alan

 

[CharlesWhiteman]

Hey there Alan,

I've tried it in a number of ways but typicallly a reference on

TblInvoices.CompanyCode = forms!FrmDatabase.CompanyCode

and a join TblInvoices.InvoiceNumber to TblfinanceReceipts.InvoiceNumber

but whereas there are two invoices due the query returnls only one value where a payment has been made.

If I try other ways I get many repeated values which don't fully addup.

 

[MSAccessRookie]

Hey there Alan,

I've tried it in a number of ways but typicallly a reference on

TblInvoices.CompanyCode = forms!FrmDatabase.CompanyCode

and a join TblInvoices.InvoiceNumber to TblfinanceReceipts.InvoiceNumber

but whereas there are two invoices due the query returnls only one value where a payment has been made.

If I try other ways I get many repeated values which don't fully addup.

Looks like you might want a Where Statement as well. Is it possible for you to state your query in the format below?

[B]Select[/B] {[COLOR=red][B]Whatever information that you want[/B][/COLOR]}
[B]From[/B] Table1 {[B][COLOR=red]Left[/COLOR]/[COLOR=red]Right[/COLOR]/[COLOR=red]Inner[/COLOR][/B]} [B]Join[/B] Table2 [B]On[/B] {[COLOR=red][B]Whatever they join on[/B][/COLOR]}
[B]Where[/B] [COLOR=green][B]TblInvoices.CompanyCode = forms!FrmDatabase.CompanyCode[/B][/COLOR];