Gaps in related table

C

CW

I have an Inquiries table where all our shipping orders are logged (using a
form called Main). Sometimes we have to make shipping crates and when this
occurs there's another form (Crates) on which details are entered and then
saved in the Crates table.
The Inquiries and Crates tables are joined with a type 2 join, using the
common field Ref.
Problem: if we don't have to supply crates on a certain order, that order
doesn't have an entry in the Crates table. When I run my query to look up
all the crate costs that we have incurred, I want every single order to be
shown and if there were no costs for a given order I want 0.00 to be shown,
rather than the record not to be displayed at all.
I have tried changing the type of join between the tables and I have fiddled
around with Nz in the query but nothing has worked so far - advice, please!
Many thanks
CW
 
J

Jerry Whittle

Show us the SQL of what you have tried. Open the query in design view. Next
go to View, SQL View and copy and past it here.
 
C

CW

Jerry -
Many thanks for responding but I have just succeeded, having fiddled around
a bit more with Nz I have now got the syntax right and it is displaying 0.00
as I wanted.
Thanks anyway
CW
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Create records automatically in related table? 2
tabledesign suggestions for costs-table for several customer/destinations/loads? 1
Input into one field to generate a value in another 3
Default value depending on previous field 5
DB not hanging together 1
Inventory/Sales/Warehouses 6
Display targets for current month 3
Restrict the values in a combo 5

Top