Can anyone out there help
I have a query which is based on a preceeding query which is producing a very strange result. The first query "Spend so Far Query 1A" produces a result of "1/12 Maintenance Sum" in a field called description. The second query "Spend so Far Query 1AA" which also has a field called description produces "?"
I would expect the result to be "1/12 Maintenance Sum"
Any help would be appreciated
I have posted the sql of the two queries below
Spend so Far Query 1A as follows
SELECT [Cost Authorisation Table].[Cost Auth ID], [Cost Authorisation Table].[PO Numnber], [Cost Authorisation Table].Status, [Cost Authorisation Table].Date, Right([Date],4) AS [Year], [Cost Authorisation Table].Building, [Cost Authorisation Table].[Consolidated Code], [Cost Authorisation Table].[Total Cost], Right([Date],7) AS [Pre Month Calculation], Left([Pre Month Calculation],2) AS [Month], [Labour Cost Table].Description, Right([Description],3) AS [Not OTH and NOT CAP], [Labour Cost Table].[Total Line Cost]
FROM [Cost Authorisation Table] LEFT JOIN [Labour Cost Table] ON [Cost Authorisation Table].[Cost Auth ID] = [Labour Cost Table].[Cost Authorisation ID]
WHERE ((([Cost Authorisation Table].Status)<>"CANCELLED" And ([Cost Authorisation Table].Status)<>"ON HOLD") AND ((Right([Date],4))=[Forms]![Cost Authorisation Non - Project Form]![Year]) AND (([Cost Authorisation Table].Building)=[Forms]![Cost Authorisation Non - Project Form]![Building]) AND (([Cost Authorisation Table].[Consolidated Code])=[Forms]![Cost Authorisation Non - Project Form]![USD Code]) AND (([Cost Authorisation Table].[Total Cost])<>0) AND ((Right([Description],3))<>"OTH" And (Right([Description],3))<>"CAP"));
And now Spend so Far Query 1AA
SELECT [Spend so Far Query 1A].[Cost Auth ID], [Spend so Far Query 1A].[PO Numnber], [Spend so Far Query 1A].Status, [Spend so Far Query 1A].Date, [Spend so Far Query 1A].Year, [Spend so Far Query 1A].Building, [Spend so Far Query 1A].[Consolidated Code], [Spend so Far Query 1A].[Total Cost], [Spend so Far Query 1A].[Pre Month Calculation], [Spend so Far Query 1A].Month, [Spend so Far Query 1A].Description, [Spend so Far Query 1A].[Not OTH and NOT CAP], Sum([Spend so Far Query 1A].[Total Line Cost]) AS [SumOfTotal Line Cost]
FROM [Spend so Far Query 1A]
GROUP BY [Spend so Far Query 1A].[Cost Auth ID], [Spend so Far Query 1A].[PO Numnber], [Spend so Far Query 1A].Status, [Spend so Far Query 1A].Date, [Spend so Far Query 1A].Year, [Spend so Far Query 1A].Building, [Spend so Far Query 1A].[Consolidated Code], [Spend so Far Query 1A].[Total Cost], [Spend so Far Query 1A].[Pre Month Calculation], [Spend so Far Query 1A].Month, [Spend so Far Query 1A].Description, [Spend so Far Query 1A].[Not OTH and NOT CAP];
And help would be appreciated
I have a query which is based on a preceeding query which is producing a very strange result. The first query "Spend so Far Query 1A" produces a result of "1/12 Maintenance Sum" in a field called description. The second query "Spend so Far Query 1AA" which also has a field called description produces "?"
I would expect the result to be "1/12 Maintenance Sum"
Any help would be appreciated
I have posted the sql of the two queries below
Spend so Far Query 1A as follows
SELECT [Cost Authorisation Table].[Cost Auth ID], [Cost Authorisation Table].[PO Numnber], [Cost Authorisation Table].Status, [Cost Authorisation Table].Date, Right([Date],4) AS [Year], [Cost Authorisation Table].Building, [Cost Authorisation Table].[Consolidated Code], [Cost Authorisation Table].[Total Cost], Right([Date],7) AS [Pre Month Calculation], Left([Pre Month Calculation],2) AS [Month], [Labour Cost Table].Description, Right([Description],3) AS [Not OTH and NOT CAP], [Labour Cost Table].[Total Line Cost]
FROM [Cost Authorisation Table] LEFT JOIN [Labour Cost Table] ON [Cost Authorisation Table].[Cost Auth ID] = [Labour Cost Table].[Cost Authorisation ID]
WHERE ((([Cost Authorisation Table].Status)<>"CANCELLED" And ([Cost Authorisation Table].Status)<>"ON HOLD") AND ((Right([Date],4))=[Forms]![Cost Authorisation Non - Project Form]![Year]) AND (([Cost Authorisation Table].Building)=[Forms]![Cost Authorisation Non - Project Form]![Building]) AND (([Cost Authorisation Table].[Consolidated Code])=[Forms]![Cost Authorisation Non - Project Form]![USD Code]) AND (([Cost Authorisation Table].[Total Cost])<>0) AND ((Right([Description],3))<>"OTH" And (Right([Description],3))<>"CAP"));
And now Spend so Far Query 1AA
SELECT [Spend so Far Query 1A].[Cost Auth ID], [Spend so Far Query 1A].[PO Numnber], [Spend so Far Query 1A].Status, [Spend so Far Query 1A].Date, [Spend so Far Query 1A].Year, [Spend so Far Query 1A].Building, [Spend so Far Query 1A].[Consolidated Code], [Spend so Far Query 1A].[Total Cost], [Spend so Far Query 1A].[Pre Month Calculation], [Spend so Far Query 1A].Month, [Spend so Far Query 1A].Description, [Spend so Far Query 1A].[Not OTH and NOT CAP], Sum([Spend so Far Query 1A].[Total Line Cost]) AS [SumOfTotal Line Cost]
FROM [Spend so Far Query 1A]
GROUP BY [Spend so Far Query 1A].[Cost Auth ID], [Spend so Far Query 1A].[PO Numnber], [Spend so Far Query 1A].Status, [Spend so Far Query 1A].Date, [Spend so Far Query 1A].Year, [Spend so Far Query 1A].Building, [Spend so Far Query 1A].[Consolidated Code], [Spend so Far Query 1A].[Total Cost], [Spend so Far Query 1A].[Pre Month Calculation], [Spend so Far Query 1A].Month, [Spend so Far Query 1A].Description, [Spend so Far Query 1A].[Not OTH and NOT CAP];
And help would be appreciated
