By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,747 Members | 1,672 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,747 IT Pros & Developers. It's quick & easy.

How to create a query showing values that are 0

P: 16
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
Jul 2 '10 #1

✓ answered by 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.

Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,769
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.
Jul 2 '10 #2

P: 16
SQL View -
Expand|Select|Wrap|Line Numbers
  1. SELECT Customer.[Customer Code]
  2.       ,Orders.[SK Works no]
  3.       ,Orders.[Order No]
  4.       ,Product.[Product Description]
  5.       ,Orders.[Qty Ordered]
  6.       ,[Total Delivered on work no].[Line Item Totals]
  7.       ,[How Many outstanding qry].Outstanding
  8.  
  9. FROM   Customer INNER JOIN 
  10.        (((Product INNER JOIN
  11.        Orders
  12.   ON   Product.[Product Code]=Orders.[Product Code]) INNER JOIN
  13.        [How Many outstanding qry]
  14.   ON   Orders.[SK Works no]=[How Many outstanding qry].[Sk Works No]) INNER JOIN
  15.        [Total Delivered on work no]
  16.   ON   Orders.[SK Works no]=[Total Delivered on work no].[Sk Works No])
  17.   ON   Customer.[Customer Code]=Product.[Customer Code]
  18.  
  19. WHERE  (((Customer.[Customer Code])=[What customer?]))
Jul 2 '10 #3

NeoPa
Expert Mod 15k+
P: 31,769
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.
Jul 2 '10 #4

P: 16
Thanks for your help all working as it should now
Jul 5 '10 #5

NeoPa
Expert Mod 15k+
P: 31,769
I'm very pleased to hear it :)

Welcome to Bytes!
Jul 5 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.