Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    2

    Unanswered: access sql statement probs

    hi

    im writing an sql query in access that involves calculating a gross total

    the data is being collected from two tables...

    one is the clients table which only contains client data

    the other contains statement information from which figures are collected

    there is a link between the IDs in both tables

    the query i want to execute is as follows....

    SELECT Clients.CFName, Clients.CAddress, Clients.CNotes, Sum(([Rent Agreed]+[Balance brought forward])-([Furniture]+[Repairs]+[Other])) AS TotalGrossYear, Statement.Can, Statement.ChequeDateFrom, Statement.ChequeDateTo
    FROM Clients INNER JOIN Statement ON Clients.ClientID = Statement.ClientID
    WHERE (((Statement.Can)=0));

    i get an error message saying ...

    you tried to execute a query that does not include the specified expression. 'CFName' as part of an aggregate function.

  2. #2
    Join Date
    May 2002
    Location
    London
    Posts
    87
    You need to Group on the Fields that you are not summing thus:

    SELECT Clients.CFName, Clients.CAddress, Clients.CNotes, Sum(([Rent Agreed]+[Balance brought forward])-([Furniture]+[Repairs]+[Other])) AS TotalGrossYear, Statement.Can, Statement.ChequeDateFrom, Statement.ChequeDateTo
    FROM Clients INNER JOIN Statement ON Clients.ClientID = Statement.ClientID
    GROUP BY Clients.CFName, Clients.CAddress, Clients.CNotes, Statement.Can, Statement.ChequeDateFrom, Statement.ChequeDateTo
    WHERE (((Statement.Can)=0));

    which you can do in design view using the dropdown in the 'Total' row of the design grid.

  3. #3
    Join Date
    Jul 2003
    Posts
    2

    more problems

    hi

    for some reason the sum part isnt calculating that way i want it to...

    basically i want to calculate all the statement amounts together in a grand total for the period not get figures for each statement.

    for example if one client had five statements then this query would simply display this clients details and then the total of the five statements figures income together where income = (rent + bbf)-(repairs+furn+other).

    i can achieve this in mysql/php with no problems with this ...

    $sql = "SELECT Clients.ClientID, Clients.ClientFirstname, Clients.ClientSurname, Clients.ClientAddress, Clients.ClientPostcode, Clients.PropertyAddress, Clients.PropertyPostcode, SUM((landlord_statement.RentAgreed+landlord_statem ent.BBF)-(landlord_statement.Repairs + landlord_statement.Furniture + landlord_statement.SetupFee + landlord_statement.Other)) as SUM FROM Clients, landlord_statement WHERE Clients.ClientID = landlord_statement.landlordID AND Clients.Status = 0 AND landlord_statement.Can = 0 AND landlord_statement.ChequeDateFrom BETWEEN '$dateFrom' AND '$dateTo' GROUP BY Clients.ClientID ";

  4. #4
    Join Date
    May 2002
    Location
    London
    Posts
    87
    Well you are going to need to put the other grouping fields in the GROUP BY clause for it to work.

    Also are there values in all five value columns in landlordstatement if the are any nulls the summing wont work as 1+Null=Null. You can use nz() to get round this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •