Sorting and Grouping

[vise]

Hi All,

I have a table in Access that I wish to sort and then group. The fields are as follows: shift, line number, and number of errors. I want to sort by shift; group by line numbers( [1,2,4 - sealing];[3,5,6,9 - grouting]; [7,8,10 - locking]), and number of errors per groups. I wish to create a graph according to the number of errors per shift and grouped line. I tried using the wizard in access, to no avail, I can't seem to get the line numbers to group. Thanks for your help!
I want something as such, if possible:

# of errors
|
| each colum represents a group of lines.
| |
| || |
| || | | ||
|__|||______|||_____|||__________
8-4 4-12 12-8
Shift
-vise

 

[vise]

sorry about the formatting, that's how it comes out

 

[dhookom]

Do you have a value in a field that groups your lines? If not, you should add one. Then, focus on the query in your row source possibly creating a crosstab query. I always create my graphs in forms and set the Locked = No and Enabled = Yes. Once you get the graph correct, you can copy and paste it onto the report.

Duane
MS Access MVP

 

[vise]

How would i go about adding such a field?
I tried creating an access crosstab query and i got the following sql view output. would you know how i can fix it up to get my output?

TRANSFORM Count(SetUpErrors.[# of Errors]) AS [CountOf# of Errors]
SELECT SetUpErrors.[Shift Responsible], SetUpErrors.[Line Number]
FROM SetUpErrors
GROUP BY SetUpErrors.[Shift Responsible], SetUpErrors.[Line Number]
ORDER BY SetUpErrors.[Shift Responsible]
PIVOT Format([Time],"mmm&quot In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"

thanks,
vise

 

[dhookom]

You first need to create a table of lines (if you don't have one) and add a field [LineGroup]. You can then add this table to your query and join the Line field to the SetUpErrors.[Line Number].

Duane
MS Access MVP