Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Other Forums  MS Access  Showing only distinct objects in a left join strin

Author  Topic  Eowyn Starting Member 5 Posts Posted - 2001-06-21 : 09:43:57 I've got 3 tables in access. Table 1 contains projectid Orderid Date Amount1 Table 2 contains projectid Year Month Amount2 Table 3 contains projectid year month Amount3 For every month of the year there will be from 0 to many Amount1 per month for every projectid, but always one entry per month for Amount2 and Amount3. For each month, I would like all Amount1 entries to be listed in a table, and amount2 and 3 should be shown one time - even if there was no value for Amount1 that month. The wanted result should be a table like this: Orderid Date Month Year Amount1 Amount2 Amount3 123 1.1.2001 1 2001 100 200 300 124 2.1.2001 1 2001 231 125 6.1.2001 1 2001 521 126 1.2.2001 2 2001 231 500 400 127 3.2.2001 2 2001 324 3 2001 234 523 128 6.4.2001 4 2001 567 345 234 I've almost got the solution: SELECT [Table1].[Orderid], [Table1].[Date], [Table2].[Month], [Table2].[Year], [Table1].[Amount1], [Table2].[Amount2], [Table3].[Amount3] FROM (Table2 LEFT JOIN Table1 ON (Month([Table1].[Date]) = [Table2].[Month]) AND (Year([Table1].[Date]) = [Table2].[Year])) LEFT JOIN Table3 ON ([Table2].[Month] = [Table3].[Month]) AND ([Table2].[Year] = [Table3].[Year]); This is almost the thing, only it gives out: Orderid Date Month Year Amount1 Amount2 Amount3 123 1.1.2001 1 2001 100 200 300 124 2.1.2001 1 2001 231 200 300 125 6.1.2001 1 2001 521 200 300 126 1.2.2001 2 2001 231 500 400 127 3.2.2001 2 2001 324 500 400 3 2001 234 523 128 6.4.2001 4 2001 567 345 234 Not Orderid Date Month Year Amount1 Amount2 Amount3 123 1.1.2001 1 2001 100 200 300 124 2.1.2001 1 2001 231 125 6.1.2001 1 2001 521 126 1.2.2001 2 2001 231 500 400 127 3.2.2001 2 2001 324 3 2001 234 523 128 6.4.2001 4 2001 567 345 234 You see amount2 and 3 are repeated if there is more than one orderid that month. Can I make it so that Amount2 and 3 never shows more than one time for each month?