Adding fields to a Union Query 1

[ZOR]

Using the query below,
1. How could one add the table ID number (Autonumber) of the sortfield into the query (not for any sorting purpose, just to add data in)

2. The query adds a leading record space, how can I eliminate nulls

3. If I wanted a blank row to give a break between items to make reading easier (continuos form display), how could I do it. eg:

Monday Garage
Shop
----- SPACE -----
Tuesday Chemist
------ SPACE -----
etc


SELECT [First field] AS TextA, Min([Second field]) As TextB, [First field] AS sortField
FROM yourTable
GROUP BY [First field]
UNION SELECT " ", [Second field], [First field]
FROM yourTable A
WHERE [Second field] > (SELECT Min([Second field]) FROM yourTable B WHERE B.[First field] = A.[First field])
ORDER BY 3, 2;

Thought I would post a seperate thread, as I star is pretty mean to offer for the effort. Regards

 

[ZOR]

Sorry to be such a problem. There is still no space which existed in earlier code.

SELECT [First field] AS TextA, Min([Second field]) As TextB, [First field] AS sortField, 1 AS typeField
FROM yourTable
GROUP BY [First field]
UNION SELECT " ", [Second field], [First field], 2
FROM yourTable A
WHERE [Second field] > (SELECT Min([Second field]) FROM yourTable B WHERE B.[First field] = A.[First field])
-------------------------------------------------------
!!! THE LINE BELOW GAVE A SPACE AFTER THE LAST CREWFORENAME !!
UNION SELECT " ", " ", [First field], 3

FROM yourTable
ORDER BY 3, 4, 2;

It gave a break in the list to make reading the rows easier. Regards

 

[PHV]

Sorry, forgot to amend the sort clause:
SELECT A.CrewTitle AS TextA, A.Crewforename As TextB, A.ID1 AS TextC, A.[CheckBox Field] AS TextD, A.CrewTitle AS sortField, 1 AS typeField
FROM CASTLIST A INNER JOIN (
SELECT CrewTitle, Min(Crewforename) AS minForename FROM CASTLIST GROUP BY CrewTitle
) B AN A.CrewTitle = B.CrewTitle AND A.Crewforename = B.minForename
UNION SELECT " ", Crewforename, ID1, [CheckBox Field], CrewTitle, 2
FROM CASTLIST A
WHERE Crewforename > (SELECT Min(Crewforename) FROM CASTLIST B WHERE B.CrewTitle = A.CrewTitle)
UNION SELECT " ", " ", Null, Null, CrewTitle, 3
FROM CASTLIST
ORDER BY [highlight]5, 6[/highlight], 2;

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

 

[ZOR]

Thanks again PHV, just saw this posting pop up again. Thanks for the help earlier, in fact I was trying to see how to put the checkbox field into the working query of the one tonight, but now you have wrapped it all up. All the best regards