1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved Can you create Reports and Forms that update based on date?

Discussion in 'Business Applications' started by r24igh, Sep 1, 2016.

Thread Status:
Not open for further replies.
Advertisement
  1. r24igh

    r24igh Thread Starter

    Joined:
    May 18, 2016
    Messages:
    31
    Hi All,

    I'm trying to create a Form and a Report in a Database that I've built. Ideally, what I'm trying to do is create a yearly rolling Form (for data entry) and Report (that runs off that data in the table that is updated by the form).

    The table I have is essentially this:
    Effort in Days August September October November December etc
    Project 1 1 2 0 1 1
    Project 2 2 2 2 2 2
    Project 3 0 0 4 4 4

    To make it easier for myself to maintain in the future, I've included fields all the way up to the end of 2018. The reports and forms are pulled from this table.

    The stakeholders I've made this for want to a) be able to update projects themselves (easy enough with a form and project search) and b) have the reports on a yearly view. My current solution is to delete the forms and reports each month and then recreate them, moving the months out to the right. This has been fine up until now as it's essentially been in UAT/ proof of concept. Now we're starting to properly use this and I do not have the time to spend forever updating this every month.

    The reports and forms are pulled from a query that is currently this:

    SELECT RealTime.[Project Name], RealTime.[Employee Name], RealTime.PPM, RealTime.[Actual or Prospect], RealTime.[01/08/2016], RealTime.[01/09/2016], RealTime.[01/10/2016], RealTime.[01/11/2016], RealTime.[01/12/2016], RealTime.[01/01/2017], RealTime.[01/02/2017]
    FROM RealTime
    WHERE (((RealTime.[Actual or Prospect])="Actual"));

    I know this doesn't work, but for the sake of argument, what I'm trying to get to is something that is like this:

    SELECT RealTime.[Project Name], RealTime.[Employee Name], RealTime.PPM, RealTime.[Actual or Prospect], RealTime.month(now()-1), RealTime.month(now()), RealTime.month(now()+1), RealTime.month(now()+2) and so on...

    Does this make sense? Any help/ suggestions/ advice would be much appreciated!
     
  2. r24igh

    r24igh Thread Starter

    Joined:
    May 18, 2016
    Messages:
    31
    Apologies - the numbers under the months didn't come through clearly. What it should be is the months as column headers and the project name and effort in days per month under each project/ month.
     
  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,794
    What you have may work, but it is not how a conventional database would be designed, it is normal to have the report data in a sub table instead of many individual fields.
    This data is then collated in the query for use in the report.
     
    r24igh likes this.
  4. r24igh

    r24igh Thread Starter

    Joined:
    May 18, 2016
    Messages:
    31
    Thanks OBP
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1177268

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice