Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
B.Site | B.Month | B.Item | B.Amount |
---|---|---|---|
My Place | JanGloves | 30.00 | |
My Site | JanShoes | 40.00
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!
Rather than join use Concatenate
Thanks Manish - I'll give it a try.
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...
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