Automatic Step through

  • Thread starter Thread starter Jessie
  • Start date Start date
J

Jessie

Hello.

I Have a main table that I need to break into 4 different
tables using one of the fields as criteria.

For example:
I have say 2000 records that have different Group Codes.

I am imagining a way to Run a query that will make a table
that will have a list of all of the group codes then use
some code to use each record in that table as criteria to
break the Group codes into their own tables.

Right now the I have a query that prompts the User for the
group codes because they constanly change I can't write
the criteria into the query, but would like to find a way
to dynamically program the VBA to seperate the main table
into tables that are group code specific.

Hopefully someone can help me. I would be so happy about
it.

Jessie
 
This is how I understand it:
a record in the 'main' table contains a field 'group code'. That group code determines to what table, out of 4, the record is copied
(or moved). Which means that those 4 tables are more or less identical to the main tables, otherwise the fields\data wouldn't match.

Then, the group codes, although constantly changing, result in the record being allocated to 1 of the 4 tables.

My questions:
1) Why 4 separate tables instead of keeping the records in the initial table? Just create an extra field (say bytGroup) and based on
the groupcode you put a value 1 to 4 in that new field. Then you can sort and query on the same table, instead of having 4 different
tables (plus queries plus forms). But maybe you have a good reason for this approach, so let us know.
2) How do you know which groupcode results in what table-number (1 to 4)? Is that known already? Does the user\admin have that list
or is there a formula in the group code that 'calculates' the table number? If you have that list, put it in a separate table and
let Access figure it out. If there is a formula, let Access calculate the outcome and put the value in bytGroup.

So, before you continue, have a look at your design, because maybe this can be solved in a much easier way.


Jacques
www.troisj.com



Hello.

I Have a main table that I need to break into 4 different
tables using one of the fields as criteria.

For example:
I have say 2000 records that have different Group Codes.

I am imagining a way to Run a query that will make a table
that will have a list of all of the group codes then use
some code to use each record in that table as criteria to
break the Group codes into their own tables.

Right now the I have a query that prompts the User for the
group codes because they constanly change I can't write
the criteria into the query, but would like to find a way
to dynamically program the VBA to seperate the main table
into tables that are group code specific.

Hopefully someone can help me. I would be so happy about
it.

Jessie
 
I guess my wording was perhaps a little vague.

I don't really need to make new tables, but I do need to
export the records (Seperated by group code) to text to
send to Client.

The group codes are dynamic and we never know what they
will be until we are actually processing and sending the
records out. We get a pool of records weekly and each pool
will have between 2 and 50,000 records with about 10 group
codes. We do some processing and converting of data in
some different fields. Depending on what is processed
during a day that is what goes back to client. It all
stays in the tblToClient, but we query that table and
export it with the different Group Codes as criteria in
the query. Sometimes we have to run the query 5-6 times.
Editing it between each export to get only the specific
records we need.

The data that goes to Client in text form is identical to
that of the 'main' table, but the actual file is supposed
to be named for the group code.

What I am envisioning is making a query that will contain
one record for every Group code that is different in
tblToClient like:

SELECT tblToClient.GroupCd
FROM tblToClient
GROUP BY tblToClient.GroupCd;

I think I might have to make a table out of it:

SELECT tblToClient.GroupCd AS GroupCD INTO tblGroupCodes
FROM tblToClient
GROUP BY tblToClient.GroupCd;

And then using that Table or query as my recordset and
step through the data using the data from each record in
the GroupCD field as my criteria for the export until it
comes to the end.

Is it even possible??
-----Original Message-----
This is how I understand it:
a record in the 'main' table contains a field 'group
Click to expand...
code'. That group code determines to what table, out of 4,
the record is copied
(or moved). Which means that those 4 tables are more or
Click to expand...
less identical to the main tables, otherwise the
fields\data wouldn't match.
Then, the group codes, although constantly changing,
Click to expand...
result in the record being allocated to 1 of the 4 tables.
My questions:
1) Why 4 separate tables instead of keeping the records
Click to expand...
in the initial table? Just create an extra field (say
bytGroup) and based on
the groupcode you put a value 1 to 4 in that new field.
Click to expand...
Then you can sort and query on the same table, instead of
having 4 different
tables (plus queries plus forms). But maybe you have a
Click to expand...
good reason for this approach, so let us know.
2) How do you know which groupcode results in what table-
Click to expand...
number (1 to 4)? Is that known already? Does the
user\admin have that list
or is there a formula in the group code that 'calculates'
Click to expand...
the table number? If you have that list, put it in a
separate table and
let Access figure it out. If there is a formula, let
Click to expand...
Access calculate the outcome and put the value in bytGroup.
So, before you continue, have a look at your design,
Click to expand...
because maybe this can be solved in a much easier way.
Jacques
www.troisj.com



"Jessie" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
I don't really need to make new tables, but I do need to
export the records (Seperated by group code) to text to
send to Client.
Click to expand...

I'd suggest just a little VBA. I can't think of a good SQL only
solution!

But if you create a Recordset based on a SELECT DISTINCT query
selecting just the codes in the current table, you should be able to
step through that recordset, creating new querydefs which could be
exported one by one using TransferDatabase. AIR CODE, which will
surely need work:

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim qd As DAO.Querydef
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DISTINCT GroupCode FROM yourtable;")
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
strSQL = "SELECT * FROM yourtable WHERE GroupCode = '" _
& rs!GroupCode & "';"
Set qd = db.CreateQuerydef("qExp" & [GroupCode], strSQL)
<code to export this stored Query>
rs.MoveNext
Loop
End If
 
Back
Top