Results 1 to 4 of 4
  1. #1
    wondering is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    2

    Invoicing

    Hi,



    I am new to this forum, but have used a forum a few years back to solve some of my database issues and was very helpful and I am hoping I can sort the current problem.

    I have access 2016 running a database designed for the Entertainment business.

    Basically, it handles issuing of contracts to various purchasers etc and then produces invoices as required for each event.

    The problem I have run into is that I do not know how to get a list of all events in a 30 day period that show all the events and which ones have had invoices issued and which ones have not.

    Tables I have in place for this are:

    Artist (Events), Contracts, Purchasers, Invoices and Invoice details.

    Artist table has a one to many relationship with contracts

    Contracts has a one to many relationship with invoices

    Purchasers has a one to many relationship with invoices as well

    Invoices has a one to many relationship with Invoice details.

    I can produce a query showing all invoices issued for a specified period, but I cannot show contracts without invoices issued. Which is a pain as I want to see what needs to be issued.

    Any help would be greatly appreciated.

    Thank you in advance.

  2. #2
    AccessToGo is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2018
    Location
    UK
    Posts
    15
    Hi, I would use an EXISTS on a subquery or rather a NOT EXISTS()...
    In new query SQL view
    SELECT * FROM [CONTRACTS] WHERE (NOT EXISTS (SELECT 1 FROM [INVOICES] WHERE [A]=[B] AND [XYZ])) AND (anything else which limits contracts)
    where [A]=[B] is what joins the two tables
    and [XYZ] is whatever else it is that limits the invoices sub query

    Regards
    Chris

  3. #3
    wondering is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    2
    Hi Chris,

    Sorry for my very slow reply but managed to get a bit sick over the weekend.

    I will give that a try, sounds like the solution and thank you.

    Stephen

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    or you can use a left join

    SELECT DISTINCT Contracts.* FROM Contracts LEFT JOIN Invoices ON Contracts.PK=Invoices.FK
    WHERE ContractDate Between [Date1] AND [Date2]

    to only see those contracts without an invoice, add a further criteria to the WHERE clause

    AND Invoices.FK is Null

    if you want to see the sum of invoices

    SELECT DISTINCT Contracts.*, ttlInValue FROM Contracts LEFT JOIN (SELECT FK, SUM(InvValue) AS ttlInvValue FROM Invoices GROUP BY FK) AS ttlInvoices ON Contracts.PK=ttlInvoices.FK
    WHERE ContractDate Between [Date1] AND [Date2]

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

Similar Threads

  1. Replies: 5
    Last Post: 02-08-2018, 07:33 PM
  2. Using up Blank Records in an Invoicing Database
    By Nod_Swerdna in forum Access
    Replies: 4
    Last Post: 05-08-2015, 10:14 AM
  3. Use Access for Invoicing -
    By deSiguy in forum Access
    Replies: 13
    Last Post: 02-22-2015, 01:30 PM
  4. neeed help on invoicing db (newbie)
    By sanlen in forum Access
    Replies: 2
    Last Post: 02-15-2012, 07:17 PM
  5. Automatic Invoicing
    By anoob in forum Access
    Replies: 8
    Last Post: 01-15-2011, 01:05 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