Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Easy Method to convert from row to column format

Posted on 2014-01-26
3
404 Views
Last Modified: 2014-03-03
I have a table with the following structure
  ProductID / ProductionDate / NumberProduced
  888               12/15/2013           25
  889               12/15/2013           17
  890               12/15/2013           19

  888               12/16/2013           12
  889               12/16/2013           30
 
  888               12/17/2013           75
  890               12/17/2013           50

I need to print a report that converts this from a row format to a columnar format
                      First          Second     Third
  Product      Date          Date          Date               Total
                     12/15/13   12/16/13  12/17/13
  888             25              12              75                   sum(888)
  889             17              30                                     sum(889)
  890             19                                50                   sum(890)
                     sum(15)     sum(16)    sum(17)         Total(sum)

The dates will be variable from 1-31 days (ability to run for whole month)
I had thought of  doing the following
  Create a query (will be modified by code for days 1-31). Query will place data in a work table with columns labeled from 1-31 (example column names: Day01Production, Day02Production, etc)
  Then my report or form will access the values in these fields to print or display.
  There are a couple of other fields that will need to be presented also. I just kept it simple
  Can this be done with a pivot table? I have worked with them in Excel. Not much experience with pivot and Access.
0
Comment
Question by:rodneygray
3 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 39810878
They're known as Crosstabs in Access. Essentially you create a Crosstab query, and then build a report using that Crosstab.

Here's an Office tutorial that might help:

http://office.microsoft.com/en-us/access-help/make-summary-data-easier-to-read-by-using-a-crosstab-query-HA010229577.aspx
0
 
LVL 10

Assisted Solution

by:Luke Chung
Luke Chung earned 250 total points
ID: 39812006
Here's an example of Creating an Annual 12 Month Summary Report without VBA Code by Creatively Using a Microsoft Access Crosstab Query
http://www.fmsinc.com/MicrosoftAccess/query/crosstab-report/index.html

It's designed to handle any 12 month period. Could be adapted to handle days vs. months. A free sample database is included that you can download. Hope this helps.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39813837
Previous comments looks fine.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question