We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
2 days, 20 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Excel to Access split data into tables
Posted on 2013-01-04
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
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
[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
Active today
Running these two queries ought to do it:
SELECT [Badge No] AS BadgeNo, ID AS EmpID
INTO tblBadgeNumbers
FROM EMPLOYEE
SELECT Zone, ID AS EmpID
INTO tblZones
FROM EMPLOYEE
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!
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!
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| Outlook VBA question - How do I mark a message as read | 2 | 60 | |
| Access Form - Button Format Issue | 13 | 51 | |
| MS Access Duplicate Data Assistance | 9 | 32 | |
| Access VBA for Search Engine | 7 | 15 |
AutoNumbers should increment automatically, without duplicates. But sometimes something goes wrong, and the next AutoNumber value is a duplicate. This article shows how to recover from this problem.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses
Course of the Month2 days, 20 hours left to enroll
752 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.