?
Solved

Separating mutliple data in one MS Access table column

Posted on 2012-03-14
7
Medium Priority
?
309 Views
Last Modified: 2012-03-15
I imported an excel spreadsheet into Access and want to know how to separate data from one column into two columns.  The column is [Last Inventory Software Scan] and the data it stores is date in format(mm/dd/yyyy) and time in format(hh:mm am/pm).  The time isn't required and don't know how to separate it or should i separate it within excel before importing?  If best to separate in excel, what are those steps.
0
Comment
Question by:jsawicki
[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
7 Comments
 
LVL 18

Assisted Solution

by:Jerry Miller
Jerry Miller earned 200 total points
ID: 37722736
The easiest way would be a 'Text to Columns' in Excel before importing it. I am sure that there is a delimiter or it is a fixed width. Anything in Access would be much harder.

http://support.microsoft.com/kb/214261
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 400 total points
ID: 37722775
you can import the excel file, and after importing you can separate the date from the time with

datevalue([Last Inventory Software Scan])  

to get the time, use

timevalue([Last Inventory Software Scan])
0
 

Assisted Solution

by:hutchiesit
hutchiesit earned 200 total points
ID: 37722979
Best to do the separation in Excel.
One way would be text to column : On the Data Menu or Ribbon, select Text to Column and follow the instructions.

However, if in excel the data is currently classifed as a Date/time format you could change the format to a simpler date format such as dd/mm/yyyy and excel will change the data for you.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:jsawicki
ID: 37723093
I will try these suggestions tomorrow at work and let you know how it goes.  

Capricorn1:  for your suggestion, i assume this will be written in the query field.

Thanks.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37723096
yes, you are right
0
 

Author Comment

by:jsawicki
ID: 37725098
The date and time values worked perfectly. thanks Capricorn

For the text to columns in excel, for some reason when i used the wizard, it separated the the time into a different column, however, it zero'd out the time in the orginally column so i was left with the data below in the date column.  When the cell was clicked on, it displayed as 2/24/2012 12:00 AM.  Did i miss a critical step within the wizard to remove the data when splitting the cells or is that just what i would have to live with if i choose this path in excel?  Since that is a useful tool, i wanted to see if i am doing something wrong before giving Capricorn sole solution credit.  Thanks all for your time


2/24/2012 0:00      9:39:00 AM
2/29/2012 0:00      5:02:00 AM
2/29/2012 0:00      5:02:00 AM
2/29/2012 0:00      8:23:00 AM
2/24/2012 0:00      9:05:00 AM
0
 

Author Comment

by:jsawicki
ID: 37725228
Nevermind, i got it figured out.  I just wasn't choosing the correct data type.  Thanks all
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

765 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