Recent

Author Topic: [Solved] SQL for MS Access  (Read 2419 times)

daveinhull

  • Sr. Member
  • ****
  • Posts: 297
  • 1 divided by nothing must still be 1!
[Solved] SQL for MS Access
« on: October 15, 2017, 02:27:08 pm »
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][+][-]
  1. SELECT MIN(SH_Trained) AS Trained, SUM(Stakeholders.SH_Management_Years) AS SumManYears,
  2.        SUM(Stakeholders.SH_Domain_Years) AS SumDomainYears, Avg(Stakeholders.SH_Management_Years) AS AvgManYears,
  3.        Avg(Stakeholders.SH_Domain_Years) AS AvgDomainYears, MIN(Stakeholders.SH_Management_Years) AS MinManYears,
  4.        MIN(Stakeholders.SH_Domain_Years) AS MinDomainYears, Stakeholder_Types.SH_Type, MainData.ID
  5. FROM (Stakeholder_Types
  6. INNER JOIN Stakeholders ON Stakeholder_Types.ID = Stakeholders.T_Stakeholder_Type)
  7. INNER JOIN (MainData
  8. INNER JOIN ATMS_To_Appraisal ON MainData.ID = ATMS_To_Appraisal.T_Appraisal) ON Stakeholders.ID = ATMS_To_Appraisal.T_ATM
  9. GROUP BY Stakeholder_Types.SH_Type, MainData.ID
  10. HAVING Stakeholder_Types.SH_Type = 'ATM' AND MainData.ID = :ID ;

Any thoughts?

Thanks
Dave
« Last Edit: October 15, 2017, 04:11:28 pm by daveinhull »
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

daveinhull

  • Sr. Member
  • ****
  • Posts: 297
  • 1 divided by nothing must still be 1!
Re: SQL for MS Access
« Reply #1 on: October 15, 2017, 04:11:08 pm »
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
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: [Solved] SQL for MS Access
« Reply #2 on: October 16, 2017, 09:21:55 am »
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.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

 

TinyPortal © 2005-2018