By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 852 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

Need help with splitting up a table into separate tables

P: 3
I have an access table that has around 4000 records showing various carrier names, origin and destination city, state and zip along distance and cost etc.

Is there a way to automate a process that will break up the table into multiple tables based on carrier names? For example, each separate table will only contain data for one particular carrier.

Please help me with this. I am not very savvy with Access but need to finish up the project very soon.

Thanks for your time in advance.
May 18 '07 #1
Share this Question
Share on Google+
7 Replies


LacrosseB0ss
100+
P: 113
I'm not entirely sure why you'd need to do this. Access is designed to allow for massive amounts of data and I've seen worse.

But nevertheless, I would say the easiest way is using queries. If you know the names of the carriers you can go something like this:
"SELECT * INTO {newTableName} FROM {table} WHERE CarrierName = "{name}";" and this will make you a new table with information on one specific carrier. Repeat this for each carrier by changing the {newTableName} and {name} parts and before you can say "stat holiday" you'll be done.

Hope this helps
-LB


I have an access table that has around 4000 records showing various carrier names, origin and destination city, state and zip along distance and cost etc.

Is there a way to automate a process that will break up the table into multiple tables based on carrier names? For example, each separate table will only contain data for one particular carrier.

Please help me with this. I am not very savvy with Access but need to finish up the project very soon.

Thanks for your time in advance.
May 18 '07 #2

P: 3
I'm not entirely sure why you'd need to do this. Access is designed to allow for massive amounts of data and I've seen worse.

But nevertheless, I would say the easiest way is using queries. If you know the names of the carriers you can go something like this:
"SELECT * INTO {newTableName} FROM {table} WHERE CarrierName = "{name}";" and this will make you a new table with information on one specific carrier. Repeat this for each carrier by changing the {newTableName} and {name} parts and before you can say "stat holiday" you'll be done.

Hope this helps
-LB

Thanks a ton LB.

Actually tthe reason I am trying to split the tables by carrier name is to send the different tables to the corresponding carriers. If I do that manually cut and paste in excel, I have to do that more than 100 times as there are more than 100 different carriers in the main table.

I will try ur instruction and will et you know how did it go.

Thanks again.
May 18 '07 #3

LacrosseB0ss
100+
P: 113
if your programmably advanced you could write a macro to search through the (sorted) data and when it hits a new carrier do this. I'm guessing the program would take some 5 minutes to run.

I will also add, to make your life even easier you can use Access's built in "Export" feature. When you have your tables made select the one you want to export and click File->Export. In the popup window that appears change the bottom drop down to "Excel {format}" and give it a file name ("Carrier_{name}" for example). Then hit go and you'll get your spreadsheet for the requested table. . Easy as pie!

-LB
May 18 '07 #4

P: 3
Hi LB,

I guess you guys might just laugh at me cause I really do not know any further than creating tables in Access. I am not into IT field and just need to create this automation.

I tried to work on your instructions, but most probably I was not doing it right, so it did not work. Nor do I have any clue where to search for errors.

Will it be asking for too much if I request you to write the basic Commands so that I will just copy and paste it in the query. Beyond that I dont have any idea to create this by myself.

Thanks again for your help.
May 18 '07 #5

maxamis4
Expert 100+
P: 295
Since your a beginner I will suggest the simplest way but the wrong way to do this. Create a query for each carrier you want to use. In the criteria box put the carrier you want to query by. This will return only the values for that carrier. Then if you need to send them a spreadsheet right click on the query select export. Then in the drop down box scroll down to MS excel 97-2003 and click next and export it to a location where you can find it. This is the easiest way to do what you are asking. It is wrong in the logic that there are more efficent ways and time saving, but this requires programming to do it. good luck

goto these links to help you get started. I know how frustrating it can be to learn something: http://www.mvps.org/access/
http://www.bcschools.net/staff/AccessHelp.htm
Thanks a ton LB.

Actually tthe reason I am trying to split the tables by carrier name is to send the different tables to the corresponding carriers. If I do that manually cut and paste in excel, I have to do that more than 100 times as there are more than 100 different carriers in the main table.

I will try ur instruction and will et you know how did it go.

Thanks again.
May 18 '07 #6

LacrosseB0ss
100+
P: 113
well said Maximus. Yeah, I'm a professional programmer and I use Access 8 hours a day. Sometimes I forget what the learning process was like way back when. Your SQL queries will look something like this:

SELECT *
FROM table INTO newTable
WHERE CarrierName = "name";

Replace table with the name of your 4000 record table. Replace newTable with the name of the table you would like the records to go. And finally, replace name with the Carrier name you are pulling at that given time.

Let's say the Carrier's name is Bob Jones Inc. And you master table is named Carriers. And you want to pull all of Bob's records into their own table and name it BobJonesCarrierRecords. Your query will look like this:

SELECT *
FROM Carriers INTO BobJonesCarrierRecords
WHERE CarrierName = "Bob Jones Inc.";

Sorry I got all technical. Hopefully this simplifies things a bit.


Since your a beginner I will suggest the simplest way but the wrong way to do this. Create a query for each carrier you want to use. In the criteria box put the carrier you want to query by. This will return only the values for that carrier. Then if you need to send them a spreadsheet right click on the query select export. Then in the drop down box scroll down to MS excel 97-2003 and click next and export it to a location where you can find it. This is the easiest way to do what you are asking. It is wrong in the logic that there are more efficent ways and time saving, but this requires programming to do it. good luck

goto these links to help you get started. I know how frustrating it can be to learn something: http://www.mvps.org/access/
http://www.bcschools.net/staff/AccessHelp.htm
May 18 '07 #7

P: 1
Use this below coding to convert table into seperate tables with maximum records you can give manually.

[z<Mod-redacted>]

Expand|Select|Wrap|Line Numbers
  1. Public Split() 
  2.  
  3. Dim i, j, k, c As Integer
  4. Dim tn, nn As String
  5. Dim r1, r2, r3 As Recordset
  6. Dim tc As Integer
  7. Dim a1 As TableDef
  8.  
  9. c = InputBox("Maximum Records Allowed")
  10. tn = InputBox("Table Name")
  11. Set r1 = CurrentDb.OpenRecordset(tn)
  12. r1.MoveFirst
  13. tc = 0
  14. DoCmd.RunSQL "SELECT * INTO t FROM " & tn & " WHERE 1=2"
  15. Set r2 = CurrentDb.OpenRecordset("t")
  16. Do
  17. j = 1
  18. Do
  19. r2.AddNew
  20. For k = 0 To r2.Fields.Count - 1
  21. r2.Fields(k).Value = r1.Fields(k).Value
  22. Next k
  23. r2.Update
  24. r1.MoveNext
  25. j = j + 1
  26. Loop While r1.EOF = False And j <= c
  27. tc = tc + 1
  28. nn = tn & tc
  29. DoCmd.RunSQL "Select * into " & nn & " FROM t"
  30. DoCmd.RunSQL "Delete t.* from t"
  31. Loop While r1.EOF = False
  32. r2.Close
  33. DoCmd.RunSQL "Drop table t"
  34. MsgBox tc & " Tables created successfully"
  35.  
  36. end sub
Thanks & Regards,
M. Hariharan
Nov 17 '13 #8

Post your reply

Sign in to post your reply or Sign up for a free account.