Prev: use OnClick code
Next: Prevent duplicate selection from combobox
From: hobbit2612 via AccessMonster.com on 23 Jan 2010 15:50
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1

From: John W. Vinson on 23 Jan 2010 16:51
On Sat, 23 Jan 2010 20:50:52 GMT, "hobbit2612 via AccessMonster.com"
<u27332(a)uwe> wrote:

>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.
--

John W. Vinson [MVP]
From: hobbit2612 via AccessMonster.com on 25 Jan 2010 12:36
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

John W. Vinson wrote:
>>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.

--
Message posted via http://www.accessmonster.com

 | 
Pages: 1
Prev: use OnClick code
Next: Prevent duplicate selection from combobox