Results 1 to 6 of 6
  1. #1
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164

    Convert an Excel Holiday Tracker to an Access Database with good table design and forms

    Click image for larger version. 
<br /><script async src=
    Name: Spreadsheet to Access Database Table and Forms.jpg  Views: 17  Size: 134.7 KB  ID: 20054" class="thumbnail" style="float:CONFIG" />

    Hi All,

    I would like to convert this excel spread sheet to an access database, At the moment there is a tab for each employee using the same template on the attached screenshot.

    How would I convert this into a normalised Access Database, Which and how many tables would I need, IE Employee table with EmpID, HolidayBooking table etc

    Would the structure be something like a Holiday booking table, Fields of: BookingID, EmpID, Holiday 1, Holiday 2, Holiday3 etc. and a Staff Id table of names, Basic holidays per year information and job role.

    I have played around with it but I can't seem to get the right logic flowing.

    Ideally I would like the user form to look similar to the spread sheet version and also print off an report of an similar design.

    I have a reasonable level of knowledge of access reports, Table,Macros and queries.

    It's the Table foundation structure I'm not sure which way to approach it

    Any help would be useful.


    Thanks

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    honestly to me it would be just:
    key (auto number)
    Employee (look up field to Employee table)
    Date

    that's it... I suppose you could add a comment, or holiday name or perhaps StaffID if you need to record who made the entry - but those fields are peripheral...

    everything involving counts of dates taken or controlling them from taking too many or wrong days - all that occurs as part of the form user interface and are not table issues.....

  3. #3
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164
    I have opted for this as the tables and relationship.
    What do you think?

    Thanks for your helpClick image for larger version. 

Name:	uploadfromtaptalk1426528645949.jpg 
Views:	12 
Size:	114.0 KB 
ID:	20067

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would combine the tables "tblAccruedDil" and tblUsedDil" with a field for Accrued or Used.
    You are using data as field names: NewYear, G Friday, etc ...... this is a bad design.

    Consider
    tblHolidays
    ----------
    HolidayID_PK Autonumber
    StaffID_FK Long
    HolidayDesc Text ("New Year", "Good Friday", etc)
    HolidayDate Date/Time (1/1/2015)
    AccruedUsed Text (Accrued or Used)

  5. #5
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Look at this link for an example of Employee Leave. It might help

    http://www.databaseanswers.org/data_...ping/index.htm

  6. #6
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164
    Thanks for your help and answer, Much appreciated!

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

Similar Threads

  1. Want to know if this looks like a good database design
    By tmcrouse in forum Sample Databases
    Replies: 7
    Last Post: 09-28-2014, 08:46 PM
  2. Replies: 1
    Last Post: 04-01-2014, 10:54 AM
  3. Is this a good database design?
    By Someday in forum Database Design
    Replies: 4
    Last Post: 07-22-2012, 06:50 PM
  4. Payslip Holiday Pay design
    By crxftw in forum Access
    Replies: 5
    Last Post: 07-22-2011, 11:58 AM
  5. Replies: 6
    Last Post: 08-04-2010, 01:16 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