×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Help taking horizontal data and displaying it vertically (continuously)

Help taking horizontal data and displaying it vertically (continuously)

Help taking horizontal data and displaying it vertically (continuously)

(OP)
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!

RE: Help taking horizontal data and displaying it vertically (continuously)

A representative example of your table would be helpful along with how the report should look mapping the data in the table to your report.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Help taking horizontal data and displaying it vertically (continuously)

It would help if you provided some actual field names. You can create a union query like:

CODE --> SQL

SELECT JOB_ID, DateA As theDate, "A" As DateType
FROM YourTable
UNION ALL
SELECT JOB_ID, DateB, "B"
FROM YourTable
UNION ALL
SELECT JOB_ID, DateC, "C"
FROM YourTable; 

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

RE: Help taking horizontal data and displaying it vertically (continuously)

(OP)
Sorry everyone. Some representative data is below:

I need to take a table similar to this-

ID JOB_ID DROP_NUMBER RECORD_COUNT START_DATE1 END_DATE1 START_DATE2 END_DATE2 START_DATE3 END_DATE3 START_DATE4 END_DATE4 MAIL_VOLUME
1 12345 001 10000 7/1/2023 7/5/2023 7/6/2023 7/7/2023 7/8/2023 7/15/2023 7/15/2023 7/20/2023 9000
2 12345 002 11000 7/1/2023 7/5/2023 7/6/2023 7/7/2023 7/8/2023 7/15/2023 7/15/2023 7/20/2023 10500
3 12345 003 15000 7/1/2023 7/7/2023 7/7/2023 7/10/2023 7/11/2023 7/20/2023 7/20/2023 7/28/2023 14200
4 12550 001 5000 7/10/2023 7/13/2023 7/13/2023 7/14/2023 7/14/2023 7/16/2023 7/16/2023 7/19/2023 4800
5 12550 002 5500 7/22/2023 7/24/2023 7/25/2023 7/26/2023 7/27/2023 7/29/2023 7/29/2023 7/31/2023 5200
6 12600 001 25000 8/2/2023 8/4/2023 8/5/2023 8/8/2023 8/9/2023 8/15/2023 8/15/2023 8/23/2023 23500

(I do not know why the data shifted from the headings, but I am hoping you guys can make it out)

And have it output vertically so that the JOB_ID is at the top of the column, followed by the DROP_NUMBER, RECORD_COUNT, START_DATE1, END_DATE1, etc. This data will be selected based on the month and year that the START_DATE1 value falls into. The user will be prompted for the month and year, and the data selection should output the data vertically. The columns for this report will not be consistently the same from month-to-month. Some months there may be only a single entry, and other months there may be 20.

RE: Help taking horizontal data and displaying it vertically (continuously)

Use the Pre tag from the format icons above. Then use the same tag and data with the desired output.

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

RE: Help taking horizontal data and displaying it vertically (continuously)

ID JOB_ID DROP_NUMBER RECORD_COUNT START_DATE1 END_DATE1 START_DATE2 END_DATE2 START_DATE3 END_DATE3  START_DATE4 END_DATE4 MAIL_VOLUME
1  12345    001          10000      7/1/2023    7/5/2023   7/6/2023   7/7/2023   7/8/2023   7/15/2023 7/15/2023   7/20/2023   9000
2  12345    002          11000      7/1/2023    7/5/2023   7/6/2023   7/7/2023   7/8/2023   7/15/2023 7/15/2023   7/20/2023  10500
3  12345    003          15000      7/1/2023    7/7/2023   7/7/2023  7/10/2023  7/11/2023   7/20/2023 7/20/2023   7/28/2023  14200
4  12550    001           5000     7/10/2023   7/13/2023  7/13/2023  7/14/2023  7/14/2023   7/16/2023 7/16/2023   7/19/2023   4800
5  12550    002           5500     7/22/2023   7/24/2023  7/25/2023  7/26/2023  7/27/2023   7/29/2023 7/29/2023   7/31/2023   5200
6  12600    001          25000      8/2/2023    8/4/2023   8/5/2023   8/8/2023   8/9/2023   8/15/2023 8/15/2023   8/23/2023  23500
 
So. based on this data - how the output should look like if user selects: Month 7 and Year 2023 ponder

---- Andy

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

RE: Help taking horizontal data and displaying it vertically (continuously)

Is this what you are hoping to display?

Row	TheLabel       12345-1   12345-2   12345-3   12550-1    12550-2    12600-1
1	JOB_ID         12345     12345     12345     12550      12550      12600
2	DROP_NUMBER    1         2         3         1          2          1
3	RECORD_COUNT   10000     11000     15000     5000       5500       25000
4	START_DATE1    7/1/2023  7/1/2023  7/1/2023  7/10/2023  7/22/2023  8/2/2023
5	END_DATE1      7/5/2023  7/5/2023  7/7/2023  7/13/2023  7/24/2023  8/4/2023
6	START_DATE2    7/6/2023  7/6/2023  7/7/2023  7/13/2023  7/25/2023  8/5/2023
7	END_DATE2      7/7/2023  7/7/2023  7/10/2023 7/14/2023  7/26/2023  8/8/2023
8	START_DATE3    7/8/2023  7/8/2023  7/11/2023 7/14/2023  7/27/2023  8/9/2023
9	END_DATE3      7/15/2023 7/15/2023 7/20/2023 7/16/2023  7/29/2023  8/15/2023
10	START_DATE4    7/15/2023 7/15/2023 7/20/2023 7/16/2023  7/29/2023  8/15/2023
11	END_DATE4      7/20/2023 7/20/2023 7/28/2023 7/19/2023  7/31/2023  8/23/2023 

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

RE: Help taking horizontal data and displaying it vertically (continuously)

(OP)
dhookom, yes! That is the output that I need. I then want to format it on a report. How did you get to that outcome?

I apologize if I was not being specific enough. I don't really post to these kind of boards often.

I appreciate all of the help I can get!

RE: Help taking horizontal data and displaying it vertically (continuously)

(OP)
The row count in my source data could go anywhere from one record to 50 records, so I need to make sure it can grow as needed from left-to-right.

RE: Help taking horizontal data and displaying it vertically (continuously)

This requires a couple queries. The first is a union query to normalize the data. Typically the dates in your data would be stored in a separate, related table with each date creating a new record.

First create the union query quniJobs which must be written in SQL view:

CODE --> quniJobs

SELECT ID, 1 As Row, JOB_ID & "-" & DROP_NUMBER As Col,  "JOB_ID" AS TheLabel, JOB_ID AS TheValue
FROM tblMike
UNION ALL
SELECT ID, 2, JOB_ID & "-" & DROP_NUMBER, "DROP_NUMBER", DROP_NUMBER
FROM tblMike
UNION ALL
SELECT ID,3,  JOB_ID & "-" & DROP_NUMBER,"RECORD_COUNT", RECORD_COUNT
FROM tblMike
UNION ALL
SELECT ID, 4, JOB_ID & "-" & DROP_NUMBER, "START_DATE1", START_DATE1
FROM tblMike
UNION ALL
SELECT ID, 5, JOB_ID & "-" & DROP_NUMBER, "END_DATE1", END_DATE1
FROM tblMike
UNION ALL
SELECT ID, 6,  JOB_ID & "-" & DROP_NUMBER,"START_DATE2", START_DATE2
FROM tblMike
UNION ALL
SELECT ID, 7, JOB_ID & "-" & DROP_NUMBER, "END_DATE2", END_DATE2
FROM tblMike
UNION ALL
SELECT ID,8,  JOB_ID & "-" & DROP_NUMBER, "START_DATE3", START_DATE3
FROM tblMike
UNION ALL
SELECT ID, 9, JOB_ID & "-" & DROP_NUMBER, "END_DATE3", END_DATE3
FROM tblMike
UNION ALL
SELECT ID,10, JOB_ID & "-" & DROP_NUMBER, "START_DATE4", START_DATE4
FROM tblMike
UNION ALL 
SELECT ID,11, JOB_ID & "-" & DROP_NUMBER, "END_DATE4", END_DATE4
FROM tblMike; 

Then create a crosstab query qxtbJobs based on the union query:

CODE --> qxtbJobs

TRANSFORM First(quniJobs.TheValue) AS FirstOfTheValue
SELECT quniJobs.Row, quniJobs.TheLabel
FROM quniJobs
GROUP BY quniJobs.Row, quniJobs.TheLabel
PIVOT quniJobs.Col; 

Creating a report from this will be challenging since I expect the JOB_IDs will change over time. I have an example of creating a report from a dynamic crosstab on a friend's web site.

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

RE: Help taking horizontal data and displaying it vertically (continuously)

Me:
user selects: Month 7 and Year 2023
Duane: shows the output
MikeRei:
That is the output that I need

Are you OK with the last column? It is NOT for Month 7 and Year 2023

---- Andy

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

RE: Help taking horizontal data and displaying it vertically (continuously)

(OP)
Andrzejek, you are correct, the last row in my source data is for the month of August. I would need that row to be excluded if the user selected July of 2023 (month 7 and year 2023). One of the other issues I am having is selecting the correct recordset for a monthly output.

I am currently parsing out the month, day and year values from a field called MAIL_DATE in my source data, and then I have a form that allows the user to select the month number and year value (in separate fields). I then query on the two values that I parsed out from the MAIL_DATE that the user selected (USER_MONTH and USER_YEAR) to select the JOB_IDs that I want output in my report.

Is there any easier way around this when the MAIL_DATE field contains date values? I want the user to be able to select an entire month for the year versus typing in a beginning and end date.

You guys are the greatest!

RE: Help taking horizontal data and displaying it vertically (continuously)

Since you are going after the data in START_DATE1, why not just allow user to select from that field?

cboYears - Select Distinct Year(START_DATE1) From MyTable Order By 1
After Year is selected:
cboMonths - Select Distinct Month(START_DATE1) From MyTable Where Year(START_DATE1) = cboYears.Value Order By 1

---- Andy

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

RE: Help taking horizontal data and displaying it vertically (continuously)

I’m confused where MAIL_DATE comes from since it sounded like you were filtering on START_DATE1.

I would use a combo box to row source of:

SELECT Format(START_DATE1,"YYYY-MM") As ym
FROM YourTableName
GROUP BY Format(START_DATE1,"YYYY-MM")

Then you could use the same expression to filter your query.

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

RE: Help taking horizontal data and displaying it vertically (continuously)

Wouldn't that be simply:
SELECT DISTINCT Format(START_DATE1,"YYYY-MM") As ym
FROM YourTableName
Order By 1

smile

---- Andy

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

RE: Help taking horizontal data and displaying it vertically (continuously)

Andy, same result for the row source.

Mike, if you use a parameter in queries that feed a crosstab, you must set the data type of the parameter.

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

RE: Help taking horizontal data and displaying it vertically (continuously)

(OP)
OK, so after a little work on my end, I found a way to get what I needed for monthly selection, and I now have all of my data that I want included in the report stored into a single table. Here is where I am now:

[TestTable] contains the following fields;

JOB_NUMBER(combination of JOB_ID and DROP_NUMBER (formatted as [JOB_ID] & "-" & [DROP_NUMBER]))
CUSTOMER
PROJECT_MGR
ORIGINAL_COUNT
MAIL_VOLUME
PROJECT_NAME
MAIL_DATE
IMAGING_DATE
DATA_DATE
JOB1_DATE
JOB2_DATE
JOB3_DATE
JOB4_DATE
JOB5_DATE
JOB6_DATE
JOB7_DATE

I am going to attempt the dual-query approach laid out by dhookom above, and will be back once I have some time to do some testing.

Thank you, again!

RE: Help taking horizontal data and displaying it vertically (continuously)

You can also consider a multiple column report which might be much simpler. There is no code or extra queries. You would need to see how this is all handled with more jobs.

Design View


Print Preview

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

RE: Help taking horizontal data and displaying it vertically (continuously)

(OP)
I like the idea of the multi-column report, but my issue is dealing with the column count change each time. I would like a canned report that can be ran by the user for each month, but some months there will be 2 entries, and some months there will be 20. How can I get around that with the report?

I have the output that I want from the qxtbjobs query, so now I am trying to figure out if I should export it to Excel, or just have a report that can be ran and shared.

Thank you!

RE: Help taking horizontal data and displaying it vertically (continuously)

"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_DATE1 END_DATE1 START_DATE2 END_DATE2" with the rest of the dates empty?

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

---- Andy

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

RE: Help taking horizontal data and displaying it vertically (continuously)

Mike,

The information you give must be...

CLEAR, CONCISE & COMPLETE.

What happens in vagueness, stays in vagueness!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Help taking horizontal data and displaying it vertically (continuously)

(OP)
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

RE: Help taking horizontal data and displaying it vertically (continuously)

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

RE: Help taking horizontal data and displaying it vertically (continuously)

Quote:

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...

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Help taking horizontal data and displaying it vertically (continuously)

(OP)
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.

RE: Help taking horizontal data and displaying it vertically (continuously)

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Help taking horizontal data and displaying it vertically (continuously)

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:

CODE --> vba

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close