Pivot Tables with Access 2016

[DianeH]

Does anyone know of a way to create Pivot Tables with Access 2016? I have an enormous fraud case, potentially millions of records (could be over 10 million). I need to look for suspected "relationships" in the data, but have no way of knowing up front what those relationships are going to be, how many there will be, or in what fields. For example: multiple claims from one address. Or multiple claims from one person. The problem is that I don't have a specific suspect. I'll be combing all of the records looking for suspects.



Ordinarily, I would do this in Excel. I would sort and filture multiple different ways and create numerous pivot tables to look for the relationships. But, in this circumstance, I'm afraid I'm going to have more records than Excel can handle.

TIA,

Diane

[ranman256]

Access can hold 1 or 2 million recs but has a basic pivot table. (Crosstab query)
mine stopped at 2.1 mill. to the limit.

Excel's pivot is much more versatile.
Theres also Tableau and power pivot.
you may need SQL server backend with Access front end.

[isladogs]

Pivot tables were deprecated from A2013 onwards.
Do you have a copy of A2010 you can use?

There is no specific limit to the number of records in an Access dB. There is a 2GB file size limit however.

I have tables with several million records in Access without hitting the 2GB size limit. However the number of fields and size of the data is also important.
However perhaps you should consider placing the data in SQL Server. The free Express version has a 10GB size limit which should be plenty.
Paid versions also exist with additional features and size limits but I've never needed them.

[DianeH]

Thank you. I should know more today about the potential number of records. If it isn't as many as I first thought, I may just stick with Excel. We have SQL, but I have never used it.

[DianeH]

We do have SQL, but I have never used it. I might check with some of our other folks and see if anyone else has experience with it. Otherwise, I'm probably going to have to stick to Excel. I am very comfortable with Excel, but think the number of records in this case could exceed its limitations.

[Gicu]

Have a look if my free utility http://forestbyte.com/ms-access-util...able-designer/ can help you. IYou design the pivot table in Access, based on a selected query and then dynamically creates an Excel file and exports the raw data and creates the pivot table.

Cheers,

[DianeH]

Have a look if my free utility http://forestbyte.com/ms-access-util...able-designer/ can help you. IYou design the pivot table in Access, based on a selected query and then dynamically creates an Excel file and exports the raw data and creates the pivot table.

Cheers,

Thank you very much!!!! I'll check this out.

Diane