Page 3 of 3 FirstFirst 123
Results 31 to 33 of 33
  1. #31
    captike is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2022
    Location
    Alachua, FL
    Posts
    16


    Quote Originally Posted by Ajax View Post
    Always better to provide data which truly reflects the real life situation. You need a different type of query

    Code:
    SELECT tblEquipment.Unit, tblEquipment.Position, [c].[equipmentID] Is Not Null AS serviced
    FROM tblEquipment LEFT JOIN (SELECT tblMaintenance.EquipmentID
    FROM tblMaintenance
    WHERE (((tblMaintenance.MaintenanceType)="PM") AND ((Year([MaintenanceDate]))=Year(Now()))))  AS C ON tblEquipment.EquipmentID = C.EquipmentID
    WHERE (((tblEquipment.Unit) Is Not Null));
    Nailed it!! This does exactly what I need. I can't thank you enough. If I can ask, what kind of query is this? Specifically, what is the C.EquipmentID doing?

  2. #32
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it's from this part of the query

    Code:
    (SELECT tblMaintenance.EquipmentID
    FROM tblMaintenance
    WHERE (((tblMaintenance.MaintenanceType)="PM") AND ((Year([MaintenanceDate]))=Year(Now()))))  AS C
    I named it C (known as aliasing) as just a short name for 'current' - shorter words, less typing

    the query is just selecting the records for each equipment which qualify as 'currently serviced'

    you could write this as a separate query and join it to the equipment table instead if you wanted to

  3. #33
    captike is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2022
    Location
    Alachua, FL
    Posts
    16
    Ok, I think I got that. So much to learn.....Thank you again for all of your help. I do appreciate it.

Page 3 of 3 FirstFirst 123
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 03-03-2018, 05:08 PM
  2. Replies: 2
    Last Post: 02-14-2017, 02:23 AM
  3. Replies: 2
    Last Post: 11-12-2015, 10:46 AM
  4. Replies: 6
    Last Post: 05-26-2013, 09:10 PM
  5. Visual Formatting Glitches on Report
    By trb5016 in forum Reports
    Replies: 0
    Last Post: 07-29-2010, 09:33 AM

Posting Permissions

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