3 Table Select Query

[colmtourque]

If I have 3 tables all tied on ID Number two of which have dates.
Table1.idnumber
table2.idnumber table2.date
table2.idnumber table3.date

I want a select query that joins the three tables and grabs all the data in them based on month. I have been combatting this for 2 weeks and I am still no closer to grabbing the data. The dates might of might not be the same, some months one data will have data on a particular id number some other months it will not.

Thanks in advance.
A very confused tourque

 

[Pat Hartman]

You'll need three queries. Query1 selects data from Table2 for a specified date range. Query2 selects data from Table3 for a specified date range. Query 3, joins Table1 to Table2 with a left join and also joins Table1 to table2 with a left join. You will end up with all the rows from table1 and any data from tables 2 and 3 that match the ID of a record in Table1.

 

[colmtourque]

I hope this is not to much but I did those queries and I get a heck of a lot of duplication:
Query1
SELECT DISTINCTROW [Acct: Fixed Charges].IDNumber, Format$([Acct: Fixed Charges].[Date],'mmmm yyyy') AS [Date By Month], Sum([Acct: Fixed Charges].CoreCharge) AS Core, Sum([Acct: Fixed Charges].[Tech Fee]) AS Tech, Sum([Acct: Fixed Charges].UnitedWay) AS UnitedWay, Sum([Acct: Fixed Charges].Web) AS Web
FROM [Acct: Fixed Charges]
GROUP BY [Acct: Fixed Charges].IDNumber, Format$([Acct: Fixed Charges].[Date],'mmmm yyyy'), Year([Acct: Fixed Charges].[Date])*12+DatePart('m',[Acct: Fixed Charges].[Date])-1;


Query2
SELECT DISTINCTROW [Acct: Var Charges for Report Crosstab Query].[ID Number], Format$([Acct: Var Charges for Report Crosstab Query].[Date],'mmmm yyyy') AS [Date By Month], Sum([Acct: Var Charges for Report Crosstab Query].[Sum Of Office Supplies]) AS [Office Supplies], Sum([Acct: Var Charges for Report Crosstab Query].[Sum Of Other Charges]) AS Other, Sum([Acct: Var Charges for Report Crosstab Query].[Sum Of Phone Charges]) AS Phone, Sum([Acct: Var Charges for Report Crosstab Query].[Sum Of Printing and Copies]) AS Printing, Sum([Acct: Var Charges for Report Crosstab Query].[Sum Of Shipping and Postal]) AS Shipping
FROM [Acct: Var Charges for Report Crosstab Query]
GROUP BY [Acct: Var Charges for Report Crosstab Query].[ID Number], Format$([Acct: Var Charges for Report Crosstab Query].[Date],'mmmm yyyy'), Year([Acct: Var Charges for Report Crosstab Query].[Date])*12+DatePart('m',[Acct: Var Charges for Report Crosstab Query].[Date])-1;


Query3
SELECT DISTINCTROW WanUserInformation.[ID Number], WanUserInformation.[Last Name], WanUserInformation.[First Name], 1 AS Expr1, [Acct: Combined: Var].[Office Supplies], [Acct: Combined: Var].Other, [Acct: Combined: Var].Phone, [Acct: Combined: Var].Printing, [Acct: Combined: Var].Shipping, [Acct: Combined: Fixed].Core, [Acct: Combined: Fixed].Tech, [Acct: Combined: Fixed].UnitedWay, [Acct: Combined: Fixed].Web, [Acct: Combined: Var].[Date By Month], [Acct: Combined: Fixed].[Date By Month]
FROM (WanUserInformation INNER JOIN [Acct: Combined: Var] ON WanUserInformation.[ID Number] = [Acct: Combined: Var].[ID Number]) INNER JOIN [Acct: Combined: Fixed] ON WanUserInformation.[ID Number] = [Acct: Combined: Fixed].IDNumber
GROUP BY WanUserInformation.[ID Number], WanUserInformation.[Last Name], WanUserInformation.[First Name], 1, [Acct: Combined: Var].[Office Supplies], [Acct: Combined: Var].Other, [Acct: Combined: Var].Phone, [Acct: Combined: Var].Printing, [Acct: Combined: Var].Shipping, [Acct: Combined: Fixed].Core, [Acct: Combined: Fixed].Tech, [Acct: Combined: Fixed].UnitedWay, [Acct: Combined: Fixed].Web, [Acct: Combined: Var].[Date By Month], [Acct: Combined: Fixed].[Date By Month]
HAVING ((([Acct: Combined: Var].[Date By Month])="January 2002")) OR ((([Acct: Combined: Fixed].[Date By Month])="January 2002"));


I tried this, I even tried adding in a table that had just the Months involved and joining that to the datebymonth fields but that left out any dates that were not in BOTH tables.

Still extremly confused;
Tourque

 

[Pat Hartman]

DistinctRow and Group By are conflicting instructions. Remove the DistinctRow and I don't understand why you have the Year() function in the GroupBy clause. Remove it also.

My instructions for query3 should have read:
Query 3, joins Table1 to Query1 with a left join and also joins Table1 to Query2 with a left join.

Query3 does not need a Group By since there should only be a single row for each ID.

To change the join type to LEFT, you can double click on the center of the join line in the QBE grid to bring up the join properties or you can switch to SQL view and change the word INNER to LEFT.

 

[colmtourque]

Pat thanks I tried that but still no dice let me see if I am explaining it wrong.
Because I think that might be the problem. I fully understand what you are saying (or at least I have fooled myself into thinking that).
The three tables in my original example.
If table two and three have different date information.
Table 2 will have one entry for april and table 3 4 entrys. when I do the query I get the 4 entries for table 3 but the table 2 entry repeated 4 times.
I tried the left join but still got the same results.

 

[Pat Hartman]

I rethought your problem and determined that you are trying to write a single query for a group of tables that contain more than one 1-to-many relationship. The data in table 1 is 1-to-many with the data in table2 and the data in table1 is 1-to-many with the data in table3 BUT, the data in table2 and table3 has no relationship. The result of a query that joins these three tables is a cartesian product which as you can see, makes no sense.

To properly represent this structure, you'll need to use a report that uses a main report to display the data from table1 and two separate subreports. One each to display the data from tables 1 and 2.

 

[colmtourque]

I'll move this to the report area and point back here then to continue. Thanks!!!