Results 1 to 12 of 12
  1. #1
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    23

    Using aliases in other parts of a query

    I'm totaling up amounts owed and subtracting amounts paid and only want to show a record when it still owes money. I'm very close but I can't figure out how to use the alias from the SELECT in the WHERE clause



    Code:
    SELECT tblReceivables.Customer, Sum(tblReceivables.InvTotal) AS totalowed, Sum(tblCustomerPayments.amount) AS totalpaid, (totalowed-totalpaid) AS balance
    FROM tblReceivables LEFT JOIN tblCustomerPayments ON tblReceivables.InvoiceNumber = tblCustomerPayments.fkInvoiceID
    GROUP BY tblReceivables.Customer;
    I've tried just WHERE balance > 0 from both the SQL editor and the builder but when run it asks me for balance. Also tried totalowed - totalpaid but it just asks for both.

    I know it'll be simple but I just can't seem to figure it out.

    thanks!

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I don't know if you can subtract aggregate functions in the same query that they are created in like that. Also, in using aggregated values in criteria, they must be in the HAVING clause, not the WHERE clause.

    Maybe try something like:
    Code:
    SELECT tblReceivables.Customer, Sum(tblReceivables.InvTotal) AS totalowed, Sum(tblCustomerPayments.amount) AS totalpaid, Sum(tblReceivables.InvTotal-tblCustomerPayments.amount) AS balance
    FROM tblReceivables LEFT JOIN tblCustomerPayments ON tblReceivables.InvoiceNumber = tblCustomerPayments.fkInvoiceID
    GROUP BY tblReceivables.Customer
    HAVING Sum(tblReceivables.InvTotal-tblCustomerPayments.amount)>0;

  3. #3
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    23
    You're right, it just doesn't like the aliasing. I had to sum() again and subtract to make it work.

    Code:
    Having(Sum(tblReceivables.InvTotal) -Sum(tblCustomerPayments.amount))>0;

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by JoeM View Post
    I don't know if you can subtract aggregate functions in the same query that they are created in like that. Also, in using aggregated values in criteria, they must be in the HAVING clause, not the WHERE clause.
    Maybe try something like:
    Code:
    SELECT tblReceivables.Customer, Sum(tblReceivables.InvTotal) AS totalowed, Sum(tblCustomerPayments.amount) AS totalpaid, Sum(tblReceivables.InvTotal-tblCustomerPayments.amount) AS balance
    FROM tblReceivables LEFT JOIN tblCustomerPayments ON tblReceivables.InvoiceNumber = tblCustomerPayments.fkInvoiceID
    GROUP BY tblReceivables.Customer
    HAVING Sum(tblReceivables.InvTotal-tblCustomerPayments.amount)>0;
    Hi Joe
    I agree with the first point but disagree with the WHERE comment.
    You can use WHERE but, in the query designer, the expression has to be used twice.
    Once for the sum and again for the where filter

    I think you will find this version should also work and the advantage is the query may run slightly faster
    Code:
    SELECT tblReceivables.Customer, Sum(tblReceivables.InvTotal) AS totalowed, Sum(tblCustomerPayments.amount) AS totalpaid, Sum(tblReceivables.InvTotal-tblCustomerPayments.amount) AS balance
    FROM tblReceivables LEFT JOIN tblCustomerPayments ON tblReceivables.InvoiceNumber = tblCustomerPayments.fkInvoiceID
    WHERE Sum(tblReceivables.InvTotal-tblCustomerPayments.amount)>0
    GROUP BY tblReceivables.Customer;
    Last edited by isladogs; 08-29-2019 at 12:26 AM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    23
    The query is actually looking at a many-many relationship (multiple invoices which may each have multiple payments). Do I not need to sum Invtotal and amount separately then subtract?

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Rhodan
    Yes I think you are correct. Try it and check the result for a few records
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You can use WHERE but, in the query designer, the expression has to be used twice.
    Once for the sum and again for the where filter
    Interesting, that is not how I learned it. In a nutshell, I was taught that you use WHERE on the unaggregated data criteria, but HAVING on aggregated data.
    Everything that I have read seems to support this also, i.e. https://support.office.com/en-us/art...5-bd155a89f02f

    I think you will find this version should also work and the advantage is the query may run slightly faster[/QUOTE]
    Why would it run slightly faster? Do you have any supporting documentation for that or have you done your own tests?
    Just curious if there really is an advantage, as I have never heard that before.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Joe
    Using WHERE, the filter is applied before the data is grouped.
    Using HAVING, the data is first grouped then filtered.

    I've done lots of speed tests including HAVING vs WHERE.
    If the fields used in the filters are indexed, tests indicate WHERE is about 12-15% faster as the indexes mean the whole dataset doesn't need to be grouped.
    If indexing isn't available the difference is much smaller - less than 2% - as the whole dataset needs to be searched in each case before it can be grouped.

    You can find some of my speed tests at http://www.mendipdatasystems.co.uk/s...sts/4594424200
    In particular look at Optimising Queries and HAVING vs WHERE.
    The latter page is about to be updated as the tests I used were on indexed date fields but I later realised the way I did them didn't make use of the indexes..
    I've since done a revised version using indexes on dates and WHERE was 14% faster in aggregate queries.

    I've read the link you provided before. It doesn't say you need to use HAVING but does mention the order of query execution.
    Have a look at the corresponding page on the WHERE clause https://support.office.com/en-us/art...2-BEF126E4C0B1
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Using WHERE, the filter is applied before the data is grouped.
    Using HAVING, the data is first grouped then filtered.
    Interesting, because one would think you need to group the data to do the sum!

    You obviously have looked deeply into this.
    I guess I learned something new today!

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You do need to group the data before you sum but using WHERE you exclude the unwanted data before grouping it.
    The only real advantage of HAVING is that the field can be displayed in the query results.
    Using WHERE the field isn't visible. To see it, you need to add the field again using Group By, this time without the filter.

    Or to put it a different way, using the query designer on an aggregate query, if you filter a grouped field, the query SQL shows HAVING.
    FIRST its grouped on all the data then filtered removing unwanted data
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Thanks for all the good information!

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You’re welcome. Let me know what you think if you look at any of the speed tests. There are some unexpected outcomes that go against long held beliefs
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Parts List on Parts Requisition Report
    By billgyrotech1 in forum Access
    Replies: 16
    Last Post: 06-03-2019, 01:17 PM
  2. Parts and prices query
    By faca in forum Queries
    Replies: 6
    Last Post: 07-23-2018, 07:37 AM
  3. need query with duplicate aliases into excel
    By phineas629 in forum Import/Export Data
    Replies: 4
    Last Post: 05-18-2014, 03:50 PM
  4. Replies: 3
    Last Post: 08-02-2012, 11:37 AM
  5. Finding Table Aliases
    By hfile in forum Access
    Replies: 2
    Last Post: 12-26-2011, 02:38 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