Results 1 to 6 of 6
  1. #1
    Janzi is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    5

    Thumbs up Displaying previous totals, and current totals, on a report

    Hi,

    I could really use some help.



    Here's the general scenario:
    I am the Project Manager, managing a project for my client.
    I have multiple vendors
    They bill me for work on various subprojects (called CDs) within the project.
    I then bill my client, providing them with a summary of the billings , grouped by CD and Vendor.
    All vendor bills (hours and material) are entered into a table WO (Work Orders) and classified as 'Pending'

    When I am ready to bill them to the client, I append them to a table Bills, which has the following fields:

    WOID (Id number of the work order that was billed)
    DrawNo (which progress draw this was billed to the client under)
    BillDate
    BillAmt - appended from table WO

    I run a report to show me what I billed the client for each billing cycle or draw, i.e. by the draw number.

    Table Bills has items under multiple draws, i.e. Draw 22, 23, etc.

    I want to run a report which shows:
    Billing for Current Draw (by Vendor and by CD)
    Total Previously Billed (by Vendor and by CD)

    I have two queries, one shows me a list of all CDs billed, by Vendor, and one shows me only the current billing (i.e. where DrawNo is Max).

    I want to combine this info to show it on a report as such:

    CD # --- Vendor --- Prev Billed --- Current Billed --- Total Billed


    Can't figure it out!

    Any help would be greatly appreciated.

    Sorry if its confusing, if any clarification is required please ask

    Thank you!

    Janzi
    Last edited by Janzi; 12-06-2011 at 11:19 AM.

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    fundamentally you must create an aggregate query that results in:

    CD # --- Vendor --- Prev Billed
    one line per CD/Vendor

    then you must create a select query that results in:
    CD # --- Vendor --- Current Billed
    one line per CD/Vendor

    then you must create a new query that joins these 2 queries together on the CD and Vendor
    it also should have just one line per CD/Vendor

    then create a calculated column for the Total Billed.

    hope it helps.

  3. #3
    Janzi is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    5
    Hi NTC!

    Thank you for the response.

    I follow your logic and had already created the two queries you had mentioned prior to posting the question here.

    Where I am struggling is in the third query which joins the first two, on the CD and Vendor and has just one line per CD/Vendor

    A simple join does not work, because all CDs/Vendors don't exist in both queries.

    i.e. in the past month CDs 1,2,3 and 4 were worked on by Vendor A, B and C
    In the current month, only CDs 2,3,5 and 6 were worked on by Vendors A,B,D,F,G.

    Hope I'm being clear enough.

    So any help you can provide in how to join these two queries would be much appreciated!

    Thank you!

    Janzi

  4. #4
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    yes, one must have exact symettry with a record on both sides. you can force this by the type join you use - (i.e. All of x query : : makes a join line with an arrow)which will result in nulls returning when there is no record on the other side.....then you may need another step to make the nulls into 0.

    there is a limit as to what I am able to understand and advise inherent in a posting type forum. I focused on this line you posted:
    CD # --- Vendor --- Prev Billed --- Current Billed --- Total Billed

    this implies there is a value for each field....but there is legitimate situations where there may no Prev Billed - obviously with a new customer. I have situations where management wants to see all customer activity in each report even when there has been no purchase by a customer....and so one sometimes has to get the full universe of records that are to be reported on and force nulls with the join type....and go from there.

    hope it helps.

  5. #5
    Janzi is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2011
    Posts
    5
    Yes your understanding is correct.

    There are instances where there may be no previous billed, but also instances where are is no current bill, only the previous balance.

    At the moment I am just struggling to create to query, as it is not producing the desired results.

    Any help in creating the join query you mentioned would be great.

    Thanks again!

  6. #6
    Janzi is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2011
    Posts
    5
    Got it to work!

    What I had to do was do a DOUBLE JOIN, joining (with the arrow) both the CD and the Vendor fields, otherwise the query was returning duplicates!

    Awesome! A mountain of frustration gives way to a swathe of sweet success!

    Thanks for the help NTC!

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

Similar Threads

  1. Report with monthly totals?
    By KrisDdb in forum Access
    Replies: 7
    Last Post: 12-06-2011, 11:49 AM
  2. Replies: 0
    Last Post: 11-04-2011, 06:09 AM
  3. Month totals and Year totals
    By marksnwv in forum Access
    Replies: 1
    Last Post: 08-05-2011, 10:13 AM
  4. Summing Report Totals
    By bugme in forum Reports
    Replies: 3
    Last Post: 05-09-2011, 09:36 AM
  5. Totals in a query-based report
    By babylikesburgh in forum Reports
    Replies: 4
    Last Post: 02-24-2010, 03:08 PM

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