Random month column

  • Thread starter Thread starter Robbie Doo
  • Start date Start date
R

Robbie Doo

I have a report that 3 columns. One shows the last (latest) month totals.
Second column shows the records from previous period and the third column
shows the YTD records and the Labels;

Clients Dec 2008 Oct 2008 Cumulative Since (Date)


When I pull the report using a criteria Between (let's say) 1/1/2008 and
12/31/2008. I get the Dec 2008 totals alright, Cumulative is alright. But I
can't get the Oct 2008 records and label to work right.

It's the same situation if I change the end date to another month (say
9/30/2008). The previous records before September are not shown correctly.

Any suggestions/help?
 
Hi Karl,

The data collection before Dec was done in Oct. No data for Nov. So the
immediate data that should show in the column is Oct. And if I chose the End
Date to be Oct the immediate month before that the data was collected was in
May. So the column should show May.

Here's the copy of the SQL;

SELECT Main_NA.Disposition, Main_NA.[SB 87 Step], Main_NA.SampleMonth, [NA
Disposition].DispID, Main_NA.Districts, Districts.Division,
Report_Status.Status_Id, Report_Status.Status
FROM Report_Status, [NA Disposition] INNER JOIN (Divisions INNER JOIN
(Districts INNER JOIN Main_NA ON Districts.DistNo = Main_NA.Districts) ON
Divisions.DIV = Districts.Division) ON [NA Disposition].DispID =
Main_NA.Disposition
WHERE (((Main_NA.SampleMonth) Between [Forms].[Cri_Date_Stat].[txtStartDate]
And [Forms].[Cri_Date_Stat].[txtEndDate]) AND ((Main_NA.Districts) Not Like
9) AND ((Report_Status.Status_Id)=[Forms].[Cri_Date_Stat].[txtStatus]))
ORDER BY Main_NA.SampleMonth;

Thank you.
 
The SQL you post does not total anything. In looking at the field names I
do not see anything that appears like it would be totaled.

Disposition, [SB 87 Step], SampleMonth, DispID, Districts, Division,
Status_Id, and Status do not seem like field to to be totaled.

If you data is collected that sporadically how is Access to know which is
current and last collected month?


Robbie Doo said:
Hi Karl,

The data collection before Dec was done in Oct. No data for Nov. So the
immediate data that should show in the column is Oct. And if I chose the End
Date to be Oct the immediate month before that the data was collected was in
May. So the column should show May.

Here's the copy of the SQL;

SELECT Main_NA.Disposition, Main_NA.[SB 87 Step], Main_NA.SampleMonth, [NA
Disposition].DispID, Main_NA.Districts, Districts.Division,
Report_Status.Status_Id, Report_Status.Status
FROM Report_Status, [NA Disposition] INNER JOIN (Divisions INNER JOIN
(Districts INNER JOIN Main_NA ON Districts.DistNo = Main_NA.Districts) ON
Divisions.DIV = Districts.Division) ON [NA Disposition].DispID =
Main_NA.Disposition
WHERE (((Main_NA.SampleMonth) Between [Forms].[Cri_Date_Stat].[txtStartDate]
And [Forms].[Cri_Date_Stat].[txtEndDate]) AND ((Main_NA.Districts) Not Like
9) AND ((Report_Status.Status_Id)=[Forms].[Cri_Date_Stat].[txtStatus]))
ORDER BY Main_NA.SampleMonth;

Thank you.

KARL DEWEY said:
Post the SQL of your query.

You do know that Dec is not preceded by Oct but by Nov.
Click to expand...
Click to expand...
 
The only thing that is being summed is [SB 87 Step] because it has 3
different steps. All the steps are being counted in Random month order.

KARL DEWEY said:
The SQL you post does not total anything. In looking at the field names I
do not see anything that appears like it would be totaled.

Disposition, [SB 87 Step], SampleMonth, DispID, Districts, Division,
Status_Id, and Status do not seem like field to to be totaled.

If you data is collected that sporadically how is Access to know which is
current and last collected month?


Robbie Doo said:
Hi Karl,

The data collection before Dec was done in Oct. No data for Nov. So the
immediate data that should show in the column is Oct. And if I chose the End
Date to be Oct the immediate month before that the data was collected was in
May. So the column should show May.

Here's the copy of the SQL;

SELECT Main_NA.Disposition, Main_NA.[SB 87 Step], Main_NA.SampleMonth, [NA
Disposition].DispID, Main_NA.Districts, Districts.Division,
Report_Status.Status_Id, Report_Status.Status
FROM Report_Status, [NA Disposition] INNER JOIN (Divisions INNER JOIN
(Districts INNER JOIN Main_NA ON Districts.DistNo = Main_NA.Districts) ON
Divisions.DIV = Districts.Division) ON [NA Disposition].DispID =
Main_NA.Disposition
WHERE (((Main_NA.SampleMonth) Between [Forms].[Cri_Date_Stat].[txtStartDate]
And [Forms].[Cri_Date_Stat].[txtEndDate]) AND ((Main_NA.Districts) Not Like
9) AND ((Report_Status.Status_Id)=[Forms].[Cri_Date_Stat].[txtStatus]))
ORDER BY Main_NA.SampleMonth;

Thank you.

KARL DEWEY said:
Post the SQL of your query.

You do know that Dec is not preceded by Oct but by Nov.


:

I have a report that 3 columns. One shows the last (latest) month totals.
Second column shows the records from previous period and the third column
shows the YTD records and the Labels;

Clients Dec 2008 Oct 2008 Cumulative Since (Date)


When I pull the report using a criteria Between (let's say) 1/1/2008 and
12/31/2008. I get the Dec 2008 totals alright, Cumulative is alright. But I
can't get the Oct 2008 records and label to work right.

It's the same situation if I change the end date to another month (say
9/30/2008). The previous records before September are not shown correctly.

Any suggestions/help?
Click to expand...
Click to expand...
Click to expand...
 
But the SQL you posted DOES NOT COUNT!

The SQL you posted does not separate data into different months.

If all you want out is - Clients Dec 2008 Oct 2008 Cumulative Since
(Date)
- why all of the other fields?

Robbie Doo said:
The only thing that is being summed is [SB 87 Step] because it has 3
different steps. All the steps are being counted in Random month order.

KARL DEWEY said:
The SQL you post does not total anything. In looking at the field names I
do not see anything that appears like it would be totaled.

Disposition, [SB 87 Step], SampleMonth, DispID, Districts, Division,
Status_Id, and Status do not seem like field to to be totaled.

If you data is collected that sporadically how is Access to know which is
current and last collected month?


Robbie Doo said:
Hi Karl,

The data collection before Dec was done in Oct. No data for Nov. So the
immediate data that should show in the column is Oct. And if I chose the End
Date to be Oct the immediate month before that the data was collected was in
May. So the column should show May.

Here's the copy of the SQL;

SELECT Main_NA.Disposition, Main_NA.[SB 87 Step], Main_NA.SampleMonth, [NA
Disposition].DispID, Main_NA.Districts, Districts.Division,
Report_Status.Status_Id, Report_Status.Status
FROM Report_Status, [NA Disposition] INNER JOIN (Divisions INNER JOIN
(Districts INNER JOIN Main_NA ON Districts.DistNo = Main_NA.Districts) ON
Divisions.DIV = Districts.Division) ON [NA Disposition].DispID =
Main_NA.Disposition
WHERE (((Main_NA.SampleMonth) Between [Forms].[Cri_Date_Stat].[txtStartDate]
And [Forms].[Cri_Date_Stat].[txtEndDate]) AND ((Main_NA.Districts) Not Like
9) AND ((Report_Status.Status_Id)=[Forms].[Cri_Date_Stat].[txtStatus]))
ORDER BY Main_NA.SampleMonth;

Thank you.

:

Post the SQL of your query.

You do know that Dec is not preceded by Oct but by Nov.


:

I have a report that 3 columns. One shows the last (latest) month totals.
Second column shows the records from previous period and the third column
shows the YTD records and the Labels;

Clients Dec 2008 Oct 2008 Cumulative Since (Date)


When I pull the report using a criteria Between (let's say) 1/1/2008 and
12/31/2008. I get the Dec 2008 totals alright, Cumulative is alright. But I
can't get the Oct 2008 records and label to work right.

It's the same situation if I change the end date to another month (say
9/30/2008). The previous records before September are not shown correctly.

Any suggestions/help?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top