Results 1 to 7 of 7

Making a query that posts data in a matrix like presentation?

  1. #1
    Steven Seagal is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2012
    Posts
    20

    Making a query that posts data in a matrix like presentation?

    I have data that is organized by a group and method in which these numbers are processed. It looks like this:

    Number
    Group
    Method
    100-A
    Alpha
    Auto
    100-B
    Alpha
    Auto
    100-C
    Alpha
    None
    100-D
    Alpha
    Manual
    101-DA
    Alpha
    Manual
    100-B
    Alpha
    None
    171 - CD
    Omega
    Auto
    171 -B48
    Omega
    Auto
    551-3
    Beta
    Manual
    4829-1
    Beta
    Manual
    3424
    beta
    Manual



    Now, I want the data to look like this when I run a query in Access:
    Group
    Auto

    Manual
    None
    Alpha

    2
    2
    2
    Omega

    2
    0
    0
    Beta

    0
    3
    0

    How can I do that? Thanks

    Note the formatting on the second table is a bit weird, but I think you all know where I'm getting at.

  2. #2
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,021
    Use something like the following query

    Code:
    Select Group, Sum(IIF([method]='Auto',1,0)) As Auto, sum(IIF([method]='Manual',1,0)) As Manual, sum(iif([method]='None',1,0)) as None
    You could also try a crosstab where the Row header is group the column header is method and the value is Count.

  3. #3
    Steven Seagal is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2012
    Posts
    20
    I did exactly that and only that got an error that says "The Select statement includes a reserved word or an argument name that is misspelled or missing or the punctuation is incorrect"

    I then stuck a From [Source]; above the line but still no luck. I only get "invalid SQL statement...."

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,784
    You might also play with the crosstab query wizard.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,021
    My post was only showing the Select portion of a standard query I did not include the From Clause, Where clause and Group by Clause. You did not specify any information for use in the from or where clause The group by would have group as the only field. Your best bet at this point is to use the crosstab wizard to create your query.

  6. #6
    Steven Seagal is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2012
    Posts
    20
    Thanks all. the wizard was very helpful

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,784
    Happy to help. Developing databases is definitely a step up from making movies!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Horizontal presentation
    By cacabanga in forum Access
    Replies: 4
    Last Post: 02-11-2012, 07:18 PM
  2. PivotChart presentation
    By wharting in forum Forms
    Replies: 5
    Last Post: 08-25-2011, 11:07 PM
  3. Automatically delete posts
    By carstenhdk in forum Access
    Replies: 4
    Last Post: 06-09-2010, 02:41 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
  •  
Tech Forums: Microsoft Office Forums