Macro in Access

[billykiller05]

I am new to access and I would like to write a macro to run a query. I receive a file each month so I need to be able to change the table for which the query is ran. I need some help getting started.

 

[Joe4]

How is the new data each month put in Access? Imported? Linked?

Essentially, I think you would want to continue to put the data in the same table every time, then you can run the same query every time. Then it is just a matter of importing the data.

Perhaps you can tell us a little more about the whole process?

 

[billykiller05]

It is imported from a text file into a new table. We have to keep each month seperate.

 

[Joe4]

It is imported from a text file into a new table. We have to keep each month seperate.

You probably don't want to do this, it is considered to not be good database form. A simple example can show why.

Let's say that you have 24 months of data, hence 24 tables. Now let's say that you wanted to look for a specific record, but you don't know what month it is in. You would have to write 24 queries!

Another example, let's say that you wanted to add up all the records across all months for a certain ID. Once again, you would have to write 24 queries (or a query with 24 unions)!

As you can see, it can cause a lot of unnecessary work.

The better way to do it is to add a field to your one main table that has some sort of month/date identifier. That way, all your records are in one table, but can easily by queried out by month.

If it is too hard to add this field to the data being imported in, here is how I would do that:
1. Import your data into a "Temporary Table"
2. Create an "Append Query" that takes the records from your "Temporary Table" and moves them to your "Main Table" while at the same time populating your MonthID field (through a calculated field).

 

[billykiller05]

Ok I belive I can combine the tables into one file or at lease begin doing this for the future table. How do I set up a macro to run a query?

 

[Joe4]

Just go into the Macro section, start a new macro, and select the OpenQuery action. It will list all the arguments, and even provide help on what should be in each one.

Also note, that there are also actions for importing data, like TransferText (for text files) and TransferSpreadsheet (for Excel data), in case you want to automate you data import, if possible.

Also, once you create a Macro and save it, if you highlight the macro and to Tools | Macro | Convert Macros to Visual Basic, this will convert your Macro to Visual Basic code. This is helpful if you wish to make it more dynamic, like using variable names for files, and prompting the user for a file name.

Good luck and post back if you get stuck!

 

[billykiller05]

How do I add a column with the date when importing my text file to access?

 

[Joe4]

Two ways to go about it:

1. Add the Date field to the structure of your table, and add the field to each record of your data file before importing.

That may not be feasible, so here is the other option I was alluding to.

2. Make a "Temporary" table, which should be the same structure as your main table (except it doesn't have this Date field).
Then add the Date field to the structure of your Main table.
Import your data into your Temporary table.
Create an Append Query from your Temporary table which will add all the data from your temporary table to the Main table.
In this Append Query, add a calculated field to populate the Date field in your Main table, something like this:

MonthID: "JAN08"

Then when this Append Query runs, it will add this field to all the records you are adding.
Note that you will need to maintain this field each month (or try to do something dynamic programmatically).

A similar, but different approach to do this Step 2 is to add the Date field to the structure of your Main table, and import the data directly into this Main table. Since the Date field is not in your data file, this Date field will be blank for all these new records. You could then use an Update Query to identify all records that are missing this Date field and populate it with the value of your choice.

If you need help on creating Action Queries (Append, Update), you can take a look at Access' built-in help on Action queries for some directions and examples.

 

[billykiller05]

I have created my table and I am importing my text files with the period column. However once I import the first text filed and run my update query to insert the date I can not get the update query to run for the next table and insert the different date for blank records.

 

[Joe4]

I have created my table and I am importing my text files with the period column. However once I import the first text filed and run my update query to insert the date I can not get the update query to run for the next table and insert the different date for blank records.

I think you might be blending some of the methods together. Here are the three methods (all methods include updating the Main table to include this new Date field):

1. Add Date field values to data file BEFORE importing. Import data into Main table (this method requires no temporary tables or Action Queries).

2a. Create Temporary table (which does not contain Date field). Import data into Temporary table. Use Append Query to move data from Temporary table to Main table and add populate date field values (this method requires adding nothing to original data file).

2b. Import data directly into Main Table. Use an Update Query to identify records missing Date field values, and update these Date field values to specified values (this method requires no temporary tables and adding nothing to original data file).

So, which of these methods are you trying to use?