Seperating 450 records per group from a table into new tables

[Hoardling1 via AccessMonster.com]

Access 2003
I have a table with over 4,500 records. What I need to know is a query or
VBA that will seperate the record group into smaller tables with 450 records
per table and a label for the new table. Any ideas?

 

[Jeff Boyce]

While this is (theoretically) possible, why?

As in "why have 10 identically-structured tables, each with 1/10th of the
original table's records"?

This is not what Access is designed to do, and both you and Access will have
to work overtime and come up with kludges to make this work.

If you'll describe more specifically "what" you are trying to accomplish,
rather than "how", folks here may be able to offer more specific
suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dale_Fye via AccessMonster.com]

Cannot imagine why you would want to do this!

Can you provide some explaination as to why you would want to do this?

Can you provide some details about the structure of the table? Do you have an
Autonumber field, or some other field you could use to sort by?

Dale

 

[Bob Barrows]

Access 2003
I have a table with over 4,500 records. What I need to know is a
query or VBA that will seperate the record group into smaller tables
with 450 records per table and a label for the new table. Any ideas?

I'm with the others expressing bafflement at this request. Why not just
create 10 views (saved queries) that retrieve the relevant records and
use them the way you wish to use the tables?

As to how to do this, a make-table query will do it if you can specify
how to identify the records for each table.

 

[Hoardling1 via AccessMonster.com]

The reason I am trying to do this is that I am emailing to all the accounts
in the table. The problem is that email will only handle 500 at a time. So I
have 1 large table which I need to seperate into smaller sections. I
currently seperate the records individually and place in a table, time
consuming. Next, I have a query that looks at one of the smaller tables.
There is a form for me to review what the query looks like then I can create
an email, subject and body, and mail this set. Then I have to repoint the
query to the next group of 450, I repeat this action. Now, what I am trying
to do is automate this as much as possible. So I was looking for a way to
break the larger table down automatically.

 

[John Spencer]

If you have a primary key field you could use a ranking query to assign
a rank number and then use that to grab groups of records

SELECT A.Name, A.Street, A.City, A.State, A.ZIP
FROM YourTable As A LEFT JOIN YourTable as B
ON A.PrimaryKey > B.PrimaryKey
GROUP BY A.Name, A.Street, A.City, A.State, A.ZIP
Having Count(B.PkPrimaryKey) Between 0 and 450

If the primary key is a number field then you could do a little math to
break things into smaller groups.

SELECT *
FROM YourTable
WHERE PrimaryKey Mod 10 = 0

If you were worried about exceeding the limit you could break into
smaller groups using a larger number then 10. Perhaps 15 or even 20.

Or just break things down using the alphabet

SELECT *
FROM YourTable
WHERE Yourtable.CustomerName Like "[ab]*"

That would get all names starting with a or b. You would just need to
figure out the breakdown.

OR create a table with two fields - a text field with letters a to z and
a second field with numbers 1 to N where you break the addresses down
into groups. Then use the table and create a join between it and the
names - a non-equi join

SELECT YourTable.*
FROM YourTable INNER JOIN LetterGroups
On YourTable.CustomerName Like LetterGroups.Letters & "*"
WHERE LetterGroups.iGroupNumber = 1

You could even use two letters to break down your table into groups.
AA 1
AB 1
AC 1
AD 2
AE 2
etc.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Dale_Fye via AccessMonster.com]

Or you could create a single query against your accounts table, loop through
it and build the string of members for the To (or more appropriately the BCC)
parameter. When you reach 450 or whatever number you want in each email,
then Use SendObject to send your email. Then, continue through the recordset,
counting from 1 until you reach 450 again, SendObject, repeat.

HTH
Dale

The reason I am trying to do this is that I am emailing to all the accounts
in the table. The problem is that email will only handle 500 at a time. So I
have 1 large table which I need to seperate into smaller sections. I
currently seperate the records individually and place in a table, time
consuming. Next, I have a query that looks at one of the smaller tables.
There is a form for me to review what the query looks like then I can create
an email, subject and body, and mail this set. Then I have to repoint the
query to the next group of 450, I repeat this action. Now, what I am trying
to do is automate this as much as possible. So I was looking for a way to
break the larger table down automatically.

While this is (theoretically) possible, why?

[quoted text clipped - 18 lines]

 

[Jeff Boyce]

As John & Dale have pointed out, it isn't necessary to "move" any records.

Instead, use a query to 'get' as many as your email routine needs to
handle... then use the query to get the 'next' batch.

Regards

Jeff Boyce
Microsoft Office/Access MVP