Combine fields in different tables and amount is duplicated/stacked

[slpgqn]

Hi



Need help. I have a few input tables and I am trying to combine the fields from 2 tables into one query. How do I go about doing it without having the project amount (highlighted in red) being duplicated?

TIA

Table 1			Table 2				Query
Project	Project Amount		Project	Budget	Budget Amount		Project	Project Amount	Budget	Budget Amount
1	1000		1	1A	100		1	1000	1A	100
2	2000		1	1B	100		1	1000	1B	100
3	1500		1	1C	100		1	1000	1C	100
4	2000		1	1D	100		1	1000	1D	100
5	1200		2	2A	500		2	2000	2A	500
			2	2B	500		2	2000	2B	500
			3	3A	300		3	1500	3A	300
			3	3B	300		3	1500	3B	300
			4	4A	250		4	2000	4A	250
			4	4B	250		4	2000	4B	250
			5	5A	150		5	1200	5A	150
			5	5B	150		5	1200	5B	150

[ranman256]

It WILL duplicate. It's how the queries work due to your data.

[June7]

Perhaps you should do an aggregate query of Table2 to summarize data by project then join that query to Table1.

Otherwise, build a report and use its properties to prevent repetition of the ProjectAmount value. Options:

1. set textbox HideDuplicates property to Yes

2. set a group on the Project identifier and put the ProjectAmount field in the group header.

3. report/subreport arrangement