Linking Tables delivering different results

[Newtrick]

Hello. I am creating queries to join data from various tables but the results i'm getting are different. Showing the individual queries below and what happens when i link them. Note that i have tried all the different join options and still get different data. Can anyone help?



1. Single table query - these results are correct

SCODE	First Of Data Partners	Sum Of Budget
DBMD	Verve & Groundtruth	$53,333.33
DBMV	Google	$26,666.67
		$80,000.00

2. 2nd table query - these results are correct

SCODE	First Of Vendor	Sum Of Cost to Client
DBMD	DBM	57,826.70
DBMV	DBM	201,889.34
FB/IG	Facebook/Instagram	239,575.61
GGLS	Google Ads	72,747.43
		572,039.08

3. Combined Query (joined where the SCODE column which is from a separate table links both tables and set up so that all of the SCODEs are supposed to be shown but only the matching table data should be included - #2 in the join options)

SCODE	Sum Of Budget	Sum Of Cost to Client
DBMD	$106,666.67	231,306.80
DBMV	$106,666.67	201,889.34
	$213,333.33	433,196.14

[June7]

Show the SQL statement of third query.

[Newtrick]

Here it is. Thank you so much for your quick reply.

SELECT DISTINCTROW SCODE.SCODE, Sum([BB buy summary access].Budget) AS [Sum Of Budget], Sum([Bobbi Lumina Access].[Cost to Client]) AS [Sum Of Cost to Client]
FROM (SCODE INNER JOIN [Bobbi Lumina Access] ON SCODE.[SCODE] = [Bobbi Lumina Access].[SCODE]) INNER JOIN [BB buy summary access] ON SCODE.[SCODE] = [BB buy summary access].[SCODE]
GROUP BY SCODE.SCODE;

[June7]

That does not appear to be a query that is joining those two aggregate queries to a table of SCODE unique values. If you join those two queries to a table of unique SCODE values, each row should already be unique because the queries show each SCODE only once.

SELECT SCODE.SCODE, [Sum of Cost to Client], [Sum of Budget] FROM (SCODE INNER JOIN [BudgetSummaryQuery] ON SCODE.[SCODE] = [BudgetSummaryQuery].[SCODE]) INNER JOIN [BuySummaryQuery] ON SCODE.[SCODE] = [BuySummaryQuery].[SCODE];


Why use First() to return a vendor name? Of what use is this?

[Newtrick]

Again, thanks. The SCODE file is a separate table and each of the two cost columns are linked via look up fields labeled SCODE on the table. Does that answer your question? Also, i did not code in "First()", it automatically included that when i selected fields for the query.

[June7]

You say you want "set up so that all of the SCODEs are supposed to be shown but only the matching table data should be included". To accomplish this would have to INNER join the two aggregate queries to each other. Then in another query join that query to table of all SCODE values using LEFT or RIGHT JOIN.

[Newtrick]

SUCCESS! I am very grateful for the help. Enjoy your day.