Grouping in a query

[grenee]

Good day all.

My table is "Instructors" and two fields are "InstructorName" and "date". I want to run a query which would group by "InstructorName". Problem is that the names are not grouping where the dates are different for an InstructorName. Even when the date check box is unchecked the problem still exists.

Can anyone assist me in surmounting this problem.

 

[plog]

You explained what you want the names to do, now explain what you want to happen with the dates. Do you need them in the query at all? If so, which of those values do you prefer to show--lowest, highest?

 

[Royce]

Are you trying to do this in the query design view? In Code? In a report? Are you getting an error?

Also "Date" is a reserved word. It should never be used as the name of a field. Try changing it to something like dtmMyDateField.

 

[grenee]

Thanks for your response.

I simply do not need the dates in the query the dates are needed only to obtain a specific date period for example: 1st Jan 2014 to 31st Dec 2014. But I don't need the dates to show up.

 

[Royce]

You need sql something like this:
SELECT Customer.Name
FROM Customer
WHERE (((Customer.TimeCreated) Between #4/1/2015# And #7/1/2015#))
GROUP BY Customer.Name;

Or in the query design view for the Date line set the "Total" line to WHERE and uncheck the SHOW box.

And if you do not change the field name to something other than "date" you will hate yourself for years! The worst problem being subtle problems that you can't track down.

 

[grenee]

Hi,

Very simple solution but yet I did not know it.

Thank and thanks also of the advise on the use of the keyword "Date"