Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2013
    Posts
    5

    Unanswered: Noob Help Please

    Hi,

    I have a table that stores all the information I need for the query I'm trying to make but I am having problems getting the results I need. I need to select data where the column [sales person] is the same and the column [product name] is the same and then if the [days in inventory] with [sold] = null is greater than [days in inventory] with [sold] <> null. I already have the WHERE criteria set but it is selecting all records from all sales person and product. Any direction on what would work best (ie SQL statement / VBA) would be greatly appreciated.

    Basically I need to make sure that each sales person is selling their oldest product first and return the records when they failed to do so.

    Hope that makes sense,
    Sean

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    One thing that new users have a great problem with SQL and NULL values is that nothing ever equals NULL and NULL never equals anything else (including another NULL).

    SQL provides predicates of IS NULL and IS NOT NULL for comparing for/against NULL values.

    This ought to get you going. Ask if you have more questions!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2013
    Posts
    5
    Quote Originally Posted by Pat Phelan View Post
    One thing that new users have a great problem with SQL and NULL values is that nothing ever equals NULL and NULL never equals anything else (including another NULL).

    SQL provides predicates of IS NULL and IS NOT NULL for comparing for/against NULL values.

    This ought to get you going. Ask if you have more questions!

    -PatP
    Pat, Thank you for such a quick response. Sorry I think I asked my question wrong. How do I go about selecting data from each sales person and product without typing in their name and then each product name? I see your point on the null value and will fix that. Again thanks for the insight and any additional help.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I wouldn't bother with doing them onesie, I'd do it for all of them in one swell foop!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Mar 2013
    Posts
    5
    Quote Originally Posted by Pat Phelan View Post
    I wouldn't bother with doing them onesie, I'd do it for all of them in one swell foop!

    -PatP
    I wish I could but here is the problem, we hold each sales reps responsible for selling their oldest item first. So lets say [Rep A] sells [Product 1] with a [days in inventory] of 2, and [Rep B] doesn't sell their [Product 1] with a [days in inventory] of 21. If I did all the records without going rep by rep wouldn't this bring back a record of not using the oldest first?

  6. #6
    Join Date
    Jul 2005
    Posts
    13
    It sounds like you may need to think about splitting your table up a bit - do some 'normalising', unless I'm not reading this correctly. Do you have a separate 'user' (salesrep) table separate from their sales activity? You could also do with a separate 'product' table, then have a table that records all the 'activities' (sales?) If you did this it would get rid of loads of stodge, as you would merely have 'links' to fixed values (people/products) in your activity table rather than having their name in every time. Also you could tell a query then to look (from a small dropdown form) to search 'Name', selling 'product', it would return all 'activities', and also you set your query to ORDER BY 'Days in Inventory' (highest first). Apologies if I've misunderstood.

  7. #7
    Join Date
    Mar 2013
    Posts
    5
    Quote Originally Posted by MNorton View Post
    It sounds like you may need to think about splitting your table up a bit - do some 'normalising', unless I'm not reading this correctly. Do you have a separate 'user' (salesrep) table separate from their sales activity? You could also do with a separate 'product' table, then have a table that records all the 'activities' (sales?) If you did this it would get rid of loads of stodge, as you would merely have 'links' to fixed values (people/products) in your activity table rather than having their name in every time. Also you could tell a query then to look (from a small dropdown form) to search 'Name', selling 'product', it would return all 'activities', and also you set your query to ORDER BY 'Days in Inventory' (highest first). Apologies if I've misunderstood.
    Thank you for your input. What I ended up doing was making two queries on with sold items and one with items not sold and then cross-referenced them against each other. I'm sure there is a better way of coding this but I am getting the results I need as of now. Thanks again to both of you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •