How do I split one large table into multiple tables based on field value?

[jimpaige]

Thought I would provide a better explanation of what I need to do:

I have one table with all company invoice records for a month, each record
has a field [REP].
We have 20 different sales reps [REP] and that will vary from
month to month.

I want to generate a separate table for each [REP], each containing all the
records with the applicable [REP]

I do not want to type in each of the [REP] codes to run 20+ different
queries.

I know Access reasonably well, but not SQL or VBA. But I can learn enough to
do this if needed.

 

[Amy Blankenship]

Thought I would provide a better explanation of what I need to do:

I have one table with all company invoice records for a month, each record
has a field [REP].
We have 20 different sales reps [REP] and that will vary
from
month to month.

I want to generate a separate table for each [REP], each containing all
the
records with the applicable [REP]

I do not want to type in each of the [REP] codes to run 20+ different
queries.

I know Access reasonably well, but not SQL or VBA. But I can learn enough
to
do this if needed.

What is the ultimate goal of splitting out the 20 different parts of the
data? Are you trying to create a report, or what?

 

[jimpaige]

Thought I would provide a better explanation of what I need to do:

[quoted text clipped - 14 lines]

to
do this if needed.

What is the ultimate goal of splitting out the 20 different parts of the
data? Are you trying to create a report, or what?

To prepare a separate report for each file, and to email that report as a
snapshot file (so the recipent only receives a listing of their invoice
records).

 

[John W. Vinson]

To prepare a separate report for each file, and to email that report as a
snapshot file (so the recipent only receives a listing of their invoice
records).

If you're assuming that you must have a separate Table for each report, that
assumption *IS WRONG*.

It's possible - perfectly standard in fact - to create a report based on a
Select Query. That report can be output to a snapshot; then you change the
parameters of the query, rerun the report for a different recipient, and
generate *that* report.

In short... it is neither necessary nor appropriate to make separate tables.

John W. Vinson [MVP]

 

[Amy Blankenship]

Thought I would provide a better explanation of what I need to do:

[quoted text clipped - 14 lines]

to
do this if needed.

What is the ultimate goal of splitting out the 20 different parts of the
data? Are you trying to create a report, or what?

To prepare a separate report for each file, and to email that report as a
snapshot file (so the recipent only receives a listing of their invoice
records).

You're going to have to enter the rep code in a query to do a make table
query anyway, and then you've got all these extra useless tables. If you
write a parameter query (put [Enter Rep] on the where line under the Rep
column), then that makes the process fairly painless. If you want, you can
also do a query by form, and use a combobox to select the rep you want.

HTH;

Amy

 

[jimpaige via AccessMonster.com]

[quoted text clipped - 8 lines]

snapshot file (so the recipent only receives a listing of their invoice
records).

You're going to have to enter the rep code in a query to do a make table
query anyway, and then you've got all these extra useless tables. If you
write a parameter query (put [Enter Rep] on the where line under the Rep
column), then that makes the process fairly painless. If you want, you can
also do a query by form, and use a combobox to select the rep you want.

HTH;

Amy

I think I am doing what you recommend. I have a macro that runs a select
query and emails the snapshot report.

BUT I have to run the macro 20 times and input 20 different [REP] codes to
get the 20 separate reports.

I thought a comobox would only produce 1 query with 1 or more criteria (each
selction in combo box being 1 criterion). So I would still have to run it
20 times to get 20 separate reports. Am I wrong about the use of comobox?

How can I run the 20 different queries from one command, without having to
input each [REP] code.
(as for selecting into smaller tables, I thought it might be easier to do
this in 2 steps: 1) select off into tables by [REP] and 2) then run
another macro that runs the reports plus emails a snapshot)

I have a table I use in my query that has 1 record for each [REP] with other
fields I use [RepName]; [RepEmail], [RepPhone]-- in addtion ot the table fo
all company invocies that also has a [REP] field.

 

[Amy Blankenship]

Thought I would provide a better explanation of what I need to do:

[quoted text clipped - 8 lines]

snapshot file (so the recipent only receives a listing of their invoice
records).

You're going to have to enter the rep code in a query to do a make table
query anyway, and then you've got all these extra useless tables. If you
write a parameter query (put [Enter Rep] on the where line under the Rep
column), then that makes the process fairly painless. If you want, you
can
also do a query by form, and use a combobox to select the rep you want.

HTH;

Amy

I think I am doing what you recommend. I have a macro that runs a select
query and emails the snapshot report.

BUT I have to run the macro 20 times and input 20 different [REP] codes to
get the 20 separate reports.

I thought a comobox would only produce 1 query with 1 or more criteria
(each
selction in combo box being 1 criterion). So I would still have to run
it
20 times to get 20 separate reports. Am I wrong about the use of
comobox?

You could probably automate the process. But since you find entering a
parameter too onerous, then I figured actual coding might well try your
patience. Plus you posted this in microsoft.public.access.queries and not
microsoft.public.access.modulescoding, so that did color my answer.

How can I run the 20 different queries from one command, without having to
input each [REP] code.

You'd need to write a Sub or function. The specific code involved would
depend on exactly what you want to have happen.

(as for selecting into smaller tables, I thought it might be easier to
do
this in 2 steps: 1) select off into tables by [REP] and 2) then run
another macro that runs the reports plus emails a snapshot)

And when you select off into tables, that would require you to enter which
rep you want. So you're adding a step here, and not getting anything extra
but a lot of tables that will bloat your database. I fail to see why you
think that is more efficient.

I have a table I use in my query that has 1 record for each [REP] with
other
fields I use [RepName]; [RepEmail], [RepPhone]-- in addtion ot the table
fo
all company invocies that also has a [REP] field.

Your query should be able to join the two, if that is the issue.

HTH;

Amy