Code Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there away to insert this SQL code
SELECT Count([AFB.RiskReference]) AS NoOfRisks FROM (SELECT DISTINCT [AFB.RiskReference] from AFB)

into this

SELECT Synds.SyndicateName, Users.UserName, AFB.COB, Sum(AFB.Time) AS TotalTime, AFB.DocType, AFB.Task, Sum(AFBBenchmarks.Benchmark) AS TotalBMTime, [TotalTime]-[TotalBMTime] AS [+/-BMT], ([TotalTime]/[TotalBMTime]) AS [TimeTakenAs%OfBMT
FROM ((AFB INNER JOIN Users ON AFB.User = Users.UserInitials) INNER JOIN AFBBenchmarks ON (AFB.DocType = AFBBenchmarks.DocType) AND (AFB.COB = AFBBenchmarks.COB) AND (AFB.Task = AFBBenchmarks.Task)) INNER JOIN Synds ON AFB.Syndicate = Synds.Psuedony
WHERE (((AFB.Date)>=[forms]![Reports]![txtFromDate] And (AFB.Date)<=[forms]![Reports]![txtToDate])
GROUP BY Synds.SyndicateName, Users.UserName, AFB.COB, AFB.DocType, AFB.Tas
HAVING (((Users.UserName)=IIf([forms]![Reports]![cboUsers]="ALL",[UserName],[forms]![Reports]![cboUsers])))

Thanks
 
I doubt it. You can run it as a separate query, you will need to put the key
from table AFB into the first query, but that will messup your count, since
every record would satisfy the distinct keyword.

You might try adding DCount, but the results will be very slow:

DCount("RiskReference","AFB") AS NoOfRisks

It also will return only 1 value and apply it to each row.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Wesley said:
Is there away to insert this SQL code:
SELECT Count([AFB.RiskReference]) AS NoOfRisks FROM (SELECT DISTINCT [AFB.RiskReference] from AFB);

into this:

SELECT Synds.SyndicateName, Users.UserName, AFB.COB, Sum(AFB.Time) AS
Click to expand...
TotalTime, AFB.DocType, AFB.Task, Sum(AFBBenchmarks.Benchmark) AS
TotalBMTime, [TotalTime]-[TotalBMTime] AS [+/-BMT],
([TotalTime]/[TotalBMTime]) AS [TimeTakenAs%OfBMT]
FROM ((AFB INNER JOIN Users ON AFB.User = Users.UserInitials) INNER JOIN
Click to expand...
AFBBenchmarks ON (AFB.DocType = AFBBenchmarks.DocType) AND (AFB.COB =
AFBBenchmarks.COB) AND (AFB.Task = AFBBenchmarks.Task)) INNER JOIN Synds ON
AFB.Syndicate = Synds.Psuedonym
WHERE (((AFB.Date)>=[forms]![Reports]![txtFromDate] And (AFB.Date)<=[forms]![Reports]![txtToDate]))
GROUP BY Synds.SyndicateName, Users.UserName, AFB.COB, AFB.DocType, AFB.Task
HAVING
Click to expand...
(((Users.UserName)=IIf([forms]![Reports]![cboUsers]="ALL",[UserName],[forms]
![Reports]![cboUsers])));

Thanks.
Click to expand...
 
Back
Top