Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2009
    Posts
    204

    Unanswered: Table limit stopping my progress....Ideas on how to proceed?

    Hello,

    What I am working on now....Where I work we have a point system, where everytime someone is absent or late etc. they receive an attendance point. There is currently a spreadsheet that tracks this, but it's not entirely user friendly. Basically for each user it has 2 cells for every day. I am trying to replicate this in Access, and originally I was thinking putting a 2 fields for each day, so on the page you would see 744 boxes....31 across and 24 down.

    I thought the best way to set this up would have been to put another field in my existing employee table for each day and have the form reflect that, but I ran into a problem when I discovered the 255 field limit on tables.

    I am having difficulty figuring out a work around for this. Does anyone have any ideas?

    Thanks.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Why not make a table with just 4 fields?

    Employee---Text
    Date--------DateTime
    Absent------Yes/No
    Late---------Yes/No
    Inspiration Through Fermentation

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or have 3 tables
    one for employees
    one for attendancecodes (or whatever you decide to call 'em
    one for attendence details (which is an intersection of employee and attendancecode with date

    the reason for the attendance codes is so that if someone decides to extend the system you don't have to chaneg the underlying data

    you coudl extend attendance codes to include say a good/bad flag ie code AAA is a bonus (say stayed late, code zwQ is a bad flag (say late, absent)

    heck you could go even further and add some form of raking or severity.

    you data entry becomes simple you put in the employee ID, the date and select the relevant code

    the reporting/ display becomes ugly if you want some form of composite report day by day, week by week. like most things in the systems world it can be done, but it will nto look nice.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2009
    Posts
    204
    That makes sense, I was thinking against that at first since I thought it would take putting in the employee name again, but a combo box would solve that so I was thinking wrong.

    How would I get ab unbound field on the table with 700-something unbound date boxes to show up with a number on the correct date? That's more or less the format HR wants.

  5. #5
    Join Date
    Oct 2009
    Posts
    204
    Ok I think I got it, I have all the tables and fields set up, I just need to do a dlookup on each date field to return the absent-code...(I think)

    Thanks for your help.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ?
    play to the db strengths
    dont try to cram 700 unbound date boxes.. its a crappy design, it'll blow the limits of Access, which I think will chicken out around 250 odd controls.

    make it simple
    combo box for employee ID
    use a date control to constrain user input to a valid date
    select from, say a combo the reason code, update the table

    then depending on what you need you may want another forms and or reports to summarise the data
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2009
    Posts
    204
    Okay I'm having a problem with my dlookup using multiple criteria.

    Here is what I have:
    Table = tbl_pointassign......Fields: AbsentName, AbsentDate, AbsentCode
    I want the field to return the AbsentCode if the AbsentDate is 1/1/2009 and the AbsentName matches what is in EmployeeCombo in the same form.

    Here is how it is behaving: If I take out the
    And "[AbsentName] = '" & [Forms]![frm_points]![EmployeeCombo] & "'"
    it will give me the desired record, but it's not name-specific. If I use what I have below, it returns, no matter what any criteria is, the first AbsentCode in the table.

    What am I doing wrong?

    =DLookUp("[AbsentCode]","[tbl_pointassign]","[AbsentDate]=#1/1/2009#" And "[AbsentName] = '" & [Forms]![frm_points]![EmployeeCombo] & "'")

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You have some extra quotes in there:

    =DLookUp("[AbsentCode]","[tbl_pointassign]","[AbsentDate]=#1/1/2009#" And "[AbsentName] = '" & [Forms]![frm_points]![EmployeeCombo] & "'")

    If it still doesn't work, make sure the bound column of the combo is returning the name. Often the bound column may be hidden, and will be an employee ID rather than the name.
    Paul

  9. #9
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by pbaldy View Post
    You have some extra quotes in there:

    =DLookUp("[AbsentCode]","[tbl_pointassign]","[AbsentDate]=#1/1/2009#" And "[AbsentName] = '" & [Forms]![frm_points]![EmployeeCombo] & "'")

    If it still doesn't work, make sure the bound column of the combo is returning the name. Often the bound column may be hidden, and will be an employee ID rather than the name.
    That worked perfectly. Thank you!

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problemo!
    Paul

  11. #11
    Join Date
    Oct 2009
    Posts
    340
    just for the sake of clarity to anyone else that might read this string - let's make it clear that the table field maximum (left to right) is 255...

    thus your description: 744 boxes....31 across and 24 down

    this is only 31 fields and 24 records and does not max out an Access table. Access has unlimited records capability until one meets the overall 2G file size limitation.

  12. #12
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by NTC View Post
    just for the sake of clarity to anyone else that might read this string - let's make it clear that the table field maximum (left to right) is 255...

    thus your description: 744 boxes....31 across and 24 down

    this is only 31 fields and 24 records and does not max out an Access table. Access has unlimited records capability until one meets the overall 2G file size limitation.
    Actually it is 744 unbound text boxes that are performing calculations on a form. But that apparently is within size limits for a form, as they are all on there and it is working fine.

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    A lot of calculations to preform and probably wouldn't pass my 3 second max form loading time. I assume you open the form and then 'push' the values to the fields? a lot of dlookups would be a killer.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by pkstormy View Post
    A lot of calculations to preform and probably wouldn't pass my 3 second max form loading time. I assume you open the form and then 'push' the values to the fields? a lot of dlookups would be a killer.
    I'm sorry, I don't know what you mean by "push." The values are there by default when the form opens (which takes a split second), and in the On Dirty of my name combo box I have a refresh. The refresh takes about a second and a half, but that's only since it's on our slow shared drive

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •