Query for Splitting Table (1 Viewer)

[angela]

I have a large provider data table with 10,000 rows approximately. I'd like to split the table by the "specialty" column so I have multiple tables based on the speciality column.

Is there a query or SQL so that i Can do this quickly without having to do multiple make table queries?

 

[spikepl]

And the purpose of splitting the table would be what exactly?

Since the data started life in a common table, splitting it just based on the value of one particular field, where you at any time can extract what you want by using a query, seems inadvisable.

 

[angela]

And the purpose of splitting the table would be what exactly?

Since the data started life in a common table, splitting it just based on the value of one particular field, where you at any time can extract what you want by using a query, seems inadvisable.

I need the data split into smaller tables so I can do provider counts:

e.g. 1 table for PCPs, 1 table for OB Gyn

Normally, I'd set up a make table query, and do queries for OB Gyn and PCPs.

I was just wondering if there is a quicker way of doing this, as there are over 100 specialities. Instead of setting up 100 queries to make the tables, is there a quicker way that access will break the table by the specialty column for me ?

 

[spikepl]

So if I understand you correctly, the reason is that you don't know how to count records that fulfil specified criteria?

Because splitting data into small buckets, just to count what is in each bucket, is a novel approach, but not a very useful or efficient one.

 

[DCrake]

Just use select queries for the source data. Or you can use agregate queries for grouping and counting.

 

[angela]

Counting the data is part 1 of the task. I also need separate tables for each specialty. I guess there is not way to do this other than the select queries and make table query for each individual specialty.

 

[spikepl]

You have not answered the question for what purpose you need a separate table, when you can get the very same result (ie a record set) out of a query without setting up separate tables.

 

[DCrake]

Making tables for each specialty would be the LAST thing I would do. What are you wanting to do with the data anyway?

 

[angela]

Making tables for each specialty would be the LAST thing I would do. What are you wanting to do with the data anyway?

I have to run reports based on each specialty, and need to keep them separate. I also need to submit separate tables of each speciality to the customer.

 

[spikepl]

Sorry but your answer still does not provide any justification for splitting the data. One can select data for the reports from a table by using a query, and likewise for data export, which is much faster, and can be fully automated, instead of splitting/creating tables.

So is it because you don't know how to go about it? Or are there any other reasons?

 

[DCrake]

You can create a single report that is grouped by specialty and telling it to start a new page after each specialty.

When you say you have to "separate tables of each speciality to the customer."

How are you presenting the data to them?

 

[angela]

Sorry but your answer still does not provide any justification for splitting the data. One can select data for the reports from a table by using a query, and likewise for data export, which is much faster, and can be fully automated, instead of splitting/creating tables.

So is it because you don't know how to go about it? Or are there any other reasons?

I wouldn't have posted if I knew of a quicker way to do it. I'm sorry I asked.

 

[angela]

You can create a single report that is grouped by specialty and telling it to start a new page after each specialty.

When you say you have to "separate tables of each speciality to the customer."

How are you presenting the data to them?

I need to send them the data in excel. I don't want to make 100 make table queries, and export each data separately into excel. I wanted to know if there was a quicker way of "breaking" or splitting the table into multiple tables by the specialty field.

So I'd have one table for each specialty with the name, address, zip and TIN.

 

[spikepl]

We are trying to determine what you need, and what you have stateted so far contravenes all normal practices. So either there is a requirement that we have not detected, or you don't know how to deal with extracting specified data from a table by using a query. If that is the case, then say so, otherwise we can just keep guessing.

Update:

The purpose of the grilling is to determine what the actual problem to be solved is, not to make you feel "sorry that I posted". Now if it is simply an issue of extracting data using a query, for counting or for export, then that is what we can help you with.