Results 1 to 2 of 2
  1. #1
    hnguyen1987 is offline Novice
    Windows Vista Access 2000
    Join Date
    Aug 2010
    Posts
    1

    Crosstab Query Question

    Hi,



    I am fairly new to access and am trying to create a report that pulls in the top 20 vendors descending by the sum of their expenses (shown) over 3 years.

    The table has all the $ for all the vendors for every year.

    I need something along the lines of:

    2008 2009 2010 SUM
    V1 15 15 14 '08-10
    V2 13 15 14 '08-10
    V3 12 13 13 '08-10
    V4 10 10 7 '08-10

    Please be very explicit with what I should do. Normally I use design mode... but simple copy paste SQL never hurts too!

    Thanks!

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Assume the table like this:
    table name: tbl
    fields: vendorname text(50),
    year long,
    expense currency

    Use following query as data source of your report:
    Query1: SumExp: select top 20 vendor,sum(expense) as sumExpense from tbl group by vendor order by sum(expense) desc

    Query2: top20detail: select tbl.* from tbl inner join sumexp on tbl.vendor=sumexp.vendor

    Query3: result: TRANSFORM Sum(tbl.expense) AS SumOfexpense
    SELECT tbl.Vender, Sum(tbl.expense) AS SumOfexpense1
    FROM tbl GROUP BY tbl.Vender PIVOT tbl.year;

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

Similar Threads

  1. crosstab query criteria
    By Sharron in forum Queries
    Replies: 3
    Last Post: 12-15-2011, 04:31 AM
  2. Crosstab Query
    By lukewarmbeer in forum Access
    Replies: 2
    Last Post: 08-13-2010, 05:10 AM
  3. Replies: 1
    Last Post: 07-30-2010, 10:28 AM
  4. Query - Crosstab ?
    By rob4465 in forum Access
    Replies: 1
    Last Post: 01-28-2010, 08:41 AM
  5. Criteria in Crosstab Query
    By wasim_sono in forum Queries
    Replies: 1
    Last Post: 12-12-2006, 05:14 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