Split a field into two records if a condition exists 1

[greekpatriott]

Is there a way to split a field's record into two if a condition exists?
I am breaking my head to find a way to do it, but I have seen something similar to this.

For example if the difference between two fields, eg. the enddate and endmonthstartdate, is negative then it would split a field called result by doing sum calculations. Is this possible???
Please I am stack on this and I cant find a solution myself.
Regards
Panos

 

[greekpatriott]

Correction
Is there a way to split a field's record into two if a condition exists?
I am breaking my head to find a way to do it, but I have not seen something similar to this.

For example if the difference between two fields, eg. the enddate and endmonthstartdate, is negative then it would split a field called result by doing some calculations. Is this possible???
Please I am stack on this and I cant find a solution myself.
Regards
Panos

 

[greekpatriott]

Any Santaclause programmer that can make my Christmass better?????
Cheers

 

[greekpatriott]

Ok I thought of a solution. I do all the calculations in the main query. Then I split the query into two seperate queries. Now I have to perform something like a union all query to combine all the records.

Hmmmmmmm now I need to know about the sql. What a messssss.
Ok these are the two sql of my queries.

SELECT [Control Plan Q].[Hierarchy Position], [Control Plan Q].[Package ID], [Control Plan Q].[Work Item ID], [Control Plan Q].Difference, [Control Plan Q].ENDSTART
FROM [Control Plan Q]
ORDER BY [Control Plan Q].[Hierarchy Position], [Control Plan Q].[Package ID], [Control Plan Q].[Work Item ID];
and
SELECT [Control Plan Q].[Hierarchy Position], [Control Plan Q].[Package ID], [Control Plan Q].[Work Item ID], [Control Plan Q].Expr2, [Control Plan Q].ENDDATE
FROM [Control Plan Q]
WHERE ((Not ([Control Plan Q].Expr2)="0"))
ORDER BY [Control Plan Q].[Hierarchy Position], [Control Plan Q].[Package ID], [Control Plan Q].[Work Item ID];

So can one help me out to add the data in the second query to the first using a union query? Cheers.

 

[greekpatriott]

ok I managed to sort it out. I just added a Union all query between the two sql. So it is sorted out. No need for help.

Ok anyway thank to this place I got many solutions. So thanks eveyone who helped me so far. PHV, Marcus and many more so thanks.

 

[greekpatriott]

Who ever wanders what is the final SQL this is it:

SELECT [Control Plan Q].[Hierarchy Position], [Control Plan Q].[Package ID], [Control Plan Q].[Work Item ID], [Control Plan Q].Difference AS [Control Mhrs], [Control Plan Q].ENDSTART AS [Date]
FROM [Control Plan Q]
ORDER BY [Control Plan Q].[Hierarchy Position], [Control Plan Q].[Package ID], [Control Plan Q].[Work Item ID];

UNION ALL SELECT [Control Plan Q].[Hierarchy Position], [Control Plan Q].[Package ID], [Control Plan Q].[Work Item ID], [Control Plan Q].Expr2 AS [Control Mhrs], [Control Plan Q].ENDDATE AS [Date]
FROM [Control Plan Q]
WHERE ((([Control Plan Q].Expr2)<>"0"))
ORDER BY [Control Plan Q].[Hierarchy Position], [Control Plan Q].[Package ID], [Control Plan Q].[Work Item ID];

It is excellent what one can do why Access. Just you have to device a solution. I knew what I wanted though I did not know how to do it. Thanks to the Union all query the problem is solved.

 

[PHV]

You may play with only one ORDER BY clause:
SELECT [Hierarchy Position], [Package ID], [Work Item ID], Difference AS [Control Mhrs], ENDSTART AS [Date]
FROM [Control Plan Q]
UNION ALL
SELECT [Hierarchy Position], [Package ID], [Work Item ID], Expr2, ENDDATE
FROM [Control Plan Q]
WHERE Expr2<>'0'
ORDER BY 1, 2, 3;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244