Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Posts
    10

    Unanswered: Extending a Query

    I have an aggregate query that works fine, however I need to remove the hard-coded dates as follows;

    SELECT Sum(IIf([vat],iif([weekendingdate] Between #04/01/2011# and #31/12/2099#, [takings]/1.2,iif([weekendingdate] BETWEEN #30/11/2008# and #01/01/2010#,[takings]/1.15,[takings]/1.175)),[takings])) AS Total, dates.WeekNo, [n-weeklytransactions].weekendingdate FROM departments INNER JOIN (([business units] INNER JOIN [n-weeklytransactions] ON [business units].buID = [n-weeklytransactions].businessUnit) INNER JOIN dates ON [n-weeklytransactions].ztotaldate = dates.Startdate) ON (departments.department = [n-weeklytransactions].department) AND (departments.bus_unit = [n-weeklytransactions].businessUnit) GROUP BY dates.WeekNo, [n-weeklytransactions].weekendingdate HAVING ((DatePart('yyyy',[n-weeklytransactions]![weekendingdate])=DatePart('yyyy',Now())-1)) ORDER BY dates.WeekNo;

    This works fine
    SELECT 1.2 as rate, Sum(IIf([vat],[takings]/rate,[takings])) AS Total, dates.WeekNo, [n-weeklytransactions].weekendingdate FROM departments INNER JOIN (([business units] INNER JOIN [n-weeklytransactions] ON [business units].buID = [n-weeklytransactions].businessUnit) INNER JOIN dates ON [n-weeklytransactions].ztotaldate = dates.Startdate) ON (departments.department = [n-weeklytransactions].department) AND (departments.bus_unit = [n-weeklytransactions].businessUnit) GROUP BY dates.WeekNo, [n-weeklytransactions].weekendingdate HAVING ((DatePart('yyyy',[n-weeklytransactions]![weekendingdate])=DatePart('yyyy',Now())-1)) ORDER BY dates.WeekNo;

    What I need to do is to incorporate this
    SELECT (select top 1 VAT_Rate
    from VAT_Rates
    where VAT_Rates.VAT_Date <= [n-weeklytransactions].ZTotalDate
    order by VAT_Rates.VAT_Date desc) AS Rate, *
    FROM [n-weeklytransactions] ;

    I tried
    SELECT Max((select VAT_Rate from VAT_Rates where VAT_Date <=ZTotalDate order by VAT_Rates.VAT_Date desc)) AS vrate, Sum(IIf([vat],[takings]/[Vrate],[takings])) AS Total, Month([n-weeklytransactions]![weekendingdate]) AS [Month], Year([n-weeklytransactions]![weekendingdate]) AS [Year]
    FROM departments INNER JOIN (([business units] INNER JOIN [n-weeklytransactions] ON [business units].buID = [n-weeklytransactions].businessUnit) INNER JOIN dates ON [n-weeklytransactions].ztotaldate = dates.Startdate) ON (departments.department = [n-weeklytransactions].department) AND (departments.bus_unit = [n-weeklytransactions].businessUnit)
    GROUP BY Month([n-weeklytransactions]![weekendingdate]), Year([n-weeklytransactions]![weekendingdate]), vrate
    ORDER BY Month([n-weeklytransactions]![weekendingdate]);

    but I get the following error "You tried to execut a query that does not include the specified expression 'ztotaldate' as part of an aggreate function"

    Any ideas if this is possible?

    Regards
    Pete

  2. #2
    Join Date
    Jan 2005
    Posts
    146
    SELECT Max((select VAT_Rate from VAT_Rates where VAT_Date <=ZTotalDate order by VAT_Rates.VAT_Date desc)) AS vrate
    When using dates in Access queries you need to surround them with hash tags ##

    Code:
    SELECT Max((select VAT_Rate from VAT_Rates where VAT_Date <=#" & ZTotalDate & "# order by VAT_Rates.VAT_Date desc)) AS vrate

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I do sincerely hope the division by 1.2 is not some form of VAT calculation.. please tell me it ain't so. even if it isn't a VAT calculation these are magic numbers and if subject to change will cause problems if coded like this. push those values into a table and pull the appropriate values as required from that table. that way round if the values change there is no development impact what so ever

    what is ztotaldate. the query parser is telling you it cannot find that column in the table or query [n-weeklytransactions]
    check for typos
    check ztotaldate does exist in [n-weeklytransactions]
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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