How to create a query showing values that are 0

[Sandretto]

Hey

I've made an outstanding order database for the company I work for. I've created a query which calculates how many are delivered on each job number and how many are outstanding. Using tables -

tblOrders ( This contains order information and assigns the job num which is primary key)
tblDeliveries ( Contains all delivery information linked to orders through job no)

When I run the query only the orders which have been part delivered are displayed, so when the total delivered add to 0 they are not being displayed I think this is because the job numbers are not entered into the deliveries sheet until some are delivered is there way round this?? I'm bit rusty with databases haven't used them for years!!!

Thanks

[NeoPa]

There could be many reasons. Why don't you start by posting the SQL of the query you're running and we can see what may be a solution.

[Sandretto]

SQL View -

SELECT Customer.[Customer Code]
      ,Orders.[SK Works no]
      ,Orders.[Order No]
      ,Product.[Product Description]
      ,Orders.[Qty Ordered]
      ,[Total Delivered on work no].[Line Item Totals]
      ,[How Many outstanding qry].Outstanding
 
FROM   Customer INNER JOIN 
       (((Product INNER JOIN
       Orders
  ON   Product.[Product Code]=Orders.[Product Code]) INNER JOIN
       [How Many outstanding qry]
  ON   Orders.[SK Works no]=[How Many outstanding qry].[Sk Works No]) INNER JOIN
       [Total Delivered on work no]
  ON   Orders.[SK Works no]=[Total Delivered on work no].[Sk Works No])
  ON   Customer.[Customer Code]=Product.[Customer Code]
 
WHERE  (((Customer.[Customer Code])=[What customer?]))

[NeoPa]

Well, the first thing I notice is that all the JOINs are INNER JOINs (See SQL JOINs). This means that if a related record is missing from any of the matching tables or queries then it will not appear in the output. Can you check for any missing values that they actually appear in all of the related tables and queries. If they do then we may need to look again, but I suspect this is your problem.

[Sandretto]

Thanks for your help all working as it should now

[NeoPa]

I'm very pleased to hear it :)

Welcome to Bytes!