Relationships, Grouping, Aggregate Function - make it simple

[wxkeep]

Well, I have been posting for the past couple of weeks regarding this project and now I fear that I've become more complicated than I need to be and would love some insight from everyone here.

I am building a salesman database. It has evolved to the point of having three tables for Representatives, Buying Regions, and Sales (tblReps, tblRegs, tblSales respectively) that each look like this:

tblReps
RepId RepFirstName RepLastName RepPhone
1 John Doe 5551212
2 Jane Doe 5551234
3 Mickey Mouse 5550000

tblRegs
RegID RepId RegName RegCode RegNew
1 2 Southwest 105A N
2 1 Northeast 987C Y
3 2 Southeast 583B N
4 3 Northwest 875D N

tblSales
SalesId SaleSeq RegId Date Amount
1 1 3 9/25/2005 1000
2 3 1 9/27/2005 2000
3 2 3 9/26/2005 3000
4 4 2 9/27/2005 1000


Since each Region can have more than one sale - I've linked tblSales.RegId to tblRegs.RegId and since each rep can have more than one region I've linked tblRegs.RepId to tblReps.RepId

I have one query that pulls all the sales for everyone that ends up looking something like this:

RepCode SaleSeq SalesId RegName RegCode Amount Date
1 4 4 Northeast 987C 1000 9/27

But now what I would like is to make a report that can display the highest sales in each region for each salesman.

FINAL REPORT <RepFirstName RepLastName>

<sales.Date> <RegName> <RegCode> <RegNew> <Sales.Amount>
. . . . .
. . . . .
. . . . .

The problem is - when I run a query for the that has a max(Sales.Amount) as High_sales I can't add everything else without getting aggregate function errors.

I've read that I can do either A) Subquery or B) multiple queires off one another - but I'm trying to ultimately pull data from all three tables for one final report and since this is a company wide database - try and do it as easily as possible so I don't perform too many unneccessary queries etc. Can anyone help steer me towards the best approach to accomplish this final goal? Hopefully I can work backwards from that solution to put some other smaller reports that only combine data from 2 tables at a time. Thank you very much for looking at this for me!

 

[MajP]

I would think you have a few ways to do it, but your final report does not seem to reflect any aggregation. Find the highest sale per region per person should be easy, but where you display it determines the method to use. Something like this returns that information:

qryMaxRegRep
SELECT tbSales.regID, tbSales.repID, Max(tbSales.amount) AS MaxOfamount
FROM tbSales
GROUP BY tbSales.regID, tbSales.repID;

If in your report you linked the repID and regID to the same fields in the sales table you would have the a column with the max in that region for that rep. You could then put this in the footer of your region/Rep section. You could also just do this right on the report like you would with sums. I think the key is using section footers and headers.