If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Joining Multiple Tables
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 |
#2
|
|||
|
|||
Joining Multiple Tables
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 |
Thread Tools | |
Display Modes | |
|
|