Combining multiple queries into one

[MilaK]

I would like to display the count of each “aa_change” in each “tumor_type” in one field and the count of distinct “samples_ids” in each “tumor_type” in another field on a continuous form.

Instead of a summary table I would like incorporate these statistics with each record in “Variant” table. The form will display each record from "tblVariant" and also one column for count of “aa_change” in each "tumor type" and a column with the count of distinct “sample_ids” in each “tumor_type”.

Please see the screenshot of the table structure. I’ve managed to come up with three separate queries that return the correct information but I’m not sure how to combine them into one, if possible. This seems very complicated.

Query1: Count of each aa_change in tumor_type
SELECT tbl_Samples.tumor_type, Count(tbl_Variants.aa_change) AS exp, tbl_Variants.aa_change
FROM tbl_Samples INNER JOIN tbl_Variants ON tbl_Samples.sample_id = tbl_Variants.sample_id
GROUP BY tbl_Samples.tumor_type, tbl_Variants.aa_change;

Query2: Count of distinct sample ids for each tumor_type.
SELECT tbl_Samples.tumor_type, Count(tbl_Samples.[sample_id]) AS exp
FROM tbl_Samples
GROUP BY tbl_Samples.tumor_type;

Query3: all fields from tblVariants.

Thanks for your help, Mila

 

[sneuberg]

I suggest trying a join between query1 and query2 on tumor_type. If that gives you duplicates in the output then try setting the Unique Records property to Yes.

 

[plog]

I can see what you want, but can't really envision the specific SQL to do it, but I can walk you through getting there using the query builder.

Create a new query and from left to right bring in these datasources tblVariants, tbl_Samples, Query1 and Query2.

LEFT JOIN them (meaning all from the table/query listed first and matches from those listed second):
tbl_Variants.sample_id -> tblSamples.sample_id
tblSamples.tumor_type -> Query1.tumor_type
tblSamples.tumor_type -> Query2.tumor_type

In the bottom area, bring down all the fields you want the query to show. Run that and it should provide you with what you want. If not, please provide sample data demonstrating what you want. I would need 2 sets:

A. Starting sample data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Based on the data in A, show what your final query should result in.

 

[sneuberg]

@plog

Wouldn't tbl_Variants.sample_id -> tblSamples.sample_id produce all of the records in tbl_Variant table and so produce duplicate records for the same types?

@Milak

I would be helpful if you could upload the database or a copy of the database with just the tbl_Variants and tblSamples tables.

 

[plog]

Wouldn't tbl_Variants.sample_id -> tblSamples.sample_id produce all of the records in tbl_Variant table and so produce duplicate records for the same types?

Yes to everything before the 'and' and no to everything after. It wouldn't produce duplicate records, it would show the same values for the variants of the same types, that's what he said he wanted. He wanted the final query to have the same amount of records as tbl_Variants, my query will do that. However the values from Query1 and Query2 will go to multiple records in tbl_Variants.

Now if he were to take my query and total them up expecting them to equal the total number of samples, he would be wrong . So, I'm pretty confident that I gave him what he asked for. However, I'm only 50% confident what he asked for is what he wants.

 

[MilaK]

Please the attached example db for clarification. Thanks so much for your help.

Mila

p.s. Mila is not a boy's name.

 

[plog]

The only thing your post clarified was the gender of the name Mila. What is that database suppose to show us?

Did either of our suggestions help? Did you attempt either?

 

[sneuberg]

What is that database suppose to show us?

I asked her to upload the database so that I could test stuff. I tested your proposal. The SQL I came up with is:

SELECT tbl_Variants.ID, tbl_Variants.variant_id, tbl_Variants.sample_id, tbl_Samples.tumor_type, qry_variant_freq.exp, qry_variant_freq.aa_change, qry_tumor_type.exp
FROM ((tbl_Variants LEFT JOIN tbl_Samples ON tbl_Variants.sample_id = tbl_Samples.sample_id) LEFT JOIN qry_variant_freq ON tbl_Samples.tumor_type = qry_variant_freq.tumor_type) LEFT JOIN qry_tumor_type ON tbl_Samples.tumor_type = qry_tumor_type.tumor_type;

This produces a lot of records. 2205 for the 108 that are in the tbl_Variants table. This is because the qry_variant_freq produces many records per type, e.g., 12 for Brain, 5 for Breast, 25 for Lung because of the different aa_changes. This may be what the OP wants.

 

[MilaK]

Ok, sorry.

On “frmVariants” form, field “#of Variants per Tumor Type” should display the number of occurrences of variants [aa_change] in “tumor type”. For example: the first tumor type is LUNG and variant is V600E. This variant appears in 3 Lung cases, therefore, it should show 3.

The “#of samples per tumor type” should display how many distinct samples are in that tumor type. There are 16 Lung samples in the database so it should display 16 in that field.

So, the frequency of a particular variant is (#of Variants per Tumor Type/ #of samples per tumor type) is 3/16.

 

[MilaK]

I asked her to upload the database so that I could test stuff. I tested your proposal. The SQL I came up with is:

SELECT tbl_Variants.ID, tbl_Variants.variant_id, tbl_Variants.sample_id, tbl_Samples.tumor_type, qry_variant_freq.exp, qry_variant_freq.aa_change, qry_tumor_type.exp
FROM ((tbl_Variants LEFT JOIN tbl_Samples ON tbl_Variants.sample_id = tbl_Samples.sample_id) LEFT JOIN qry_variant_freq ON tbl_Samples.tumor_type = qry_variant_freq.tumor_type) LEFT JOIN qry_tumor_type ON tbl_Samples.tumor_type = qry_tumor_type.tumor_type;

This produces a lot of records. 2205 for the 108 that are in the tbl_Variants table. This is because the qry_variant_freq produces many records per type, e.g., 12 for Brain, 5 for Breast, 25 for Lung because of the different aa_changes. This may be what the OP wants.

Right, the query should return every single record in Variant table. thanks

 

[MilaK]

@sneuburg
Perhaps your query can be used to lookup both values to display on the form?

I've added this lookup to the query that returns all variants and populates the form but something is wrong with the expression.

Expr1: DLookUp("qry_variant","qry_final_query","[tumor_type]=" & [Me]![tumor_type] And "[aa_change]=" & [Me]![aa_change])

Thanks

 

[plog]

sneuberg is correct about the duplicate records. The reason for this is Query1. I assumed it was grouped on just tumor_type, it is also grouped on aa_change. Apologizes and good catch to sneuberg.

Mila, what should your final query show for ID=792? We know there should be only 1 record, but what data should be in that record?

 

[sneuberg]

@sneuburg
Perhaps your query can be used to lookup both values to display on the form?

I've added this lookup to the query that returns all variants and populates the form but something is wrong with the expression.

Expr1: DLookUp("qry_variant","qry_final_query","[tumor_type]=" & [Me]![tumor_type] And "[aa_change]=" & [Me]![aa_change])

Thanks

I thought you were going to use the final result as a record source for a subform. I don't understand what you want this DLookup to do. What's qry_variant? It doesn't look like a field. Could you explain what you want in the output and how it should look?

 

[MilaK]

please see attached query results for id 792.

Alsi, I'm still confused about how to to display these query results of from. I've tried Dlookup and it failed. Thanks

 

[MilaK]

I thought you were going to use the final result as a record source for a subform. I don't understand what you want this DLookup to do. What's qry_variant? It doesn't look like a field. Could you explain what you want in the output and how it should look?

Yes, but I also need to include other fields from tbl_Variants as well. Please see frm_Variants for clarification.

You are suggesting on using a subform that would be filtered as the user clicks on each record on the main Variant form, correct?

Thanks

 

[plog]

Huh? I thought you wanted 1 record per variant ID?

If those are the results you want, then my method produces them.

 

[sneuberg]

I think it needs to look like:

This produces all the data in the tbl_variants (108 records) and the required result explained in post #9. The addition outer join on aa_change keeps the result down to just one record per tbl_Variant record. You just need to change the record source of the frm_Variants to this query and then in the control sources of the Text50 (#of Variants per Tumor Type) and Text51 (#of sampleids per Tumor Type) pick the applicable expression in the drop down.

I changed qry_all_variants to have all the fields from tbl_variants rather than the wild card (*) so that the fields would show up in design view. The SQL for this is:

SELECT qry_all_variants.*, qry_variant_freq.Exp, qry_tumor_type.Exp
FROM (qry_all_variants LEFT JOIN qry_variant_freq ON (qry_all_variants.aa_change = qry_variant_freq.aa_change) AND (qry_all_variants.tumor_type = qry_variant_freq.tumor_type)) LEFT JOIN qry_tumor_type ON qry_all_variants.tumor_type = qry_tumor_type.tumor_type;

The database with this query (qryFinal) is attached.

 

[MilaK]

Thank you, this is exactly what I was looking for.

 

[MilaK]

I just realized that I will not be able to edit any records on the form with the “final query” as the row source. I will need to make changes to data displayed on the form. Is there anything I can do to more it editable? Thanks and sorry to bother you again. Mila

 

[sneuberg]

I've been away on vacation since last Thursday and don't know if you still need a solution but I'll post this anyway. The way to get around the editing problem is to use subqueries or DLookUps. I prefer DLookups as I believe they are faster and are easier to create. However both of these are real performance killers. If you have a lots of records this may not work for you and you might have to add edit buttons in the form. But try this first and let me know.

In the attached database you will find a query that uses DLookups. It is named qryFinalWithDLookup. It uses qry_variant_freq which is the same as before and qry_tumor_type where I changed the count expression name to TypeCount and qry_variant_freq where I changed the count expression name to VarFreqCount. You should be able to use qryFinalWithDLookup as the record source in your form as I describe for the previous version. Please note that this in this version the fields from qry_all_variants will be updateable but the counts will not be.