Results 1 to 5 of 5
  1. #1
    Rubijon is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Oct 2011
    Posts
    5

    Complex reporting (well for me anyway)

    Hello,



    I was hoping someone would be able to help me or even tell me if this is possible.

    I have a database which contains a schedule for about 500 different construction projects, in which there are some sites (about 60 for now) which have multiple projects associated with them, example; one project occurs 12/6/11 (SiteScheduleDate) and the next occurs on 11/11/11 (SiteSecondActivityDate) in the same site.

    I'm currently using separate reports

    Report 1
    SiteName (text)
    SiteAddress (text)
    SiteLeaseExpiry (date/time)
    SiteStatus (number - combo box)
    SiteFitOutType (number - combo box)
    SiteScheduleDate (date/time)

    Report 2
    SiteName (text)
    SiteAddress (text)
    SiteLeaseExpiry (date/time)
    SiteStatus (number - combo box)
    SiteFitOutType (number - combo box)
    SiteSecondActivity (number - combo box)
    SiteSecondActivityDate (date/time)


    - All of this data is stored in that same table (tblSchedule)

    What I'm hoping to do is merge the two reports together and have a single report that will replace the fields highlighted red with the fields highlighted blue once the SiteScheduleDate has passed or SiteStatus has been changed to 'completed'.

    And to make things more difficult I know next to nothing about coding, I'm assuming this will need some code. I will be doing a course in VBA but not until early next year. So any help will be greatly appreciated as you will make me look like a god in my bosses eyes.

    I hope that make sense and thanks for your time.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Have you looked at Access report Grouping and Sorting functionality? I think it was a little harder to understand in 2003 than 2007/2010 but it might be what you need to do. Build group sections in the report. Access Help has guidelines on this. Google: Access 2003 report grouping
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Rubijon is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Oct 2011
    Posts
    5
    Quote Originally Posted by June7 View Post
    Have you looked at Access report Grouping and Sorting functionality? I think it was a little harder to understand in 2003 than 2007/2010 but it might be what you need to do. Build group sections in the report. Access Help has guidelines on this. Google: Access 2003 report grouping
    Thanks for the reply June.

    I had a look at the grouping and sorting function which I couldn't really get to work the way I wanted.

    However, I think I over complicated things by thinking code was needed. I tried an IIf statement which works a treat:

    =IIf([SiteScheduleDate]<=Date(),[SiteSecondActivityDate],[SiteScheduleDate])

    And so on for the other fields. I do have another question though, I currently use the following IIf statement to work out the financial year and quarter for SiteScheduleDate & SiteSecondActivityDate:

    =IIf(Month([SiteScheduleDate])>6,Year([SiteScheduleDate])+1,Year([SiteScheduleDate])) & " / " & IIf(Month([SiteScheduleDate])<=3,"3rd",IIf(Month([SiteScheduleDate])<=6,"4th",IIf(Month([SiteScheduleDate])<=9,"1st",IIf(Month([SiteScheduleDate])<=12,"2nd"))))

    How would I go about incorporating the FY/QTR statement to =IIf([SiteScheduleDate]<=Date(),[SiteSecondActivityDate],[SiteScheduleDate]) to reflect which date shows on the report? I've tried all the things I can think of (which is very limited), but can't get it to work.

    Any help appreciated.

    Thanks.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You need to test if SiteScheduleDate is in the same year/qtr as current date? Do the same year/qtr calculation for current date in another field. That will give the SiteScheduleDate year/qtr and the current date year/qtr to compare.

    Or do the current year/qtr calculation in Criteria under the SiteScheduleDate year/qtr calculated field.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Rubijon is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Oct 2011
    Posts
    5
    I got it working.

    Thanks for your help June.

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

Similar Threads

  1. Query reporting help
    By kwooten in forum Queries
    Replies: 5
    Last Post: 06-10-2011, 07:03 AM
  2. Exception Reporting
    By shexe in forum Queries
    Replies: 16
    Last Post: 09-09-2010, 09:14 AM
  3. Reporting Issue
    By watzmann in forum Access
    Replies: 1
    Last Post: 08-30-2010, 08:16 PM
  4. Question on Reporting
    By jbarrum in forum Access
    Replies: 20
    Last Post: 01-14-2010, 02:05 PM
  5. Dynamic reporting
    By pushpm in forum Programming
    Replies: 0
    Last Post: 04-22-2009, 12:45 PM

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