No data shows when I run a query

[maff811]

Hi there,

I have a small database in MS Access that is causing me some issues.

I have a Main Form "A" which has a number of Subforms "B", "C", etc.

Main Form A has 'autonumber' as the unique identifer for each record, whereas subforms B, C etc have their primary key as simply 'number'. These fields are linked.

I am able to show records for each subform that relate to the Main Form just fine. However, there are some details in the subforms that contain no data - this is normal, as some information may not be applicable.

When I do a Select query on the corresponding tables based on the primary key of the Main Form, if no data is stored in subform B's table, the query shows no data. This throws out the entire query.

My desired outcome is for '0' to be presented as opposed to nothing.

Any advice?

Thanks

[TheSmileyCoder]

In the query designer, double click the link between the two tables, and select option 2 or 3 as applicable. This is called a one-way join, and I believe that is what you are after.

This will however not show 0, it will show blank (Null). If that is not sufficient please just say so and we can work something out. I just dont want to complicate things, unless its really necessary.

[maff811]

Hi Smiley,

I need to show '0' instead of blank or null.

THanks for a quick reply.

[annakalli]

i have the same problem, did u find the answer

[MMcCarthy]

I find this bit confusing ...
"Main Form A has 'autonumber' as the unique identifer for each record, whereas subforms B, C etc have their primary key as simply 'number'. These fields are linked."

Do you mean that the primary key in tables behind subforms B and C are set to the same values as the primary key in table A?

You need to give a lot more information on what data (tables and/or queries) are behind each of the subforms and how they are linked to each other before we can help.