Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2014
    Posts
    3

    CrossTab to Summary Table

    Hi,

    I have two tables that I want to combine into a third (summary) table. I need a clue as to the steps necessary to do this. Thanks in advance.

    Dennis



    Table 1: Techs
    Fields: Name 250 350 450 550
    ----- ---- --- ---- ----
    Joe 10 11 12 13
    Bob 11 12 14 16

    for Joe 10 is the percentage commission rate for a sale where sale amount > $250 and < $350
    for Bob 14 is the percentage commission rate for a sale where sale amount > $450 and < $550

    Table 2: Sales data
    Fields: Date Tech Sale_Amount
    --------- ---- ------
    1/1/2014 Joe $280
    1/14/2014 Joe $360
    2/2/2014 Joe $410
    1/3/2014 Bob $380
    3/2/2014 Bob $370


    I want to end up with a summary table that has;

    Fields: MonthYear Tech Sales Count Avg Rate
    ---------- ---- ------ ------ ---- -----
    JAN2014 Joe $640 2 $320 10
    FEB2014 Joe $410 1 $410 11
    JAN2014 Bob $380 1 $380 12
    MAR2014 Bob $370 1 $370 12



    I've programmed before but never in Access.

    Thanks,
    Dennis

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Table 1 is not a normalized structure. Retrieving the correct rate for the sale amount will require a long expression using either IIf() or Switch() or a custom function.

    Build a query that joins the two tables on the salesperson ID. Then in that query create field with the above expression. Also calculate a YearMo field with Format([SaleDate], "yyyymm"). Then set the query as an aggregate by clicking the Totals button on the ribbon. Select the appropriate aggregate function under each of the fields to summarize and set the YearMo and EmployeeID and EmployeeName fields as GROUP criteria.

    Or build a report and use Grouping & Sorting features with aggregate calcs in group footer section.

    Advise not to use reserved words as field names. Date is a reserved word.
    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
    Join Date
    Jun 2014
    Posts
    3
    June 7,

    Thanks for the quick response and guidance.

    Normalizing Table 1 seemed to me to cause more hairyness than it solved. Do you think normalizing would make it more usable? How would you suggest it be normalized (if you are able and willing to take the time to suggest that).

    I won't use "date" as the field name, thanks.

    Dennis
    P.S. More advice that helps me get clear is always appreciated. If you are so inclined, here are the Use Cases I'm shooting to fulfill.

    This is a sales commission calculator which is used as part of a payroll process.


    Use Case 1
    Payroll admin role enters sales records and commission is calculated.
    Sales people have different commission rates which increase as dollar volume increases.
    JobTypes have percentage based commissions, arbitrary value commission or no commissions.


    Use Case 2
    Payroll admin produces commission report to be given to each sales person for review.


    Use Case 3
    Payroll admin corrects errors and produces final sales person report (which gets entered into payroll system).


    Use Case 4
    Deleted


    Use Case 5
    Payroll admin creates monthly record. (Subject of current post.) The commission used in the current period is based on the prior months sales and this record will hold that commission rate.


    Use Case 6
    General manager role reviews sales transactions and commissions paid for any arbitrary date span.


    Use Case 7
    GM & Admin enter arbitrary commission overrides for individual transactions or monthly commission rate.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Normalizing table 1 would mean need a field in table 2 to save the rate record ID or the rate itself.
    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.

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

Similar Threads

  1. Combine Crosstab query with Table to export together to Excel
    By kattatonic1 in forum Database Design
    Replies: 3
    Last Post: 04-04-2014, 10:56 AM
  2. Replies: 4
    Last Post: 02-24-2014, 09:20 AM
  3. Trying to Avoid Crosstab - Junction Table
    By alpinegroove in forum Queries
    Replies: 13
    Last Post: 01-08-2012, 05:56 PM
  4. Summary table query Access 2007
    By DesCall in forum Access
    Replies: 4
    Last Post: 05-05-2011, 02:38 AM
  5. make a temp table from crosstab query
    By stigmatized in forum Programming
    Replies: 0
    Last Post: 07-26-2010, 03:01 PM

Tags for this Thread

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