• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

Excel to Access split data into tables

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
Ozxar
Asked:
Ozxar
  • 2
1 Solution
 
Patrick MatthewsCommented:
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
 
OzxarAuthor Commented:
Thanks! I will give it a try
0
 
OzxarAuthor Commented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now