Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    riley23258 is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2022
    Posts
    8

    Find records with value in multiple columns of a table

    Table shown below. Need to setup a query to prompt a name input and have it search multiple columns (Op1, Op2, Op3). I'm trying to identify a task list by person and sometimes the individual's name can be as the primary operator (Op1) or the secondary, tertiary, etc.
    Attached Thumbnails Attached Thumbnails TasksTbl.jpg  

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Are you new to database? What exactly does your table represent?

  3. #3
    riley23258 is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2022
    Posts
    8
    Yes, fairly new. I actually figured out my first problem with an SQL statement



    Now I just need to be able to sum all of the minutes and hours per person.

  4. #4
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    You have what is known as Repeating Groups

    Your field names OP1, OP2, Op3, OP4 are fields in this table but they should be Records in a separate table.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As mike mentioned, your "table" is not designed as a normalized table structure. It appears to be something you might have been doing in Excel and transferred to Access.
    From my understanding/interpretation of your column names you have included a number of "subjects" into your single table.

    -work/task/activity --- based on description
    -OP1, OP2, Op3, OP4 ---is repeating (could be options for who could do/did the work)
    -seq_id repeats existing fields --station and sequence (no need for duplication/dependency)


    Again, we could help with design if you provided a description of the "business/processes" this database is intended to support.

    If this is an ongoing database to support your business, then getting it designed for database as soon as possible is key. If it's a one time exercise, then you'll be dealing with some "curious workarounds" to get the result(s) you seek.

  6. #6
    riley23258 is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2022
    Posts
    8
    Thanks for the responses.

    Yes, this was an excel sheet passed on by a client. The goal of the database is to generate a report that allows the user to enter an employee name and see their assigned task lists as well as the sum of the time in minutes to complete.

    Additionally, would like to show a utilization report which would show each employee, their total time dedicated to tasks and then their utilization % based on a set available time worked per day.

    OP1, OP2, OP3, etc. are the identified employees that complete each of the tasks.

  7. #7
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi
    Can you upload a copy of the database?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  8. #8
    riley23258 is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2022
    Posts
    8
    I've attached the dB with just the table and query I have as of now.
    Attached Files Attached Files

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Would be better to normalize your DB first, then it would be very very easy.
    Is you DB going to replace the Excel workbook?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Can you explain what the 1 table represents?

    You said An Employee has Assigned Tasks.

    Can you explain the whole process in more detail?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    A quick look at the table and your posts suggests:

    -you have employees
    -your business involves performing some tasks
    -tasks appear to involve carpentry, plumbing, electrical, flooring, HVAC, general maintenance, inspections, ....
    -you Sequence could imply the order in which a specific task is done (could be steps/subtasks..)
    -you may have an estimated time to complete a task/subtask, but this is not shown directly
    -- you have 171 distinct tasks/subtasks in your table
    [SELECT distinct tbl_FinalSeq.Description
    FROM tbl_FinalSeq;]
    -tasks appear to have an overarching, customized categorizing technique(Category)
    -tasks have a variation in spellings in their descriptions (PTAC, P-TAC,comnplete,complete, caulking/chaulking..)
    -Sequence does not determine Description (different D04 have different description)
    -- you do NOT show any Customers (at least not directly)
    -it is unclear what Units represents
    -Trade is only populated for some tasks(unclear why this is)
    -you are using minutes, seconds and hours for some aspect of "time used" or some other metric??

    A guess as to the industry: you could be building mobile homes for general sale, or a series of similar homes in a residential area

    You have a number of entities and for normalized database this would mean a number of related tables.

    In order to assist you with design, readers need a description of your business at the "30000 ft) level. And increasingly more detailed descriptions of the various tasks. Best to describe these using a who, what, where, when, how much and how often sort of approach so readers can see things in context.

    The key to database is to get your tables and relationships designed and tested to ensure the structure supports the business.

  12. #12
    riley23258 is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2022
    Posts
    8
    Yes the idea is that the DB would replace the excel.

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    A quick look at the table and your posts suggests:

    -you have employees
    -your business involves performing some tasks
    -tasks appear to involve carpentry, plumbing, electrical, flooring, HVAC, general maintenance, inspections, ....
    -your Sequence could imply the order in which a specific task is done (could be steps/subtasks..)
    -you may have an estimated time to complete a task/subtask, but this is not shown directly
    -- you have 171 distinct tasks/subtasks in your table
    [SELECT distinct tbl_FinalSeq.Description
    FROM tbl_FinalSeq;]
    -tasks appear to have an overarching, customized categorizing technique(Category)
    -tasks have a variation in spellings in their descriptions (PTAC, P-TAC,comnplete,complete, caulking/chaulking..)
    -Sequence does not determine Description (different D04 have different description)
    -- you do NOT show any Customers (at least not directly)
    -it is unclear what Units represents
    -Trade is only populated for some tasks(unclear why this is)
    -you are using minutes, seconds and hours for some aspect of "time used" or some other metric??

    A guess as to the industry: you could be building mobile homes for general sale

    You have a number of entities and for normalized database this would mean a number of related tables.

    In order to assist you with design, readers need a description of your business at the "30000 ft) level. And increasingly more detailed descriptions of the various tasks. Best to describe these using a who, what, where, when, how much and how often sort of approach so readers can see things in context.

    The key to database is to get your tables and relationships designed and tested to ensure the structure supports the business.

    Basic High Level Project Management data model. It may give you an idea of how the pieces could fit together.

    Click image for larger version. 

Name:	GenericProjectConceptualModel.jpg 
Views:	16 
Size:	14.6 KB 
ID:	48097

  14. #14
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Detailed explanation please
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  15. #15
    riley23258 is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2022
    Posts
    8
    Explanation:

    We are trying to build a DB that would help us with assigned task management. This is for a modular housing manufacturer. We have an assembly process with a sequence of tasks. For each task, we've assigned a designated employee(s) to complete those tasks daily. Currently, we are having trouble with articulating task assignments to each individual we wanted to develop a DB that we could generate a report out that would show each individuals assigned tasks.

    I've attached here a reworked DB. It works and both reports work 1) all tasks for all employees and 2) enter employee name and show tasks for that specific individual. There's some additional forms I'd like to add that will allow 1) addition of new employees and assignment to tasks 2) addition/edit of tasks.

    Where I'm struggling is how to organize my tables and data elements to simplify the reporting and form creation.
    Attached Files Attached Files

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 05-21-2017, 05:40 PM
  2. Replies: 3
    Last Post: 08-31-2015, 09:04 AM
  3. Replies: 1
    Last Post: 02-13-2015, 01:56 PM
  4. Replies: 1
    Last Post: 05-14-2012, 04:12 AM
  5. Lookup Columns Multiple Records in report
    By schultzy in forum Reports
    Replies: 1
    Last Post: 01-02-2010, 12:21 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