Pivot table

[bbridgers05]

My company uses an excel spreadsheet to track an enormous amount of data and I am attempting to build a database to get rid of the God awful excel spreadsheet. One issue that I am having is that in access 2016, you no longer have the option to create a pivot table and we use pivot tables to calculate time to hire, in days. This is put together in a pivot table, using just dates. Is there anyone that may be able to help me out? Thanks so much!

[Gicu]

Have a look at my free pivot table designer (http://forestbyte.com/ms-access-util...able-designer/) which allows you to design your pivot table straight in Access and export it to Excel (it creates the Excel file itself and exports both the raw data and the pivot table on two separate sheets). Let me know if you have problems setting it up.

Cheers,
Vlad

[bbridgers05]

Thanks so much!

[ArviLaanemets]

1. The usual way in access is to have a report, which is based on query. When calling a report from form control. e.g. clicking on button designed for it, you can also pass filter conditions to report;
2. In case users are more used to excel, or there are users who only need to see results, and don't have Access installed, you can design Excel report workbook(s), which use ODBC query/queries to read data from Access database. To avoid problems with data locking, you better have data in back-end Access (or e.g. SQL Server) database. When using excel reports, you can:
a) read data from Access with ODBC query in exactly in this format the end user needs the data. Usually this means separate queries for different kind of info;
b) read all or part (you can use entries in specified Excel worksheet cells as query parameters) of general data from Access database with ODBC query, and design one or several report sheets based on query results (e.g. using Pivot table). The difference with your current spreadsheet is, there are no formulas in query result table, and when you use parametrized query, then the amount of read data is considerably less.