Help taking horizontal data and displaying it vertically (continuously)

[MikeRei]

Hello,

I am writing, because I have an issue that I just cannot overcome, and this site seems like the place to go with MS Access/VBA questions.

I have a table that has roughly 20 different date fields attached to records by their JOB_ID number. It is all in a single table, and I have no issue building a report that outputs a single records in the format that I would like to use. The issue is display the data in a continuous manner vertically. Each JOB_ID should have it's own column, and all dates listed below from top-to-bottom.

I have not been successful trying to get the layout right, so I was hoping someone on here could give me a hand.

Please let me know if more information is needed.

Thank you!

 

[Andrzejek]

some months there will be 2 entries"
Do you mean "some months there will be 2 DROP_NUMBER entries" (records)
or
"some months there will be [date] 2 entries:
START_DATE[highlight #FCE94F]1[/highlight] END_DATE[highlight #FCE94F]1[/highlight] START_DATE[highlight #FCE94F]2[/highlight] END_DATE[highlight #FCE94F]2[/highlight]" with the rest of the dates empty?

"some months there will be 20"
20 DROP_NUMBERs or 20 pairs of START_DATE# END_DATE#

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Mike,

The information you give must be...

CLEAR, CONCISE & COMPLETE.

What happens in vagueness, stays in vagueness!


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[MikeRei]

My apologies, my friend...

I meant that some months there will be only a couple of columns to include (unique JOB#s). Some months, I may have 20+ columns to include (unique JOB#s).

I am querying on any record that has a [MAIL DATE] that starts with the month the user selects, so some months there are a couple of columns to include (by Job#), and some there may be many more.

If I am being unclear, please just let me know. I appreciate all of the help and support I have been given. Your input has helped me a lot.

Many thanks!

Mike

 

[dhookom]

Can we assume by "columns" you are referring to derived columns (based on Job_ID) which have nothing to do with fields? Did you try my suggestion regarding multi-column layout?



Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SkipVought]

I am trying to figure out if I should export it to Excel, or just have a report that can be ran and shared.

Assuming that you have Excel 365

Use a parameter query...

SELECT *
FROM YourTable
WHERE Year(START_DATE1)=? And Month(START_DATE1)=?

Either:
1) EXPORT your query resuts and IMPORT into Excel
2) Query YourTable in Access from Excel

Then:
Simply use the TRANSFORM() function to change all your Data Rows to Columns.

For instance, if your Query Date resides on sheet Query starting in cell A1, then on sheet Transform in A1
A1: =TRANSFORM(OFFSET(Query!A1,0,0,COUNTA(Query!A:A),COUNTA(Query!1:1)))

My example uses a parameter query in Excel accessing data on sheet Data...

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[MikeRei]

dhookom, I messed around with the report, but I cannot get the labels on the left side using this method.

SkipVought, I have a way to export to Excel currently, but my issue is modifying the column widths and cell colors. I do not have much experience modifying Excel through VBA.

 

[SkipVought]

Explain what column width adjustments you need and what colors.

Column width adjustment to fit text length is simple. Please state your requirements.

Cell color can be changed to match certain content automatically through the Conditional Formatting feature. Please state your requirements.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[dhookom]

There are still methods to get the labels on the left. I created a main report with just the labels (cut from an report I will use as the subreport). I used the multi-column report as the subreport and removed the labels. On the main report, I selected all of the labels on the left and set the tag properties to "label".

This is the main report design view:

To get more sets of labels on the left for more sets of columns, I added the code as follows to the On Print of the main report:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    Dim ctl As Control
    Dim intCounter As Integer
    Dim intC As Integer    'largest number of labels required/possible
    intC = 3
    For Each ctl In Me.Section(0).Controls
        If ctl.Tag = "label" Then
            Me.CurrentX = 0
            For intCounter = 1 To intC
                Me.CurrentX = 0
                Me.CurrentY = ctl.Top + Me.Section(0).Height * intCounter
                Me.Print ctl.Caption
            Next
        End If
    Next
End Sub

The final result is:

If you have more than two sets of columns, we may need to adjust the code.
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016