query not returning some records

[Slez via AccessMonster.com]

I have a crosstab query that is pulling data from 4 tables:
Project, which is on the one side of a one-to-many relationship with...
Bid, which is on the one side of a one-to-many relationship with...
Item, which is on the one side of a one-to-many relationship with...
ItemDetail

One of the fields is a calculated field that sums data from the ItemDetail
table.
LineTotalSellPrice: Sum(([Quantity]*([UnitPrice]+[QuoteCost]))*[Markup])

Even if there are no records in the tables Item or ItemDetail, I still need
information to be queried containing records from the tables Project and Bid.
It seems that currently any cases where records don't exist for the
calculated field LineTotalSellPrice to calculate, records from the parent
tables do not get returned. Is this an application where I need to use Nz or
Null functions in my expression? If so, how can it be altered to make this
work? If not, what else might be causing these records not to be returned?

Thanks in advance for any assistance!
Slez

 

[Allen Browne]

Use outer joins.

Double-click the line joining 2 tables in the upper pane of query design.
Access pops up a dialog with 3 options.
Choose either #2 or #3 (depending what you want.)

The lines joining the tables will then have arrow heads on one end. You
should end up with the arrows pointing away from the most important table,
i.e.:
Project --> Bid --> Item --> ItemDetail

More info on outer joins and the criteria under them here:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

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

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

I have a crosstab query that is pulling data from 4 tables:
Project, which is on the one side of a one-to-many relationship with...
Bid, which is on the one side of a one-to-many relationship with...
Item, which is on the one side of a one-to-many relationship with...
ItemDetail

One of the fields is a calculated field that sums data from the ItemDetail
table.
LineTotalSellPrice: Sum(([Quantity]*([UnitPrice]+[QuoteCost]))*[Markup])

Even if there are no records in the tables Item or ItemDetail, I still
need
information to be queried containing records from the tables Project and
Bid.
It seems that currently any cases where records don't exist for the
calculated field LineTotalSellPrice to calculate, records from the parent
tables do not get returned. Is this an application where I need to use Nz
or
Null functions in my expression? If so, how can it be altered to make
this
work? If not, what else might be causing these records not to be
returned?