Results 1 to 9 of 9
  1. #1
    Steven.Allman is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2010
    Posts
    118

    Update Query to update a query

    OHH yeah, you read that correctly.


    So, as a stop gap I built a quick Sharepoint List to track attendance for approximately 2400 employees. Well, 9 months later they havent gotten a smarter person to do this.
    Keep in mind I am military and this tracks specifics on personnel location so I cannot share the actual DB with anyone.
    Ill try to explain what I am doing....
    I have 31 fields, (1,2,3.....31) one for each day of the month.
    Their are 15 options for each day on what the duty status is.
    I have queries built for daily reports I do for the leaders that lists how many people are at work, how many are teleworking, how many are on leave, etc etc etc.
    Problem is I modify my queries EVERY DAY.
    Tomorrow morning I will modify 6 queries and change [19th] to [20th].
    Is there a query I could build, like a macro almost, that will update a query field.....
    Im thinking I open with a form that has a simple drop down with 1,2,3....31 and if I select 20 and hit enter, then THAT initiates an update of all queries and
    SELECT [PerStat v1].[19th]
    becomes
    SELECT [PerStat v1].[20th]

    As I type this, i am thinking its probably much simpler than I think...


    PLEASE understand, this was a 1 hour process to build initially, and was again supposed to be temporary. For 150 personnel to be making changes to their personnel daily, giving them an access database front end would have been a nightmare. People dont know and are afraid of Access. so a simple website where they sort their folks and select a drop down was a MUCH better option from my end for the short term, which has now become the standard. IF you want to sharpshoot my methodology, please feel free to provide me a better solution or point me in the right direction as well.

  2. #2
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I have 31 fields, (1,2,3.....31) one for each day of the month.
    Generally when we see repeating fields such as this it is a red flag that your data is not normalized.
    you are storing data as fields.

    Can you share what your tables and fields look like?
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  3. #3
    Steven.Allman is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2010
    Posts
    118
    Database2.accdb
    This has 30 some odd generic records.
    So visualize as you look at this.
    FIELD name IS the date...
    I have 150 users of every skills level inputting via Sharepoint, NOT an access database.
    Access uses a linked table to the sharepoint for me to retrieve and create my reports.

  4. #4
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I've never used sharepoint so I really dont know what the differences may be.

    As far as an access database goes it has flaws.
    Repeating fields, data stored as a field.
    Lookup fields
    attachment field, although i dont know its impact in sharepoint.

    I would have at least 3 tables
    staff - PKey,name,rank,etc
    Statuses- a lookup table with PKey and a status
    DailyStatus = PKey,Staff(FK),Status(FK),Date

    Then you would only need to pull records with one query using a where clause of
    "where dteDate = " & Date()
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  5. #5
    Steven.Allman is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2010
    Posts
    118
    Quote Originally Posted by moke123 View Post
    I've never used sharepoint so I really dont know what the differences may be.

    As far as an access database goes it has flaws.
    Repeating fields, data stored as a field.
    Lookup fields
    attachment field, although i dont know its impact in sharepoint.

    I would have at least 3 tables
    staff - PKey,name,rank,etc
    Statuses- a lookup table with PKey and a status
    DailyStatus = PKey,Staff(FK),Status(FK),Date

    Then you would only need to pull records with one query using a where clause of
    "where dteDate = " & Date()
    Im not sure how that would help me.
    Do you see the intent?
    2400 employees. 31 days.
    End result, I need to know where all 2400 were every day of the year.
    And multiple tables will not work, because its a SharePoint list. Not even sure how that would help.

    So give me an example of how I would store YOUR status on June 21st, and My status on June 20th and his status on June 19th. im not storing the date as a field. The status ON a date is a field.

    Using your method, each employee would have 31 "records" at the end of the month, and I get that concept as well, but it wouldnt work for 150 users of different skkill levels on the end user piece.

    Really wish you could add a DB larger than 400K on here, I could show you what I pull/report etc.

  6. #6
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    To me it looks like your using a spreadsheet.

    As I said I know nothing about sharepoint and dont understand why it wouldnt work for 150 users of different skill levels on the end user piece.
    I'll take your word for it.
    In access this looks like a simple small db to me.

    Perhaps someone who knows sharepoint will chime in.

    Good luck with your project.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  7. #7
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    So give me an example of how I would store YOUR status on June 21st, and My status on June 20th and his status on June 19th. im not storing the date as a field. The status ON a date is a field.
    What I'm talking about would look more like the attached example.
    Bear in mind I didnt add any error handling or data validation.
    As you can see having normalized data makes it simple to make dynamic queries.

    Also you can post larger DB's if you Compact and add to a zip file
    My attached file is 636kb and zipped its 39kb
    Attached Files Attached Files
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  8. #8
    Steven.Allman is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2010
    Posts
    118
    So imagine this... I dont want this to be an expression; but I want it to actually ask me when I run the query/report WHAT FIELD NAME...
    So in this query i have a sample of below, I am asking, "Who didnt input their attendance today?' So today, when i run that report, i want it to ask me "What day" and I enter 20th, which isnt a criteria for the field, but is ACTUALLY the field..

    SELECT [PerStat v1].[What Field Name], [PerStat v1].NAME, [PerStat v1].RANK
    FROM [PerStat v1]
    WHERE ((([PerStat v1].[What Field Name]) Is Null));

  9. #9
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    End result, I need to know where all 2400 were every day of the year.
    so after 31 days how do you know what month it is?
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

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

Similar Threads

  1. Replies: 2
    Last Post: 08-03-2017, 04:58 AM
  2. Replies: 1
    Last Post: 10-27-2016, 12:14 PM
  3. Query Update/Select Query (Wont Update)
    By NickWren in forum Access
    Replies: 1
    Last Post: 03-30-2016, 10:41 AM
  4. Replies: 7
    Last Post: 08-26-2013, 06:05 AM
  5. Replies: 2
    Last Post: 08-30-2012, 07:59 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