Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    ennayram is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    7

    Help with an expression (aka formula)

    Hello,

    I am working with an access database containing locker numbers, names, shifts, etc. I have a query set up to show me the empty lockers using the "null" criteria. Now I want to add an expression that will look up the information already associated with the locker number (aka the other person in that locker, their shift, etc). I'm thinking of using an IIF with a nested lookup but I'm struggling with how to write the expression to get the data I want. Any suggestions?

    Example of data:
    NAME PHONE STATUS Tue_SHIFT Wed_SHIFT Thu_SHIFT Fri_SHIFT Sat_SHIFT 1__WEEK 2__WEEK 3__WEEK 4__WEEK 5__WEEK Locker_NO
    MaryAnne XXX-XXX-XXXX Employee 1 X X X X X 128
    "Empty" XXX-XXX-XXXX ABCDEF 128

    Query shows Locker 128 is available for use by another individual. Now I want to write an expression that will look up "MaryAnne and associated data".

    Regards,

    MaryAnne

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the reason you are struggling is because your data does not appear to be normalised - you seem to be using an excel methodology

    Before being able to suggest any sort of solution you need to explain your data in more detail

    1. What do the '1' and 'X' represent?
    2. Why do the first few columns represent shift and the latter ones weeks?
    3. What do the week numbers mean?
    4. What happens if there is a 6_Week?
    5. What happens 'next week'?
    6. Do you not have shifts for Sunday? (understandable) and Monday?
    7. What is the relevance of the 'Empty' row?

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    your layout is Excel thinking, not database design.
    db fields should be:

    tSchedule:
    NameID, WorkDay, Shift

    tEmployees:
    NameID, PHone, Status, Locker, FirstN, LastN, addr, etc

  4. #4
    ennayram is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    7
    The 1 identifies the shift (Wednesday first shift). The X's under weeks represents which weeks in a given month that person is scheduled. In my example I am scheduled all weeks with the maximum number of weeks in a given month being 5. So I am scheduled every Wednesday first shift (6am-noon). We don't have shifts on Sundays or Mondays as we are a religious organization. The Empty row represents that there is room in that locker for another person to be assigned to it. I want to be able to pull the existing data for "MaryAnne" so that when leadership goes to assign a new person a locker, they don't assign them to a locker with someone on the same shift/days. Does that make sense? FYI, I inherited this database and am very much a novice.

    Thanks,

    MaryAnne

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not clear to me. Looks like your query result already shows MaryAnne record.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    ennayram is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    7
    The information included in my original post was for example purposes. I'm afraid that most of the information in the database is confidential and without it wouldn't make any sense (all name fields would be blank, etc.). That's why I used an example. My query shows empty lockers, I want to pull existing data so that it shows like my example. I hope that makes more sense.

    Thanks,
    MaryAnne

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You can always copy db and remove confidential info and use dummy data (Mickey Mouse, Donald Duck, etc) to provide for analysis. But really, names aren't of much value without address, phone, email, SSN, birthdate.

    Are those 2 records from same table?

    Maybe:

    Select * FROM table WHERE Locker_NO IN (SELECT Locker_NO FROM table WHERE [Name] Is Null);
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    maybe this could help with the issue of sensitive data?

    https://www.accessforums.net/showthread.php?t=77482
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    The 1 identifies the shift (Wednesday first shift).
    We don't know your system so you need to explain in detail - from your quote are we to infer that a 2 means second shift? If so, what happens when someone does first and second shift for the same day? That happens in real life, why not in your case?

    And are we to understand that a locker can be shared by two (or more people)? if so, on what basis - perhaps they are on different shifts? in which case which doesn't the empty row have a 2 in it (assuming my inference above is correct).

    Similarly if there is a shift change at say 1pm then typically the morning shift will return to their locker at 1pm and take say 5 mins to clear it, whilst the afternoon shift will arrive at say 12:55 and leave it at 1pm - which means a locker used by the morning shift cannot be used by the afternoon shift.

    Perhaps you can explain in simple terms how your locker system actually works - plain English, no jargon, no database speak. e.g.

    Person A is allocated a locker for the current month for the period of their shift and is cleared completely
    They will always, without exception work the same shift each week for that month
    The following month they may be allocated a different locker
    The following month they may work different shifts
    They may share that locker with someone else on the same shift
    etc.

  10. #10
    ennayram is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    7

    Copy of database parsed down

    CopySistersLockers.accdb
    Hopefully attaching database. I appreciate all the interest and replies. I feel like perhaps people are trying to drill down too far. Each locker is designed to be shared by 2 people. What we do beyond that as far as shifts, changes, designs, etc. is not relevant to my question of how to get a report or query showing which lockers have room for a second person (currently showing as blank in the name field).

    Regards,
    MaryAnne

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you try query suggested in post #7?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    ennayram is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    7
    I'm sorry but I am extremely novice at Access, hence the trouble. I've got the query for null value in the name field which is giving me a list of spaces. Now I need to get existing data for the same locker. "Select * FROM table WHERE Locker_NO IN (SELECT Locker_NO FROM table WHERE [Name] Is Null)" seems to be what I already have... Unless I'm misunderstanding, which is more than possible.

    Regards,

    MaryAnne

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I would think not since you say you are not able to get the desired output - but hard to be sure since you never posted your query statement and I have not yet downloaded db.

    Did you try it to compare?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Each locker is designed to be shared by 2 people.
    It may be obvious to you, but not at all clear to me

    query showing which lockers have room for a second person (currently showing as blank in the name field).
    but to provide this requirement try

    Code:
    SELECT DISTINCT LOCKERS.NAME, LOCKERS.Locker_NO
    FROM LOCKERS LEFT JOIN LOCKERS AS LOCKERS_1 ON LOCKERS.Locker_NO = LOCKERS_1.Locker_NO
    WHERE (((LOCKERS.NAME)<>"") AND ((Nz([LOCKERS_1].[NAME]))=""))
    ORDER BY LOCKERS.Locker_NO

  15. #15
    ennayram is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    7
    June7 and Ajax, thank you so much! You are brilliant and I really appreciate your help. Once I realized you were talking SQL language I did some research and got it!

    Regards,

    MaryAnne

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

Similar Threads

  1. Replies: 4
    Last Post: 08-11-2019, 05:09 PM
  2. Replies: 7
    Last Post: 05-12-2018, 01:13 PM
  3. Replies: 3
    Last Post: 07-10-2015, 01:19 PM
  4. Replies: 9
    Last Post: 01-14-2015, 05:10 PM
  5. Replies: 4
    Last Post: 11-19-2013, 06:53 PM

Tags for this Thread

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