Union Query

[scoe]

Hi


As very new to this I do not seem to be able to get my head around Union queries. I have two queries that have 3 identical fields and two unidentical ones, what I need is to pull all the fields into one query, giving me one set of fields.

The code I have at present is:
[SELECT ALLB, CHC24, CCare24, allb24ft, B24ft
FROM [All Support and Budget 24 FT]
UNION SELECT ALLB, CHC24, CCare24,allb24pt, B24pt
FROM [All Support and Budget 24 PT]

This brings me the following fields:
ALLB, CHC24, CCare24, allb24ft, B24ft

I also need it to include the results of allb24pt, B24pt, I'm assuming this has to do with the join clause but it just isn't doing anything for me.

I would be grateful for any advise on this as this is the second time I have had to do something like this and I cheated the first time but feel I really need to know the best way to get the information together.

Thank you

[alansidman]

Do you wish for allb24pt and b24pt to be in separate columns? If this is the case, then in your first query that has the FT data, add two columns as follows:
allb24pt:"" and B24ft:""

In your second query, add two columns allb24ft:"" and B24ft:"" Make sure the order of the columns is exactly the same for the two queries. Now run your Union Query. You should get all the results you want with the data in the right fields (columns).

Alan

[ssanfu]

@Alan,

Do you wish for allb24pt and b24pt to be in separate columns? If this is the case, then in your first query that has the FT data, add two columns as follows:
allb24pt:"" and B24ft:""

For the first query, shouldn't that be allb24pt:"" and B24pt:"" ???

[alansidman]

@Steve;
Yup! Thanks for pointing that out for the OP.

Alan

[scoe]

Thank you for your help, I have had a go however I am now getting two rows instead of one, I know I'm missing something that is probably very simple

My code looks like this:
[SELECT ALLB, CHC, [Childcare 24], B24ft, allb24ft, "" AS B24pt, "" AS allb24pt
FROM [All Support and Budget 24 FT]
UNION SELECT ALLB, CHC, [Childcare 24], B24ft, allb24ft, "" AS B24pt, "" AS allb24pt
FROM [All Support and Budget 24 PT];]

And this is what is coming out:
(ALLB, CHC and childcare are my duplicates)

ALLB	CHC	Childcare 24	B24ft	allb24ft	B24pt	allb24pt
£120.00	1	£0.00	0	0
£120.00	1	£0.00	0	1

[alansidman]

I think that you probably need to have Select query instead of a Union Query. Try ths"

SELECT [All Support and Budget 24 FT].ALLB, [All Support and Budget 24 FT].CHC, [All Support and Budget 24 FT].[Childcare 24], [All Support and Budget 24 FT].B24ft, [All Support and Budget 24 FT].allb24ft, [All Support and Budget 24 PT].B24pt, [All Support and Budget 24 PT].allb24pt
FROM [All Support and Budget 24 FT] INNER JOIN [All Support and Budget 24 PT] ON [All Support and Budget 24 FT].ALLB = [All Support and Budget 24 PT].ALLB;

This will create duplicate values because I joined the two tables on the Price and you probably have a unique identifier that you have not provided us that you would join the two tables on. Perhaps if you posted a copy of your db that has been sanitized for confidential information, we could provide you with a more exact answer.

[scoe]

Thank you for all your help, I may well yet put the db on as I need to get this to work correctly and it is proving to be more difficult than I imagined... I am playing with the ideas you have already shared and have gone back to the original queries.
Thank you again for your support.

[scoe]

Thank you , just to update, I went back to the original data and broke it down to two select queries and then combined them to get the correct answer in the end, thank you again for all your help.

[alansidman]

you are welcome