Create tables based on values in a field

[joe789]

Hi Folks,

Something I have been working on, and can't seem to figure out (if it is even possible). Any suggestions would be greatly appreciated.

I have a huge table that I am working with (about 10 fields, 700,000 records). The table is stored on SQL Server and I have created a view for the table via Access 2000. One of the fields in the table can have any of 77 different text values.

Is it possible to somehow create a query or macro so that a unique table is created for each one of the 77 different text values. For example, the table contains 10 fields and field number 1 can be anyone of 77 different text values for each record. I would like to create seperate tables with each table just showing all records for one of the possible 77 different values in field number 1.

I know I can use the create table action query to do this, but with this much data it is not only very slow but I would have to do it 77 times.

Thank you and any help is appreciated,

Thanks,

Joe

 

[Pat Hartman]

Having 77 separate tables is going to cause other problems and is not a good idea.

If you want data with only a specific value in a particular field, use a parameter query. Since your table is SQL server, only the selected rows will be returned to your db. Further criteria in the query would be even better. You really want to limit the number of rows returned as much as possible.

 

[The_Doc_Man]

Agree with PAT. Having 77 tables that split things previously part of a single table is DE-normalizing the data.

I would suggest 77 pre-defined queries with identical code but 77 different values in the CRITERIA field.

As to generating this, I could do it through some VBA code, but the question would be how to generate the 77 names in a meaningful way. If there are any special characters in the 77 possible values then you might well be out of luck.

If you are lucky and the 77 values are all something simple - like text sequences of 2 or 3 alphameric characters - you can do this in code.

Basically, if I HAD to do this - which I probably wouldn't because of other issues I'll mention - I would write some VBA code that does the following:

1. Open a recordset to the table that lists the 77 possible keys.

2. In a loop through that recordset, get the key value.

3. Generate a query name through concatenation of the key value and some unlikely name like "SHOWMEALL" & Trim$(stKeyVal)

4. Generate the query string through concatenation of the rest of the query and an appropriate WHERE clause, like

stQry = "SELECT * FROM [tblMyBigTable] WHERE [KeyField] = """ & Trim$(stKeyVal) & """"

5. Define a querydef variable. Set your querydef variable to

stQryDefVar = CurrentDB.CreateQueryDef( stName, stQry )

Don't forget that you should, as a matter of good practice, reset the query def variable back to nothing inside the loop. See the MS Access example on the CreateQueryDef method.

 

[Pat Hartman]

I have to disagree with Doc. I would NOT create 77 queries. I would create ONE. Simply use a parameter to select which of the 77 possible values you want returned. You can make a small form with an unbound combobox to present the 77 possibilities. Then use a button on the form to open a form or report using the where argument to filter the data to be displayed.

 

[The_Doc_Man]

Pat, you and I are actually not in disagreement. I wouldn't do it either. My answer was of the "If you've got to do this, then..." category. For those dead set on taking an awkward approach.

And I apologize that I forgot to address the biggest issue I see with this: CLUTTER.

When you have 77 views, after a while you face a confusion factor that you get overwhelmed by the apparently endless sea of queries. We have this in a database in my office right now. I have tried to reduce the clutter, but everyone wants just a little bit of difference between their query and anyone else's query. But they don't want to learn how to do that themselves. Finding the one query that contains exactly what you want is as frustrating a setup as that larger table that has everything in it all jumbled together.

I wouldn't do this as a parameter query, either. I would make it a parent/child table setup. Make a table (perhaps by a make-table query based on a find-unique query) that lists the possible values of this key field. Perhaps make it the ONLY control on the parent form. Make a combo-box setup to query that list.

Make a sub-form that shows all the the information in the big table, but that has a parent/child link on this key field. Then when you change the contents of the combo box, automatically do a requery in the {fielc}_Change event routine. Something along those lines, perhaps.