asked on

MSAccess: Create a new table based on values in field

Dear all,

I'm not sure how to automatically create a new table with all the fields of the main table for each country, i.e. Australia, Canada. I can easily create a query for each country ( select ** into ** where **) but I want to avoid going through this process manually.

I have a main table, see below.

RecordID Country Value
1 Australia 1
2 USA 2
3 Australia 3
4 Canada 2

The query should create three new tables.
RecordID Country Value
1 Australia 1
3 Australia 3

RecordID Country Value
2 USA 2

RecordID Country Value
4 Canada 2

Thanks

ste5an

Do you store the country name in your Country column? Create a country table and store only a reference in your table. It's called domain key normal form (DKNF)

You need three Make Table queries. Each having a different condition on Country.

But: Why don't you use simply three queries for that?
And: This kind of design is under most circumstances not necessary. Maybe you can give us more information about your context.

Scott McDaniel (EE MVE )

Agree with ste5an that you don't normally break out tables in this manner. About the only time would be when you want GroupA to ONLY have access to CountryA, and GroupB to ONLY have access to CountryB, and the only way to guarantee that is to give them country-specific data. If you don't really care if GroupB can see data from CountryA, then it make little sense to break out data in that fashion, and can cause issues down the road.

AndyC1000

ASKER

Thanks for your responses. I should of written the context. Its a data processing task, the above example was a sample. Once these tables are created I was going to look at creating a VB script to export the tables automatically.

ste5an

Depending in the export mechanisms you can use queries like

SELECT * FROM tableName WHERE countryCode = 'CA';

Open in new window

directly. So maybe you don't even need to create a table. Otherwise use VBA to create views. See CreateQueryDef.

Sub CreateQueryDefX() 

  Dim dbsNorthwind As Database 
  Dim qdfNew As QueryDef 

  Set dbsNorthwind = OpenDatabase("Northwind.mdb")     
  Set qdfNew = dbsNorthwind.CreateQueryDef("NewQueryDef", "SELECT * FROM tableName WHERE countryCode = 'CA'") 
  dbsNorthwind.Close 

End Sub

Open in new window

AndyC1000

ASKER

Is there any way of creating the query or using CreateQueryDef without specifying the countryCode. i.e. query a list of distinct countries then a script to populate the where clause with the countryCode from in the list? I'm trying to avoid going through the dataset and manually creating queries to export the data for each country.

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial