Large delimited text file - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Large delimited text file

Clay Boepple

2 years ago

I am linking and working with data from a delimited .txt file.  The file has about 20 fields, but the data type for fields 5-20 changes depending on the value of field 4.  Then there are about 20 different values for field 4.  Field 3 is like a customer #.  So data in the text is something like this:

Field3:     Field4:     Fields5-20:
Cust#1      1           Contact Information
Cust#2      1           Contact Information
Cust#3      1           Contact Information
Cust#1      2           Customer #1 Family Information
Cust#1      3           Customer #1 Car1 Information
Cust#1      3           Customer #1 Car2 Information

I know how to make a Query on each value of Field4 and make a table based on that Query.  Then, change the data type of fields 5-20 in that new table.  I'm sure there's a better way than having 20 Queries to split this data out into the various tables?  This data file is updated daily and so it also needs to be automatically update the tables.

Thanks for the help.

	Adam Schwanz @Reply 2 years ago
If Then IIF Function You should just be able to make one query or piece of code, but it may get very complex, I would do it with VBA as it's easier to read/understand then using nested IIF statements imo. `If Field4=1 Then Field5=A Field6=B etc.. ElseIf Field4=2 Then Field5=1 Field6=2 etc. End If` Or you can use a Switch Function, but I prefer if/then.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View