Results 1 to 4 of 4

Query to list all results from table 1 with criteria from table 2

  1. #1
    Relwal is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    7

    Query to list all results from table 1 with criteria from table 2

    Hi,

    I have the following query:



    SELECT tbl_Clients.Client_Name, IIf(([tbl_Clients].[Active]=True),[tbl_NextMonthsPlan].[Days],Null) AS [Planned Days]
    FROM tbl_Clients LEFT JOIN tbl_NextMonthsPlan ON tbl_Clients.Client_ID = tbl_NextMonthsPlan.Client_ID
    GROUP BY tbl_Clients.Client_Name, IIf(([tbl_Clients].[Active]=True),[tbl_NextMonthsPlan].[Days],Null);

    and get this result (screenshot1):
    Click image for larger version. 

Name:	screenshot1.png 
Views:	10 
Size:	16.6 KB 
ID:	26979

    I need to add in Plan_Date and Payroll_ID parameters from the tbl_NextMonthsPlan table.
    Click image for larger version. 

Name:	screenshot2.png 
Views:	10 
Size:	21.3 KB 
ID:	26980

    I have tried to do this, but keep getting only a list of clients who have days against their name, I still need the full list even if the days is null.

    Thanks

  2. #2
    aytee111 is online now Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,454
    In one line of criteria: Client_ID not null (AND) Plan_date=... (AND) Payroll_ID=...
    On second line of criteria: (OR) Client_ID=Null

  3. #3
    JoeM is online now VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,308
    Are you remembering to add the new fields to either your GROUP BY statement, or making them Aggregates in the SELECT clause?

    It might be beneficial if you could post some small examples of what the data in each table looks like, and what your expected results should look like.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  4. #4
    Relwal is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    7

    Thumbs up

    Quote Originally Posted by aytee111 View Post
    In one line of criteria: Client_ID not null (AND) Plan_date=... (AND) Payroll_ID=...
    On second line of criteria: (OR) Client_ID=Null
    Works perfectly, thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 01-04-2017, 01:44 PM
  2. List records not in table with multiple criteria
    By cactuspete13 in forum Queries
    Replies: 20
    Last Post: 08-06-2014, 05:26 AM
  3. Replies: 9
    Last Post: 05-13-2014, 07:22 AM
  4. Replies: 5
    Last Post: 05-21-2013, 01:21 PM
  5. Replies: 0
    Last Post: 02-23-2010, 11:56 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums