General Access Structure Question

[caw442000]

I am new to access however over 10 yrs ago I did some basic databasing so I have a base knowledge I suppose. I am pretty handy with Excel however my boss is on an access kick now so I have to learn it. He has tasked me with a project where I have a database I imported from excel of over 6700 rows and 65 columns. Now some of the columns have information in the from cells that wont be imported. And some cells have errors and text in them which turn the column that is imported into access into a text when it should be a date or whatever. First question is how should I deal with making those tables date/time while leaving the error for the ones that have error and leaving the ones that have words written with words? Eventually all this information will be used to for tables that look like excel but easier to use for him as well as charts that will be easily customizable to change similar to pivot table. If any one has any suggestions on how to go about tackling this task it would be much appreciated. I will do the research later on whatever is suggested to tackle this database but was just looking for general direction. Also if it helps the information is hospital related with a surg#, 1 surg, 1 nurse, and various information on turnaround time and surg on sched. All informaton is based on surg # so that is the primary key. If it is more advantageous to break up the table into multiple tables I can do that also. In addition this database will grow weekly so whatever I do has to be able to import more data by the week.

Thanks

[alansidman]

If before importing into Access, you convert the Excel file to a .csv file and then use the wizard to import the table, you will have more control over each of the new fields (columns) that you are importing. Access when importing Excel, guesses as to the field types based upon the first 20 rows of data. With a .CSV file, you can specifiy yourself what type of field you should have.

Alan

[caw442000]

Thank you for your input. That is good to know about the control aspect of importing the information. Would you recommend importing them all at once then separating them into smaller tables and creating relations or would you recommend separating the information first in excel and importing multiple smaller tables then creating relations. I want to do this as efficent as possible. All of the data is related so i could leave in 1 table but when I start doing charts forms and query tables I want it to be quick.

Thanks again

[alansidman]

Personally, I like to have clean data in Access. It may be easier to split the information in Excel befor importing. You can run make table queries in Access, but if you are more comfortable in Excel, I would do the splitting there.

[caw442000]

Thanks I think I will separate everything there and give the tables there keys there as well. Step 1 complete now i have to figure out if access is capable of doing what I need it to do in regards to formula calculations. Things that were easy in excel I don't know if it will be easy in access. For example: Is access able to calculate whether or not an appt is the first of the day for that surgeon or would it be easier or better for that matter to just do linked tables and allow excel to do the heavy formula lifting. I still want the process to run quickly. Any ideas?

Thanks again

[alansidman]

Access can do many of the same functions that Excel has. Here is a good site to review Access functions and their syntax.

http://www.techonthenet.com/access/index.php

check the links on my signature block for other good links.

Alan

[caw442000]

thanks. I will check it out.