Solved

Excel to Access split data into tables

Posted on 2013-01-04
3
307 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 93

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

623 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