I have two unrelated table (screenshot is attached)
I would like to combine them into a query that can be used as row-source for a continuous form(example is also attached).
I've tried a Union query and Crosstab query but so far my attempts were unsuccessful. Could you please suggest how to achieve what I'm looking for? I hope this is possible to achieve in Access.
I'm sure it's possible but before I suggest something I'd just like to confirm that all you want is a grid with the GeneNames and SampleName; no values like Start or end. Is that right? Also if the result is not editable will that be a problem?
On the form I would like to display what I've attached on the example, however, the query should have "start" and "end" fields available if posible? My goal is to build linkouts using these fields. So when the user click on a specific gene a related webpage will open. The sample_name will also be a part of the hyperlink.
The form will be read-only.
I can attach Access db example with the two table and the form, if this will help?
Yes the db example would help. And if the end goal is for the form to present hyperlinks I would like to know the form of the address so that I make sure my suggestion will support it. I don't need it to be exact. Something like:
Getting to the crosstab query part is fairly easy. In the attached database I added what's called a cross product query which is just the tables with no join. This produces the permutation of the records of both tables which is what you want. I added an expression which is entirely bogus but shows what we need to do and that is to form an expression that represents the URL. I took that query and used it in the query wizard to come up with the cross tab query qryAddress_Crosstab.
The problem is going to be getting it into a form. Access just isn't good at dynamic forms where the number of columns varies. If your user will always have full Access this is less of a problem as the code can just design a form to fit at run time; however, that's not possible with Access run time. I think the typical solution is to add a bunch of controls to the form and then bind and make them visible as needed. Even for this method you need to specify the maximum number of columns (samples in this case) beforehand.
I welcome the challenge but I haven't done this before so it might take me a while to come up with a solution. I'll give you a status on my progress no later than tomorrow night.
Thanks Steve, The only problem is see is how to bind the sample names that will be different each time to controls on the form. The form is bound to specific fields on the query (sample names). Perhaps it's better to change the query so the sample names are in column A and genes go across? The gene names will be static but the sample names will vary.
Thanks Steve, The only problem is see is how to bind the sample names that will be different each time to controls on the form. The form is bound to specific fields on the query (sample names).
I was just working on that. I was going to loop through the field names of the crosstab query (sample names) and try assigning them to a form with generic controls names like T1, T2, T3.
Perhaps it's better to change the query so the sample names are in column A and genes go across? The gene names will be static but the sample names will vary.
If you can live with a form with a fixed number of controls that would really simplify this task. Can you? Or would you rather I pursue a dynamic solution? Are gene names really static? I mean isn't there a possibility that they'll discover a new gene or something?
In any case could you give me an idea of the maximum number of samples and gene names there might be? I'm not sure what they are at the moment but there are limits to how far horizontally we can go.
I've attached a database with a solution that works with limitations. To see it work open the form frm_link_outs and click on a sample name. You will see the URL in a message box. I have the FollowLink commented out as it of course just causes an error on my system. If you want to test the follow link you can remove the uncomment the FollowHyperlink URL in CommonOnClick function which is in the Common Event Procedures module.
I'm not explaining how this work in this post as I want you to consider the limitation this approach has and decide if you still want to use this. The limitation is the maximum width of the form which according to this is 22,75 in. (57.79 cm). I found this to be true. When I tried to add 100 textboxes of the width you see on the form it stopped me at 19. So I set up this form to handle a maximum of 18 samples. This number could be increased if you are willing to reduce the font size and could be increased to over 100 if the vertical property was set to true. Note that this mode reads like the edge of a book on a shelve not like a neon sign.
I've included a form frmGeneSample which does the same thing as this grid except with combo boxes which of course could be listboxes. This was so easy to put together it seems unlikely that you haven't considered this approach but I thought I'd offer it as an alternative anyway. Wouldn't this way be just as easy for your users?
If you still want to go with the grid solution let me know and I'll finish this up with some needed error handling, an explanation of how it works and instructions for you on how to set it up.
I really like the function that you've wrote and the way you call the function from each field click event.
I had it set up as the frmGeneSample earlier but they want a grid so they can click and import multiple samples.
I've modified the cross-tab query so the sample names are in the first column and the genes go across. I can reduce the width of the other fields so they fit on the form.
Each sample has its own link-out that is not associated with these genes; therefore, it has to be set up with sample names in the first column.
Is it OK if I make changes to the code and send it back to you so you can add the additional error-checking? Thanks, Mila
There is code in the Maintenance Program module that helped make the frm_link_outs form. The New Controls subroutine is what I used to add the textboxes. So if you are going to redo the form try to figure this out as it will save you a lot of time.
Bed time now for me. I'll get the explanation on how this works to you tomorrow.
This explanation discusses the solution in the attached database which has change a bit since the last version. Please refer to it for the following discussion.
If you add form references to the queries that are part of the crosstab query the references must be added to the parameters in the queries where they are used.. If you don’t you will get the error “The Microsoft database engine does not recognize xxxx as a valid field name or expression” where xxx will be the form reference. Allen Browne explains this in this page http://allenbrowne.com/ser-67.html". Also since the query is used as a source for a recordset you will get an error about two few parameters unless the references are declared as parameters in the code. This is explained somewhat in the link: https://msdn.microsoft.com/en-us/library/office/aa160564(v=office.11).aspx
Since this is something you might want to do this version is set up with form references and includes the requirements stated in the previous paragraph.. If you open frm_link_outs you will see the textboxes which are in the qrySampleGeneXproduct query as criteria.
The qrySampleGeneXproduct (previous named qryAddress) query forms a cross product of the tbl_region and tbl_sample tables. If you open this in design view and click on the Parameters in the Show/Hide group of the DESIGN tab you will see how the parameters are declared. Please also note how the criteria for gene and sample_name are set up. In the previous version I had an expression named address which I thought I would expand into a URL, but I could find anyway to present a hyperlink in a query as an expression. It seems that a place is needed for the hyperlink to be stored, i.e. a table field. Anyway the third column for the crosstab is just a repeat of the sample_name.
Moving on to the qrySampleGeneXproduct_Crosstab query (previously named qryAddress_Crosstab) this was just created from the qrySampleGeneXproduct using the wizard. The gene was specifield as the row , sample_name as column and the first of the sample_name as the cell.
The frm_link_outs form was the form you upload with the textboxes added with NewControls subroutine found in the Maintenance Program module. To run this you just place your cursor in the subroutine and press F5. This adds MaxColumns textboxes to a form with the names of the textboxes being in the form T0, T1, T2… TMaxColumns. The size and positions of the textbox is specified (Height, Width, Left, Top) and other properties are specified. The one most important to note is the OnClick event property, i.e,
Code:
ctlText.OnClick = "=CommonOnClick([T0],[T" & i & "])"
This on click event runs the function CommonOnClick with the application gene and sample name as arguments. You might also note that some of the properties are set up as though the textbox were a hyperlink. This is just to make it look like a hyperlink and so that the mouse pointer changes to a hand when you hover over them.
Now for how this form gets hooked up with the cross tab query. This is done by the code the follows.
Code:
Dim SourceQuery As String
Dim CrsRs As DAO.Recordset
Dim qdef As DAO.QueryDef
Dim MaxColumns As Long
Dim i As Long
MaxColumns = 18
SourceQuery = "qrySampleGeneXproduct_Crosstab"
Set qdef = CurrentDb.QueryDefs(SourceQuery)
qdef.Parameters("[forms]![frm_link_outs]![SampleFilter]") = [Forms]![frm_link_outs]![SampleFilter]
qdef.Parameters("[Forms]![frm_link_outs]![GeneFilter]") = [Forms]![frm_link_outs]![GeneFilter]
Me.RecordSource = SourceQuery
Set CrsRs = qdef.OpenRecordset
If CrsRs.Fields.Count - 1 > MaxColumns Then
MsgBox "There are too many samples to display"
DoCmd.Close acForm, "frm_link_outs"
Exit Sub
End If
For i = 0 To CrsRs.Fields.Count - 1
Me("T" & i).ControlSource = CrsRs.Fields(i).Name
Me("T" & i).Visible = True
Next i
For i = i To MaxColumns
Me("T" & i).Visible = False
Next i
CrsRs.Close
This code opens the cross tab query as a record set and for each field in the crosstab query assigns a textbox to it and makes it visible. It then hides the unused textboxes. It also assigns the query as the record source of the form. The code is run when the form is loaded and when the form is filtered. If the number of sample names in the crosstab query exceeds the number of available textboxes the user gets a message box stating this and the form is closed.
With the form loaded the On Click events of the textboxes with the samples_names call the CommonOnClick function located in the Common Event Procedures module. The code for this functions is:
Code:
Public Function CommonOnClick(gene As Variant, sample_name As Variant)
On Error GoTo CommonOnClick_Err
'Application.FollowHyperlink "http://localhost:60151/load?file=file:///L:/" & run_name & "/bam/" & sample_name & "_region_" & gene & ".bam&goto&locus=" & chr & ":" & start & "-" & runend
Dim run_name As Variant
Dim URL As String
Dim RegionRs As DAO.Recordset
Set RegionRs = CurrentDb.OpenRecordset("SELECT * FROM tbl_regions WHERE gene = '" & gene & "'")
If RegionRs.EOF And RegionRs.BOF Then Exit Function 'record not found
run_name = DLookup("[run_name]", "[tbl_samples]", "[sample_name] = '" & sample_name & "'")
If IsNull(run_name) Then Exit Function
URL = "http://localhost:60151/load?file=file:///L:/" & run_name & "/bam/" & sample_name & "_region_" & gene & ".bam&goto&locus=" & RegionRs!chr & ":" & RegionRs!start & "-" & RegionRs!End
MsgBox URL
'FollowHyperlink URL
RegionRs.Close
CommonOnClick_Exit:
Exit Function
CommonOnClick_Err:
MsgBox Error$
Resume CommonOnClick_Exit
End Function
The gene and sample_name values are passed to this function from the form. The main purpose of this function is to create a URL out of the applicable data. Part of that data is the gene and sample_name but other data must be retrieved based on them. Since only the run_name is required from the tbl_samples table a DLookup is used to retrieve it. But since chr, start, and End are needed from the tbl_regions table a recordset is opened on that table. This data is then concatenated into the URL. If no records are found in either case the function is just exited. You may want to handle this differently. A generic error handler has been add to this code which will at least keep the code from going into debug if the FollowLink fails.
