Results 1 to 7 of 7
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows XP Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    Show Count from different Zip Codes

    I am trying to build a query that shows the count of items sold, based off certain zip codes, based off of date sold. This is the query SQL I am using:
    Code:
    SELECT COUNT(data.ID) AS [Count], salesRepName
    FROM data
    WHERE data.ZIP = "12345" OR data.Zip = "444444" OR data.Zip = "66666"
    AND data.SOLDDATE BETWEEN #01/01/2013# AND #04/01/2013#
    The issue I am running into is it is returning a blank query. I want it to show for example sake --
    0 Mark Jones
    0 Richard Javier



    How should I set up my query to show this?

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Have your sales people actually sold product between those dates? Or do you want every sales person to show even if their sales amount was 0?

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you post a copy of your db with anything confidential removed?
    Do you get data if you take the Solddate constraint away?
    I'd remove constraints to make sure I had the query working generally then refine it with the constraints.
    Can you test with a few specific dates to ensure data is there?

  4. #4
    jo15765's Avatar
    jo15765 is offline Expert
    Windows XP Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Quote Originally Posted by rpeare View Post
    Have your sales people actually sold product between those dates? Or do you want every sales person to show even if their sales amount was 0?
    Some yes...others no.

    I want every sales person to show even if there amount is 0

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    then you'll have to add in your table storing all of your sales people's names and to a join FROM your employees TO your sales data (include all records from employees) this will force even zero sales employees to show.

  6. #6
    jo15765's Avatar
    jo15765 is offline Expert
    Windows XP Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Quote Originally Posted by rpeare View Post
    then you'll have to add in your table storing all of your sales people's names and to a join FROM your employees TO your sales data (include all records from employees) this will force even zero sales employees to show.
    So if I am following you, I need to create a seperate table that holds all of my sales reps names. Then join the my sales rep table with my data table and the join will cause even 0's to show?

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Yes, though if you are currently typing in your employee every time you can work with that design as well, though it will be a lot more tedious because John Smith would be treated differently than a mistyped Jon Smith. Whereas if you have an employee table like:

    Code:
    tblEmployees
    EmployeeID (autonumber)
    EmployeeFN
    EmployeeLN
    Other Employee Info
    
    
    EmployeeID  EmployeeFN  EmployeLN ----> other fields
    1           Donald      Duck
    2           Mickey      Mouse
    All you'd have to store in your sales table is the employee ID and you'll get the correct fn/ln combination every time long as your data entry is from a combo box that limits selections to the list of employees.

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

Similar Threads

  1. Show buttion in form if count of qry >0
    By tcheck in forum Access
    Replies: 1
    Last Post: 03-05-2013, 03:30 PM
  2. COUNT Group Count Records COUNT FUNCTION
    By PMCOFFEY in forum Access
    Replies: 9
    Last Post: 11-09-2012, 09:40 PM
  3. Queries and codes
    By alliandrina in forum Queries
    Replies: 2
    Last Post: 05-03-2012, 05:53 PM
  4. Zip Codes
    By Laurie B. in forum Access
    Replies: 6
    Last Post: 02-25-2011, 02:38 PM
  5. Replies: 1
    Last Post: 01-24-2011, 03:03 PM

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