Creating New Tables based on unique values in Master Table

[Trevor11]

I have a table something like this:

ID1 ID2
1 1
1 2
1 3
2 5
2 6
2 7
3 10
3 11
3 12

I would like to create new tables using ONE query based on each unique occurance of Id1, naming each table with the detail from Id1. So that I end up with something like this:

1 (table)
Id1 Id2
1 1
1 2
1 3

2 (table)
Id1 Id2
2 5
2 6
2 6

3 (table)
Id1 Id2
3 10
3 11
3 12

Any help with this would be greatly appreciated.

 

[dhookom]

One query can't create multiple new tables.

Is there a reason why you want/need to break normalization rules?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Trevor11]

I need to Create seperate tables for each Unique occurance of Id1. These tables are then exported to .csv files for seperate loads into an CRM tool which only allows importing against specific Campaigns (Id1).

 

[fredericofonseca]

use VBA code for that then.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[PHV]

Why not exporting a query instead of the table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Trevor11]

I finally got this figured out, Thanks for all your pointers. It really helps

Function Populate_Individual_Camp()

DoCmd.SetWarnings False
Dim dbsCurrent As Database
Dim rstCampMaster As Recordset
Dim strCampID As String
Dim strCampID2 As String
Dim strSQL As String
Dim strCampTblName As String

Set dbsCurrent = CurrentDb()
strSQL = "select distinct [ID1] into tblDistinctCamp from [Table1] "
DoCmd.RunSQL strSQL

Set rstCampMaster = dbsCurrent.OpenRecordset("tblDistinctCamp", dbOpenDynaset)
Do While Not rstCampMaster.EOF
strCampID = rstCampMaster("ID1")
strCampID2 = Replace(strCampID, ".", "")
'rstCampMaster("ID1")
strCampTblName = "[Multiple Table Number " + strCampID2 + "]"
strSQL = "select * into " + strCampTblName + " from [Table1] where [Campaign Name] = '" + strCampID + "' "
DoCmd.RunSQL strSQL
If Not rstCampMaster.EOF Then
rstCampMaster.MoveNext
End If
Loop

Set rstCampMaster = Nothing
dbsCurrent.Close
DoCmd.SetWarnings True
End Function