Records and Associated Notes Query

[hobbit2612 via AccessMonster.com]

Hi,

I wonder whether someone may be able to help me please.

I have two tables, one showing invoice details, the other showing notes about
the particular invoice, linked via a InvoiceID field.

What I'm trying to do is to create a report that allows the user to select
all the invoices and selected notes which they choose via a tick box on the
notes form.

The problem I have is that when there are no notes the query returns a blank
even though I have the table relationship as 'All' invoices and 'Only' those
that match in the notes table.

Can anyone shed any light on where I may be going wrong?


Many thanks

Chris

 

[John W. Vinson]

Hi,

I wonder whether someone may be able to help me please.

I have two tables, one showing invoice details, the other showing notes about
the particular invoice, linked via a InvoiceID field.

What I'm trying to do is to create a report that allows the user to select
all the invoices and selected notes which they choose via a tick box on the
notes form.

The problem I have is that when there are no notes the query returns a blank
even though I have the table relationship as 'All' invoices and 'Only' those
that match in the notes table.

Can anyone shed any light on where I may be going wrong?

Not without seeing the query, but - at a guess - you have an incorrect Join.
The default Inner Join will return data only if BOTH tables contain data;
you'll need to select the Join line in the query design window and choose
option 2 (or 3) - "show all records in Invoices and matching records in
Details".

One concern: if you have Invoices related one-to-many to Details, and also
related one to many to Notes, but no connection between Details and Notes,
you'll get a "combinatorial explosion". If an invoice has 10 detail rows
associated with it, and three notes, you'll see all thirty possible
combinations! You would do better to use a Report based on Invoices, with two
Subreports - one based on Notes and the other on Details.

 

[hobbit2612 via AccessMonster.com]

Hi John,

Many thanks for the reply.

I took away your assumptions and concerns and you were quite correct in both.
When I went back to work today I made some changes to how the data was set up
and the relationships in the database and it works a treat.

Thanks you so much for shoiwng me the way. Your time and help is greatly
appreciated.

Regards

Chris

Hi,

[quoted text clipped - 12 lines]

Can anyone shed any light on where I may be going wrong?

Not without seeing the query, but - at a guess - you have an incorrect Join.
The default Inner Join will return data only if BOTH tables contain data;
you'll need to select the Join line in the query design window and choose
option 2 (or 3) - "show all records in Invoices and matching records in
Details".

One concern: if you have Invoices related one-to-many to Details, and also
related one to many to Notes, but no connection between Details and Notes,
you'll get a "combinatorial explosion". If an invoice has 10 detail rows
associated with it, and three notes, you'll see all thirty possible
combinations! You would do better to use a Report based on Invoices, with two
Subreports - one based on Notes and the other on Details.