Page 2 of 2 FirstFirst 12
Results 16 to 27 of 27
  1. #16
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737

    Yikes! I'll have to pay more attention to the post dates. Too bad there wasn't a thread icon in the list to flag ones that are, say, 6 months old.

  2. #17
    Broes is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    10
    Quote Originally Posted by Micron View Post
    I have some experience with equipment reliability and maintenance scheduling, so I'm not sure what you mean by "track the schedule" but I take it you're not looking for a way to announce due dates for maintenance or record details of it. What about an Access based Outlook type of calendar? I have a copy of one which I think I got from this forum but I haven't played with it much. I know it can hold more than one appointment on the same day, but not sure it would hold 50 if you're looking to do that for a number of machines.

    As for post #9 where you mention a zero problem, I'd say it's because you're trying to mix text ("0") with numbers in your use of DatePart.
    Yes, mixing text with numbers that was something I was thinking of but as said I have trouble with finding a solution for putting it in another (the right) way.

    What I am trying to archieve is a report for a lot of projects with the original schedule for each project and the next thing is to put the actual progress on the second line (more details). Ideally I can choose my report period and the columnheads (weeknumbers) adapt to this. Something like in the attachement.
    I am aware that Access is not a scheduling programm and that there are software products developed for this purpose but I have al my project data in an Access database and I want to keep it there.
    I am not sure if an Outlook type of calender is suitable for this kind of job but of course I want to give it a try/search.
    Attached Thumbnails Attached Thumbnails ProjectSchedule.PNG  

  3. #18
    Broes is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    10
    Quote Originally Posted by ssanfu View Post
    Just for the sake of etiquette....

    @Broes,
    What you have done by posting in this thread is called "Hi-jacking a thread".
    This is considered bad for two reasons:
    1) You are asking a thread in someone else's thread.... and this thread is almost 2 years old!
    2) You will get more views and possible responses if it is your own thread and not someone else's

    It is better to start a new thread and reference a thread that is close to your question.
    Something like:
    "I have a problem that is something like this thread https://www.accessforums.net/showthread.php?t=18459&p=345889#post345889

    How can I .........."



    Because it is a new thread, you get more reads than hi-jacking a thread - especially if the old thread is marked solved.. (I know this thread was not marked as such)


    Waiting to see what solution you get....
    Sorry I don't want to Hi-Jack anything but as the thread was still open and very old and I didn't have to change the subject and my question was very similar to the starters question off this thread, it was very inviting for me to jump right in
    But thanks for your comment, next time I will start a new thread.

  4. #19
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    When you create a crosstab query you are almost always going to end up with a variable number of columns.

    Let's say in january you see 3 customers and you run a crosstab query where you see each customer's sales in a separate column you would have something like:

    Category Customer A Customer B CustomerC
    Hardware 500 1000 1500
    Software 2000 1500 500
    Services 4500 3000 2500

    The following month you see 4 customers you would have something like:

    Category Customer D Customer E Customer F Customer G
    Hardware 400 1200 1600 2000
    Software 5000 4500 4000 3500
    Services 2000 3000 4000 3000


    You built your original report for a specific time period, and bound all your fields to a specific column name. For example if my test crosstab produced the first data set above, and I created a report based on the fields 'Customer A', 'Customer B' and 'Customer C' when I ran the report for the following month none of the columns are named the same except for 'category' so it produces the error you are encountering.

    The reason my example in the busdriver.zip example file works is because it has the same column names month after month after month, namely the numeric value for the day of the month.

    if you want to build a report that dynamically works with crosstab queries you would have to do a lot more coding/setup on the report as with the example database I wrote here:

    https://www.accessforums.net/showthread.php?t=45032

  5. #20
    Broes is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    10
    Thanks, you're right, I have to find a way to to change the column names if I want to choose different time periods.
    I already had a look at your crosstab query and I have to dive further into it.
    But first I still have to solve the issue with the leading zero; filling "tblReportSchedule".
    So where is it going wrong in this peace of code?


    Code:
    sSQL = sSQL & "SELECT [tblProjecten]![Nr], iif(#" & dCurrDate & "# between [startdate] and [enddate], [offtimeid],null) as Expr2, '" & Right("0" & DatePart("ww", dCurrDate, 2, 2), 2) & DatePart("yyyy", dCurrDate, 2, 2) & "' AS DateOff "
        sSQL = sSQL & "FROM tblProjecten "

  6. #21
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You have a number of problems to overcome.

    1. Change your code setting up tblReportSetup to put the year first, then the month, this will allow you to sort properly in your column headers
    2. Change the same code to use the PROJECTID not NR
    3. If you want this to be usable format your column headers to be the week number, not the entire date string
    4. Alter your generated SQL code so you don't create duplicate records. You are linking to tblprojecten unnecessarily when building this query.
    5. Remember conditional formatting can only do 3 layers, in one of your posts you had 5 or 6 colors, if you're going that deep on your coloring you'll have to be more tricky than this example.

    This example is set up to use a dynamic (column headers change) crosstab query using the example I linked to earlier in this thread

    See example:

    ProjectenSchedule.zip

  7. #22
    Broes is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    10

    Thumbs up

    Amazing
    This is what I was looking for!

    I encountered two issues when I was playing with your example database.
    The first one (red in picture) shows week 26 for ScheduleID 18 in tblReportSetup but StartDate and EndDate in tblSchedule are lying in week 6 and 8.
    The second one is the shift in week nummers (green in picture), this is the case for all records in tblReportSetup, it looks like SchedWeek is 1 to high?
    Hopefully you can help me out with this.


    Click image for larger version. 

Name:	ProjectenScheduleIssue.png 
Views:	145 
Size:	94.6 KB 
ID:	27512

  8. #23
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I did not modify your week calculation. Your process is returning week 52 for CY 2016 for the 1/1/2017 date, that is your schedweek 1. Everything after that is incremented by one per week. Unless you see a variation of more than 1 week from what you would expect this is working as intended. Your other option is to modify your week calculation to be weeks from the inception of the program as opposed to the week of the year, that would give you a week span that always starts at 1.

  9. #24
    Broes is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    10
    It took me a quite a while but I think I have discovered how your code is doing the trick
    But after a lot of trial on errors I still don't get it why I am keep getting "wrong" week or no week numbers in in tblReportSetup and that this table doesn't seem to correspondent with the report. So hopefully you will have time to have another look at my database.Click image for larger version. 

Name:	ProjectSchedule.png 
Views:	130 
Size:	64.0 KB 
ID:	27885
    Attached Files Attached Files

  10. #25
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I ran the database for the same date range in your screen shot and this is what I got:

    Click image for larger version. 

Name:	Untitled.png 
Views:	131 
Size:	37.8 KB 
ID:	27887

    which doesn't match what you posted.

  11. #26
    Broes is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    10
    Strange I can't explain it, I tried this on two different PCs but same result as in post #24
    But apart from that, didn't you expect that week 4 should be filled in the report?

    Edit: if I change my format in Region and Language Setting from "Dutch (Netherlands)" into "English (United States)" it works out the right way . Not the desired solution but it explains the difference.

  12. #27
    Broes is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    10
    Took me some time but thanks to rpeare I am getting there
    I made some modifications, put things in took things out.
    Attached is the new database.

    Still some issues to solve;
    I have to change my format in Region and Language Setting from "Dutch (Netherlands)" into "English (United States)" everytime and of course I want to keep it on the Dutch setting.
    I haven't got a clue how to change this.

    The grouping in the "frmScheduleMilestonesReportSetup" is commented out because it generates an error.

    Nice to haves;
    It would be nice if I could create a report with 3 months in the past and 9 months ahead.

    Coloring the column or columnhead of the current week in the report.

    Sometimes I want fill in other milestones via the "sfrmtblMilestones" in the form "frmProject" that are not defined in "tblmilestoneType".
    But I think that will be a difficult thing to do.

    So again if someone can point me in the right direction? Any help will be greatly appreciated.
    Every comment is more than welcome.


    Attached Thumbnails Attached Thumbnails SchedProjecten20170509.jpg  
    Attached Files Attached Files
    Last edited by Broes; 05-09-2017 at 02:49 PM. Reason: Format problem

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. scheduling appointments
    By mikejames in forum Programming
    Replies: 1
    Last Post: 09-30-2011, 04:45 PM
  2. Replies: 3
    Last Post: 04-28-2011, 10:46 PM
  3. Scheduling Problem
    By Marie in forum Queries
    Replies: 2
    Last Post: 01-19-2011, 09:10 PM
  4. scheduling db
    By JSHEL in forum Database Design
    Replies: 1
    Last Post: 12-12-2010, 09:28 AM
  5. Scheduling database in Access
    By ACS Newbie in forum Access
    Replies: 4
    Last Post: 01-21-2010, 10:32 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