Strange Union Query Error

[wisey01]

Hey guys,

Getting a weird error where my union query is scrambling the data under different headings.... SQL is below. I know its absolutely terribly designed, but I'm kinda new to this stuff, and definitely open to advice!

SELECT [Application List].[TC or #], [Application List].[Test Object], [MS SQL Server 2008R2 Test Script].[Test Case], IIf([MS SQL Server 2008R2 Test Script]![1st Test Results]="Fail" Or [MS SQL Server 2008R2 Test Script]![2nd Test Results]="Fail" Or [MS SQL Server 2008R2 Test Script]![3rd Test Results]="Fail","Failed",IIf([MS SQL Server 2008R2 Test Script]![1st Test Results]="Pass" Or [MS SQL Server 2008R2 Test Script]![2nd Test Results]="Pass" Or [MS SQL Server 2008R2 Test Script]![3rd Test Results]="Pass","Passed",IIf([MS SQL Server 2008R2 Test Script]![1st Test Results]="Not Relevant" Or [MS SQL Server 2008R2 Test Script]![2nd Test Results]="Not Relevant" Or [MS SQL Server 2008R2 Test Script]![3rd Test Results]="Not Relevant","Not Relevant","Incomplete"))) AS Status, [MS SQL Server 2008R2 Test Script].[Tester 1], [MS SQL Server 2008R2 Test Script].[Tester 2], [MS SQL Server 2008R2 Test Script].[Tester 3], [MS SQL Server 2008R2 Test Script].[Defect Number], [MS SQL Server 2008R2 Test Script].Comments
FROM [MS SQL Server 2008R2 Test Script] INNER JOIN [Application List] ON ([Application List].[TC or #] = [MS SQL Server 2008R2 Test Script].[TC or #]) AND ([MS SQL Server 2008R2 Test Script].[Test Object] = [Application List].[Test Object]);
UNION SELECT IIf([MS Excel Test Script]![1st Test Results]="Fail" Or [MS Excel Test Script]![2nd Test Results]="Fail" Or [MS Excel Test Script]![3rd Test Results]="Fail","Failed",IIf([MS Excel Test Script]![1st Test Results]="Pass" Or [MS Excel Test Script]![2nd Test Results]="Pass" Or [MS Excel Test Script]![3rd Test Results]="Pass","Passed",IIf([MS Excel Test Script]![1st Test Results]="Not Relevant" Or [MS Excel Test Script]![2nd Test Results]="Not Relevant" Or [MS Excel Test Script]![3rd Test Results]="Not Relevant","Not Relevant","Incomplete"))) AS Status, [MS Excel Test Script].[TC or #], [MS Excel Test Script].[Test Object], [MS Excel Test Script].[Test Case], [MS Excel Test Script].[1st Test Results], [MS Excel Test Script].[2nd Test Results], [MS Excel Test Script].[3rd Test Results], [MS Excel Test Script].Comments, [MS Excel Test Script].[Defect Number]
FROM [Application List] INNER JOIN [MS Excel Test Script] ON ([Application List].[Test Object] = [MS Excel Test Script].[Test Object]) AND ([Application List].[TC or #] = [MS Excel Test Script].[TC or #]);

Basically it isnt listing the correct data under the right column headings.... I think it might be something to do with my relationships but dont know enough to be sure. Any help would be much appreciated!

I have attached a screenshot as well, all the data is controlled into the correct fields.

 

[Galaxiom]

Your union query is a consequence of your data structure. Sorry but you need start again after you gain some understanding of normalization and relational data structures.

If you keep going as you are the problems and workarounds will continue to bug you.

Search the forum and internet for normalization.

 

[PeterF]

I agree with Galaxiom about normalistation.
The first query for a union defines the order and column names.
Your second query has a different order then the first so you end up with the wrong result.

 

[wisey01]

Can you recommend a thread or place to start?

That's about as complex as the database is gonna get thankfully, so if this issue is solved I should be just about done with it anyway.

Edit: Ah thankyou, so if I just make sure the SQL structures match it should be ok?

Edit: Edit: Worked like a charm, thanks guys!

 

[Galaxiom]

Also note that there are code tags which display code better. There is a sticky thread about it somewhere.

 

[Galaxiom]

That's about as complex as the database is gonna get thankfully, so if this issue is solved I should be just about done with it anyway.

That is what you think now but when some users get hold of it they will ask for more. They always do.