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
-- 
Logo

>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

What kind of problem?  Could you be more specific?  (e.g. "query returns no
records"/wrong records...)
You inner join the tables on the primary/foreign keys and some of your
records are missing?

-- 
Message posted via http://www.accessmonster.com