Macro to perform importing and report making

[seraphim]

Hi,

This is my first post, just started using Access for work. What I want to do is to create a macro to generate a daily roster for work.

The steps that I want to implement in the macro are:
1/ Import table - call it 'roster' (already in required format)
- I want to automate the import process
2/ Link 'roster' to existing 'employees' table
3/ Create report based on fields from both the 'roster' table and the 'employees' table.

The steps are the same everytime, but I can't work out how to automate the design process for the report making.

Any help appreciated, spent hours trolling through the net but couldn't find anything specific enough.

Cheers

Gavin

 

[FoFa]

Not 100% sure of your question, but basically you create a report (you can use the report wizard), then use the runreport macro command to execute it.

 

[Adeptus]

You don't need to recreate the report every time. Just create it once (manually, or using the Wizard), then open it after importing the new roster table.
You also don't need to "link" the Employee table to the new roster table.
Basically, Access is just looking for a table with a specific name. If you replace that table with a new one with the same name, the rest of the database won't care.

Where are you importing it from? Another database? Try making it a linked table instead of importing it, then it will always be current.
Otherwise, delete the old data from the table & insert the new data
OR delete the old table & import the new table.

 

[seraphim]

Hi,

Thanks for the replay, sorry about the cloudines of my post. Essentially what I want to do is accept as input a MS EXcel table into my macro. The output will be the report. All the steps in between are to be done automatically. So

1/ Import table 'roster' - I want all the steps when importing to be done automatically. Also I want to copy over the previous days info located in 'roster'
2/ Link 'roster' to existing 'employees' table
3/ Generate report, again all the steps that require user input in the wizard, I want done automatically.

So I want to do is to set a default import table and report format macro which doesn't require user interaction.

Hope that's clearer, maybe not, soory new to Access.

Gavin

 

[Michael J Ross]

I think I know what you want, the way to do it is

1. Create your Roster table in design view
2. Create the query in design view that joins roster and employee tables to be used as the source of your report and also a delete query to clear the records in your roster table after the report has been ran.
3. Design your report
4. Create a button somewhere and on its on click event build the macro
Use Transfer Spreadsheet to import the Excel File
Use Open report to run the report
Use Open Query to run the delete query to empty out the records from the roster table so it is empty for the next day's roster.

 

[seraphim]

Thanks for the help

Hi,

Thank you all for the help, rosters are now being generated at the push of a button. I ended up using techniques from all of the posts, as Adeptus suggested I made a generic report and 1 table for the roster that is updated with the new details everytime. I also used a macro as suggested by Michael J Ross.

The macro steps are:
1/RunsSQL (with the command 'DELETE FROM Roster' to clear tthe previous data)
2/ Run Command -> Import
3/Open Report

Again thanks for the help.

Gavin