Crosstab Report

[kindred169]

I'm attempting to setup a report at my plant. I assume I need to setup a Crosstab report, but I don't really know how to go about doing it. I'm running into a few problems so if you can solve any or all, I'd appreciate it. Essentially, I have 3 tables. One has employee data such as name, pay number, and shift. Its linked by pay number to another table that has employee spending by FW as well as total spending for the year and thats linked to a table that just has fiscal week (the numbers 1-52).
Problem one is the FW values are text not number (I have no control over this, its the way the company has it set up) so I use Val([FW].[FW]) in order for it to sort properly. This for some reason converts the FW to dollars. Not sure why.
Second, I want to setup a report that gives an employees name and then horizontally displays his spending for FW1, FW2, FW3, etc. up to FW52 then lists the next employee and does the same. I know I could just do a crosstab query but one it doesn't present it as a report, and two I need to select what shift of workers I want to draw from and crosstab queries don't allow user input.
Third, every worker doesnt buy something every week so for example WorkerA doesn't buy anything until week 11, I need every value up until week 11 to be blank or 0. The table I'm drawing from does not allow me insert 0's on weeks they don't purchase things so I have to have the report just understand that and correct for it.
I've tried using a format similar to the sample report Microsoft provides called "SalesAverage" but I can't seem to apply it to my work. Any suggestions or help are welcomed! Thanks.

 

[sqrob]

hi kindred169

would the use of the midstring function solve your issue with your week numbers? i am assuming that your numbering scheme is FW1, FW2 ... FW52. if it is then insert your variables into this : Mid(string, start[, length]) where string is your FW, start is the position to begin picking up data and the length can be ignored as some of your variables are 3 characters in length while others are 4.

if your scheme is FW-1, FW-2 ... FW-52, just add one more to your start position.

don't forget to format to numbers, rather than currency.

hope this helps with the first part of your problem.

 

[dhookom]

1) If all your FW are text and they are used as column headings then it doesn't make any difference how they sort. Just find the Crosstab Query Properties and enter your values in the the Column Headings Property. You should have 52 values entered into this property.
2) You can filter your crosstab by entering any criteria/parameter in the Query Parameters dialog. This might be something like (assuming a text shift field)
[Enter Shift] Text
3) See #1

Come on back if you have other questions.

Duane
MS Access MVP