If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Urgent - Problems with grouping Unique values
I have three tables; tblsalesrep, tblclients, tblgeneral.
tblclients references tblsalesrep to show what salesrep is assigned to that particular client, tblgeneral is for all intensive purposes an orders table that refs tblclient. here is the problem(s); i am attempting to write a report based on a date parameter from RepairedDate from tblgeneral that shows me the salesrep, the total number of unique clients and the sum of the repaircost. the problem i am having is that if a client has made multiple orders than the number of clients increases on the report. all i am attempting to do is show if salesman 1 made 35 orders from only 12 different clients totaling 3500.00, i want the report to show 12 clients - not 35 which it is currently doing. Thanks in advance |
#2
|
|||
|
|||
Urgent - Problems with grouping Unique values
Use Totals in your query, with functions:
Sum on repaircost Where on the date field(s) Group By on all others That should do it. HTH, Nikos "FJP" wrote in message ... I have three tables; tblsalesrep, tblclients, tblgeneral. tblclients references tblsalesrep to show what salesrep is assigned to that particular client, tblgeneral is for all intensive purposes an orders table that refs tblclient. here is the problem(s); i am attempting to write a report based on a date parameter from RepairedDate from tblgeneral that shows me the salesrep, the total number of unique clients and the sum of the repaircost. the problem i am having is that if a client has made multiple orders than the number of clients increases on the report. all i am attempting to do is show if salesman 1 made 35 orders from only 12 different clients totaling 3500.00, i want the report to show 12 clients - not 35 which it is currently doing. Thanks in advance |
Thread Tools | |
Display Modes | |
|
|