Access 2007 - Need Help with Report Design

[lrdave36]

So close, but so far. I have a report request that seems nearly impossible to do. They want the report to show like this:


3422 4332 5123
PRAJ IADC I ADC
PR00 IADC IA ADC


The numbers across are user id codes which is a field in my table. PRAJ and PR00 are trans codes which is another field. I have fields for each individual letter underneath the codes.


Here is what my table looks like:


User id Trans Code ADD_P CHG_P DEL_P I_P
_______ __________ ____ ____ _____ ___

3422 PR00 A C D I



The report is displaying the same data, it is just a problem of getting multiple user id fields to show up on the same line.

Unfortunately, Access wants to display the user ids vertically instead of horizontally across.

The only way I see to do this is to create a unique table for each user id and then plug those fields onto the same line. Is there an easier way?

 

[dhookom]

I wish you could have shown more than a single record. At least all the records it takes to get at the report sample layout you provided.

You can probably use a crosstab query as the record source of your report.

It would help if your table(s) were normalized. You might need to create a normalizing union query first but I can't tell.

Duane
Hook'D on Access
MS Access MVP

 

[lrdave36]

Thank you for the response. Sorry I should have showed more information. Here is a portion from the actual table:

0214	PRAK	I				
0214	PRAT	I	A	C		
0214	PR00	I	A	C	D	
0214	PR01	I	A	C	D	
0214	PR02	I	A	C	D	
0214	PR03	I	A	C	D	
0214	PR04	I	A	C	D	
0214	PR05	I	A	C	D	
0214	PR06	I				
0214	PR07	I				
0214	PR08	I	A	C	D	
0214	PR09	I	A	C	D	
0214	PR10	I				
0214	PR11	I				
0214	PR13	I	A	C	D	
0214	PR14	I				
0214	PR15	I	A	C	D	
0214	PR16	I	A	C	D	
0214	PR17	I	A	C	D	
0214	PR18	I	A	C	D	
0214	PR19	I	A	C	D	
0214	PR20	I	A	C	D	
0214	PR23	I	A	C	D	
0214	PR24	I				
0214	PR25	I				
0214	PR27	I				
0214	PR30	I	A	C	D	
0214	PR41	I				
0214	PR42	I	A	C	D	
0214	PR45	I	A	C	D	Q
0214	PR46	I				
0214	PR51	I	A	C	D	Q
0214	PR52	I	A	C	D	Q
0214	PR54	I	A	C	D	
0214	PR55	I				
0214	PR56	I	A	C	D	Q
0214	PR57	I	A	C	D	Q
0214	PR58	I				
0214	PR59	I				
0214	PR65	I				
0214	PR66	I				
0214	PR67	I	A	C	D	
0214	PR85	I	A	C	D	Q
0214	PR91	I				
0214	PR99	I				
0417	PR01	I				
0417	PR02	I				
0417	PR03	I	A	C	D	
0417	PR04	I				
0417	PR05	I	A	C	D	
0417	PR06	I	A	C	D	Q
0417	PR07	I				
0417	PR08	I	A	C	D	
0417	PR09	I				
0417	PR10	I				
0417	PR11	I				
0417	PR13	I				
0417	PR14	I				
0417	PR15	I				
0417	PR16	I	A	C		
0417	PR17	I				
0417	PR18	I				
0417	PR19	I				
0417	PR20	I	A	C	D	
0417	PR23	I				
0417	PR24	I				
0417	PR25	I				
0417	PR27	I				
0417	PR28	I				
0417	PR30	I				
0417	PR31	I	A	C	D	
0417	PR32	I	A	C	D	
0417	PR33	I	A	C	D	
0417	PR34	I				
0417	PR35	I				
0417	PR36	I				
0417	PR37	I				
0417	PR38	I				
0417	PR39	I	A	C	D	
0417	PR40	I	A	C	D

I've never worked with cross tab queries. I took a look, and their function is similar to what I want to accomplish. I want the user ids in the rows to become my column headers.

Unfortunatel when I tried to create the crosstab query, Access only let me add in 3 fields for some reason.

 

[dhookom]

You seem to have added another column and not provided a table name. I named the new field "New" and the table "Actual" based on "Here is a portion from the actual table"

Try a crosstab with SQL like:

TRANSFORM First([ADD_P] & [CHG_P] & [DEL_P] & [I_P] & [New]) AS Letters
SELECT Actual.[Trans Code]
FROM Actual
GROUP BY Actual.[Trans Code]
PIVOT Actual.[User ID];

Duane
Hook'D on Access
MS Access MVP