Query not working as expected

[Damien McBain]

G'day,

My database has a main data table and a number of other joined tables for
storing descriptions etc (ie data table stores material number, Materials
table stores unique material numbers with associated descriptions and other
info etc). The associated tables have one to many joins with the main data
table and referencial integrity is enforced in all cases.

One query (which I use for running a report on individual records from the
data table complete with descriptions etc from the associated tables) isn't
working properly. It will not return a record unless there is a value in the
material number field. The material number field (in the main data table)
has the properties:
FieldSize: Long Integer
Format: Fixed
Decimal Places: 0
Default Value: 0
Required: No
Indexed: No
All the others are blank.
In the lookup tab in table design view the associated material master table
is referenced as row source etc (dunno why I did that).

Any ideas why my query won't return records where material number is blank
or zero? There is not criteria set in the query on any field.

Damien

 

[Allen Browne]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

 

[Allen Browne]

In query design view, you have 2 tables.
Double-click the line joining them.
Access offers you 3 options.
Choose the one that gives you the records you want.

More information in article:
The Query Lost My Records!
at:
http://members.iinet.net.au/~allenbrowne/casu-02.html

 

[Mark]

Damien

Sounds like you need to fix your join to be a left outer join which
will show all records from your main table and only those matching
records from the secondary table (if there is one)

 

[Damien McBain]

Damien

Sounds like you need to fix your join to be a left outer join which
will show all records from your main table and only those matching
records from the secondary table (if there is one)

You are both dead right. I thought I had a one-to-many relationship between
the materials (one) table and the main data table (many). This is not the
case so the queries haven't inheirited the join properties I thought they
would.
Stupid mistake.
Thanks for replying Allen & Mark.

Damien