Results 1 to 9 of 9
  1. #1
    NISMOJim is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    273

    Spreadsheet style

    I'm having a problem figuring out how to make a report work. I am building off a query that has the following fields;
    Course
    Name
    DateCompleted
    I would like a report that looks like a matrix or spreadsheet as follows;



    Course 1 Course 2 Course 3 Course 4 Course 5
    Name 1 1/10/08 2/14/09 9/27/10
    Name 2 2/14/09 5/12/10 1/19/09
    Name 3 12/4/10 11/19/07 5/26/10 4/13/11
    etc...

    Of course each record wants to start a new line, even when grouped by course, so names show up repeatedly instead of all on one line.
    I am thinking about using a label for each person and another for each course (maybe in a group header or footer) and then a bunch of textboxes with IF statements to complete the grid. This seems way too complicated, and can only be updated with a new person or course by manually making another label.
    Is this type of report possible in Access 2007, or should I just export the data to Excel & work something out with that?
    Thanks for any suggestions.

    Edit:
    OK, that format didn't show up quite like I typed it, but you get the idea like on a spreadsheet how some of the boxes are blank here & there.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    No, this type of data manipulation is not easily done with Access and queries. I do produce a report like this with VBA code and a 'temp' table, a table that holds records for the process then is purged afterwards. You can see example at http://forums.aspfree.com/microsoft-...ry-322123.html

    EDIT: Crosstab query possible solution, see later post.
    Last edited by June7; 05-21-2011 at 10:33 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    look into the CrossTab feature. (query or report) It comes closest to providing what you want. It really helps to have an Access textbook when first working with this feature - as they all provide step by step examples. Easily found at Amazon or any large book store.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Wow, crosstab does seem to work with this data, as long as there aren't more than 256 courses:
    TRANSFORM First(Table1.[DateCompleted]) AS FirstOfDateCompleted
    SELECT Table1.[Name]
    FROM Table1
    GROUP BY Table1.[Name]
    PIVOT Table1.[Course];

    Use the Query wizard. Access Help has some info.

    Unfortunately, crosstab does not work for my data, not without a bunch of intermediate queries to prep the data and then I can have only 24 records (now fields left to right) per page so still need VBA code to grab 24, print page and repeat.
    UPDATE: Takes a lot of intermediate queries to get my data in structure that crosstab can work with. The last query before crosstab has to write records to temp table because the crosstab errors on the intermediate query because of table alias name. Weird.
    Last edited by June7; 05-21-2011 at 08:28 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    NISMOJim is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    273
    I just tried the crosstab query (query only so far, not the report). The layout looks perfect and easily expandable when names or courses are added. The only problem is what to do with the Totals row for the date. The query shows every row of each column as the same date depending on what is put in the Totals row of the query (First, Last, etc.), but not the actual date that each person completed the training. Is there a way to show the actual date completed in each cell instead of the First, Last, Sum, etc.?

    Another thing I tried on the report is adding an array of textboxes to the detail section like a spreadsheet as follows;
    Name: A1, A2...B1, B2...C1, C2...etc.
    Control Source:=IIf([EName]="Smith, John"* And [Course]="ABC",[TrngDt])
    Each box with it's own IIf statement depending on Name & Course.
    The idea is to then make them all too short to be visible and only the footer section really shows up on the report
    This also works good in the detail section, each box showing the date in the correct box for each record displayed. In the page footer section is another set of these spreadsheet boxes where the control source equals the corresponding boxes in the detail section. The problem with this is that out of the whole array of boxes in the footer only one shows a date, and that is of the last record. All the other boxes are blank. I have also tried moving the boxes to the report footer with the same results.

    A solution for the crosstab query would be ideal since it is automatically expandable, however if there is only a solution for the report part showing all of the dates, I don't mind updating the reports manually as people or courses are added.

    Thanks again for helping me out with my ineptitude.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    My test shows output according to your sample. Because this uses the First function instead of Count or Sum or Avg, I believe requires that the source table have Course and Name as unique value when combined. If this is not the case will need to do preliminary query to pull the latest record for each.

    Headers/Footer show only one record. Not understanding need to repeat data in headers/footers. Modification of report for additional people/courses might be okay if doesn't happen often, but if is chronic and crosstab won't work, recommend VBA solution.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    NISMOJim is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    273
    My Training table has fields for Last Name, First Name & Middle Initial. In a Names query, I selected these fields, and added an EName field that combines Last Name, First Name & Middle Initial. EName is what I'm using as the row heading, and Course from the Training table as the column heading, along with TrainingDate from the Training table. Is the ENames field what is throwing me off? Still only getting the first date for each course filling in the whole column.

    As for the report angle, changes wouldn't happen too often, although I have another one to build when I get this working that will have a few hundred people to keep track of. The rows & columns of IIF textboxes I added in the detail section are all shown for each record (they will be hidden when working properly), although only one box for each record would have a date as the result of the expression. What I was hoping for in the footer is the spreadsheet look where FooterA1 = DetailA1, etc. to capture the combination of all of these values, showing only once on the report. I tend to do things the hard way, but I hope that makes sense.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Shouldn't be a problem. I tried to replicate your table structure as you describe. I did a crosstab directly from the table. Still works.
    TRANSFORM First(Table1.DateCompleted) AS FirstOfDateCompleted
    SELECT [LastName] & ", " & [FirstName] & " " & [MiddleInitial] AS EName
    FROM Table1
    GROUP BY [LastName] & ", " & [FirstName] & " " & [MiddleInitial]
    PIVOT Table1.Course;

    Can you provide your db or at least the table(s) of interest for this problem? Just remove any confidential data (phone numbers, etc.)

    Is it really important to have the data left to right on report instead of conventional grouping?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    NISMOJim is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    273
    This worked out perfectly. I was getting EName from another query instead of joining them in this query and naming it EName. That must have made all the difference. I just threw a report together and everything is working as planned. Thanks so much for your help!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Generating a Gnantt-style chart
    By Mtraugh in forum Reports
    Replies: 1
    Last Post: 03-24-2011, 05:51 PM
  2. Importing Spreadsheet
    By derfalpha in forum Access
    Replies: 1
    Last Post: 01-04-2011, 10:47 AM
  3. ListViewCtrl - XP Style Display
    By GarretNeedsHelp in forum Forms
    Replies: 0
    Last Post: 04-11-2010, 09:17 AM
  4. Display report in spreadsheet style
    By w2vijay in forum Reports
    Replies: 1
    Last Post: 02-11-2010, 11:51 AM
  5. Extracting data from a disastrous excel-style Table
    By milehighfreak in forum Import/Export Data
    Replies: 2
    Last Post: 12-16-2009, 07:13 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums