Displaying different values of one field on one report

[VicM]

Hope I can be clear on this.

I've created a report to fill in a regulatory agency's annual submittal form for residual waste shipments to landfills, etc.

The form basically has 3 sections

1) A generator's demographic (name, address, ID#, etc.) and a waste code describing the type of waste.

2) Section containing six identical areas for the facilities where the waste was shipped, the amounts shipped and their physical state (liq., solid, gas, etc.). So if we shipped the same waste in two forms, e.g. solid and liquid, to the same facility, that would comprise two entries in this section; one for the solid, one for the liquid.

Each of the raw records also has an Activity Code associated with it. The activity code is not used in the grouping criteria for section 2 because aggregating the wastes for this section pertains only to waste code, facility, physical state and sums of weights for each of these groupings. However, the activity code comes into play in section 3.

3) This section of the form contains a line where up to four activity codes are to be entered for the waste code/facility/physical state groups in section 2. IF there are more, we just have to enter four of them.) Theoretically, one line item in section 2 might have more than one activity code, some of which may be similar to codes of the other line items in section 2.

I can create a query where I can capture all the activity codes for the combinations in section 2. The query will display multiple records for a given line item in section 2 if that line item has multiple activity codes associated with it, as well as the activity codes associated with other line item.

But I'm at a loss in how to display up to any four of these codes on the same row.

If anyone has any thoughts on a possible solution, it would be enormously appreciated, as these forms must be filed before the end of February.

Thanks in advance,

Vic

 

[scriverb]

I would like to see the SQL for that query you are talking about so that I can be more accurate in my analysis. But, from what you are tell us you must be performing some kind of join between your table and another associated table like tblActivityCodes to get the multiple rows correct?

Please give an example of the table and a sample of the SQL to show how you are doing this. Bob Scriver

 

[VicM]

scriverb:

Won't be back in office till Monday. Will try to respond to your request then.

Thanks,

Vic

 

[VicM]

scriverb:

The SQL statements contain more information than is needed in order to demonstrate this problem. I have distilled the information and hopefully presented it so you can understand it better.

Perhaps for additional clarification I can point you to the actual form I'm attempting to fill in. The address is :

http://www.dep.state.pa.us/eps/docs...103d/doc2002bbb013902e/2560-fm-lrwm0330gm.pdf

The Acrobat page for the form (330-GM)is 18 or 19. (Two identical forms are supplied in the document.)

The area of the form I'm having problems with is in section V., Waste Specific Source Reduction Activity. You will note in the lower left corner there are four 2-digit areas, each preceded with a W.

The activity codes are defined in the above document on Acrobat pages 25 and 26.

Each of the line items in section IV is the sum of the waste amounts shipped to a facility in a year and grouped according to physical state and a unit type code. But each of the raw data records comprising the aggregate for each line item can have a different activity code. Sample of raw data:

WCode Facil Quant PSt UTCode ActCode
001 Facil1 23560  S 05 W99
310 Facil2 100  S 02 W62
310 Facil2 250  S 05 W63
310 Facil3 400  S 05 W67
507 Facil1 28260  S 05 W14
507 Facil1 24200  S 05 W21
507 Facil1 31200  S 05 W22
507 Facil1 9600  S 05 W24
507 Facil1 42320  S 05 W25


These raw records are aggregated to sum the quantities and be grouped by waste code, shipped-to facility, physical state and unit type code. For purposes of section IV, the activity codes are not included since each of the raw records can be different. Sample of the aggregated data, from which each waste code is entered onto a separate form, and each facility, for a given waste code, is entered onto a separate line item in section IV:

WCode Facil Quant PSt UTCode
001 Facil1 23560  S 05
310 Facil2 350  S 05
310 Facil3 400  S 05
507 Facil1 135580  S 05


For section V, I'm using a subreport which filters for waste code and activity code, grouping by each. In this way I will have unique records contaning a waste code and an activity code. There then can be anywhere from 1 to ? records with the same waste code but different activity codes. An example of the SQL result for the subform is:

WasteCode ActCode
001 W99
310 W62
310 W63
310 W66
507 W14
507 W21
507 W22
507 W24
507 W25


This is where I need to pick out up to four of those activity codes and enter them into section V of each form. So for the form containing waste code 001 there is only 1 activity code. For the form containing waste code 310 there are 3 activity codes. And for the form containing waste code 507 there are 5 activity codes, but I only need to show 4 of them.

Hope this explains the situation more clearly.

Thanks,
Vic