Crosstab Question on result 2

[kentwoodjean]

I have a crosstab query that works great with the exception of 1 thing. When my Dec. 2005 data was imported, it did not update my December month. That still reads the 12/04 data. Can someone explain why this is happening? below is my SQL.

TRANSFORM Max(ScoreWMSMo.EFFICIENCY) AS MaxOfEFFICIENCY
SELECT ScoreWMSMo.EMPID, Avg(ScoreWMSMo.EFFICIENCY) AS [Total Of EFFICIENCY]
FROM ScoreWMSMo
GROUP BY ScoreWMSMo.EMPID
PIVOT Format([Month],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[dhookom]

Is there anything in your query that limits the records to a specific date range? Your value is Max(Efficiency) regardless of the year.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kentwoodjean]

Not that I can see. The table does not distinquish a specific range of dates and there are 2 queries that lead up to the crosstab. I put their SQL below:

Query #1

SELECT [WMI EMP PERF Monthly FINAL].EMPID, [WMI EMP PERF Monthly FINAL].UNITS AS EfficUnits, [WMI EMP PERF Monthly FINAL].EFFICIENCY, [WMI EMP PERF Monthly FINAL].DATE, CDate(Mid([Date],5,2) & "/01/" & Left([Date],4)) AS [Month]
FROM [WMI EMP PERF Monthly FINAL]
GROUP BY [WMI EMP PERF Monthly FINAL].EMPID, [WMI EMP PERF Monthly FINAL].UNITS, [WMI EMP PERF Monthly FINAL].EFFICIENCY, [WMI EMP PERF Monthly FINAL].DATE;


Query #2
SELECT ScoreWMSEffic.EMPID, ScoreWMSEffic.Month, ScoreWMSEffic.EFFICIENCY
FROM ScoreWMSEffic;

 

[dhookom]

Ok, then why do you think an older date result shouldn't appear if old date records are included in your query? Are we supposed to know anything about:
-your data
-the importing
-the updating
You haven't given us any reason to think your system isn't working exactly as you have created it.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kentwoodjean]

I apologize if I am not following you. My table has monthly data by employee (a single number) beginning 10/04 through 12/05. Below is the sample data:

EMPID DATE UNITS EFFICIENCY
103486 200512 275 153.7744675
103486 200511 780 107.2686928
103486 200510 720 73.73856831
103486 200509 1262 120.9854954
103486 200508 708 127.1524926
103486 200507 764 93.04317379
103486 200506 1507 147.9474407
103486 200505 187 91.56045687
103486 200504 550 54.99950397
103486 200503 122 72.23783812
103486 200502 1 18.76172687
103486 200501 909 110.4274344
103486 200412 145 87.11690209
103486 200411 491 65.2229334
103486 200410 582 78.38013068

Since I have not listed any date parameters and I am using Max for the month, I would expect that my Months Jan-Dec would reflect 1/05 through 12/05 data. Instead it shows 1/05 through 11/05 and then 12/04. All the other months are perfect so I cannot figure out why December shows 04 rather than 05.

 

[dhookom]

You are finding the Max of the EFFICIENCY, not max of the DATE. Your query will look through each month and return the maximum of EFFICIENCY for that month. If your Dec of 2004 has a higher EFFICIENCY than Dec of any other year (the max) then Dec of 2004 value will be displayed.

With the data you provided, I would expect the crosstab to return an Oct value of 78.38 (2004) since the 2005 value is only 73.74. Isn't 78.38 the Max of 78.38 and 73.74?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kentwoodjean]

OK, now I see what you mean. I was out in my files today trying to figure out how to make it work. The queries leading up to my crosstab (this and other crosstab reports that I have) appears to be fine and are picking up the numbers correctly by month.

Now.....with the crosstab, since the value field is numerical, using "last" or "Max" is not working here either. There will be instances where a month may have no entry for a particular item, but the next month there is one. So, in some cases I am seeing 2004 data for a month simply because the employee did no perform that function this year.

what is the best way to have this rolling but still give me the most recent 12 months that had data?

 

[dhookom]

Set a criteria against the date field so that only the most recent 12 months is available to the crosstab.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kentwoodjean]

thanks Duane, that did the trick..........almost. I used the date criteria of: >=DateSerial(Year(Now()), Month(Now)-12,1). It shows everything but January of the current year. That is probably because the data does not usually arrive on the 1st of the month. It could be as late as 10-15 days into the following month. (I.e.) Jan records will arrive about Feb 10-15. Therefore, I would like my month of Jan06 to reflect my Jan05 results until my Jan06 files arrive.

Do I need to add something to my criteria above? If so, what would that be?

 

[PHV]

Perhaps this ?
Between DateSerial(Year(Now()), Month(Now())-13,1) And DateSerial(Year(Now()), Month(Now()),0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kentwoodjean]

Perfect. Thanks so much.