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

Posted on 2008-06-23
Last Modified: 2010-04-21
Is there anything I should be very careful of ?
Question by:7601105166084
  • 4
  • 3
  • 2
LVL 30

Expert Comment

ID: 21848021
Why not?
LVL 30

Expert Comment

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.


Author Comment

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 ..
LVL 30

Expert Comment

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!
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.


Author Comment

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

Does this ever apply to importing spreadsheets?
LVL 11

Accepted Solution

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

Author Comment

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.
LVL 11

Expert Comment

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

Author Closing Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
HasData 9 37
How can I name a sub macro so Shift/F10 would work. 3 22
linked subforms are yielding error:  ... (800110108) 3 16
error 3022 in access vba 3 14
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

867 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

20 Experts available now in Live!

Get 1:1 Help Now