SELECTING A TABLE AT RUNTIME 1

[kaniz]

Is it possible to select a table from a database at run time and do a cross tab on the table selected. Help please thank you

 

[MichaelRed]

Your inquiry is a bit lean on the details of the overall process.

In the most general context (selecting an arbitrary table and doing a cross tab on user selected fields), the possability exists, but the cade would be somewhat extensive.

On the other hand, if you have tables with the same structure and always want the same CrossTab results (possibly with some different criteria), then it is reasonably easy.

Just write the query SQL string with the table name as variable, and pass the table name to the routine as a varaible. Generate the SQL string and make that string the .SQL property of the query def.

An alternative would be to write a small module to temporarily ReName your table(s) to a common name (such as "MyXtabSource&quot, execute you crosstab query, and rename the temp table to it's original name. This is not recommended for multiuser saystems, as someone else may need access to the table (under it's original Name) while you are running the crosstab.

A third (and probably more drastic procedure) would be to copy the desired source table to a temp table and execute the crosstab query on the temp table.

There are other variations on the theme which are quite 'doable' - as long as all of the potential sources for your crosstab have the fields of the crosstab in common.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[kaniz]

Thank you for your reply. all the tables have the same struture, same fields,same lenghts same everything, I was hoping to have a combo box to selet the table but do I tell it to do a cross tab on the selected table. Newbie in access

 

[MichaelRed]

kaniz,

Well at least you picked something reasonable to attempt.

If you're a 'newbie', I will try the "easy" way.

Construct your XTab query on any of the tables. I am assuming that you will use the query buildertool. I will, for this discussion, refer to this as "qryXTab". Make sure this runs and returns the propper result for the selected table.

Change the 'view' of the query in the query builder to the SQL view. If you aren't familiar w/ this, there is a "button" on the query menu bar for "VIEW". You will see the SQL text in the query window. It will contain the KEY WORDS as in the below query, but include you table name and Field names.

TRANSFORM Count(tblStuGrade.StuName) AS CountOfStuName
SELECT tblStuGrade.StuSex
FROM tblStuGrade
GROUP BY tblStuGrade.StuSex
PIVOT tblStuGrade.StuGrade;

It should apear "highlighted" or selected, however if it doesn't, the usual Paint (select/edit) and copy work. Copy this to a module.procedure (I will refer ONLY to the procedure as "basComboXTab&quot.

It WILL show up as errors. Just make each line into a comment. Assuming you started with a blank procedure, your code window should look something like:

Public Function basComboXTab()

    'TRANSFORM Count(tblStuGrade.StuName) AS CountOfStuName
    'SELECT tblStuGrade.StuSex
    'FROM tblStuGrade
    'GROUP BY tblStuGrade.StuSex
    'PIVOT tblStuGrade.StuGrade;

End Function

In concept, the only thing we are going to do from here to the end is to replace the table name (in my example"tblStuGrade&quot with the alternative/selected table. and replace this back into the original query. There are a few details along the way.


The below is a reasonably ? commented module which at least does the hard part.

Public Function basComboXTab(strTblName As String)
    'The Argument declaration "strTblName as String" is
    'how we send the table name to this porcedure.


    'These are just the standard declarations
    Dim dbs As Database          'The database
    Dim qdf As QueryDef         'The query we reference/manipulate
    Dim strSQL As String        'The string variable to build/rebuild the query

    'Just standard instantiations of the objects
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("qryXTab")

'_____________________________________________________________________________
    'Actually do the work here.  Note.  The process is very straightforward.
    'Mechanically, I just assigned each of the fragments of the original SQL
    'statement we pasted here (as a comment) to the assignment statement,
    'concatenating the pieces.  Then, I Replaces each occurance of the table
    'name (in my case 'tblStuGrade') with the string passed to the function.

    'One of two points.  since thes individual lines will be concatenated into
    'a single string, you need to be careful to include spaces at the end of
    'most of the lines - BUT NOT the last one.  You need to NOT include
    'extra spaces within the string.

    'TRANSFORM Count(tblStuGrade.StuName) AS CountOfStuName
    strSQL = "TRANSFORM Count(" & strTblName & ".StuName) AS CountOfStuName "

    'SELECT tblStuGrade.StuSex
    strSQL = strSQL & "SELECT " & strTblName & ".StuSex "

    'FROM tblStuGrade
    strSQL = strSQL & "FROM " & strTblName & " "

    'GROUP BY tblStuGrade.StuSex
    strSQL = strSQL & "GROUP BY " & strTblName & ".StuSex "

    'PIVOT tblStuGrade.StuGrade;
    strSQL = strSQL & "PIVOT " & strTblName & ".StuGrade;"

    'This is just for debug purposes.  Put a breakpoint on the second Debug
    'statement and examine the handiwork in the debug Window.
    Debug.Print strSQL
    Debug.Print

    'This is what it is all about.
    qdf.SQL = strSQL

    'Since I do not know the remainder of your process, I will STOP here.
    'You call this function with the name of any of the tables with the
    'same structure for example:
    'basComboXTab("tblStuGrade") would essientially "Do Nothing" as it would
    'simply replace the original code.

    'You should, however, be able to just execute the query from the database
    'window and see the different results sets.

End Function

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[kaniz]

Thank you so much Michael Red, but also forgive for been so slow. I have spent a few days trying to figure it out but it does not work so I guess I am doing something very wrong.

I created a new module with the name

Public Function basComboXTab(strTblName As String)

Dim dbs As Database 'The database
Dim qdf As QueryDef 'The query we reference/manipulate
Dim strSQL As String 'The string variable to build/rebuild the query


Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryXTab&quot

_______________________________________________________
strSQL = "TRANSFORM Count(" & strTblName & ".StuName) AS CountOfStuName "
strSQL = strSQL & "SELECT " & strTblName & ".StuSex "
strSQL = strSQL & "FROM " & strTblName & " "
strSQL = strSQL & "GROUP BY " & strTblName & ".StuSex "
strSQL = strSQL & "PIVOT " & strTblName & ".StuGrade;"

End Function


I also created a query name xtab with one of my tables

I created a form and a put button
under the click procedure I have = bascomboxtab("tablename)

I am not sure what I am doing wrong.

thank you so much I really appreciate your help. I was told that this forum is very good and I can't complaint and learn on this forum than any other.

 

[kaniz]

Hello Michael Red, this is just so great it works finally. It only took me about a week to figure out.(awfull). I would give you lots of stars if they let me but I think I can only give you one. thank you so much