Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2005
    Posts
    9

    Unanswered: Problem with query getting all salesman :O(

    Hi...I'm working on a database that has 2 tables:

    Master Table

    Day
    Order #
    Salesman
    etc...

    Salesman Table
    Salesman

    The way it works...there is a relationship between the master table salesman and the field salesman in the salesman table.
    SELECT [Daily Master].Day, [Daily Master].[Order #'s], [Daily Master].[Job Name], [Daily Master].Sales, Sum([Daily Master].Sales) AS DailyTotal, SALESMAN.SALESMAN
    FROM [Daily Master] INNER JOIN SALESMAN ON [Daily Master].SALESMAN = SALESMAN.SALESMAN
    GROUP BY [Daily Master].Day, [Daily Master].[Order #'s], [Daily Master].[Job Name], [Daily Master].Sales, SALESMAN.SALESMAN
    HAVING ((([Daily Master].Day) Between [Forms]![DAILYSALESREPORT]![Start Date] And [Forms]![DAILYSALESREPORT]![End Date]))
    ORDER BY [Daily Master].[Order #'s]

    I'm also using dsum in the reports like this:

    =DSum("Nz([Sales]","[Daily Master]","[SALESMAN]= '" & [SALESMAN] & "' And Month([day])= " & Month(Date()) & "And Year([day]) =" & Year(Date()))

    It only returns values for those salesman who have sold something that day. I have a range but really they run start date and end date the same...example start date-= 1/1 end date =1/1 . I put the range there for future reports. Anyways...i need the query to show that Joe blow didn't sell anything for the day the report was run vs . him not showing up @ all. Is there a way to make this work without retooling the entire db? PLEASE HELP ) I'm very new to access and have reached a serious hump in the road!

  2. #2
    Join Date
    Jan 2005
    Posts
    9
    Also, I'm using a header in the report with the field salesman...for what it's worth :O)

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You need to use an outer join to see all the records from one table (salesman):

    ...
    FROM [Daily Master] RIGHT OUTER JOIN SALESMAN ON [Daily Master].SALESMAN = SALESMAN.SALESMAN
    ...

    That says to show ALL records from the table to the right of the '='
    Inspiration Through Fermentation

  4. #4
    Join Date
    Jan 2005
    Posts
    9
    My query now looks like this:

    SELECT [Daily Master].Day, [Daily Master].[Order #'s], [Daily Master].[Job Name], [Daily Master].Sales, Sum([Daily Master].Sales) AS DailyTotal, SALESMAN.SALESMAN
    FROM [Daily Master] RIGHT JOIN SALESMAN ON [Daily Master].SALESMAN=SALESMAN.SALESMAN
    GROUP BY [Daily Master].Day, [Daily Master].[Order #'s], [Daily Master].[Job Name], [Daily Master].Sales, SALESMAN.SALESMAN
    HAVING ((([Daily Master].Day) Between Forms!DAILYSALESREPORT![Start Date] And Forms!DAILYSALESREPORT![End Date]))
    ORDER BY [Daily Master].[Order #'s];

    It still doesn't work. When i run the report it will still only show the salesman who have sold something on that day. Do i need to do something else in addition to adding the line you suggested? Thanks for the help btw!

  5. #5
    Join Date
    Jan 2005
    Posts
    9
    actually...when i broke the relation ship from master.salesman and salesman.salesman...it worked in the since that it showed all of the salesman. The problem is that it listed every record for the date entered for every salesman.

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by diglife
    My query now looks like this:

    SELECT [Daily Master].Day, [Daily Master].[Order #'s], [Daily Master].[Job Name], [Daily Master].Sales, Sum([Daily Master].Sales) AS DailyTotal, SALESMAN.SALESMAN
    FROM [Daily Master] RIGHT JOIN SALESMAN ON [Daily Master].SALESMAN=SALESMAN.SALESMAN
    GROUP BY [Daily Master].Day, [Daily Master].[Order #'s], [Daily Master].[Job Name], [Daily Master].Sales, SALESMAN.SALESMAN
    HAVING ((([Daily Master].Day) Between Forms!DAILYSALESREPORT![Start Date] And Forms!DAILYSALESREPORT![End Date]))
    ORDER BY [Daily Master].[Order #'s];

    It still doesn't work. When i run the report it will still only show the salesman who have sold something on that day. Do i need to do something else in addition to adding the line you suggested? Thanks for the help btw!

    OOPS... slight problem. Your Group BY, Having, and Order BY statements
    all refer to the Daily Master table, therefore you'll only see records that have a match in that table. You'll probably have to use some kind of nested query.
    I'll noodle on it for a few, and if noone else gets back to you, I will.
    Inspiration Through Fermentation

  7. #7
    Join Date
    Jan 2005
    Posts
    9
    It also pulls every record from that day regardless of whether it was a sales record or not. The only way i distinguished the sales from the rest is by building a relationship between master.salesman and salesman.salesman. But when i do this with the modified query it only yields those results that have been made by a salesman and doesn't include the salesman that haven't done anything for that day.

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by diglife
    It also pulls every record from that day regardless of whether it was a sales record or not. The only way i distinguished the sales from the rest is by building a relationship between master.salesman and salesman.salesman. But when i do this with the modified query it only yields those results that have been made by a salesman and doesn't include the salesman that haven't done anything for that day.
    By removing the join alltogether, you create a Cartesian Product (or cross join). It shows all possible combinations of records from the 2 tables.
    I've never found a use for one of those, and many times they are large enough to blow up whatever you're working on.
    Inspiration Through Fermentation

  9. #9
    Join Date
    Jan 2005
    Posts
    9
    so any ideas on how i can get it too include all salesman and also include only the data from the master record that pertains to them?

  10. #10
    Join Date
    Jan 2005
    Posts
    9
    Is there perhaps a way that when i run the above query i can add code that says if [sales] <--sales field holds the value for the amount that salesman sold that day--> = o then insert a value and a date for those remaining salesman that haven't produced anything that day.

  11. #11
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    This ought to do it....

    SELECT SALESMAN.SALESMAN, DM.DAY, DM.[ORDER #'S], DM.[JOB NAME], DM.SALES, DM.DAILYTOTAL
    FROM SALESMAN LEFT JOIN (
    SELECT [Daily Master].Day, [Daily Master].[Order #'s], [Daily Master].[Job Name], [Daily Master].Sales, Sum([Daily Master].Sales) AS DailyTotal, [daily master].SALESMAN
    FROM [Daily Master]
    GROUP BY [Daily Master].Day, [Daily Master].[Order #'s], [Daily Master].[Job Name], [Daily Master].Sales, [Daily Master].SALESMAN
    HAVING ((([Daily Master].Day) Between [Forms]![DAILYSALESREPORT]![Start Date] And [Forms]![DAILYSALESREPORT]![End Date]))
    ORDER BY [Daily Master].[Order #'s]) DM ON SALESMAN.SALESMAN = DM.SALESMAN
    Inspiration Through Fermentation

  12. #12
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Since you admitted in your first post that you are new to access...
    Here's a pointer that will make your life a little easier in the long run

    Try to refrain from using spaces, # signs, and other special characters in your table and column names.
    Inspiration Through Fermentation

Posting Permissions

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