Problem with (complicated) expression in query

  • Thread starter Thread starter mr-tom
  • Start date Start date
M

mr-tom

Hi all,

I'm trying to produce some results over a rolling six months, so e.g.
somebody's total score in Jan 09 is the sum of their scores for Aug 08 to Jan
09; their Feb 09 total score is the sum of their scores for Sept 08 to Feb
09, etc.

Don't worry about year ends, we have a month ID which increments by one each
month, regardless of year (Jan 09 was 300, Dec 08 was 299 etc)

This may be the wrong way to go about it, but I'm trying to generate
something that looks like this (a bit like a crosstab):

Person Jan Score Feb Score Mar Score etc
Tom 6 5 7
Jon 0 1 2

Where the Jan score of 6 is the total of Sept 08 to Jan 09.

We're looking at whole years at a time, so excluding the year name from the
column titles is deliberate as I simply need to update the monthIDs to move
to next year.

There's one more facet. I've decided to store the monthID of the first
month of the current year in a table called tbl_StartDateEndDate as
YearStartMonthID (Jan 09 being 300)

So for Jan, I need all the scores for a person where the corresponding
MonthID is between 300 and (300-5) or in the terms of this query,
[tbl_StartDateEndDate]![YearStartMonthID] and
([tbl_StartDateEndDate]![YearStartMonthID]-5)

To do this, I've created a new query in design view and added the following
tables:
tbl_StartDateEndDate
qry_TCC_Union_Aggregator (where my source data is coming from)

I've then added two fields to the query, the first is the person's ID, the
second is the following expression (which I seem to have messed up):
01 Points: IIf([qry_TCC_Union_Aggregator]![TimeMonthId] Between
[tbl_StartDateEndDate]![YearStartMonthID] And
([tbl_StartDateEndDate]![YearStartMonthID]-5),Sum([qry_TCC_Union_Aggregator]![SumOfSumOfTotalPoints]),0)

(This would create the Jan score - I can worry about the others once I've
got this one working!)

Finally, I've made this a summary query, so group by the person's ID, and (I
guess) set the expression to "expression"...

If you can tell me where I'm going wrong, I'll be very grateful.

Thanks!

Tom.
 
MGFoster said:
mr-tom said:
Hi all,

I'm trying to produce some results over a rolling six months, so e.g.
somebody's total score in Jan 09 is the sum of their scores for Aug 08 to Jan
09; their Feb 09 total score is the sum of their scores for Sept 08 to Feb
09, etc.

Don't worry about year ends, we have a month ID which increments by one each
month, regardless of year (Jan 09 was 300, Dec 08 was 299 etc)

This may be the wrong way to go about it, but I'm trying to generate
something that looks like this (a bit like a crosstab):

Person Jan Score Feb Score Mar Score etc
Tom 6 5 7
Jon 0 1 2

Where the Jan score of 6 is the total of Sept 08 to Jan 09.

We're looking at whole years at a time, so excluding the year name from the
column titles is deliberate as I simply need to update the monthIDs to move
to next year.

There's one more facet. I've decided to store the monthID of the first
month of the current year in a table called tbl_StartDateEndDate as
YearStartMonthID (Jan 09 being 300)

So for Jan, I need all the scores for a person where the corresponding
MonthID is between 300 and (300-5) or in the terms of this query,
[tbl_StartDateEndDate]![YearStartMonthID] and
([tbl_StartDateEndDate]![YearStartMonthID]-5)

To do this, I've created a new query in design view and added the following
tables:
tbl_StartDateEndDate
qry_TCC_Union_Aggregator (where my source data is coming from)

I've then added two fields to the query, the first is the person's ID, the
second is the following expression (which I seem to have messed up):
01 Points: IIf([qry_TCC_Union_Aggregator]![TimeMonthId] Between
[tbl_StartDateEndDate]![YearStartMonthID] And
([tbl_StartDateEndDate]![YearStartMonthID]-5),Sum([qry_TCC_Union_Aggregator]![SumOfSumOfTotalPoints]),0)

(This would create the Jan score - I can worry about the others once I've
got this one working!)

Finally, I've made this a summary query, so group by the person's ID, and (I
guess) set the expression to "expression"...

If you can tell me where I'm going wrong, I'll be very grateful.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Put the criteria in the WHERE clause instead of the SELECT clause (the
following is just for one 5 month period total):

SELECT person_id, SUM(Q.SumOfSumOfTotalPoints) As TotalPoints
FROM qry_TCC_Union_Aggregator As Q, tbl_StartDateEndDate As T
WHERE Q.TimeMonthID BETWEEN T.YearStartMonthID - 5 And
T.YearStartMonthID
GROUP BY person_id

If you want a "cross-tab" query you'd have something like this (this
query assumes that qry_TCC_Union_Aggregator has the full year's data):

SELECT person_name,
SUM(IIf(Q.MonthID BETWEEN T.YearStartMonthID-5 And T.YearStartMonthID,
Q.SumOfSumOfTotalPoints)) As "Jan Score",

SUM(IIf(Q.MonthID BETWEEN T.YearStartMonthID-4 And
T.YearStartMonthID+1, Q.SumOfSumOfTotalPoints)) As "Feb Score",

SUM(IIf(Q.MonthID BETWEEN T.YearStartMonthID-5 And
T.YearStartMonthID+2, Q.SumOfSumOfTotalPoints)) As "Mar Score",

< ... etc. ... you get the idea? >

FROM qry_TCC_Union_Aggregator As Q, tbl_StartDateEndDate As T
WHERE Q.MonthID BETWEEN T.YearStartMonthID-5 And T.YearStartMonthID + 12
GROUP BY person_name

In the SUM(IIf(...)) I don't put a <false-expression> since it defaults
to NULL in an SQL statement.

The WHERE clause retrieves all rows (records) for the full year. You
may want to change the "+ 12" after last T.YearStartMonthID since I
don't know what your end-of-year MonthID number is.

NOTE: Even though Access accepts "BETWEEN hi And low" try to use
"BETWEEN low And hi" 'cuz that is the SQL standard. And - you should
use periods (.) as separators between table names and column (field)
names. Access allows you to use exclamation points (!), but other SQL
engines require periods - just in case you upgrade your queries to a
different SQL engine.

If I were you I'd use actual dates instead of the artificial MonthID,
'cuz you have an extra table that you have to maintain and it makes the
query a little more work intensive for any new programmer at your
company (learning curve).

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSmohy4echKqOuFEgEQLb1ACg51eAtPycjUU0fSQav3uGaQrH6KEAnjIF
DqxHJJf1aJ+ggoW+KhFH7moC
=V69n
-----END PGP SIGNATURE-----
Click to expand...


Thanks - I'll give that a try and report back.

Tom.
 
MGFoster said:
mr-tom said:
Hi all,

I'm trying to produce some results over a rolling six months, so e.g.
somebody's total score in Jan 09 is the sum of their scores for Aug 08 to Jan
09; their Feb 09 total score is the sum of their scores for Sept 08 to Feb
09, etc.

Don't worry about year ends, we have a month ID which increments by one each
month, regardless of year (Jan 09 was 300, Dec 08 was 299 etc)

This may be the wrong way to go about it, but I'm trying to generate
something that looks like this (a bit like a crosstab):

Person Jan Score Feb Score Mar Score etc
Tom 6 5 7
Jon 0 1 2

Where the Jan score of 6 is the total of Sept 08 to Jan 09.

We're looking at whole years at a time, so excluding the year name from the
column titles is deliberate as I simply need to update the monthIDs to move
to next year.

There's one more facet. I've decided to store the monthID of the first
month of the current year in a table called tbl_StartDateEndDate as
YearStartMonthID (Jan 09 being 300)

So for Jan, I need all the scores for a person where the corresponding
MonthID is between 300 and (300-5) or in the terms of this query,
[tbl_StartDateEndDate]![YearStartMonthID] and
([tbl_StartDateEndDate]![YearStartMonthID]-5)

To do this, I've created a new query in design view and added the following
tables:
tbl_StartDateEndDate
qry_TCC_Union_Aggregator (where my source data is coming from)

I've then added two fields to the query, the first is the person's ID, the
second is the following expression (which I seem to have messed up):
01 Points: IIf([qry_TCC_Union_Aggregator]![TimeMonthId] Between
[tbl_StartDateEndDate]![YearStartMonthID] And
([tbl_StartDateEndDate]![YearStartMonthID]-5),Sum([qry_TCC_Union_Aggregator]![SumOfSumOfTotalPoints]),0)

(This would create the Jan score - I can worry about the others once I've
got this one working!)

Finally, I've made this a summary query, so group by the person's ID, and (I
guess) set the expression to "expression"...

If you can tell me where I'm going wrong, I'll be very grateful.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Put the criteria in the WHERE clause instead of the SELECT clause (the
following is just for one 5 month period total):

SELECT person_id, SUM(Q.SumOfSumOfTotalPoints) As TotalPoints
FROM qry_TCC_Union_Aggregator As Q, tbl_StartDateEndDate As T
WHERE Q.TimeMonthID BETWEEN T.YearStartMonthID - 5 And
T.YearStartMonthID
GROUP BY person_id

If you want a "cross-tab" query you'd have something like this (this
query assumes that qry_TCC_Union_Aggregator has the full year's data):

SELECT person_name,
SUM(IIf(Q.MonthID BETWEEN T.YearStartMonthID-5 And T.YearStartMonthID,
Q.SumOfSumOfTotalPoints)) As "Jan Score",

SUM(IIf(Q.MonthID BETWEEN T.YearStartMonthID-4 And
T.YearStartMonthID+1, Q.SumOfSumOfTotalPoints)) As "Feb Score",

SUM(IIf(Q.MonthID BETWEEN T.YearStartMonthID-5 And
T.YearStartMonthID+2, Q.SumOfSumOfTotalPoints)) As "Mar Score",

< ... etc. ... you get the idea? >

FROM qry_TCC_Union_Aggregator As Q, tbl_StartDateEndDate As T
WHERE Q.MonthID BETWEEN T.YearStartMonthID-5 And T.YearStartMonthID + 12
GROUP BY person_name

In the SUM(IIf(...)) I don't put a <false-expression> since it defaults
to NULL in an SQL statement.

The WHERE clause retrieves all rows (records) for the full year. You
may want to change the "+ 12" after last T.YearStartMonthID since I
don't know what your end-of-year MonthID number is.

NOTE: Even though Access accepts "BETWEEN hi And low" try to use
"BETWEEN low And hi" 'cuz that is the SQL standard. And - you should
use periods (.) as separators between table names and column (field)
names. Access allows you to use exclamation points (!), but other SQL
engines require periods - just in case you upgrade your queries to a
different SQL engine.

If I were you I'd use actual dates instead of the artificial MonthID,
'cuz you have an extra table that you have to maintain and it makes the
query a little more work intensive for any new programmer at your
company (learning curve).

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSmohy4echKqOuFEgEQLb1ACg51eAtPycjUU0fSQav3uGaQrH6KEAnjIF
DqxHJJf1aJ+ggoW+KhFH7moC
=V69n
-----END PGP SIGNATURE-----
Click to expand...

Hi, I've started implementing this with your simple sum to begin with, which
I think in my environment should work like this:

SELECT [Adviser Alias], SUM(Q.SumOfSumOfTotalPoints) As TotalPoints
FROM qry_TCC_Union_Aggregator As Q, tbl_StartDateEndDate As T
WHERE Q.TimeMonthID BETWEEN T.YearStartMonthID - 5 And
T.YearStartMonthID
GROUP BY [Adviser Alias]

I've not really played with SQL much, so please forgive any dodgy syntax.

Unfortunately this doesn't work. I've changed the personID to Adviser Alias
(yes sadly there is a space in that name, something I can probably remove if
required).

I'm sure the error is around my [Adviser Alias] bit.

The error is:
"Data Type Mismatch In Criteria Expression"

Any ideas?

Cheers,

Tom.

P.S. All other fields are named exactly as you have them
 
Sorted this out - needed to use CInt() on the months.

Thanks so much for all your help!

Tom.




mr-tom said:
MGFoster said:
mr-tom said:
Hi all,

I'm trying to produce some results over a rolling six months, so e.g.
somebody's total score in Jan 09 is the sum of their scores for Aug 08 to Jan
09; their Feb 09 total score is the sum of their scores for Sept 08 to Feb
09, etc.

Don't worry about year ends, we have a month ID which increments by one each
month, regardless of year (Jan 09 was 300, Dec 08 was 299 etc)

This may be the wrong way to go about it, but I'm trying to generate
something that looks like this (a bit like a crosstab):

Person Jan Score Feb Score Mar Score etc
Tom 6 5 7
Jon 0 1 2

Where the Jan score of 6 is the total of Sept 08 to Jan 09.

We're looking at whole years at a time, so excluding the year name from the
column titles is deliberate as I simply need to update the monthIDs to move
to next year.

There's one more facet. I've decided to store the monthID of the first
month of the current year in a table called tbl_StartDateEndDate as
YearStartMonthID (Jan 09 being 300)

So for Jan, I need all the scores for a person where the corresponding
MonthID is between 300 and (300-5) or in the terms of this query,
[tbl_StartDateEndDate]![YearStartMonthID] and
([tbl_StartDateEndDate]![YearStartMonthID]-5)

To do this, I've created a new query in design view and added the following
tables:
tbl_StartDateEndDate
qry_TCC_Union_Aggregator (where my source data is coming from)

I've then added two fields to the query, the first is the person's ID, the
second is the following expression (which I seem to have messed up):
01 Points: IIf([qry_TCC_Union_Aggregator]![TimeMonthId] Between
[tbl_StartDateEndDate]![YearStartMonthID] And
([tbl_StartDateEndDate]![YearStartMonthID]-5),Sum([qry_TCC_Union_Aggregator]![SumOfSumOfTotalPoints]),0)

(This would create the Jan score - I can worry about the others once I've
got this one working!)

Finally, I've made this a summary query, so group by the person's ID, and (I
guess) set the expression to "expression"...

If you can tell me where I'm going wrong, I'll be very grateful.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Put the criteria in the WHERE clause instead of the SELECT clause (the
following is just for one 5 month period total):

SELECT person_id, SUM(Q.SumOfSumOfTotalPoints) As TotalPoints
FROM qry_TCC_Union_Aggregator As Q, tbl_StartDateEndDate As T
WHERE Q.TimeMonthID BETWEEN T.YearStartMonthID - 5 And
T.YearStartMonthID
GROUP BY person_id

If you want a "cross-tab" query you'd have something like this (this
query assumes that qry_TCC_Union_Aggregator has the full year's data):

SELECT person_name,
SUM(IIf(Q.MonthID BETWEEN T.YearStartMonthID-5 And T.YearStartMonthID,
Q.SumOfSumOfTotalPoints)) As "Jan Score",

SUM(IIf(Q.MonthID BETWEEN T.YearStartMonthID-4 And
T.YearStartMonthID+1, Q.SumOfSumOfTotalPoints)) As "Feb Score",

SUM(IIf(Q.MonthID BETWEEN T.YearStartMonthID-5 And
T.YearStartMonthID+2, Q.SumOfSumOfTotalPoints)) As "Mar Score",

< ... etc. ... you get the idea? >

FROM qry_TCC_Union_Aggregator As Q, tbl_StartDateEndDate As T
WHERE Q.MonthID BETWEEN T.YearStartMonthID-5 And T.YearStartMonthID + 12
GROUP BY person_name

In the SUM(IIf(...)) I don't put a <false-expression> since it defaults
to NULL in an SQL statement.

The WHERE clause retrieves all rows (records) for the full year. You
may want to change the "+ 12" after last T.YearStartMonthID since I
don't know what your end-of-year MonthID number is.

NOTE: Even though Access accepts "BETWEEN hi And low" try to use
"BETWEEN low And hi" 'cuz that is the SQL standard. And - you should
use periods (.) as separators between table names and column (field)
names. Access allows you to use exclamation points (!), but other SQL
engines require periods - just in case you upgrade your queries to a
different SQL engine.

If I were you I'd use actual dates instead of the artificial MonthID,
'cuz you have an extra table that you have to maintain and it makes the
query a little more work intensive for any new programmer at your
company (learning curve).

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSmohy4echKqOuFEgEQLb1ACg51eAtPycjUU0fSQav3uGaQrH6KEAnjIF
DqxHJJf1aJ+ggoW+KhFH7moC
=V69n
-----END PGP SIGNATURE-----
Click to expand...

Hi, I've started implementing this with your simple sum to begin with, which
I think in my environment should work like this:

SELECT [Adviser Alias], SUM(Q.SumOfSumOfTotalPoints) As TotalPoints
FROM qry_TCC_Union_Aggregator As Q, tbl_StartDateEndDate As T
WHERE Q.TimeMonthID BETWEEN T.YearStartMonthID - 5 And
T.YearStartMonthID
GROUP BY [Adviser Alias]

I've not really played with SQL much, so please forgive any dodgy syntax.

Unfortunately this doesn't work. I've changed the personID to Adviser Alias
(yes sadly there is a space in that name, something I can probably remove if
required).

I'm sure the error is around my [Adviser Alias] bit.

The error is:
"Data Type Mismatch In Criteria Expression"

Any ideas?

Cheers,

Tom.

P.S. All other fields are named exactly as you have them
Click to expand...
 
Back
Top