none
how to create this report ?!! RRS feed

  • Question

  • hello All

    i would like to create a database for employee absence schedule ( this is a template come with ms excel )!, i would like to create a report as shown in the screen shoot below :

    regards

    Thursday, April 21, 2016 5:46 AM

Answers

  • You could certainly create the table and re-produce such a report. However, a database is not a spreadsheet, and you likely gain little (if anything) unless you normalize your data.

    >put a button when I press it print a report same format.

    Well, the first problem is you need to setup the date range you desire for that report. It could certain default to this month/year. You thus need code to pull the data for the given date range.

    Due to above requirements, you likely have to pre-process the data (the given date range) and send the data out to a temp table. That temp table can be laid out similar to your given screen shot. Not every month has 29 days, so you need to (likely) write some code to fill the date values at the top of the report to only show the given number of days etc. (so code to get the start date and end date of the given month will be required).

    The above is all workable, but you need to write code to pull the  data from a normalized table (employee, AbsenceDate, AbsenceCode) into a cross tab format that goes across as opposed to rows going down (which is the format of a typical database).

    You thus can reproduce a report with very much the same look as your current report. You also want a means to select the month (and year) for the report (a form with text boxes, or drop downs to select would achieve this part).

    With normalized data, your data entry becomes simple, but the report side becomes a challenge. Ultimately, this would allow you to launch a report for any month (or even year) to display. And such a system would multi-user and allow reports or data entry from any computer on your network.

    So the starting point will be a query that selects (pulls) the correct data based on the given month/year date range. With that data, you then pre-process that selected data to the temp table which is in a cross tab like format.

    Your VBA code will also have to fill in the “date” text boxes, and also that of the weekday text boxes (makes no sense to hard code those text boxes). So the two rows (date and weekday) text boxes can be un-bound.

    All of the above is quite easy for an experienced developer, but a challenge if you not written such code and reports in Access.

    You have (as the other post suggests) start with the VBA reference.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Saturday, April 23, 2016 6:37 PM

All replies

  • Hi, Siyamand

    According to your description, if you have created Access database, you could export data from an Access database to in a file format that can be read by Excel. 

    For more information, click here to refer about Export data to Excel

    In addition you could use VBA code to create Excel file or export data to Excel file.

    You could start with Access VBA reference

    Friday, April 22, 2016 2:29 AM
  • thanks for your reply

    but I think you misunderstood me !,

    I don't want to export to excel , there is a template as attached screen shoot in excel and I want to create same thing in MS Access, I would like to have a form to put the name of the employee , date and type of absence ... and put a button when I press it print a report same format and design that I put the screen shoot in my original post.

    thanks again.

    Friday, April 22, 2016 7:06 PM
  • You could certainly create the table and re-produce such a report. However, a database is not a spreadsheet, and you likely gain little (if anything) unless you normalize your data.

    >put a button when I press it print a report same format.

    Well, the first problem is you need to setup the date range you desire for that report. It could certain default to this month/year. You thus need code to pull the data for the given date range.

    Due to above requirements, you likely have to pre-process the data (the given date range) and send the data out to a temp table. That temp table can be laid out similar to your given screen shot. Not every month has 29 days, so you need to (likely) write some code to fill the date values at the top of the report to only show the given number of days etc. (so code to get the start date and end date of the given month will be required).

    The above is all workable, but you need to write code to pull the  data from a normalized table (employee, AbsenceDate, AbsenceCode) into a cross tab format that goes across as opposed to rows going down (which is the format of a typical database).

    You thus can reproduce a report with very much the same look as your current report. You also want a means to select the month (and year) for the report (a form with text boxes, or drop downs to select would achieve this part).

    With normalized data, your data entry becomes simple, but the report side becomes a challenge. Ultimately, this would allow you to launch a report for any month (or even year) to display. And such a system would multi-user and allow reports or data entry from any computer on your network.

    So the starting point will be a query that selects (pulls) the correct data based on the given month/year date range. With that data, you then pre-process that selected data to the temp table which is in a cross tab like format.

    Your VBA code will also have to fill in the “date” text boxes, and also that of the weekday text boxes (makes no sense to hard code those text boxes). So the two rows (date and weekday) text boxes can be un-bound.

    All of the above is quite easy for an experienced developer, but a challenge if you not written such code and reports in Access.

    You have (as the other post suggests) start with the VBA reference.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Saturday, April 23, 2016 6:37 PM
  • thanks alot for your reply

    i think this will not be easy job

    any way thanks alot

    Sunday, April 24, 2016 7:50 AM
  • Your wish is not a turn-key solution. There is no button to press and voilà, there's the Access Form. You need to build the Access Form the way you want it to be and bind the different entries to table entries in tables designed by you.

    Excel is made to make it simple but you cannot extend Excel to infinity. When complexity increases you sooner or later need to switch to another solution, maybe and often, Access.

    However, creating a report like your example is quite possible in Excel but with increasing number of employees and increasing number of years, special cases etc. it will not be easily managed.
    Where the limit is, only you can tell.


    Best regards, George

    Sunday, April 24, 2016 11:43 AM