Results 1 to 4 of 4
  1. #1
    Topflite66 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    87

    Reports by Year

    I'm fairly new with Access so please bare with me if I don't explain well what I am trying to accomplish.

    I have a query that has several count expressions that I use to generate a report based on a Model Year. Because of the design of the query I don't believe I would be able to use parameters in the query itself. So I'm thinking I would create a query for each year that I can run to generate the information I need.

    Click image for larger version. 

Name:	3.jpg 
Views:	14 
Size:	107.0 KB 
ID:	32971

    SELECT qryUnitCtsbyModel.MdlYr, tbl_Model.ModelID, tbl_Model.Model, Count(IIf([tbl_OrderDetails]![OnLot]=Yes,1,Null)) AS CountOnLot, Count(IIf([tbl_OrderDetails]![VIN] Is Null Or [tbl_OrderDetails]![OnLot]=True Or Not [tbl_OrderDetails]![DateSold] Is Null,Null,1)) AS CountIncoming, Count(IIf([tbl_OrderDetails]![OnLot] Is Null Or [tbl_OrderDetails]![DateSold]=True Or Not [tbl_OrderDetails]![VIN] Is Not Null,Null,1)) AS CountNoVIN, Count(IIf([qryUnitCtsbyModel]![BdyCdID] Is Null,Null,1)) AS CountTotOrderedUnits, Count(IIf([tbl_OrderDetails]![Action] Is Not Null,Null,IIf([tbl_OrderDetails]![DateSold] Is Null,Null,1))) AS CountTotalSold, Count(IIf([tbl_OrderDetails]![DateSold] Is Not Null And [tbl_OrderDetails]![Customer]="Cust",1,Null)) AS CountTradeInCustomer, Count(IIf([tbl_OrderDetails]![DateSold] Is Not Null And [tbl_OrderDetails]![Action]="DTO",1,Null)) AS CountTradeOut
    FROM (tbl_Model LEFT JOIN qryUnitCtsbyModel ON tbl_Model.ModelID = qryUnitCtsbyModel.ModelID) LEFT JOIN tbl_OrderDetails ON qryUnitCtsbyModel.OrderID = tbl_OrderDetails.OrderID
    GROUP BY qryUnitCtsbyModel.MdlYr, tbl_Model.ModelID, tbl_Model.Model


    HAVING (((qryUnitCtsbyModel.MdlYr)="2015")) OR (((qryUnitCtsbyModel.MdlYr) Is Null))
    ORDER BY tbl_Model.Model;

    What I am wondering is, when I click the link on my Navigation form to open the report, is there a way via code or macro to select one of the queries by name and run it to generate the report? Or if there is another option? I hope that makes sense. Thank you.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Because of the design of the query I don't believe I would be able to use parameters in the query itself. So I'm thinking I would create a query for each year that I can run to generate the information I need.
    I don't think you need separate queries.
    Have you tried a Parameter Query?
    Just change the "2015" on your Criteria row to:
    [Enter Model Year]
    (must have square brackets).
    See: https://support.office.com/en-us/art...rs=en-US&ad=US

  3. #3
    Topflite66 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    87
    Quote Originally Posted by JoeM View Post
    I don't think you need separate queries.
    Have you tried a Parameter Query?
    Just change the "2015" on your Criteria row to:
    [Enter Model Year]
    (must have square brackets).
    Thank you for the trigger. I had tried setting up two parameters, one for the year and another for whether the Model Year field was Null. However, I didn't try setting up just one Parameter for the year and then in the 2nd criteria enter Is Null. That works perfect. Thank you.

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome!

    That is one of the beautiful things about Access. There is usually a way to avoid unnecessary duplication.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-19-2017, 01:09 PM
  2. Replies: 1
    Last Post: 10-14-2015, 03:45 PM
  3. Replies: 3
    Last Post: 06-22-2015, 06:36 AM
  4. Replies: 4
    Last Post: 01-09-2013, 11:16 AM
  5. Group reports on odd year
    By EricE in forum Reports
    Replies: 3
    Last Post: 12-06-2011, 09:45 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