Inserting Text Values into a Sub Query

[LittleSmudge]

I have a Query that is the subclause of a Transform ( Cross Tab ) query.

The proble I have is that if the underlying data does not contain any one of the option values then the Form displays #Name? in the text box control.

Solution thinks I

(SELECT 'Fred', 'Joe', 'Mary' As Force ) As Fa LEFT JOIN [ subQuery ]
ON Fa.Force = subQuery.Name

I can then slap that lot into the Transform confident that I will always get the field names 'Fred', 'Joe' & 'Mary' out of the CrossTab.

However, How do I actually structure it to give me 'Fred', 'Joe', 'Mary' as DATA values in the one field Force because the above puts the data in three fields Exp10001, Exp10002 and Force.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[PHV]

You may try this (typed, untested):
(SELECT 'Fred' As Force UNION SELECT 'Joe' UNION SELECT 'Mary') As Fa LEFT JOIN [ subQuery ]
ON Fa.Force = subQuery.Name

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

 

[LittleSmudge]

When I try that PHV I get an error becuase every SELECT must contain at least one table name.
( Even though the data is not coming from any table )

The closest I've go is

(SELECT 'Fred' As Force FROM tblIni UNION SELECT 'Joe'  FROM tblIni UNION SELECT 'Mary' FROM tblIni ) As Fa LEFT JOIN [ subQuery ] 
ON Fa.Force = subQuery.Name

But the reference to an unnecessary table is ugly.
I'm surprised that there's not a standard way round this sort of requirement.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[PHV]

I'm surprised that there's not a standard way round this sort of requirement.
One way is to create a table with one field named force and populated with the names you want.

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

 

[LittleSmudge]

Yes, I've actually resorted to that solution in another instance - but it seems such overkill.

It's just adding to the network trafic for something that could ( should ? ) be done in local RAM.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.