Solved

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

Posted on 2008-06-23
9
239 Views
Last Modified: 2010-04-21
Is there anything I should be very careful of ?
0
Comment
Question by:7601105166084
  • 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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 500 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

733 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