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
AndyC1000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
AndyC1000Author Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ste5anSenior DeveloperCommented:
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

0
AndyC1000Author Commented:
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.
0
ste5anSenior DeveloperCommented:
E.g.

Sub CreateQueryDefX(ACountryCode As String) 

  Dim dbsNorthwind As Database 
  Dim qdfNew As QueryDef 

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

End Sub 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.