Results 1 to 7 of 7
  1. #1
    stephen c is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2009
    Posts
    30

    Selection code/query

    Hello, all! I'm needing to do a big lookup, and am having trouble getting out of my own way.

    A table (AssignmentTable) contains five fields. The first field is Area. The next four are Category1, Category2, Category3, and Category4. Each Category field contains the name of an employee who is authorized to work in that area, on that particular category of project. So think:
    Area- Cat1 – Cat2- Cat3- Cat4
    LA - Bob - Jim - Andre – Dave


    Chicago- Bill- Dan - Steve- Mary

    There are many more Areas, this is to simplify the example. I need to build logic that says “If Area = LA and Category = Cat2 THEN Jim.

    I’m sure this is easier than I’m making it. Any help would be greatly appreciated!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if you normalised your data, it would be much easier

    instead of

    Area- Cat1 – Cat2- Cat3- Cat4
    LA - Bob - Jim - Andre – Dave

    have

    Area...Cat...Employee
    LA..... 1......Bob
    LA......2......Jim
    etc

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As ajax has advised, you should be/get familiar with Normalization. It will simplify all things database.

    Also, it is always a good strategy to identify and document your requirements. It appears that you have jumped into physical database without clear requirements and/or without some appreciation of basic relational database concepts.

    Here is a link to database design information.

    Good luck with your project.

  4. #4
    stephen c is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2009
    Posts
    30
    I'm not really great at this, it's not my job, and don't profess to know everything. I do have a basic concept of RDB's, and have dealt with many different systems, even in Report Program Generator. Our basic system is SQL. But this is Access, and Access does things a little differently sometimes, so I ask.

    My difficulty here is that for ease of management of the data for the user, a split form from Access is simplest. Using that format, yields a dataset that is not normalized. I get that. That's my problem. I was just asking if perhaps there was a way of which I am unaware as to how to deal with that.

    Thanks.

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    If you cannot change your structure as Ajax stated, maybe try a union query:

    Select Area, Cat1 as Cat from AssignmentTable
    Union All
    Select Area, Cat2 as Cat from AssignmentTable
    Union All
    Select Area, Cat3 as Cat from AssignmentTable
    Union All
    Select Area, Cat4 as Cat from AssignmentTable
    Order By Area, Cat;

    Then use the union query in a 2nd query, add the 2 fields and get your totals, etc?

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Our basic system is SQL.....Access does things a little differently sometimes
    All relational databases work to the same principles and rules around normalisation. Construct of sql code is the same but TSQL has some functionality not available in Access and visa versa. The problem is often that enterprise scale systems do not output data in a normalised way, but in a denormalised way suitable for Excel.

    Don't confuse presentation (which can be denormalised) in forms and reports with data storage (which needs to be normalised) for tables. Trying to create queries on denormalised data can be very difficult if not impossible. At the very least, you will need to use Bulzie's union query as a starting point for any query

  7. #7
    stephen c is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2009
    Posts
    30
    I think this is the direction I will unfortunately have to go. Thank you. Fortunately, it is a minimal component of things.

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

Similar Threads

  1. Replies: 31
    Last Post: 05-29-2017, 08:21 AM
  2. Replies: 12
    Last Post: 11-14-2014, 11:17 AM
  3. Printer Selection in VBA code
    By trevor40 in forum Reports
    Replies: 2
    Last Post: 03-08-2014, 03:31 PM
  4. Replies: 1
    Last Post: 05-04-2013, 12:19 PM
  5. Replies: 2
    Last Post: 01-06-2012, 09:42 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