L
Logo
I've been trying to create a query to print a revenue and expense report. I
finally got the dates to display correctly after about 100 hours of work!
Having a problem with joins now. Joining two tables works fine, but joining
the third table is not going well. Any ideas? Here's the query-
SELECT Year(Shiftdate) AS [Year], Month(Shiftdate) AS [Month], [Car Count -
Table].Acct_name, Sum([Car Count - Table].[Total $ in Envelope]) AS
[SumOfTotal $ in Envelope], [Car Count - Table].Region, Sum([Car Count -
Table].[total billed]) AS [SUM Total Billed], (([SumOfTotal $ in
Envelope]/1.0825)-[SumOfTotal $ in Envelope]) AS [Sales Tax SUM], Sum([Car
Count - Table].[Manual Total Hours]) AS [Manual Total Hours SUM],
Clients.[Management Fee], ([SUM Total Billed]+[Management Fee]+[SumOfTotal $
in Envelope]+[Sales Tax SUM]) AS [SUM Total Revenue], ([Manual Total Hours
SUM]*6.55*0.0965) AS [Payroll Tax SUM], ([SUM Total Revenue]*0.38) AS [G and
A], Sum([Car Count - Table].[Total Cars Parked]) AS [Total Parked SUM]
FROM [Car Count - Table] INNER JOIN Clients ON [Car Count -
Table].acct_no=Clients.[Customer ID]
GROUP BY [Car Count - Table].Acct_name, [Car Count - Table].Region,
Year(Shiftdate), Month(Shiftdate), Clients.[Management Fee]
HAVING [Car Count - Table].Region<>"events";
Tables to join-
[Car Count - Table].acct_no (foreign key)
Clients.[Customer ID] (Primary key)
MonthlyRevenueAndWages.acct_id (foreign key)
No problem joining the first two tables together, but adding the third is
the problem. TIA
finally got the dates to display correctly after about 100 hours of work!
Having a problem with joins now. Joining two tables works fine, but joining
the third table is not going well. Any ideas? Here's the query-
SELECT Year(Shiftdate) AS [Year], Month(Shiftdate) AS [Month], [Car Count -
Table].Acct_name, Sum([Car Count - Table].[Total $ in Envelope]) AS
[SumOfTotal $ in Envelope], [Car Count - Table].Region, Sum([Car Count -
Table].[total billed]) AS [SUM Total Billed], (([SumOfTotal $ in
Envelope]/1.0825)-[SumOfTotal $ in Envelope]) AS [Sales Tax SUM], Sum([Car
Count - Table].[Manual Total Hours]) AS [Manual Total Hours SUM],
Clients.[Management Fee], ([SUM Total Billed]+[Management Fee]+[SumOfTotal $
in Envelope]+[Sales Tax SUM]) AS [SUM Total Revenue], ([Manual Total Hours
SUM]*6.55*0.0965) AS [Payroll Tax SUM], ([SUM Total Revenue]*0.38) AS [G and
A], Sum([Car Count - Table].[Total Cars Parked]) AS [Total Parked SUM]
FROM [Car Count - Table] INNER JOIN Clients ON [Car Count -
Table].acct_no=Clients.[Customer ID]
GROUP BY [Car Count - Table].Acct_name, [Car Count - Table].Region,
Year(Shiftdate), Month(Shiftdate), Clients.[Management Fee]
HAVING [Car Count - Table].Region<>"events";
Tables to join-
[Car Count - Table].acct_no (foreign key)
Clients.[Customer ID] (Primary key)
MonthlyRevenueAndWages.acct_id (foreign key)
No problem joining the first two tables together, but adding the third is
the problem. TIA