Counting w Criteria (1 Viewer)

[weilerdo]

Hi All, I have a problem that I can't seem to tget my head around. I have a table with a field called strMoYr with the values like "December 2008" I am running a query that I need a way to only count the last 12 months. Example would be if I run the query in January 2009 I only need it to look at records going back 12 months IE January 2008. I have tried a couple of different things like in my expression of Date('M' -12) Any help would be GREATLY appreciated. Here is the query as it stands right now ( im using a like 2008 but thats not what I need:

SELECT tblCT.Team, ([1st])+([2nd])+([3rd])+([4th])+([5th])+([6th])+([7th])+([8th])+([9th])+([10th])+([11th])+([12th])+([13th])+([14th])+([15th])+([16th])+([17th])+([18th])+([19th])+([20th])+([21st])+([22nd])+([23rd])+([24th])+([25th])+([26th])+([27th])+([28th])+([29th])+([29th])+([30th])+([31st]) AS TotalAbs, Sum(IIf([01]="A",1,0)) AS 1st, Sum(IIf([02]="A",1,0)) AS 2nd, Sum(IIf([03]="A",1,0)) AS 3rd, Sum(IIf([04]="A",1,0)) AS 4th, Sum(IIf([05]="A",1,0)) AS 5th, Sum(IIf([06]="A",1,0)) AS 6th, Sum(IIf([07]="A",1,0)) AS 7th, Sum(IIf([08]="A",1,0)) AS 8th, Sum(IIf([09]="A",1,0)) AS 9th, Sum(IIf([10]="A",1,0)) AS 10th, Sum(IIf([11]="A",1,0)) AS 11th, Sum(IIf([12]="A",1,0)) AS 12th, Sum(IIf([13]="A",1,0)) AS 13th, Sum(IIf([14]="A",1,0)) AS 14th, Sum(IIf([15]="A",1,0)) AS 15th, Sum(IIf([16]="A",1,0)) AS 16th, Sum(IIf([17]="A",1,0)) AS 17th, Sum(IIf([18]="A",1,0)) AS 18th, Sum(IIf([19]="A",1,0)) AS 19th, Sum(IIf([20]="A",1,0)) AS 20th, Sum(IIf([22]="A",1,0)) AS 21st, Sum(IIf([22]="A",1,0)) AS 22nd, Sum(IIf([23]="A",1,0)) AS 23rd, Sum(IIf([24]="A",1,0)) AS 24th, Sum(IIf([25]="A",1,0)) AS 25th, Sum(IIf([26]="A",1,0)) AS 26th, Sum(IIf([27]="A",1,0)) AS 27th, Sum(IIf([28]="A",1,0)) AS 28th, Sum(IIf([29]="A",1,0)) AS 29th, Sum(IIf([30]="A",1,0)) AS 30th, Sum(IIf([31]="A",1,0)) AS 31st
FROM tblCT INNER JOIN tblvactime ON tblCT.Team = tblvactime.Team
WHERE (((tblCT.strMoYr) Like "*2008"))
GROUP BY tblCT.Team;

Thanks Again for any help

 

[MSAccessRookie]

Hi All, I have a problem that I can't seem to tget my head around. I have a table with a field called strMoYr with the values like "December 2008" I am running a query that I need a way to only count the last 12 months. Example would be if I run the query in January 2009 I only need it to look at records going back 12 months IE January 2008. I have tried a couple of different things like in my expression of Date('M' -12) Any help would be GREATLY appreciated. Here is the query as it stands right now ( im using a like 2008 but thats not what I need:

SELECT tblCT.Team, ([1st])+([2nd])+([3rd])+([4th])+([5th])+([6th])+([7th])+([8th])+([9th])+([10th])+([11th])+([12th])+([13th])+([14th])+([15th])+([16th])+([17th])+([18th])+([19th])+([20th])+([21st])+([22nd])+([23rd])+([24th])+([25th])+([26th])+([27th])+([28th])+([29th])+([29th])+([30th])+([31st]) AS TotalAbs, Sum(IIf([01]="A",1,0)) AS 1st, Sum(IIf([02]="A",1,0)) AS 2nd, Sum(IIf([03]="A",1,0)) AS 3rd, Sum(IIf([04]="A",1,0)) AS 4th, Sum(IIf([05]="A",1,0)) AS 5th, Sum(IIf([06]="A",1,0)) AS 6th, Sum(IIf([07]="A",1,0)) AS 7th, Sum(IIf([08]="A",1,0)) AS 8th, Sum(IIf([09]="A",1,0)) AS 9th, Sum(IIf([10]="A",1,0)) AS 10th, Sum(IIf([11]="A",1,0)) AS 11th, Sum(IIf([12]="A",1,0)) AS 12th, Sum(IIf([13]="A",1,0)) AS 13th, Sum(IIf([14]="A",1,0)) AS 14th, Sum(IIf([15]="A",1,0)) AS 15th, Sum(IIf([16]="A",1,0)) AS 16th, Sum(IIf([17]="A",1,0)) AS 17th, Sum(IIf([18]="A",1,0)) AS 18th, Sum(IIf([19]="A",1,0)) AS 19th, Sum(IIf([20]="A",1,0)) AS 20th, Sum(IIf([22]="A",1,0)) AS 21st, Sum(IIf([22]="A",1,0)) AS 22nd, Sum(IIf([23]="A",1,0)) AS 23rd, Sum(IIf([24]="A",1,0)) AS 24th, Sum(IIf([25]="A",1,0)) AS 25th, Sum(IIf([26]="A",1,0)) AS 26th, Sum(IIf([27]="A",1,0)) AS 27th, Sum(IIf([28]="A",1,0)) AS 28th, Sum(IIf([29]="A",1,0)) AS 29th, Sum(IIf([30]="A",1,0)) AS 30th, Sum(IIf([31]="A",1,0)) AS 31st
FROM tblCT INNER JOIN tblvactime ON tblCT.Team = tblvactime.Team
WHERE (((tblCT.strMoYr) Like "*2008"))
GROUP BY tblCT.Team;

Thanks Again for any help

As long as your date fields are Text (Like "January 2009"), then you can uise the CDate() Function to conver t them to a date format. Once you do that, it is an easy step to calculate the date 12 months back using the DateAdd() Function.

As an Aside that does not change the solution to the issue, I would like to see the layout of the data, as it appears that the data may not be stored in the most efficient or effective manner.

 

[KenHigg]

Misc. comments

- 95% of the sql statement appears to have nothing to do with the problem
- This same 95% makes it next to impossible to read
- This caused me (personally), to write the thread off just at a glance

- The "([1st])+([2nd])+([3rd])+..." part is screaming normalization issues

 

[MSAccessRookie]

Misc. comments

- 95% of the sql statement appears to have nothing to do with the problem
- This same 95% makes it next to impossible to read
- This caused me (personally), to write the thread off just at a glance

- The "([1st])+([2nd])+([3rd])+..." part is screaming normalization issues

Ken,

You are abslutely correct. I thought the same thing, and that was what I meant when I said "the data may not be stored in the most efficient or effective manner" I just did not want to cloud the issue at the time.

 

[KenHigg]

Ken,

You are abslutely correct. I thought the same thing, and that was what I meant when I said "the data may not be stored in the most efficient or effective manner" I just did not want to cloud the issue at the time.

I didn't see your comments before I posted -

As far as the actual problem, when it comes to dates and reports I usually just let the user type in a couple of dates to use as a range when the report actually runs. This seems to work well and gets them greater flexibilty while not hard coding the parameters...

 

[weilerdo]

yes you both are correct... Unfortunetly that was the only way that we could come up with to make this database work. Bob and if I remember correctly Ken you helped me create the original. Its a Employee Schedule database.

I added the CDate() Function and am going to add the DateAdd part to see if that does it.

Thanks I will give that a try and let you know.