How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Richard Your Own Question

Richard
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 16842
Experience:  Over 15 year experience resolving Microsoft Office Issues
32989067
Type Your Microsoft Office Question Here...
Richard is online now
A new question is answered every 9 seconds

If I have a spreadsheet with 3 rows of data about a student,

Customer Question

If I have a spreadsheet with 3 rows of data about a student, with most of the fields in the 3 rows being repetitive, can I combine these 3 rows to display as one row that displays all of the non-repetitive data, by creating a pivot table? If so, how do I do this. My example is releated to a college report produced by a query that checks a database for students in danger of failing. Of course, if there is a student that is danger in multiple classes, it displays all 3 in the spreadsheet, repeating the personal information(name, number, address, etc.) in each row and listing the courses. The users want all 3 courses to be displayed in one row, along with one copy of the personal info. It would look like this:

name address Crn Subj Crs Num Crs title Instructor (then start again with next course on same line)
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Rachel-Mod replied 1 year ago.
Hello,
I'm a moderator for this topic. I've been working hard to find a professionals to assist you with your Question , but sometimes finding the right professionals can take a little longer than expected.

I wonder whether you're ok with continuing to wait for an answer. If you are, please let me know and I will continue my search. If not, feel free to let me know and I will cancel this question for you.

Thank you!
Customer: replied 1 year ago.


I can wait a little longer.

Expert:  Rachel-Mod replied 1 year ago.
Thank you for your patience. We will continue the search for a professional for you.
Rachel
Expert:  Richard replied 1 year ago.
Thank you for your question, my name is Richard.

Yes you can and you can Group within the Pivot Table.

Select all the data, and then create a Pivot Table on the data,

It should Group this (removing the duplicates) within the chart.

If not, upload the file so I can look at it directly.

- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"
Customer: replied 1 year ago.


I highlighted the data and then did insert, pivot table and created a pivot table and chose some basic fields. It does appear to group it by student, but it's not in a very readable format and I don't know enough about pivot tables to make it readable. It also has the big blank box that says "Drop column fields here" and "Drop data items here". I don't know what to do with that. Can you please give me the step by step on how to turn it into a readable report. The file number is XXXXX Thanks!

Expert:  Richard replied 1 year ago.
Thanks,

Start with unticking all but one field to add to the report.

This you see under Choose Fields to Add to Report.

Only add the field that you want to display, this will not repeat.

This is the main field, then drag additional fields to the right of it for Calculations on it.

Are you able to do this?

Customer: replied 1 year ago.

Yes. I started with the id field, which is displayed in the 1st column on the left. Then I dragged the course title field into the "drop data items here" block. It creates another column just to the right of the id column, but the new column takes a heading of "Total" and just displays a count of the number of courses each student is failing. How do I get it to display the course title(s)(plural for those failing multiple courses) all on one row?

Expert:  Richard replied 1 year ago.
Can you download this file

http://wikisend.com/download/936170/spreadsheet with pivot table.xls

Is this what you require?

Customer: replied 1 year ago.


Can't download it. When I put the mouse on the link, it changes color to orange and doesn't give me the pointer to click on the link.

Expert:  Richard replied 1 year ago.
Can you try this link please

http://tinyurl.com/cumrx9r

Let me know if you have any difficulties please

Customer: replied 1 year ago.


That's not what I'm looking for. If you go back to the original spreadsheet and look at the first 2 lines. The student's name isXXXXX and he's failing 2 courses(ENGL 102 and PECS 110). I want the pivot table to combine these 2 lines into one line that lists all of his personal information, then lists both courses(with other info about each course) back to back, reading from left to right, on the same line with his personal info. I want only one line per student, even if they're failing multiple classes. If it can't be done by a pivot table, it could be done using MSAccess if you know how.

Expert:  Richard replied 1 year ago.
Ok, what you want is Database functionality, not a Spreadsheet or a Pivot Table.

I made a Database in Access for you, the issue is your data needs cleaning up.

You can review your data imported from the excel files in the Tables in my Database file.

And the Main Form shows the records linked. Though you will see blanks as the data in Excel has blanks.

You can download the file here

http://tinyurl.com/c7mzkgx

If you clean up the data, you can view each student and their linked records.

If you have any difficulties at all, please do not hesitate to let me know so I can assist you further

Thank you
Expert:  Richard replied 1 year ago.
Hello,

Where you able to review the Database I made for you?

Customer: replied 1 year ago.


Yes, but unless it's just a matter of me not knowing how to use it, I don't think it solves my problem. When I open it, I see the 2 forms and I can put in an id # XXXXX the fmain form and it looks up some information for the student. If I open the other form, it displays the courses and id, but it still has multiple lines per student. Is there something I'm supposed to do to make it display all the information for a student on one single line?


 


This all comes from MSAccess macros and queries that pull this data from oracle tables and generate the report in Access. I think you may be on the right path by separating the data into 2 tables and then querying the two tables so that it generates 1 line of output per student, even if that student is failing(or in danger of failing) more than 1 class, but I need to know how to put this in the original query, so when the user runs it at midterm, it automatically does all the steps to produce the output they are looking for. Let me know if you need to see any of the queries or macros that we run.

Expert:  Richard replied 1 year ago.
You only open the Main form, the other form is linked inside the main form.

As I indicated you first need to Clean Up the data in the Tables, by removing empty rows, columns ect.

You just copy the data into the Tables in Access to do this.

Customer: replied 1 year ago.

I apologize for my lack of experience with Access, but I'm not understanding what I'm supposed to do to see a report. When I open fmain, it's just a blank form with the fields id, etc., off to the left hand side. Am I supposed to key in an id# XXXXX pull up information for that student? Thanks for your patience as i try to learn this.
Expert:  Richard replied 1 year ago.
It is no problem.

You use the Navigation buttons at the bottom.

They are like Play buttons to go through the record.

You also see the record number.

You see this at the bottom?

Customer: replied 1 year ago.


Yes. When I go to the 2nd record(Josh Pittman), it displays 2 lines for him with fields ID1,Campus,id,AS(blank),CL,GST(Blank),Private,Action(Blank),PS1(Blank) and part of PS2(Blank). Are you saying that if i remove the empty columns, that it will pull the rest of Josh's information onto 1 row(or record), instead of spreading it over 7 or 8 records? If so, how do I delete a column that's blank?


 


As a side note, I'm not sure that i will be able to remove these fields from the users report, b/c even though they may be empty columns for the students selected for this semester, students selected in subsequent semesters may have information attached to their records that belongs in these empty fields. Thanks!!

Expert:  Richard replied 1 year ago.
It is the empty Rows.

Look in the Tables in Access, You will see the Data like Excel, except each row needs to be complete.

You can delete the rows within the Table there.

Let me know how you go please.

Customer: replied 1 year ago.

I went into the Grades table and removed the blank columns. Do I have to go into the Names table and remove the rows that only have information in 1 column?. For Josh Pittman in the names table, I need to manually delete 6 rows?

Expert:  Richard replied 1 year ago.
Correct, you just want one name.

As the ID of the Student links to Multiple Rows in the other Table (based on the ID).

So one Name in Name and in the other Table you can have the multiple courses and information.

Customer: replied 1 year ago.

Even after I delete the empty rows, it still displays the courses in multiple rows for any student that's failing multiple classes. They want the courses displayed on the same line if a student has more than one.
Also, Would my user have to go thru and perform all these manual deletes every time they run the macros and queries that generate the Access report?
Expert:  Richard replied 1 year ago.
You have the user enter the data directly into Access, you should not be using Excel for your data entry work, this is why you have the problem.

It is going to display a course per row, this is how databasing works, as each course is its own record.

Why is this an issue?

Customer: replied 1 year ago.
Relist: Other.
The expert wasn't really listening to my problem and what I was trying to do. i kept having to explain it over and over.
Expert:  Richard replied 1 year ago.
I am, you are not understanding how databasing works, this is the issue.

Can you carefully look at my last statement,

Again, provide me the database so I can look at it.

You are not explaining over and over, you are not noting what I am trying to tell you.

Expert:  Richard replied 1 year ago.
Please let me know
Expert:  Richard replied 1 year ago.
Thanks

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 

Related Microsoft Office Questions

Chat Now With A Microsoft Office Technician
Richard
Richard
Microsoft Office Technician
16842 Satisfied Customers
Over 15 year experience resolving Microsoft Office Issues