[Solved] SQL for MS Access
lazarus / 16-Databases

----------------------------------------------------------------------
daveinhull · 2017-10-15 12:27

Hi

Just wanted to say that this site is great for getting some good help
- I'm really progressing on project while also gaining some good
education :-)

I've got another quick (hopefully) query. The following SQL works fine
in MS Access (with of course the 'ATM' changed to "ATM" and the :ID
appropriately replaced)m but it doesn't work from FPC. I get zero
records and then an error when trying to access a field saying, e.g.
field not found "SumManYears".

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
MIN(SH_Trained) AS Trained, SUM(Stakeholders.SH_Management_Years) AS
SumManYears,       SUM(Stakeholders.SH_Domain_Years) AS
SumDomainYears, Avg(Stakeholders.SH_Management_Years) AS AvgManYears, 
     Avg(Stakeholders.SH_Domain_Years) AS AvgDomainYears,
MIN(Stakeholders.SH_Management_Years) AS MinManYears,     
 MIN(Stakeholders.SH_Domain_Years) AS MinDomainYears,
Stakeholder_Types.SH_Type, MainData.IDFROM (Stakeholder_TypesINNER
JOIN Stakeholders ON Stakeholder_Types.ID =
Stakeholders.T_Stakeholder_Type)INNER JOIN (MainDataINNER JOIN
ATMS_To_Appraisal ON MainData.ID = ATMS_To_Appraisal.T_Appraisal) ON
Stakeholders.ID = ATMS_To_Appraisal.T_ATMGROUP BY
Stakeholder_Types.SH_Type, MainData.IDHAVING Stakeholder_Types.SH_Type
= 'ATM' AND MainData.ID = :ID ;
Any thoughts?

Thanks
Dave

----------------------------------------------------------------------
daveinhull · 2017-10-15 14:11

All sorry, a really silly error on my part  , just kept convincing
myself that the SQL must be wrong and never took the time to look at
other things.
I (foolishly) was using a .Exec rather than .Open on the query

----------------------------------------------------------------------
mangakissa · 2017-10-16 07:21

Differences:
- Open  (or active) sends a query to the database and reads the result
into the dataset.
- execsql sends a query, but doesn't do anything wich the results.