Join tables but not duplicate data..

[ndeeleysww]

Hi,

Apologies if the title of this question doesn't make sense - I don't know how to describe it!

I have two tables. Table A shows a list of sites, a Month, and a budget for that month:

A.Site	A.Month	A.Budget
My Place	Jan	1000
My Place	Feb	2000
My Site	Jan	1000

The budget shows the total sum that site is allowed to spend.

I need to join this in Sense to Table B, which shows what actually has been spent, by item:

JanJan40.00

B.Site	B.Month	B.Item	B.Amount
My Place	Gloves	30.00
My Site	Shoes

I've tried joining the two tables using every join method I can think of, but when I do the Budget column is duplicated, and therefore the total sum of that budget is incorrect in the app:

A.Site	A.Month	B.Item	B.Amount	A.Budget
My Place	Jan	Gloves	30	1000
My Place	Jan	Shoes	40	1000
My Place	Jan	Gloves	20	1000
TOTAL			90	3000

How do I join the tables so that the Amount is Totalled by the Budget is shown only once?

Hope that makes Sense!

[MK_QSL]

Rather than join use Concatenate

[ndeeleysww]

Thanks Manish - I'll give it a try.

[ndeeleysww]

I did try this, but although it worked, it wasn't as flexible as I needed. The data was appended to the end of my query and not associated with the columns I needed, so I couldn't filter properly.

What I did in  the end was ran a query against my two tables, joining what was needed, then used this as a basis for a sql union, applying null fields to columns that did not exist. It was a hassle, but I got round it...

[phaneendra_kunc]

Why cant you keep these two tables separate and create a Concatenated Key? this will ensure you wont have duplicates.

Like Site&'-'&Month as Key