Grouping Data to Export to Excel

[DavidWE]

I need to create a report to dump into Excel. I might create the report in Access first and then create the Excel report from that – I haven’t decided yet. The report will need to pull data from two tables – Employee and PayJournal. The tables are linked by EmployeeID. The report will display the Employee ID, Employee name, Department, and the wages for each day of the week. The wages will come from the PayJournal table. The ID, name, and department are from the Employee table. The data needs to be grouped by date and department, and that is the part I would like to get some advice on. There will be a group of data associated with Department DLC, another group associated with Department DLE, and so on. I would also need to print new headings on each department break. Each department will have subtotals beneath it. The code will need to be written to handle a variable number of employees in each department.

I have written programs to group data by fields in other languages. One approach is to load all the data from the tables into an array and sort. I could also create a temporary table from a query and sort the data by date and department.

I already know how to dump tables into Excel without grouping the data. I also know how to place specific values into cells.


Can anyone provide some advice on the best approach using Access to create this report in Excel? Are there any sites with code examples similar to what I need? All ideas are welcome.

Thanks

 

[mearle]

Forget about re-inventing the wheel by using arrays, you just need to write a query, linking these two tables together, selecting the required fields, and sorting appropriately (by date and Department at least). Grouping by date could be tricky - you might need to add a insert a calculated field, such as Year(date) to group on, depending on date periods you need to use.
The query could then be used as the basis of an access report, or to dump into excel somehow (eg copy and paste), and use Excel's grouping facilities.

 

[DavidWE]

Thanks, Mearle. I was just working on the query to group by date and department (and maybe ID and name). I'll probably go ahead and create the report in Access and then worry about placing it in Excel. The tricky part might be where I have to break things down by day. I should be able to calculate each day from the start and end dates for the week (I think it will be run weekly).

I'll have to make more progress, and then I'll have more specific questions to post. In the meantime, if anyone has a link to code that might help, please post.