?
Solved

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

Posted on 2008-06-23
9
Medium Priority
?
249 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 30

Expert Comment

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

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 30

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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

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 …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…
Suggested Courses

770 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