Results 1 to 7 of 7
  1. #1
    makivelli is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    5

    Report that Pulls Next Mon-Fri Dates from Multiple Date Fields

    Good Morning All,



    I have a small amount of experience with Access but am very interested in learning much more. I was given a task to create a database for a coworker and have come to a feature(?) that I would really like to get working.

    Basically, at the end of each work week, my coworker looks up the court hearings for the following week so she can plan out what ones she needs\wants to attend. There are several fields that have these court event dates in them all of which are for a different type of event. i.e. Initial Appearance, Trial, Change of Plea, Sentencing, etc.

    How can I go about creating a report that will look into those date fields and pull the ones that have dates for the following Monday through Friday?

    I am running Windows 10 - Access 2016.




    Thanks for any and all help,

    Joe







    P.S. - This is my first post on this forum. I apologize if I placed this in the wrong location. I am also not sure of what information is required for a post such as this. But I will learn quickly.

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    It would probably be easier if you had a separate table for all your dates. A "CourtDates" table with one date field and the a CourtDateType field to identify if this record is for initial appearance, trial, etc... Then you only have to search records by one date field instead of several.

    Can you post a screenshot of your current table relationship window?

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I certainly agree with the design suggestion. You can determine the next Monday and Friday with:

    ?dateadd("d",8-weekday(date(),2),date())
    8/27/2018
    ?dateadd("d",8-weekday(date(),2)+4,date())
    8/31/2018
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    makivelli is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    5
    @kd2017

    Thank you very much for the fast reply!

    Click image for larger version. 

Name:	Current_Relationships.PNG 
Views:	17 
Size:	29.5 KB 
ID:	35230

    I hope this is what you were asking for. I only just now created that CourtDates table but haven't formatted it at all.



    @pbaldy

    Thank you for the fast reply as well. When I get to the point of using what you posted I will let you know how it goes.

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    You'll want to add a foreign key field to the CourtDates table to link back to the primary key of the VW_Listing table (USAO#). One VW_Listing has many CourtDates.

    On another note, you're using natural primary keys in place where it's probably better to just use a AutoNumber field. For example in your Agency table should have two fields, first field called AgencyID that is an AutoNumber Primary Key, and the Second field being Agency of short text datatype. THEN in your VW_Listing table the Agency field would be a number datatype that links to the AgencyID field in the Agency table.

    It's best practice not to use spaces or symbols in your field names, just letters, numbers, and underscore characters. For example [# of Victims] becomes [NumOfVictims] or [No_of_Victims] or something like that.

  6. #6
    makivelli is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    5
    @kd2017

    1. I apologize for my lack of knowledge. I have spent the past few hours trying to figure out what you meant by "foreign key field". Am I trying to create a a one-to-many relationship from the CourtDate field to the USAO# field?

    2. Excellent advice. I will look into fixing that after this.

    3. Is it too late to rename them now? or will it mess stuff up?


    Thanks again for your help and patience.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Looks like kd has gone off duty.

    Yes, you're creating a one-to-many relationship between the primary table and the new dates table. It's no different than an invoice; you'd have an invoice table with the main data, a related table with each line item on the invoice as a record, related by invoice number.

    As to renaming, it probably depends on how deep you are into development. Renaming a field in the table with break queries, forms, etc that use that name. Your names will work, but are more trouble than they're worth in the long run. If you're early on with not many objects, I'd change names. If you've got a lot of objects, it may not be worth the bother (but remember for anything you create in the future).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Form pulls info into fields based on ID number
    By arothacker in forum Forms
    Replies: 3
    Last Post: 02-27-2014, 04:13 PM
  2. Replies: 2
    Last Post: 12-04-2013, 03:58 PM
  3. Report based on Multiple Date Fields
    By viper210 in forum Reports
    Replies: 11
    Last Post: 09-27-2012, 07:07 AM
  4. Date Range Report with Multiple Date Fields
    By StevenCV in forum Reports
    Replies: 13
    Last Post: 02-29-2012, 08:29 AM
  5. Replies: 3
    Last Post: 09-29-2009, 07:08 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