Query Help please...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am having trouble writing a query to return the data I want.

I have a Customer Table (tblCustomers) & I have an Assessment Table
(tblAssessments) where they both have the "CustomerID" Field to join them on.
1 Customer can have many Assessments (each Assessment has a different
Assessment Date)

What I would like to have returned is Only the Customers that have
Assessments and only the "newest" Assessment for each customer.

I guess the MAX function will be needed & I also played around with GROUP BY
but I can get it to work right.

Any help would be greatly appreciated & ideally if someone could write the
Query out for me.

Thank you,
Jeff
 
hi,
group by sometimes will negate an aggragate function like
max perticularly if there are many other group bys in the
query.
My suggestion is this. create a query that selects the max
assignment date and the customer id only.
insert the query into your second query linking the
assessmetn table on maxdata and customer id. the pull all
of the fields you want into the query. this way all field
in the second will be group by and the max function will
be in the other query.
this set up should do like you want.
good luck
 
Back
Top