Solved

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

Posted on 2008-06-23
9
208 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now