creating a report to count by month

[kwooten]

I am creating a report that shows gains and losses among codes. I have the gain figured out but cant get the loss, it just repeats the gain number since the gain number is assigned a loss date.



For example, in February I gained 2 code 10's (gains have an expiration of 2099) and lost 1 code 3.

I have attached an excel file of the data and 2 pivot tables that i was using to check my data.

Can anyone help me build this?

[Robeen]

I'm having trouble understanding what you need to do.
Could you explain with a little more detail?

[kwooten]

I need to create a report that tracks my gains and losses of each code for each month. The report I have created is counting the gains correctly (probably because I have a group on the gain date). I have loss next to gains but cant get it to calculate.

This is a picture of what it looks like so far.. It does not have to be in this format but does need to be clearly readable..

[Robeen]

So - all you're trying to do is count the number of records in the Loss column?

What code is in the text box that is displaying your Gain values?
And what code is in the text box that is supposed to be displaying your Loss values?

It seems to me - from your picture - that all months that are displayed have a gain - in which case there won't be a loss - right?

Can you tell us how the report is getting its data?

[kwooten]

The code in the Gain is count(LOB Enter Date). I dont have a code in the loss box. All months should be shown. Just because there isnt a gain doesnt mean I do not have a loss.

If CEO has a gain in January but a loss in February then January Gain will show 1 while the loss shows 0, however in February gain will show 0 and loss will show 1.. Does that make sense?

I am pulling the data through ODBC but in the OP I have attached an excel file of that data.

[Robeen]

What is the Record Source for the report?

In the Property Sheet for the Report - in the Data Tab - what do you have beside 'Record Source'?

Is it a Table, a Query . . . ?

[kwooten]

it is a query. I have the data coming in through ODBC and have created some fields to "group" my month data (so january 3rd and january 23rd are in the same bucket). I am thinking because I have it grouped for the Gain Date it isnt allowing me to value the loss for that particular month.

[Robeen]

I was thinking something along those lines.

Perhaps you could create a separate Query for Loss and then Combine the two queries with a third query that pulls in Gains AND Losses and then use the 3rd query as the data source for your report?

I have several Reports that use this method to display data.

Let me know if you need clarification.

[kwooten]

how will I group it in the report to make sure it is counting for January, February, ect...

[kwooten]

The query I have now calculates both Gains and Losses.. Since it is all based on dates, effdate and todate (effdate being the gain and todate being the loss). The query looks something like:

EFFDATE (GAIN) TODATE(LOSS)
CEO 1/2/2011
CEO 5/12/2011 8/19/2011

[kwooten]

here is a better picture

[kwooten]

i think i got this figured out.. I took out the month grouping and made it a total gain and total loss.. I dont have to worry about grouping by each month.

[Robeen]

Can you go to View - SQL View in your Query and paste the SQL here so we can look at it?

You can change the field names if you want - but I would like to see what your query looks like so I can answer your question better because it seems to me that your problem will be solved with Queries.

If I can see the SQL for your query it will be easier for me to understand what you are already doing.

[kwooten]

I have a formula to assign the month a number so i could get January to show up first instead of April.


SELECT HUMANIC_CPNYHIST_MASTER.HCOMPANYDESC, MonthName(Month([HUMANIC_CPNYHIST_MASTER.EFFDATE])) & " " & DatePart("yyyy",[HUMANIC_CPNYHIST_MASTER.EFFDATE]) AS [LOB ENTER DATE], (MonthName(Month([HUMANIC_CPNYHIST_MASTER.TODATE])) & " " & DatePart("yyyy",[HUMANIC_CPNYHIST_MASTER.TODATE])) AS [LOB EXIT DATE], IIf([LOB EXIT DATE]='DECEMBER 2099',Null,[LOB EXIT DATE]) AS [LOB EXIT], HUMANIC_JOBHIST_MASTER.HJOBGRD, IIf([LOB ENTER DATE]='JANUARY 2011',1,IIf([LOB ENTER DATE]='FEBRUARY 2011',2,IIf([LOB ENTER DATE]='MARCH 2011',3,IIf([LOB ENTER DATE]='APRIL 2011',4,IIf([LOB ENTER DATE]='MAY 2011',5,IIf([LOB ENTER DATE]='JUNE 2011',6,IIf([LOB ENTER DATE]='JULY 2011',7,IIf([LOB ENTER DATE]='AUGUST 2011',8,IIf([LOB ENTER DATE]='SEPTEMBER 2011',9,IIf([LOB ENTER DATE]='OCTOBER 2011',10,IIf([LOB ENTER DATE]='NOVEMBER 2011',11,IIf([LOB ENTER DATE]='DECEMBER 2099',12)))))))))))) AS [MONTH NUMBER]
FROM (HUMANIC_EMPHEADER_MASTER INNER JOIN HUMANIC_CPNYHIST_MASTER ON HUMANIC_EMPHEADER_MASTER.SSN = HUMANIC_CPNYHIST_MASTER.HSSN) INNER JOIN HUMANIC_JOBHIST_MASTER ON (HUMANIC_CPNYHIST_MASTER.TODATE = HUMANIC_JOBHIST_MASTER.TODATE) AND (HUMANIC_CPNYHIST_MASTER.HSSN = HUMANIC_JOBHIST_MASTER.HSSN)
GROUP BY HUMANIC_CPNYHIST_MASTER.HCOMPANYDESC, HUMANIC_JOBHIST_MASTER.HJOBGRD, HUMANIC_CPNYHIST_MASTER.HSSN, HUMANIC_CPNYHIST_MASTER.HCOMPANY, HUMANIC_CPNYHIST_MASTER.EFFDATE, HUMANIC_CPNYHIST_MASTER.TODATE, HUMANIC_JOBHIST_MASTER.CHGREASON, HUMANIC_EMPHEADER_MASTER.ENUMBER
HAVING (((HUMANIC_CPNYHIST_MASTER.EFFDATE)>=#1/1/2011#) AND ((HUMANIC_CPNYHIST_MASTER.TODATE)>=#1/1/2011#) AND ((HUMANIC_JOBHIST_MASTER.CHGREASON) In ('PROMOTION','LATERAL','DEMOTION')))
ORDER BY HUMANIC_CPNYHIST_MASTER.HCOMPANYDESC;