none
I don't even know how to title this RRS feed

  • Question

  • Hi

    I have a database that records a very large amount of data relating to the specification of vehicles we build.  Part of that data is the amount of labour hours for the standard product and then the additional hours for optional extra equipment.  This is then populated in 2 different reports - the reports differ only in the way they are grouped - they are grouped on their forecast completion date - one is by week and the other is by month.

    Production have asked if the report can show how many available hours there are each week.  This changes every week due to holidays, etc. I really cannot think of the best way to do this and would ask for ideas please.  I'm not asking for you guys to do it for me, but just help me with a head start on some ideas.

    Thanks very much.


    Chris | UK

    Monday, October 14, 2019 12:20 PM

Answers

  • This changes every week due to holidays, etc. I really cannot think of the best way to do this and would ask for ideas please.

    Hi Chris,

    One way or the other you have to define when is worked and when not.

    I had the best results with a table in which you specify how many hours have been worked for each day. For holidays and weekends this would normally be 0. When extending the table, you can default for weekends and holidays.

    With the hours per day you can account for extra work on a day or in the weekend, half day close because of personnel party, management meeting or calamity, etc. You even can extend it to shifts.

    Imb.

    • Marked as answer by ChrisParkin Tuesday, October 22, 2019 7:49 AM
    Monday, October 14, 2019 6:04 PM
  • You need to sum the hours to be worked over each week by all relevant employees.  For this you can firstly create a calendar table of all working days, excluding public and concessionary holidays.  You can then relate this to a table of employees by means of a table which models the binary relationship type between the calendar and employees table.  In addition to the foreign keys referencing the primary keys of the calendar and employees tables, if the hours worked per employee per day can vary, this table would include a non-key column of the hours to be worked by the employee on each day.  Otherwise the number of hours worked  each day by all employees can be a column in the calendar table, with the default value being the length of a standard day, thus allowing for variations of the length of individual days if necessary.

    It then becomes a simple matter of joining the tables in a query restricted to those employees relevant to the work in question, grouping the query by week and summing the hours.

    For means of creating a working days calendar table and excluding public/concessionary holidays from it see Calendar.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    Ken Sheridan, Stafford, England

    • Marked as answer by ChrisParkin Tuesday, October 22, 2019 7:49 AM
    Monday, October 14, 2019 10:57 PM

All replies

  • Hello Friend,

    Knowing in advance when the holidays occur I believe it is possible to filter these dates in the query generating query not?
    Delete the date from the report.

    If this answer has helped you, mark it helpful so that someone else with a question or similar problem can find an answer or help more easily. Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Database Analyst - Sql Server and Oracle]


    Monday, October 14, 2019 1:22 PM
  • You will probably need to create a Holidays table (or maybe a WorkDateExemptions table) with dates. Once you know which dates are holidays (or exempted from work dates), you can then filter forms and reports so they do not include those dates. But whatever you call the table, ACCESS needs to know which dates should be excluded from working hours.
    Monday, October 14, 2019 1:43 PM
  • For the title you could say something such as Querying for available hours in a week.

    First you need to define what is meant by available hours. The assumption is that a week is always 5 days minus the number of holidays. If there is a variation in that then that needs to be defined. Also if a day is ever different from 8 hours then that needs to be defined.

    Next you need to determine how your system defines holidays and any other relevant data. If there is a table of holidays or anything like that then it helps to say so here.

    The quality of the help here depends on the quality of the information provided in the question.



    Sam Hobbs
    SimpleSamples.Info

    Monday, October 14, 2019 3:31 PM
  • This changes every week due to holidays, etc. I really cannot think of the best way to do this and would ask for ideas please.

    Hi Chris,

    One way or the other you have to define when is worked and when not.

    I had the best results with a table in which you specify how many hours have been worked for each day. For holidays and weekends this would normally be 0. When extending the table, you can default for weekends and holidays.

    With the hours per day you can account for extra work on a day or in the weekend, half day close because of personnel party, management meeting or calamity, etc. You even can extend it to shifts.

    Imb.

    • Marked as answer by ChrisParkin Tuesday, October 22, 2019 7:49 AM
    Monday, October 14, 2019 6:04 PM
  • You need to sum the hours to be worked over each week by all relevant employees.  For this you can firstly create a calendar table of all working days, excluding public and concessionary holidays.  You can then relate this to a table of employees by means of a table which models the binary relationship type between the calendar and employees table.  In addition to the foreign keys referencing the primary keys of the calendar and employees tables, if the hours worked per employee per day can vary, this table would include a non-key column of the hours to be worked by the employee on each day.  Otherwise the number of hours worked  each day by all employees can be a column in the calendar table, with the default value being the length of a standard day, thus allowing for variations of the length of individual days if necessary.

    It then becomes a simple matter of joining the tables in a query restricted to those employees relevant to the work in question, grouping the query by week and summing the hours.

    For means of creating a working days calendar table and excluding public/concessionary holidays from it see Calendar.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    Ken Sheridan, Stafford, England

    • Marked as answer by ChrisParkin Tuesday, October 22, 2019 7:49 AM
    Monday, October 14, 2019 10:57 PM
  • Hi all

    Many thanks for the responses.  I'm getting the idea now and think I can work with what you've said.  IMB, I like your suggestions and Ken, as always, providing the example is always a massive help.  It'll be the daily calendar and I may try and play with your code so that I can exclude weekends from this as you done in your Calendar of Regular Dates.

    Thanks again.


    Chris | UK

    Tuesday, October 15, 2019 8:52 AM
  • It'll be the daily calendar and I may try and play with your code so that I can exclude weekends from this as you done in your Calendar of Regular Dates.
    My MakeCalendar_DAO function already does this by means of a parameter array passed into the function.  To exclude Saturday and Sunday dates the values for the array would be 2,3,4,5,6.  In the demo the values are selected by means of checkboxes in the form and passed into the function as follows:

                MakeCalendar_DAO _
                    Me.txtTableName, _
                    CDate(Me.txtDateStart), _
                    CDate(Me.txtDateEnd), _
                    IIf(Me.chkSun, 1, Null), _
                    IIf(Me.chkMon, 2, Null), _
                    IIf(Me.chkTue, 3, Null), _
                    IIf(Me.chkWed, 4, Null), _
                    IIf(Me.chkThu, 5, Null), _
                    IIf(Me.chkFri, 6, Null), _
                    IIf(Me.chkSat, 7, Null)


    Ken Sheridan, Stafford, England

    Tuesday, October 15, 2019 11:04 AM
  • Yeah, sorry about that Ken.  I realised that after I'd hit submit on my reply.  I just had to be patient and wait for the inevitable response confirming I'm an idiot!

    Thanks again for your help.


    Chris | UK

    Tuesday, October 15, 2019 11:08 AM