Query Help

[Jason O]

Hi

I have a table with a date field and want to group by date i.e. Records with
a date between 01/03/05 and 31/08/05 in one group, between 01/09/05 and
31/12/05 in another, and 01/01/06 and 28/02/06 in another...and so on.

How can I apply a *few* >=ddmmyy and <=ddmmyy conditions to the
same field in the group by option in query design view?

I'm a total SQL novice so any advice on how to do this in query design view
would be great. Or would I need to use expression builder?

Many Thanks.

Jason

 

[Duncan Bachen]

Hi

I have a table with a date field and want to group by date i.e. Records with
a date between 01/03/05 and 31/08/05 in one group, between 01/09/05 and
31/12/05 in another, and 01/01/06 and 28/02/06 in another...and so on.

How can I apply a *few* >=ddmmyy and <=ddmmyy conditions to the
same field in the group by option in query design view?

I'm a total SQL novice so any advice on how to do this in query design view
would be great. Or would I need to use expression builder?

Many Thanks.

Jason

One solution would be to build your initial select query with some
custom field expressions, then base your totals query on that, allowing
you to group and sort on the values.

DateGroup1: IIf([SomeDate] Between #01/03/05# AND #31/08/05#, 1, 0)
DateGroup2: IIf([SomeDate] Between #01/09/05# AND #31/12/05#, 2, 0)
DateGroup3: IIf([SomeDate] Between #01/01/06# AND #28/02/06#, 3, 0)

There are probably multiple ways to do this, and probably even a way to
do grouping based on the calculated date directly, but this was the
first thing that popped in my hea.

 

[Guest]

Here is another soution you might use. It gives you a number based on half
years since 31 DEC 04.

Semi: ((Year([YourDate])-2005)*2)+(IIf(Month([YourDate])<9,1,2))

Substitute your field for the [YourDate] in the query.

Hi

I have a table with a date field and want to group by date i.e. Records with
a date between 01/03/05 and 31/08/05 in one group, between 01/09/05 and
31/12/05 in another, and 01/01/06 and 28/02/06 in another...and so on.

How can I apply a *few* >=ddmmyy and <=ddmmyy conditions to the
same field in the group by option in query design view?

I'm a total SQL novice so any advice on how to do this in query design view
would be great. Or would I need to use expression builder?

Many Thanks.

Jason

One solution would be to build your initial select query with some
custom field expressions, then base your totals query on that, allowing
you to group and sort on the values.

DateGroup1: IIf([SomeDate] Between #01/03/05# AND #31/08/05#, 1, 0)
DateGroup2: IIf([SomeDate] Between #01/09/05# AND #31/12/05#, 2, 0)
DateGroup3: IIf([SomeDate] Between #01/01/06# AND #28/02/06#, 3, 0)

There are probably multiple ways to do this, and probably even a way to
do grouping based on the calculated date directly, but this was the
first thing that popped in my hea.

 

[Jason O]

Hi

I have a table with a date field and want to group by date i.e. Records with
a date between 01/03/05 and 31/08/05 in one group, between 01/09/05 and
31/12/05 in another, and 01/01/06 and 28/02/06 in another...and so on.

How can I apply a *few* >=ddmmyy and <=ddmmyy conditions to the
same field in the group by option in query design view?

I'm a total SQL novice so any advice on how to do this in query design view
would be great. Or would I need to use expression builder?

Many Thanks.

Jason

One solution would be to build your initial select query with some
custom field expressions, then base your totals query on that, allowing
you to group and sort on the values.

DateGroup1: IIf([SomeDate] Between #01/03/05# AND #31/08/05#, 1, 0)
DateGroup2: IIf([SomeDate] Between #01/09/05# AND #31/12/05#, 2, 0)
DateGroup3: IIf([SomeDate] Between #01/01/06# AND #28/02/06#, 3, 0)

There are probably multiple ways to do this, and probably even a way to
do grouping based on the calculated date directly, but this was the
first thing that popped in my hea.

Thanks for the help, and sorry if I'm being dim but would the above need to
be typed into expression builder? If so, all in the same expression? And are
the DateGroup1 ,2 & 3 user-defined names? Would I then be able to specify
these in a query to do the averaging of the other fields? (i.e average price
for records falling between the dates defined by DateGroup1) And could you
advise that the 1, 0 and 2, 0 at the end of the expressions signify.

I'm really sorry if I'm being *totally* thick but I am a beginner

TIA

Ja

 

[Duncan Bachen]

Thanks for the help, and sorry if I'm being dim but would the above need to
be typed into expression builder? If so, all in the same expression? And are
the DateGroup1 ,2 & 3 user-defined names? Would I then be able to specify
these in a query to do the averaging of the other fields? (i.e average price
for records falling between the dates defined by DateGroup1) And could you
advise that the 1, 0 and 2, 0 at the end of the expressions signify.

I'm really sorry if I'm being *totally* thick but I am a beginner

TIA

Ja

Each of those expressions would be a column in your query.

The portion before the : becomes the column name, and the portion after
becomes the results. So you'll have 3 columns, DateGroup1, DateGroup2,
and DateGroup3.

The IIf function, evaluates the expression and then returns either the
first value (if true) or the second value if false.

You could just as easily use a 1 instead of a 1, 2 or 3. So the field
would either contain a 1 if true, or a 0 if false. You can also use the
constants TRUE and FALSE, instead of the the 1 and 0 as another way to
accomplish the task. Basically, you're just trying to get one value if
it falls in the date range, and another if it doesn't.

The reason I used a 1, 2 and 3 is because you said that you want to
group and sort of them. Since they are 3 separate fields it's probably
irrelevant here. You just put the fields in the order you want to sort
them on first. You'd only use different numbers, if the values were all
contained in the same field. So my different numbers are misleading here.

You can then use this query as the SOURCE for another query. So if you
wanted only those records in DateGroup1, you could add criteria that
would select only those records, and then do your math on the resulting
records.

Make more sense?