Results 1 to 12 of 12
  1. #1
    sheckay is online now Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    73

    Blank Query due to criteria not being met in some fields, not all of them

    Hello. I have a query that is based on many tables. It searches all of the tables for records that have the current date (in the date field) and also includes the notes field of the corresponding record (that is included in the query). Here's a simplified example:

    Run Query ->
    Station Notes
    Station 1 User did great.
    Station 2 User had trouble
    Station 3 User quit
    Station 4 User did great.

    It works great. As long as there is a record for each table with today's date. If one doesn't exist, the query is blank. For example, if there was no user on Station 3 today, there's no entry.
    Is there a way to have the query run and return whatever records match up, while leaving blank the records for fields that do not match up? I've tried using or is null, as well as some others, but to no success.
    Thanks in advance.

  2. #2
    sheckay is online now Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    73
    I just tried creating a query for each table, and then a query based on those queries. Same result. Probably not news to all of you experienced Access users. I just thought I had something there. Not quite.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Post sample tables and attempted query.

    If you want to provide db for analysis, follow instructions at bottom of my post. This is usually the quickest way to get working solution.
    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.

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Looks like you need a left join query on 3 tables (tables for users, stations, and station notes). Something like
    Code:
    SELECT u.LastName, u.ForeName, s.Station, sn.NoteDate, sn.Note
    FROM tblUsers u
    LEFT JOIN tblStationNotes sn ON sn.UserID = u.UserID
    LEFT JOIN tblStations s ON s.StationID = sn.StationID
    WHERE sn.NoteDate = Date()
    This will return notes for all users - with Null notes field values when there were no notes for this user.

    In case you need this query to return notes based on stations instead (a list of today's notes for all stations)
    Code:
    SELECT u.LastName, u.ForeName, s.Station, sn.NoteDate, sn.Note
    FROM tblStations s
    LEFT JOIN tblStationNotes sn ON sn.StationID = s.StationID
    LEFT JOIN tblUsers u ON u.UserID = sn.UserID
    WHERE sn.NoteDate = Date()

  5. #5
    sheckay is online now Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    73

    attached file

    Database2.zip
    The form that is attached to the big query is named MinutesofProdReportFrm
    The query that is based on smaller queries is called testQuery

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    Had a look at your database, and it is non-formalized in worst possible way ever! All those tables in your database can be condensed to single table. And of-course some new tables (for users and for stations) must be created, based on info in fields in your current tables. With current design, there is no point to develop any queries, because whenever you get a new station, you have to redesign all those queries again and again! And without users or station tables, there is no way to get a result you wanted anyway.

  7. #7
    sheckay is online now Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    73
    I was going to put this in my initial post: And, somebody might ask, why not just use one table? I have multiple tables to allow notes to be entered by users randomly and independently, without overwriting the entries of other users.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I cannot recall seeing a properly designed db that got away with one table. I didn't look at your posted file because I expected you have a table for each station to say the least and AvrilLaanemets seems to have confirmed as much. Let's say that you put all station data into 1 table (assuming there is station data, such as it's ID/Name, location, and whatever else). If this table has, for example, a comments field that will hold notes like what you posted, then all of the station data gets repeated for each note. That's "bad". It is likely that you need at least
    - station table
    - user/employee table
    - notes table

    I suggest you research db normalization and redesign, otherwise your design will continue to thwart you as you go. Perhaps review
    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    or find ones that you like better. The main thing to remember is that each "entity" should have its own table but it's up to you to decide what is an entity. If you need help with design, perhaps start a new thread and post a pic of your relationships window when you've set relationships. Or just add your tables to the window and post that.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    sheckay is online now Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    73
    Thank you. I guess I will have to start over with this in mind. I thank everyone.

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by sheckay View Post
    And, somebody might ask, why not just use one table? I have multiple tables to allow notes to be entered by users randomly and independently, without overwriting the entries of other users.
    You can design frontend app to cope with this. A couple of possible solutions:
    a) Have a table (hidden from other users), where logon names of users are listed. User can edit only records he/she created. When a new record is created, user's logon name is added to record;
    b) Every user's frontend app has set filter for only his/her records displayed. When a new record is created, the user name (or whatever id you use in your table for users) is added to table automatically.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    RE: b) - when user leaves that job or goes on vacation, the design has to be altered to accommodate a new user else no one can do anything with those records. My approach was to default to current user but use a combo of users to select their records. Creator ID for the main entity was static but who edited the related details was added to the records in those related tables. A table of notes for each user is just as bad as a table for each station. If you want to maintain a history you only append records to notes table. If not, then you allow edits (which would pretty much permit wiping out the entire note as well). As noted, if knowing who edited is desired, then you capture that regardless of which route you take.

    You might want to research Environ function or fosUserName to see how you can get environment variables such as user/pc ID, etc. If using 64 bit Office, I believe you have to find a version of fosUserName that is compatible.
    Last edited by Micron; 07-06-2022 at 06:46 AM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    sheckay is online now Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    73
    I've posted what I have in the works in the general access forum. Thanks again!

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

Similar Threads

  1. Problems with Query and blank fields
    By FriQenstein in forum Queries
    Replies: 8
    Last Post: 09-03-2020, 02:18 PM
  2. Replies: 1
    Last Post: 01-31-2018, 04:06 PM
  3. Replies: 4
    Last Post: 08-30-2014, 10:23 AM
  4. Select Query on Criteria or Blank?
    By batowl in forum Queries
    Replies: 4
    Last Post: 08-17-2011, 02:34 PM
  5. Replies: 1
    Last Post: 07-13-2011, 11:00 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