Results 1 to 6 of 6

Query to calculate summary from table.

  1. #1
    SybRiN is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2017
    Posts
    14

    Lightbulb Query to calculate summary from table.

    Good day Guys



    Back at it again with my NOOBNESS in Access.

    However, i have gotten past the tables and the forms, now I am at the queries.

    What I would like to achieve is this:

    I have 2 x Tables: 1) contains the delivery/return number, project, dates etc. - It also has a field called "Transaction_Type" to stipulate either delivery or return (tbl_transactionlist)
    2) contains the actual details of the delivery/return details e.g. 10 of Item 1, 20 of Item 2 was delivered / returned (tbl_transactions)

    The doc number field is the relationship field between these two tables as the doc number is on both tables.

    I would like to make a summary query that shows per site the following:

    Item Code Site 1 Site 2
    A1 20 10
    A2 0 20
    A3 10 10
    What needs to be accounted for though, is that for example:

    Site 1 has four deliveries and 1 return of equipment code A1:

    Delivery 1: 5
    Delivery 2: 10
    Delivery 3: 8
    Delivery 4 : 3
    Return 1: 6 - Note This will be minussed from the deliveries to equal a total of 20.

    Can someone please explain to me how to do this.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    5,465
    Use the query wizard an make a CROSSTAB query.
    It will ask you to pick the row field,
    Field for the column, and field for the values.

  3. #3
    SybRiN is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2017
    Posts
    14
    Hi @ranman256

    I have tried this, but I can't seem to get more than one table in a crosstab query? Maybe I am doing something wrong.

    I attach the database for you to see.Prota Services Material Capture Sheet Rev 2.zip

  4. #4
    SybRiN is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2017
    Posts
    14
    Quote Originally Posted by SybRiN View Post
    Hi @ranman256

    I have tried this, but I can't seem to get more than one table in a crosstab query? Maybe I am doing something wrong.

    I attach the database for you to see.Prota Services Material Capture Sheet Rev 2.zip
    What I have been able to do is make 4 x Queries:

    Query 1: Extract all the returns.
    Query 2: Extract all the deliveries.
    Query 3: Summarize Query 1 by Item Code
    Query 4: Summarize Query 2 by Item Code

    How would I go about subtracting query 3 from query 4. It must show all the item codes in both queries and run the query 4 - query 3

  5. #5
    SybRiN is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2017
    Posts
    14
    Maybe this will explain better what I have achieved so far:

    Good day,

    I have the following two tables:

    tbl_transactionlist

    DOC_NR | TRANS_TYPE | SITE | TRANS_DATE
    000001 | DELIVERY | PRO1 | 14/02/2017
    000002 | DELIVERY | PRO1 | 15/02/2017
    000003 | RETURN | PRO1 | 16/02/2017

    tbl_transactions

    TRANS_NR | DOC_NR | ITEM_CODE | QUANTITY
    PRO2017-01 | 000001 | 030801 | 2
    PRO2017-02 | 000001 | 030802 | 3
    PRO2017-03 | 000002 | 030801 | 2
    PRO2017-04 | 000003 | 030801 | 1

    Now, I have a 2 x Queries to return the sum of all the deliveries / returns per site and per item_code.

    This is the result:

    qry_activedeliveries

    SITE | ITEM_CODE | QUANTITY
    PRO1 | 030801 | 4
    PRO1 | 030802 | 3

    qry_activereturns

    SITE | ITEM_CODE | QUANTITY
    PRO1 | 030801 | 1Now, I need a query to do:

    (qry_acctivedeliveries).quantity - (qry_activereturns).quantity

    The result should look as follows:

    SITE | ITEM_CODE | QUANTITY
    PRO1 | 030801 | 3
    PRO1 | 030802 | 3


    However, it is pulling through item code "030802" as QTY = 0. I think it is subtracting the return note 000001 from both item_codes.

    Please assist?

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,403
    Could you explain your tables? Generally a transaction is one record per transaction on one table associated with a master number (Doc, Item, Project, Site?). Then all information for that one transaction is together. Separating the quantity with the type doesn't make sense.

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

Similar Threads

  1. Replies: 3
    Last Post: 02-24-2016, 03:15 AM
  2. Replies: 13
    Last Post: 02-25-2015, 05:34 AM
  3. CrossTab to Summary Table
    By Dennis Willis in forum Queries
    Replies: 3
    Last Post: 06-09-2014, 12:06 PM
  4. Replies: 2
    Last Post: 09-04-2013, 08:48 AM
  5. Summary table query Access 2007
    By DesCall in forum Access
    Replies: 4
    Last Post: 05-05-2011, 01:38 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