Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Radtastic10 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    65

    Criteria syntax question

    I have a query that is looking up past products. It is leaving off some results at the moment. I have figured out the why, but I am not sure how to fix the problem. The problem is the current criteria doesn't account for if there is no secondary product. The products are from the same table. So I have a copy of the product table and the current criteria that the secondary can't equal the primary. The primary is a user input taken from a form.



    My question is what do I need to add to the criteria of the secondary product to account for the possibility of there not being a secondary?

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    Filter out the 1st 2ndary before it gets this far. union 1st and 2nd sets. imo

  3. #3
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Put Is Null in the Secondary criteria and <>[Primary] in the next criteria row, same field.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Radtastic10 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    65
    I have tried using the Is Null criteria and it still has the same information displayed as with out it. I also tried separating them. However, when I tried to separate them the entries that contain an empty/ null value for the secondary the results of the query just came back completely empty.
    Last edited by Radtastic10; 05-27-2016 at 10:23 AM. Reason: additional information

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I'm not clear on whether you want to see Null's in the Secondary or not. But, if you Don't want them, then you can use this as the Secondary criteria:

    Is Not Null AND [Secondary] <> [Primary] in the same criteria field.
    I don't know if you could get away with not having [Secondary] in it, keeping it makes it clearer.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by Radtastic10 View Post
    I have tried using the Is Null criteria and it still has the same information displayed as with out it.
    I think this sample reflects the info you posted about your data. It contains a duplicate and a Null for Secondary field.
    ID PRIMARY SECONDARY
    1 apples pie
    2 bannanas pie
    3 oranges cream
    4 lemons tart
    5 limes limes
    6 peaches
    The result of my suggestion is
    ID PRIMARY SECONDARY
    1 apples pie
    2 bannanas pie
    3 oranges cream
    4 lemons tart
    6 peaches

    Is that not what you want? The result returns a primary where there is no secondary, but neither if they are the same. The sql for this is
    Code:
    SELECT tblProducts.ID, tblProducts.PRIMARY, tblProducts.SECONDARY
    FROM tblProducts
    WHERE (((tblProducts.SECONDARY) Is Null)) OR (((tblProducts.SECONDARY)<>[Primary]));
    Last edited by Micron; 05-27-2016 at 10:11 AM. Reason: spellin & added sql
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Radtastic10 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    65
    This is what I would like to happen. However, the difference between what you have shown and my specific situation is that you have separate fields for the primary and secondary. In my database all are in the same field. This made it easier for displaying on reports and for how our data is going to be used. I am aware that this can make it a little more difficult to attain a solution that fulfills what I am looking for.

    So what I am working with essentially, to use your example is tblProducts.Primary, tblProducts.Primary_1.

  8. #8
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    You have both in the same field? How on earth do you distinguish which one is which?

    That said, please tell us what the data in your table actually looks like, when there is both primary and secondary and when there is only one. How are they separated?

  9. #9
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Yes, data samples are required. What was posted just now was two fields in one table (Primary, and Primary_1) which is what op is saying doesn't exist.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Radtastic10 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    65
    So the names are in the same field, however they are separated by the ID number of the table itself and I have also added other fields in order for others to distinguish between them. It was put together this way so that when we go into the main table we can expand a particular entry and see the information from the subsidiary table (the one with the primary and secondary info) with out having to open up multiple tables or scroll horizontally to see the essential information. Everything works exactly how we would like it to work. I am just trying to develop a query that will allow us to pick out particular info without having to scroll through every single entry.

    The problem that I am trying to address in this thread is only part of the overall query. The overall query is pulling information from multiple tables. Except the information being pulled from other tables is either just one field or very simple as far as the query is concerned.

  11. #11
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Ok, but after a couple of exhortations to post samples showing the data structure and none seems to be forthcoming, I have to say I cannot help further. I don't wish to spend more time interpreting in my head what's in front of you. You could post a stripped down and zipped copy of your db, but if you have used features that cannot be opened in 2007, I won't be able to open it. Others will probably be able to step in.
    Good luck.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    Radtastic10 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    65
    The problem is I am not able to post any form of my database. So all I can do is describe the problem to the best of my abilities. I understand that being able to see the database itself would be helpful. You did a pretty good job in representing what I am looking to do with the information. However, I don't have separate fields for the primary and secondary. In the query I have used the table and a copy of that same table in order to display the secondary. I am sure there are more efficient ways to accomplish what I am asking. However, in order to make some of those solutions work I would literally have to go back and change the framework of the current database which is not an option. Doing so would change the whole functionality of the other applications/options with in the database.

  13. #13
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I understand if you can't post a copy, but surely you can post some data, even if it's made up (as long as it accurately resembles the real thing). It's a simple matter to copy your table records, dump into Excel and copy/paste here. You'll automatically create a nicely laid out html table (witness my tables, which took less than one minute to produce). Excel's Find/Replace function easily transforms sensitive data into made up stuff.

    Be that as it may, I now get the impression you are self joining this table. Not sure why based on what appears to be a simple problem, although there could be more behind this than you have revealed. So as a last resort, I figure you need a UNION query unless I still don't get it. Assuming now that you have this
    ID PRIMARY Desc
    1 apples fruit
    2 bannanas fruit
    3 oranges fruit
    4 lemons fruit
    5
    6 peaches fruit
    7 apples fruit
    8 celery vegetable

    If you don't need the ID field you mentioned, this
    Code:
    SELECT DISTINCT tblProducts2.PRIMARY, tblProducts2.Desc 
    FROM tblProducts2
    WHERE (((tblProducts2.PRIMARY) Is Null))
    UNION
    SELECT DISTINCT tblProducts2.PRIMARY,  tblProducts2.Desc 
    FROM tblProducts2
    will give you this

    PRIMARY Desc
    apples fruit
    bannanas fruit
    celery vegetable
    lemons fruit
    oranges fruit
    peaches fruit

    Note that the first row is the nulls you (I think) wanted If you do need the id field (or any other that presents a unique value) this will not work. In that case, I think a subquery might work, but I can't look into that now.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I've been following this thread and have a couple of comments.


    Quote Originally Posted by Radtastic10 View Post
    So the names are in the same field, however they are separated by the ID number of the table itself and I have also added other fields in order for others to distinguish between them.
    So instead of designing the table correctly, more fields were added to try and correct the problem??!!
    Having a primary product and a secondary product in the same field violates The First Normal Form (fields should be atomic). It is akin to having first name and a last name in the same field. Looks good, but how do you sort by last name? Obviously it is better to have two field; you can sort by last name and you can concatenate the two fields to get the full name.

    ----------------------

    Quote Originally Posted by Radtastic10 View Post
    It was put together this way so that when we go into the main table we can expand a particular entry and see the information from the subsidiary table (the one with the primary and secondary info) with out having to open up multiple tables or scroll horizontally to see the essential information.
    All viewing, adding & editing should be done with forms. Users should (IMO) NEVER have direct access to tables.... This is a sure recipe for disaster.

    ----------------------

    Quote Originally Posted by Radtastic10 View Post
    So what I am working with essentially, to use your example is tblProducts.Primary, tblProducts.Primary_1.
    Actually it would be tblProducts.Primary, tblProducts_1.Primary

    ----------------------

    The OP still has not provided examples of the data in the field "Primary".
    Quote Originally Posted by Radtastic10 View Post
    In the query I have used the table and a copy of that same table in order to display the secondary. I am sure there are more efficient ways to accomplish what I am asking.
    So the names are in the same field, however they are separated by the ID number of the table itself
    The OP still has not provided examples of the data in the field "Primary". But maybe, in the query, the "Primary" and "Secondary" could be split much the same way as splitting "Jon Smith" into a column "Jon" and a column "Smith".
    That way a second copy of tblProducts would not be needed.

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I totally agree with ssanfu -- and I would add that you are not using a relational database as intended/designed.
    You can use a ratchet wrench to pound nails, but tools are designed for a particular purpose.

    Make up some data to show the issue. You have been given advice re Normalization and proper structure, but it's your decision as to how you intend to proceed.

    Good luck with whatever you choose.

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

Similar Threads

  1. Syntax error with where criteria
    By craig1988 in forum Modules
    Replies: 7
    Last Post: 01-29-2016, 10:57 AM
  2. Davg Multiple Criteria Syntax
    By JonathanT in forum Programming
    Replies: 1
    Last Post: 10-30-2013, 10:16 PM
  3. Simple Criteria syntax mismatch - please help!
    By whatwouldmattdo in forum Queries
    Replies: 4
    Last Post: 10-19-2011, 04:57 PM
  4. Syntax criteria for first timr
    By Casper2012 in forum Queries
    Replies: 6
    Last Post: 08-28-2011, 09:11 PM
  5. ORDER BY ... If (SQL syntax question)
    By Remster in forum Forms
    Replies: 9
    Last Post: 05-18-2011, 06:54 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