Display field values horizontally

[annie52]

My main report contains DisrepNum and various fields relating to that discrepancy report.

I want to create a subreport using a table that contains the DisrepNum and a FollowUpDate field. After linking the DisrepNum field, I will hide it in the subreport. The problem is that I cannot figure out how to display the FollowUpDate values horizontally.

In other words, if I follow up on a particular report seven different times, I want to see those seven dates listed horizontally below the line containing the report information. Crosstab queries and Columns don't seem to do this for me.

 

[dhookom]

You should be able to use a multiple column subreport that displays across then down. The main report record source should not have any of the followUpDate records.


Duane
Hook'D on Access
MS Access MVP

 

[annie52]

When I tried using multiple columns, it applied to the DisrepNum instead of the FollowUpDate. I'll try to do some more reading. Maybe I'm doing something wrong.

 

[dhookom]

Isn't DisrepNum displayed in the main report? You shouldn't need to display it in the subreport.

Duane
Hook'D on Access
MS Access MVP

 

[annie52]

Hi Duane. I didn't explain myself very well. I don't display the DisrepNum on the report. I set the subreport with four columns (across then down). When I run the report, it displays four columns across. Each column shows a vertical list of dates that apply to the 1st, 2nd, 3rd, and then 4th DisrepNum.

What I want is for the 7 dates that relate to the first DisrepNum to display horizontally. Then, on the next row, the 4 dates that apply to the second DisrepNum should also display horizontally.

 

[dhookom]

You don't have to display the DisrepNum. Your main report should have the DisrepNum as a unique value. The subreport will use this field as the link.

If this doesn't work, please come back with your significant table and field names as well as the SQL view of your main and subreport records sources.

Duane
Hook'D on Access
MS Access MVP

 

[annie52]

The main report (rptDisrepsOpen) is based on qryDisrepsOpen:

SELECT DISTINCT Disreps.DISREPNum, Disreps.DateFiled, Disreps.Followup, Technician.Initials, Disreps.Disposition, Disreps.DISREPStatus, Disreps.Reason
FROM ReqnWatch RIGHT JOIN (((Technician RIGHT JOIN Disreps ON Technician.Tech = Disreps.Tech) LEFT JOIN DisrepFollowups ON Disreps.DISREPNum = DisrepFollowups.DisrepNum) LEFT JOIN XRefReqnIDtoDisrepNum ON Disreps.DISREPNum = XRefReqnIDtoDisrepNum.XrefDisrepNum) ON ReqnWatch.ReqnID = XRefReqnIDtoDisrepNum.XrefReqnID
WHERE (((Disreps.Followup) Is Not Null));

The subreport (rptDisrepFollowupDates) is based on the table (DisrepFollowups). The table contains only two fields: [DisrepNum] and [FollowupDate].

The main report is linked to the subreport on the DisrepNum and the report is grouped on that field. I want to horizontally display all [FollowupDate] values just beneath the appropriate [DisrepNum] record. I've looked at crosstab queries and labels again but they just don't seem to apply. I guess Access doesn't like being treated as though it was a flatfile database.

 

[dhookom]

I'm not sure why you would have DisrepFollowups in the main report record source since you should display this information in your subreport only.

The subreports should be in the section containing unique DISREPNum values. I'm not sure of the relationships between your tables and if this value might appear multiple times in your record source.

Duane
Hook'D on Access
MS Access MVP