I hope the day finds everbody well. I am working on a project that just has me mind blocked and I'm sure it's a simple little thing, but I just can't get it.
I have about 700 seperate Excel spreadsheets that I need to incorporate into a single database. Each spreadsheet has about 15,000 records (rows). There are multiple problems with this task, but I have solved most of them so far. But what I am hung up on is seperating data out of one of the field. The field is a location field and it actually has 3 bits of information that need to be seperate fields. For Example: (Location is the column/field name)
Location: Miami - M0123 (Size 1)
Miami = Location Name, some names have a - in in some don't (Miami-in and Miami-out)
M0123 - Is a unique location identifier code, there are about 90 different codes
(Size1) = The size of the location, there are only 5 sizes 1 thru 5
So, instead of doing text to columns in 700 spreadsheet before importing, I am trying to seperate the data inside access. So far I have made some update queries that that work just fine by them selves.
For example: I created two new fields Location Code and Location Size. Then I made a update query where;
Criteria = like "*(Size1)
Update Location Size set to Size 1
Works great no problems. For the size, there are only 5 so no problem there. But for the location code there are 90 of them. I know I can do the same type of update query for them, but I would have to make 90 something queries and run that many for every spreadsheet imported. Do able but long and tedious.
So, I was thinking, instead of 90 queries, isn't it possible to write one long If then statement to check and update? I actually have to do this whole process twice (Assigned Location and Actual location) so I'd very much rather two long If thens vice 180+ queries.
I know how to write a if then for an equals, but not a like statement. If I could get an example of one line and most importantly, where to put it (like make a form or just run the query in a macro).
Of course, if there are any better overall ideas, that would be awesome too.
BTW - I know the size of the end product is huge, thats no problem, this is just a filter program and I will run each spreadsheet through then kick it back out in the correct excel format. The point here is to prep all the spreadsheet (which are in 3 or 4 different formats themselves) into a group that is all the eact same format so then it can be imported into a larger database later (not access).
Thank you in advance!
NOTE: Access 2007
I have about 700 seperate Excel spreadsheets that I need to incorporate into a single database. Each spreadsheet has about 15,000 records (rows). There are multiple problems with this task, but I have solved most of them so far. But what I am hung up on is seperating data out of one of the field. The field is a location field and it actually has 3 bits of information that need to be seperate fields. For Example: (Location is the column/field name)
Location: Miami - M0123 (Size 1)
Miami = Location Name, some names have a - in in some don't (Miami-in and Miami-out)
M0123 - Is a unique location identifier code, there are about 90 different codes
(Size1) = The size of the location, there are only 5 sizes 1 thru 5
So, instead of doing text to columns in 700 spreadsheet before importing, I am trying to seperate the data inside access. So far I have made some update queries that that work just fine by them selves.
For example: I created two new fields Location Code and Location Size. Then I made a update query where;
Criteria = like "*(Size1)
Update Location Size set to Size 1
Works great no problems. For the size, there are only 5 so no problem there. But for the location code there are 90 of them. I know I can do the same type of update query for them, but I would have to make 90 something queries and run that many for every spreadsheet imported. Do able but long and tedious.
So, I was thinking, instead of 90 queries, isn't it possible to write one long If then statement to check and update? I actually have to do this whole process twice (Assigned Location and Actual location) so I'd very much rather two long If thens vice 180+ queries.
I know how to write a if then for an equals, but not a like statement. If I could get an example of one line and most importantly, where to put it (like make a form or just run the query in a macro).
Of course, if there are any better overall ideas, that would be awesome too.
BTW - I know the size of the end product is huge, thats no problem, this is just a filter program and I will run each spreadsheet through then kick it back out in the correct excel format. The point here is to prep all the spreadsheet (which are in 3 or 4 different formats themselves) into a group that is all the eact same format so then it can be imported into a larger database later (not access).
Thank you in advance!
NOTE: Access 2007
Last edited:
