Expression in Union query

[MilaK]

Hello,

I get an error message " includes fields that are not selected by the query"
in the following union query. Could someone suggests how to fix it.

thanks

SELECT tbl_Variant_qry.sample_name, tbl_Variant_qry.tumor_type, tbl_Variant_qry.gene_panel, tbl_Variant_qry.run_name, Right(tbl_Variant_qry.sample_name,Len(tbl_Variant_qry.sample_name)-(InStrRev(tbl_Variant_qry.sample_name,"_IonXpress"))) AS fieldsort

FROM tbl_Variant_qry

GROUP BY tbl_Variant_qry.sample_name, tbl_Variant_qry.tumor_type, tbl_Variant_qry.gene_panel, tbl_Variant_qry.run_name, Right(tbl_Variant_qry.sample_name,Len(tbl_Variant_qry.sample_name)-(InStrRev(tbl_Variant_qry.sample_name,"_IonXpress")))

ORDER BY 
Right(tbl_Variant_qry.sample_name,Len(tbl_Variant_qry.sample_name)-(InStrRev(tbl_Variant_qry.sample_name,"_IonXpress")))

UNION Select "(All)" as AllChoice, "(All)" as Something, "(All)" as Other, "(All)" as Bogus,  "(All)" as Stuff from tbl_Variant_qry

GROUP BY tbl_Variant_qry.sample_name, tbl_Variant_qry.tumor_type, tbl_Variant_qry.gene_panel, tbl_Variant_qry.run_name, Right(tbl_Variant_qry.sample_name,Len(tbl_Variant_qry.sample_name)-(InStrRev(tbl_Variant_qry.sample_name,"_IonXpress")))

ORDER BY Right(tbl_Variant_qry.sample_name,Len(tbl_Variant_qry.sample_name)-(InStrRev(tbl_Variant_qry.sample_name,"_IonXpress")));

 

[plog]

The individual queries of a UNON should simply have a SELECT and a FROM clause. When you add more clauses you just make it too difficult to work with.

Right now you have 2 complex SELECTs in your UNION. You should break them out and save them in their own query objects (sub1 & sub2). Then you would UNION those 2 queries together with this simple SQL:

SELECT * FROM sub1
UNION ALL
SELECT * FROM sub2

That should be the UNION. Now, when you have an issue, you can debug the individual queries to see exactly what is causing the error. I advise you to break out your UNION query like I have explained, then run sub1 & sub2 on their own to see what each produces and which one is generating that error. My gut tells me its the GROUP BY of the 2nd SELECT not containing any of the fields in its SELECT.

 

[plog]

Looking at it more that 2nd SELECT is totally worthless. You have no FROM clause. I think you really need to explain what you hope to accomplish, because I suspect you might be going about this the entirely wrong way. Again though, break out your SELECTS and make the UNION super simple.

 

[MilaK]

I’m trying to fill a combobox with distinct samples names.

Each sample name repeats in tbl_Variant_qry and contains “IonXpress_number”. For example: SU-15-41899-select_IonXpress_003 and MD-15-24019-SSIV_IonXpress_004.
I would like to sort samples by “IonXpress_number” segment in ascending order. that is accomplished by expression: Right(tbl_Variant_qry.sample_name,Len(tbl_Variant_qry.sample_name)-(InStrRev(tbl_Variant_qry.sample_name,"_IonXpress"))).

Also, I would like to include “All” choice in the drop-down. In addition, I would like to include tumor_type, gene_panel and run name in the combobox but not to display them in the combobox. The combobox contains four fields but only sample_name is bound.

Thank you for taking your time to help.

 

[plog]

First, I wouldn't use tbl_Variant_qry (very confusing name by the way) as the source for this query. If you want a list of unique values, you should create a query based on whatever table ultimately holds those values.

Second, it sounds like your data isn't properly normalized. Discrete pieces of data need to be stored discretely. If you want to use part of a field to sort on, then that data should be seperated into 2 distinct fields and stored that way. You shouldn't extract part of it when needed--you should store it seperately to begin with.

Third, what would you like to do with those 3 fields that you want to include but not show? That'd doesn't make sense.

Finally, how is this combo box going to work? How is the selected data going to be used? Specifically, what happens when the 'All' is chosen?