[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Im converting a massive Excel spreadsheet using joins instead of Lookups..

Posted on 2008-06-23
9
Medium Priority
?
256 Views
Last Modified: 2010-04-21
Is there anything I should be very careful of ?
0
Comment
Question by:7601105166084
[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
  • 4
  • 3
  • 2
9 Comments
 
LVL 31

Expert Comment

by:hnasr
ID: 21848021
Why not?
0
 
LVL 31

Expert Comment

by:hnasr
ID: 21848041
I suggest that you start with a small spreadsheed that can be translated into 2 or 3 tables in access, get the idea, then take off with your project.

0
 

Author Comment

by:7601105166084
ID: 21858928
Well what about the strict data types and Ive only read about the way access imports the data - sometimes assuming the type etc ..
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 31

Expert Comment

by:hnasr
ID: 21859611
If you have a worksheet with columns
EmployeeID         EmployeeName
10                               xyz
20                               lmno

Access creates a table, you name it with fields EmployeeID of type Number, EmployeeName of type Text.
That is why I suggested to start with a small spreadsheet.
And if it is a one time job, then it can be done manually!
0
 

Author Comment

by:7601105166084
ID: 21872849
On attempting another possible solution i came accross this :

Does this ever apply to importing spreadsheets?

http://support.microsoft.com/kb/815277
0
 
LVL 11

Accepted Solution

by:
SeanStrickland earned 1500 total points
ID: 21914984
When you import an excel spreadsheet into Access, you have the ability to select the data type that you want.  When you link a table to a spreadsheet, you can do the same.  That should solve your issue with data types, but if you have problems you can always convert the data over by changing the property on your table in design view (say from Text to Numeric because the Excel values were saved as text, but are numbers or vice versa, or maybe you just clicked the wrong data type when building the import -- for Linked tables it's easier to re-link the table and use the wizard again).

When you say converting, I'm assuming this is a one-time job.  In that case, I would import the sheets on the spreadsheet (or the tables on the spreadsheet, however you have it designed) into separate tables in the access database then manipulate the data however you would like.

If you use DLookups in a query or form, note that they will run slower than a join.  A join being where you relate two tables of data that have unique (but related) information together so you can store values in separate tables.


You should not have any issues, just remember to backup your excel spreadsheets in case you DO run into something, and always keep backups of your access databases (if that's where you plan to keep the data in the future) in case the database becomes corrupted and you need to restore it.  If you run into any issues while converting the data, I would post a separate question asking for help and noting the specific issues you're having.

Sean Strickland
0
 

Author Comment

by:7601105166084
ID: 21925839
Hi Sean can you guide me through where i can set the datatype for linked tables?
and also if i try import a spreadsheet  the datatype is ghosted out?

I will try the join again but i hope this time i dont run into any duplicates - otherwise il post another question.
0
 
LVL 11

Expert Comment

by:SeanStrickland
ID: 21952321
When you first link a table, it will bring you to a screen that prompts you for field names (if first row does not contain row headers, the fields will default to Field0, Field1, Field2, etc).  At this screen, you can change the name of your column, change the datatype, and move to the next column.

That's the only way that I know to do it.  Hope it helps.

Sean Strickland
0
 

Author Closing Comment

by:7601105166084
ID: 31469826
If it aint broke - dont fix it - --Im leaving it in Excel and will use VBA there - Problem solved - Thanks anyway
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

649 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