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

 

[PHV]

3. If I wanted a blank row to give a break between items
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])
UNION SELECT " ", " ", [First field], 3
ORDER BY 3, 4, 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]

Many thanks PH, however I am getting the message - Query input must contain at least 1 Table or Query?. Thanks

 

[PHV]

Sorry for the missing line:
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])
UNION SELECT " ", " ", [First field], 3
FROM yourTable
ORDER BY 3, 4, 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]

You make it look so easy, works a treat. Is there a way to remove the blank records at the front, there are now two records commencing blank. Also does a union query allow other fields to be added that are not within the active part? Many thanks

 

[ZOR]

Getting nearer, but still a way to go. I am trying to include the ID1 field in column TextC. However because I am making sure the CrewTitle does not get repeated, I'm left with further ID1's being of the original record. Hope that's clear!! Anyone see a way round it?. I also need to include a further column, being a checkbox value for each Crewforename. Many thanks

 

[ZOR]

Forgot to paste the code!!

SELECT [CrewTitle] AS TextA,
Min([Crewforename]) As TextB,
[CrewTitle] AS sortField,
1 AS typeField,
Min([ID1]) As TextC
FROM CASTLIST

GROUP BY [CrewTitle]

UNION

SELECT " ", [Crewforename], [CrewTitle], 2, "WRONG ID1"

FROM CASTLIST A

WHERE [Crewforename] > (SELECT Min([Crewforename]) FROM CASTLIST B WHERE B.[CrewTitle] = A.[CrewTitle])

UNION

SELECT " ", " ", [CrewTitle], 3," "

FROM CASTLIST

ORDER BY 3, 4, 2;

 

[PHV]

Something like this ?
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 3, 4, 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]

Ohhhh, so pleased your back. Just tried the above, calls syntax error in From Clause? Thanks

 

[PHV]

Which version of access ?

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

 

[ZOR]

Access 2003. Thanks

 

[PHV]

Sorry for the typo, replace this:
) B AN A.CrewTitle = B.CrewTitle AND A.Crewforename = B.minForename
By this:
) B [highlight]O[/highlight]N A.CrewTitle = B.CrewTitle AND A.Crewforename = B.minForename

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

 

[ZOR]

Its now working, there is a leading blank row, and then several rows with just the sortfield having data, but then all the rows I want come shining through. I know I can join this with another query to eliminate the unwanted rows, so that gets me moving again. Won't tell you how many hours of gone by trying to get this working. A well deserved star, with I could give you more. Many thanks once again. Regards

 

[ZOR]

Can I trouble you again, I seem to have lost my space I had from the very first code. Any chance of seeing where it's gone. Best regards

 

[ZOR]

'

 

[PHV]

Sorry, I don't understand your issue.
Can you please elaborate ?

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, If you read your first answer at the start of the thread, you explain where to insert a space. I have tried adding it myself but no luck so far.

 

[PHV]

And what is your actual SQL code ?

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

 

[ZOR]

I am using the code from your last posting :

SELECT A.CrewTitle AS TextA, A.Crewforename As TextB, A.ID1 AS TextC, A.[InBlock] 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 ON A.CrewTitle = B.CrewTitle AND A.Crewforename = B.minForename
UNION SELECT "v", Crewforename, ID1, [InBlock], CrewTitle, 2
FROM CASTLIST A
WHERE Crewforename > (SELECT Min(Crewforename) FROM CASTLIST B WHERE B.CrewTitle = A.CrewTitle)
UNION SELECT "x", "z", Null, Null, CrewTitle, 3
FROM CASTLIST
ORDER BY 3, 4, 2;

I put an X,y and V in your code to see where buts went through. I tried adding another " ", before the "x" bit but that unbalanced the columns (errored). I was going to add 2 AS typeField and add bits all the way through to try and find the correct column structure, but didn't want to go to far as to lose where I was. Hope it makes sense.

 

[PHV]

Probably an issue with Null values when typeField=3:
SELECT A.CrewTitle AS TextA, A.Crewforename As TextB, A.ID1 & "" AS TextC, IIf(A.InBlock,"Yes","No") 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 ON A.CrewTitle = B.CrewTitle AND A.Crewforename = B.minForename
UNION SELECT " ", Crewforename, ID1 & "", IIf(InBlock,"Yes","No"), CrewTitle, 2
FROM CASTLIST A
WHERE Crewforename > (SELECT Min(Crewforename) FROM CASTLIST B WHERE B.CrewTitle = A.CrewTitle)
UNION SELECT " ", " ", " ", " ", CrewTitle, 3
FROM CASTLIST
ORDER BY 3, 4, 2;


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