grouping items on same report

[atray04]

I have a 2 fields that I am trying use dynamically in my report: item and itemid. When I run my report each item is listed once for each record. I am trying to group the records by file information and a record or file may have more than just one item.

Heres an example of what I have:
record 1:
Inspector: Joe Department: Food
Date: 4/20/04 Area: Stove
Score: 45

EquipmentA: 2

record 2:
Inspector: Joe Department: Food
Date: 4/20/04 Area: Stove
Score: 45

EquipmentB: 3

and so on.....

I would like my report to be more like this:
record 1:
Inspector: Joe Department: Food
Date: 4/20/04 Area: Stove
Score: 45

EquipmentA: 2
EquipmentB: 3
and so on....

How can I do this?

 

[atray04]

EquipmentA is itemid and : 2 is item
AND
EquipmentB is itemid and : 1 is item

 

[dhookom]

Can you create a group header and place all but your most detailed fields in the group header. Place the
EquipmentA: 2
EquipmentB: 3
in the detail section.

If this doesn't work then come back with a clearer description of what you have and what you want.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[atray04]

The problem here is that you can have only so many group headers and I would need about 25. So then how do I create more headers that is allowed? Would it require vb code?

-ATray

 

[dhookom]

This wasn't a clearer description of what you want (or what you have). Do you actually have 25 different fields or expressions that you would want to group by?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[atray04]

Yes I do, I had my fields in details, but if one of my fields had no data I would receive a blank. So I produced a header for each of my fields and used the format event to hide the header if there is no data for that field. The problem is im only allowed 10 headers.

if that is not more clear then what data would you like to see?

-ATray

 

[dhookom]

Do you have fields like EquipmentA, EquipmentB,... It might help if you explained your table structure and what you are attempting to do. I fear your tables might be very un-normalized.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[atray04]

Ok I have several querys that I have create from a table. My first query normalizes my table. My second query picks out the data that I need. This query contains a field called items. I wanted this field to become many fields so that each item would be a field, so I created a cross-tab query. Ok that is a little back ground info.

Now in my report I have it reference my cross-tab query and I created text boxes that point to all of my items. If I was to just put these text boxes under details I would get something like this:
Item1 2
Item2 5
Item3
Item4 8
Item5
Item6

This is not good because the boxes with no data is left there. I want to only show it as this:
Item1 2
Item2 5
Item4 8

So what I did to do this was put Item1 under Item1 Header, Item2 under Item2 Header, and so on.....

Then I used the format event and said if no data then hide the header. This solved my problem, but access is only allowing me to have 10 headers and I will need somewhere around 25 headers to do this. I hope this helps you understand my problem. Thanxs

-ATray

 

[dhookom]

I'm not sure why you "normalizes my table" then "become many fields so that each item would be a field" and finally expect to see a normalized view:
Item1 2
Item2 5
Item4 8

You have described what you are doing but not clearly defined what you are starting with and then what your final output will look like. I expect you might be able to use a multiple column subreport or modification of one of the crosstab reports samples found at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[atray04]

ok, I will give you more details on monday im going home. Really I just want to know if there is a way to insert more headers than access will let you. Like maybe through vb code. Well thank you for your help.

-Adam

 

[dhookom]

You can't have more than 10 group headers/footers. There might be solutions that meet your requirements but as stated, your requirements are well-defined (to us).

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[atray04]

Well I was trying to keep my example simple. I tried the link, but it does not seem to take care of my problem with the blanks on my report. I think you read my other posts on this project so I will give yous some samples of what I am getting now and what I want to have. Sorry about the formatting.

Records that I am getting:
RECORD 1
Inspector: Jerry Department: Electrical
Date: 4/20/2004 Area: Tranasformer
Total Score: 66

Individual Scores:

Walkways 3 Maintenance Cabinets 1 Safety Latches 3
Platforms 2 Equipment 2 Duct Tape 1
Cabinet Tops 1 Electrical Boxes 3 Paint 3
Inside Cabinets 2 Conduit 1 Masonry 1

RECORD 2
Inspector: Alicia Department: Electrical
Date: 2/25/2004 Area: Drying
Total Score: 65

Individual Scores:

Walkways 2 Maintenance Cabinets 3 Safety Latches 3
Platforms 1 Equipment 1 Duct Tape 1
Cabinet Tops 3 Electrical Boxes 2 Paint 2
Inside Cabinets 2 Conduit 3 Masonry 3

RECORD 3
Inspector: Alicia Department: Natural
Date: 6/29/2004 Area: Dicer
Total Score: 93

Individual Scores:

Walkways 3 Maintenance Cabinets 3 Safety Latches 2
Platforms 3 Equipment 3 Duct Tape 3
Cabinet Tops 3 Electrical Boxes 3 Paint 4
Inside Cabinets 3 Conduit 3 Masonry 3

This is what I am trying to do:
RECORD 1
Inspector: Jerry Department: Electrical
Date: 4/20/2004 Area: Tranasformer
Total Score: 66

Individual Scores:

Platforms 2 Equipment 2
Cabinet Tops 1 Conduit 1
Inside Cabinets 2 Duct Tape 1
Maintenance Cabinets 1 Masonry 1

RECORD 2
Inspector: Alicia Department: Electrical
Date: 2/25/2004 Area: Drying
Total Score: 65

Individual Scores:

Walkways 2 Electrical Boxes 2
Platforms 1 Duct Tape 1
Inside Cabinets 2 Paint 2
Equipment 1

RECORD 3
Inspector: Alicia Department: Natural
Date: 6/29/2004 Area: Dicer
Total Score: 93

Individual Scores:

Safety Latches 2


I have managed to get my reports to look like this:

RECORD 1
Inspector: Jerry Department: Electrical
Date: 4/20/2004 Area: Tranasformer
Total Score: 66

Individual Scores:

Walkways Maintenance Cabinets 1 Safety Latches
Platforms 2 Equipment 2 Duct Tape 1
Cabinet Tops 1 Electrical Boxes Paint
Inside Cabinets 2 Conduit 1 Masonry 1

RECORD 2
Inspector: Alicia Department: Electrical
Date: 2/25/2004 Area: Drying
Total Score: 65

Individual Scores:

Walkways 2 Maintenance Cabinets Safety Latches
Platforms 1 Equipment 1 Duct Tape 1
Cabinet Tops Electrical Boxes 2 Paint 2
Inside Cabinets 2 Conduit Masonry

RECORD 3
Inspector: Alicia Department: Natural
Date: 6/29/2004 Area: Dicer
Total Score: 93

Individual Scores:

Walkways Maintenance Cabinets Safety Latches 2
Platforms Equipment Duct Tape
Cabinet Tops Electrical Boxes Paint
Inside Cabinets Conduit Masonry

As you can see any item value that is >2 is a blank. I would like to get rid of the empty spaces all together. It was suggested to me earlier that I try puting each item in a different heading so that I may hide the headings, but since I have around 25 items I can not do that. My report's source is my cross tab query that I created which basically lists all of my items, inspector, etc. the items values in this query have already been sorted and if the items values are >2 then the value is "". I know this is a big post, but you asked for a more detailed response. Thank you for your help and any more help that you can muster.

-ATray

 

[dhookom]

I wouldn't use a crosstab. I would use a multicolumn subreport from normalized data and filter out records where the values are greater than 2.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.