Query Question

[Guest]

Hi,
I have a query that searches on Max of date to find the last date that a
store was visited. Each store has its own unique id number and the query
works. The problem occures when I try to add other criterea into the query.
The information that was logged on the visit. The query then shows other
dates that are not required. Example below:

SELECT DISTINCTROW ModuleVisits.Number, [Unit Details].Unit,
Max(ModuleVisits.Date) AS MaxOfDate, [Unit Details].Auditor
FROM [Unit Details] INNER JOIN ModuleVisits ON [Unit Details].Number =
ModuleVisits.Number
GROUP BY ModuleVisits.Number, [Unit Details].Unit, [Unit Details].Auditor
ORDER BY ModuleVisits.Number, Max(ModuleVisits.Date);

As soon as I add any further details of the visit I want the query to show
it doubles the amount of records shown

Can anyone please shed some light

 

[Guest]

When you add other fields to the query, fields that you want to filter on. In
the Group by section of the query select Expression, other wise it will
return it more then once if the filter might consist a criteria the might
return more then one value.

Like -
Between [StartDate] And [EndDate]

If you post the SQL, that includes the other filter, you might have an idea
what wrong with it, and we will be able to suggest a solution.