Results 1 to 7 of 7
  1. #1
    Brightspark98 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    57

    Best way to input data to table

    Hi,
    Can someone point me in the best direction to go for entering data to a table - the image is from excel.
    The user performs each activity for 3 or 4 weeks and then changes activity.
    I want to be able to update the table for the 4 week period.

    i.e. user 'A' does activity 'Y' on weeks 9,10,11,12.

    This is just a small sample, there are over 140 users on the full table.

    I was also going to incorporate a crosstab query in the form with ACTIVITY as Row Headings, USER as Column Headings and WEEK as the lookup to track which User has completed each activity.


    Many Thanks.
    Attached Thumbnails Attached Thumbnails taBLE.png  

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you can keep the format you gave. A form in datasheet mode will allow users to input data just like excel.

    and yes, crosstab query is the way to produce the report on each individual.

  3. #3
    Brightspark98 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    57
    Thanks,

    Is there a way to input the 4 week activities for each user at once rather than having to do each week individually. The schedule is planned 2 months in advance.
    Would it be better to set the table with entries 1-52 for 'user A', and the subsequent 52 entries for 'user B' etc.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if the weeks are contiguous you could use a form to enter

    User...Activity..FromWeek...ToWeek
    A........Y..........9.................12

    then some vba code to update the table

    for I=fromweek to toweek
    currentdb.execute("INSERT INTO myTable (User, Activity, Week) Values('" & user & "', '" & activity & "', " & I)
    next i

  5. #5
    Brightspark98 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    57
    Thanks for all of your help guys.

    I think I have a good image of where I need to go now.

    No doubt
    'I'll be back'

  6. #6
    Brightspark98 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    57
    I'm Back!

    Just had a thought,

    If I wanted match 10 users to each activity for the same period. Could I use a listbox with checkbox to make the user selection and then use combobox for activity & combo or date pickers for the training period combined with the code you have provided?

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    don't see why not. Depending on the number of employees - say less that 200, use a multiselect listbox to choose your employees, then loop through the list to do the updates

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

Similar Threads

  1. Replies: 5
    Last Post: 12-01-2015, 02:22 PM
  2. Replies: 1
    Last Post: 12-05-2014, 11:12 AM
  3. Command button to input data in a table
    By wilsgaard in forum Forms
    Replies: 1
    Last Post: 08-07-2011, 07:59 PM
  4. Replies: 4
    Last Post: 07-17-2011, 06:07 PM
  5. Replies: 3
    Last Post: 07-13-2011, 08:01 AM

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