SQL statement in DataPage

[JamesMobiloo]

I’m from a MS Access background.  I’ve created an SQL statement to count the number of records that match certain criteria.  The statement is reasonably complex but I have made it work in Access.  Now I want to find a way of re-creating the same result in Caspio.  I’ve searched and searched but I can’t even find where to begin.  I can find examples of discussions about use of SQL statements on DataPages so it appears to be possible but I can’t find where to begin.  

 

As a really simple example: let’s say I want a DataPage to show the number of records in a given table.  I can use the Aggregation tools to achieve what I need but I can’t find out how to actually view the SQL statement behind the scenes.

 

Can anyone assist?

 

James

[AtayBalunbalunan]

Hi JamesMobiloo, you can use subqueries in Calculated Fields. You can refer to this documentation with an example of counting records from a table: https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/
Just jump to Subqueries in Calculated Fields section.

[Glitch]

To start with, what type of DataPage are you using?

If you're using a Tabular or a Pivot, then you may not need SQL statements anymore since we have a Total and Aggregation function in caspio.

[RuisiHansamu]

Regarding your questions on SQL Queries, we will not be able to do so, directly. But what we can do is to create a View to join your tables, then create a Report DataPage to retrieve data from this view, in which you can apply further filters. This may help you out on starting: 

Also, there is a similar forum post that you can refer to: 

 

[Aether]

Hi All, 

Just want to let you know that MS Access will be deprecated. If you have an existing Data Import/Export task, the used MS Access will continue to function until April 1, 2024. We strongly recommend updating your scheduled import/export tasks that use MS Access before this date to avoid interruption to your applications. Refer to the article 

https://howto.caspio.com/release-notes/caspio-42-0/caspio-42-0/#:~:text=about Logs.-,Deprecations,-We have recently

https://howto.caspio.com/deprecations/deprecation-of-ms-access-for-import-export/