Prev: Difference between numbers
Next: Getting info from two tables with disimilar headings
From: Logo on 1 Mar 2010 01:10
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
From: PieterLinden via AccessMonster.com on 1 Mar 2010 02:53
>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

 | 
Pages: 1
Prev: Difference between numbers
Next: Getting info from two tables with disimilar headings