Group by - Query

[Nic]

Evening All,
Iv got a query that gets its data from a table. When going through the query wizard it enabled me to group my data by 'Date'. So, for example, in my table there are numerous records for different products on different days. The date field for each record in my table is a date/time field. Is there anyway i can group the data shown in my query by a time slot on each day eg between 6am and 2pm and 2pm till 10pm?? (does that make sense)??
To help explain, its to summarise production output in a production area that works shift work. I want to be able to display summary info of xx output for different shifts on the same day.

Any help or suggestions welcome and greatly appreciated,

Many thanks,
Nic
P.S - im self tought at this, so simple explanations appreciated!

 

[plog]

You can group by any data that is in your table or derivable from the data in your table. Since what you want is derivable from your date/time field, yes you can do what you want. First though, you should never name a field 'Date' (or 'Time' or any of the reserve words of access http://support.microsoft.com/kb/286335).

To accomplish what you want you need to create a new filed in your query. For this example I am going to call this new field shift and change your 'Date' field to 'DateField'. Paste the below into a new line of your query:

Shift: IIf(Hour([DateField])>=6 AND Hour([DateField)<=14, "Early", "Late")

Then you can group by that. It will spit out "Early" for anything between 6am and 2pm and "Late" for everything else. That means if the value in your DateField is from 2pm until 5am it will show "Late". If you have values in your database that include these, you will need to add another IIf statement inside.

 

[Nic]

Hi, got this all sorted now.
Took a bit of fiddling and trial & error, but got there in the end!
Thanks for your help......greatly appreciated!