I have two queries that I am using to generate a report from, the problem is when I run the report, three fields do not show any data at all for some reason.
Query 1:
SELECT ClientSummary.Field3 AS PM,
ClientSummary.[Client Nickname 2] AS [Project #],
ClientSummary.[Client Nickname 1] AS Customer,
ClientSummary.[In Reference To] AS [Job Name],
ClientSummary.Field10 AS Contract,
(select sum([Billable Slip Value])
from Util_bydate as U1
where U1.[Client Nickname 2] = ClientSummary.[Client Nickname 2])
AS [This Week],
(select sum([Billable Slip Value])
from Util as U2
where U2.[Client Nickname 2] = ClientSummary.[Client Nickname 2] )
AS [To Date],
[To Date]/[Contract] AS [% Spent],
0 AS Backlog,
ClientSummary.[Total Slip Fees & Costs] AS Billed,
ClientSummary.Payments AS Paid, ClientSummary.[Total A/R] AS Receivable,
[Forms]![ReportMenu]![StartDate] AS [Start Date],
[Forms]![ReportMenu]![EndDate] AS [End Date]
FROM ClientSummary;
Query 2:
SELECT JobManagement_Summary.pm,
JobManagement_Summary.[project #],
JobManagement_Summary.Customer,
JobManagement_Summary.[Job Name],
JobManagement_Summary.Contract,
IIf(IsNull([This Week]),0,[This Week]) AS [N_This Week],
IIf(IsNull([To Date]),0,[To Date]) AS [N_To Date], [% Spent],
JobManagement_Summary.Backlog,
JobManagement_Summary.Billed,
JobManagement_Summary.Paid,
JobManagement_Summary.Receivable,
JobManagement_Summary.[Start Date],
JobManagement_Summary.[End Date]
FROM JobManagement_Summary;
When I run the report from query 2 these 3 fields don't appear. N_This Week, N_To Date and % Spent. All have no data. It isn't the IIF functions, as it doesn't matter if I have those in there or remove them.
Any thoughts? If I connect directly to the first recordset it works fine, but then SQL throws the error message: Multi-level GROUP BY cause not allowed in subquery.
Is there any way to get around that message to link to it directly or does anyone have ANY clue why these fields are coming back blank? I am at wits end here!
