Modifying Data in Access then Exporting to CSV file 1

[RCSNC]

Access Newbie - Here's my problem:

I need to import a CSV file into Access 2K, modify the fields (for example - concatenate the fields firstName and lastName into "CommonName=firstName(space)lastName..." and place the modified data into a field in a different table) and then export the data back into a CSV file.

Importing the data is no problem, I've also figured out how to create a form that make the modifications I need, but am so far unsuccessful in getting the data from the form to a table so that I can perform the CSV export.

Am I heading in the right direction? If so, how do I get the data from the form into my new table? If not, please direct.

Thanks in advance,
RCSNC

 

[TelcoBT]

If you have imported the CSV file, then you have it in a table, however I would then be looking at a Query to create a new table from your imported table, manipulating the data in the process. If you read up on queries and play around in the 'Query by Example' grids that appear if you select the 'design query' option you should get the idea.

Initially you can produce a 'select' query from your imported table simply show the underlying table fields [firstName] etc. this should show you what you get. (Table fields appear in a drop down list in these cells to make life easier). Once you have a feel for how this works, then start by adding (or replacing existing fields in the query)
'calculated fields' eg using your example, in the field name box/cell where the table field names appear,you can put the following

CommonName:[importTable]![firstName] & " " & [importTable]![lastName]

This would display a column called 'CommonName' and show the 'calculated' value. Play around with the select query adding the appropriate calculated fields and once you are happy with the data showing in the 'select' query, then you can change it into a 'make table' query and produce the new table that can be the source of your export.

You can also 'filter and sort' your data on various fields.

IF you master this, then you have mastered a most important database function - you can also see your queries translated into SQL, which may then help you produce even more complex transformations of data via SQL, as the QBE grid can't always display the most complex SQL queries.

Saving the query will ensure that you can re-use it whenever you want.

 

[RCSNC]

Thank you! This was exactly the info I needed.

 

[TelcoBT]

I had so much help from people in here when I first started Access that it's good to feel I contributed for a change!

Good luck with the export.