Solved

Excel to Access split data into tables

Posted on 2013-01-04
3
300 Views
Last Modified: 2013-01-06
Experts,
 I recently imported an Excel spreadsheet into an Access DB table. My situation is this, I have some data columns in the table that I need to I need to separate into two other tables. I attached a picture of the table with dummy data to give you an idea. The columns Badge No and Zone need to be placed in their own tables, but make sure that badge no and the zone refers back to the correct employee in the employee table. I plan to use the both badge no and zone as primary keys. Is there any way of accomplishing this using queries??

Many thanks in advance!
sample-data.png
0
Comment
Question by:Ozxar
  • 2
3 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 38746078
Running these two queries ought to do it:

SELECT [Badge No] AS BadgeNo, ID AS EmpID
INTO tblBadgeNumbers
FROM EMPLOYEE

Open in new window


SELECT Zone, ID AS EmpID
INTO tblZones
FROM EMPLOYEE

Open in new window

0
 

Author Comment

by:Ozxar
ID: 38746106
Thanks! I will give it a try
0
 

Author Comment

by:Ozxar
ID: 38747647
matthewspatrick,

I ran the code and it worked fine, but I guess I worded my initial question wrong. I meant to have which zone the badge no belongs to. One badge no can only belong to one zone....but a zone can have many badge no. Could I use the same code to do this? Also, would setting up the relationships be done the same way?

Many thanks!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now