Duplicate records

I had a query with a sum on a detail field. This was fine until
I found I needed to apply proper rounding prior to summing. It
now lists a row for each detail record. I have guessed/tried to
add the case to the group by instead of the field but I am told
I cannot have a subclause.

Here is the query, can anyone help?

SELECT TOP 100 PERCENT Jobs.JobNo, Jobs.FinalReport,
Jobs.LicInvoiceNo, LicencesHeld.LicName, SUM(ItemActions.CHNet)
AS TotalNet, Jobs.AppliedFeeRate,
Areas.AreaManager,
LicenceTypes.LicenceType,CASE ABS(cast
(ItemActions.VatTotalSales*100 as int)) % 2
WHEN 1 THEN
SUM(ROUND(ItemActions.VatTotalSales+0.001,2))
ELSE SUM(ROUND
(ItemActions.VatTotalSales-0.001,2))
END AS TotalVAT
FROM ItemActions INNER JOIN
Items ON ItemActions.ItemID = Items.ItemID
INNER JOIN
Jobs ON Items.JobNo = Jobs.JobNo INNER
JOIN
LicencesHeld ON Jobs.LicID =
LicencesHeld.LicID INNER JOIN
LicenceTypes ON LicencesHeld.LicTypeID =
LicenceTypes.LicenceTypeID INNER JOIN
Areas ON LicencesHeld.AreaCode =
Areas.AreaID
GROUP BY Jobs.JobNo, Jobs.FinalReport, Jobs.LicInvoiceNo,
LicencesHeld.LicName, Jobs.AppliedFeeRate, Areas.AreaManager,
LicenceTypes.LicenceType,
ItemActions.VatTotalSales
HAVING (NOT (Jobs.LicInvoiceNo IS NULL)) AND
(LicenceTypes.LicenceType LIKE '%%') AND (Jobs.FinalReport
BETWEEN CONVERT(DATETIME,
'2006-01-01 00:00:00', 102) AND
CONVERT(DATETIME, '2999-01-01 00:00:00', 102))
ORDER BY Jobs.LicInvoiceNo


Pat Bell

.

Relevant Pages

  • Re: correlated subquery in the crosstab
    ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... I have students, courses, exam groups containing exams of courses, ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... I solved my problem using stored queries to act as subqueries. ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... The first query ... TRANSFORM FirstAS FirstOfscore ... FROM tblStudent INNER JOIN (((tblEduYear INNER JOIN tblExamGrp ON ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: Matching records for an update query
    ... then the update query would look something like ... UPDATE RegisteredMembers INNER JOIN BusinessChanges ...
    (microsoft.public.access.queries)
  • Re: Ranking query
    ... I expect that using a named query (as opposed to its SQL) will be OK. ... INNER JOIN qryRepairs AS I2 ...
    (microsoft.public.access.queries)