Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    We have a query (Query 1) that lists users from UserTable according to the query parameter "[Enter User Type (1/2):]", and another query (Query 2, below) based on Query 1.

    SELECT [Query1].User, Nz(Sum(-((IsNull([Date]))*([Status]>0)*([Status]<10))),0) AS Expr1
    FROM [Query1] LEFT JOIN (JobTable RIGHT JOIN OrderTable ON JobTable.Order = OrderTable.Order) ON [Query1].User = JobTable.User
    WHERE (((OrderTable.OrderType)=1) AND (([Enter User Type (1/2):])="1") AND ((JobTable.JobType)=1)) OR (((OrderTable.OrderType)=1) AND (([Enter User Type (1/2):])="2") AND ((JobTable.JobType)=2))
    GROUP BY [Query1].User;

    We need the results of the query to include all users from Query 1, even those where there are no matching records in JobTable, and consequently no matching records in OrderTable. For those users where there are no matching records in JobTable and OrderTable, Expr1 should be zero. Is this possible?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    By putting conditions on OrderTable.OrderType and on JobTable.JobType you force the query to use existing records in those table only. You can probably solve it by using a control on a form instead of a parameter prompt, constructing a query based on JobTable and OrderTable referring to that control, and then combining this query with Query1.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •