Help With Query

[Guest_imported]

I have designed a simple query (using 2 tables) which shows clients and the "phases" they have been through (including date when they commenced that phase). I only want to show their current "phase" and not all the previous phases. My query shows all phases in date order so I can easily see the current phase but some clients have had many change of "phases" and I want to keep these records.How can I restrict the query to only show the current "phase"? Or can I do something with the report so that it only shows the current "phase" for each client? Im sure there is some easy way to restrict the date field but as an Access novice I havent been able to work it out? Can anyone help?

 

[tomschafer]

Create another query that selects the max of the Date with any criteria you may be using. Then add that query to the query you are speaking of above.

 

[Guest_imported]

Thanks alot - it worked but how to I show the corresponding phase that goes with the most recent date. When I put the Phase in the query it shows ALL phases with the most recent date

 

[tomschafer]

Did you create a join between the "max of date" query and the tables on your main query?

 

[Guest_imported]

Sorry for the late response- I had a meeting.

When I created the "max of date" query it showed only one date (the most recent) but when I added the "phase" it showed all Phases for each client with the most recent date next to all of them. Lets try to simplify things. I have a table with 3 fields - Client ID, Date, Phase. Given that each client has many change of phases how can I view a query or Report that only shows the most recent Phase and date for each client. It cant be too hard - maybe I'm not explaining it right but I really need help.

 

[tomschafer]

You need to add a where clause that filters out the record for the ClientID you want. i.e. WHERE ClientID=

 

[THoey]

Did you try:

SELECT MyTable.ClientID,
MyTable.Phase,
MAX(MyTable.StartDate)
FROM MyTable
GROUP BY MyTable.ClientID, MyTable.Phase

Hope that helps...
Terry M. Hoey