Recent

Author Topic: [SOLVED] Can't create view (query)  (Read 10930 times)

rvk

  • Hero Member
  • *****
  • Posts: 3842
Re: Can't create view (query)
« Reply #15 on: March 18, 2015, 04:23:28 pm »
You mean you pasted the select-query I mentioned in my post in "DB Browser for SQLite" and it gave you a result (and no error) ??

In SQLiteManager I got an error-message.
« Last Edit: March 18, 2015, 04:25:06 pm by rvk »

kapibara

  • Hero Member
  • *****
  • Posts: 517
Re: Can't create view (query)
« Reply #16 on: March 18, 2015, 07:21:30 pm »
Your code actually works without changes in Lazarus. Run the attached project and press Create View and it will be created.

My guess is still that you somehow have created a database file that doesn't have the full set of tables or columns and thats why you get a message about missing column. Check the content of the actual sqlite dbfile **that is in use** manually with SQliteMan or similar.
Lazarus trunk / fpc 3.0.4 / Debian 10 - 64 bit

madref

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Can't create view (query)
« Reply #17 on: March 23, 2015, 08:08:09 am »
@kapibara:
This project creates an error called : SQLTransaction_RefereeDB.Options: Unknown property "Options".

This keeps getting weirder
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

LacaK

  • Hero Member
  • *****
  • Posts: 577
Re: Can't create view (query)
« Reply #18 on: March 23, 2015, 12:02:14 pm »
May be, that kapibara uses newer version of FPC (TRUNK 3.0) as you (2.6.4)
As TSQLTransaction.Options property was added recently ...

kapibara

  • Hero Member
  • *****
  • Posts: 517
Re: Can't create view (query)
« Reply #19 on: March 23, 2015, 11:07:51 pm »
@Lacak: Right, I was using the SVN version and forgot that TSQLTransaction has been extended.

@madref: Tried it with Laz 1.24 release and the solution is simple. Follow these steps:

1) Open the project
2) Choose Continue Loading on "unknown property"
3) Delete SQLTransaction_RefereeDB from form
4) Drop a new TSQLTransaction on the form and name SQLTransaction_RefereeDB
5) Run the project

Now it works because you have removed the extended TSQLTransaction that doesnt exist yet in the released Lazarus. And we can all enjoy having learnt something new. ;)
« Last Edit: March 24, 2015, 06:52:51 am by kapibara »
Lazarus trunk / fpc 3.0.4 / Debian 10 - 64 bit

madref

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Can't create view (query)
« Reply #20 on: March 24, 2015, 10:45:37 am »
$%^&^% GRRRRRR........Still get the same error :(
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

rvk

  • Hero Member
  • *****
  • Posts: 3842
Re: Can't create view (query)
« Reply #21 on: March 24, 2015, 11:18:29 am »
Still the "Unknown property" or your original "no such column tbl_Wedstrijden.Wed_ID" ?

The code from kapibara works correctly here. It uses the SQLTransaction_RefereeDB.Commit; after doing the CREATE VIEW (which I had forgotten) so the final CREATE VIEW in btnOriginalClick works perfect.

Did you check with SQliteMan or similar if the qry_Teams and qry_Scheidsrechters is really created (even without pressing the button)?

Or are you having other error-messages?


Another possible way to change the code if you're having trouble with that options-line is opening up uMain.lfm with a text-editor and removing the "Options = []" line from the SQLTransaction_RefereeDB object.
« Last Edit: March 24, 2015, 11:20:51 am by rvk »

madref

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Can't create view (query)
« Reply #22 on: March 24, 2015, 01:32:27 pm »
I still get the original error: no such column.....
Yes the other 2 query's are being made correctly (as they do in my own code).

Can it help if i close the database and reopen it and then try to add the query?

P.S. How do i do that?
« Last Edit: March 24, 2015, 01:39:15 pm by madref »
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

rvk

  • Hero Member
  • *****
  • Posts: 3842
Re: Can't create view (query)
« Reply #23 on: March 24, 2015, 01:42:04 pm »
Reopening the database should not make a difference because you're using the same transaction in all commands.

But you can try:

If you are sure your Madref.db3 database contains all your VIEWS (AND you checked this with SQliteMan) you could try removing the delete and create code from the FormCreate procedure:

Code: [Select]
procedure TForm1.FormCreate(Sender: TObject);
var
  cSQL, DBFile: string;
begin

  DBFile:= ExtractFilePath(Application.ExeName) +'Madref.db3';

  // if FileExists(DBFile) then DeleteFile(DBFile);   // <------ DO NOT DELETE

  SQLite3Connection_RefereeDB.DatabaseName:= DBFile;

  exit; // <---------------- This will keep the Madref.db3 and use it like it is.
           <------------ So with all the VIEWS you checked with SQliteMan

  //.... rest of code which will NOT execute


After this... what error-message do you get when pressing "Create View Overzicht Wedstrijden" ??
« Last Edit: March 24, 2015, 01:47:57 pm by rvk »

rvk

  • Hero Member
  • *****
  • Posts: 3842
Re: Can't create view (query)
« Reply #24 on: March 24, 2015, 02:00:46 pm »
I just checked again.

The view is created but it seems the view qry_Overzicht_Wedstrijden doesn't give any columns back.

When checking the DLL for that view it is this:

Code: [Select]
-- Describe QRY_OVERZICHT_WEDSTRIJDEN
CREATE VIEW qry_Overzicht_Wedstrijden AS SELECT tbl_Wedstrijden.Wed_ID, tbl_Seizoenen.SEI_ID AS Seizoen, Wed_Datum AS Datum, Wed_Tijd AS Tijd, tbl_Divisie.Divisie_Tekst AS Divisie, qry_Teams.Team AS [Team A], qry_Teams_1.Team AS [Team B],qry_Scheidsrechters.Scheidsrechter AS [Scheidsrechter 1], qry_Scheidsrechters_1.Scheidsrechter AS [Scheidsrechter 2], qry_Scheidsrechters_2.Scheidsrechter AS [Scheidsrechter 3], qry_Scheidsrechters_3.Scheidsrechter AS [Scheidsrechter 4], CASE WHEN Wed_Begeleiding THEN "x" ELSE NULL END AS B, CASE WHEN Wed_Meetellen THEN "x" ELSE NULL END AS M, CASE WHEN Wed_Show THEN "x" ELSE NULL END AS W, CASE WHEN Wed_Outlook THEN "x" ELSE NULL END AS O, tbl_Wedstrijden.Wed_Scheids_1, tbl_Wedstrijden.Wed_Scheids_2, tbl_Wedstrijden.Wed_Scheids_3, tbl_Wedstrijden.Wed_Scheids_4, tbl_Wedstrijden.Wed_Divisie AS Div, tbl_Wedstrijden.Wed_Meetellen, tbl_Wedstrijden.Wed_TeamA, tbl_Wedstrijden.Wed_TeamB, tbl_Wedstrijden.Wed_Show, qry_Teams.Team_Plaats AS Speelplaats, tbl_Wedstrijden.Wed_Plaats, tbl_IJsbanen.IJS_Plaats, tbl_Wedstrijden.Wed_Datum, tbl_Wedstrijden.Wed_Tijd, tbl_IJsbanen.IJS_Kilometers, tbl_Wedstrijden.Wed_Opmerkingen, 2*[IJS_Kilometers]*0.28 AS Euro, tbl_Divisie.Divisie_Vergoeding, tbl_IJsbanen.IJS_Parkeren FROM tbl_Divisie INNER JOIN (tbl_Seizoenen INNER JOIN ((tbl_IJsbanen INNER JOIN ((((qry_Teams AS qry_Teams_1 INNER JOIN (qry_Teams INNER JOIN tbl_Wedstrijden ON qry_Teams.Team_ID = tbl_Wedstrijden.Wed_TeamA) ON qry_Teams_1.Team_ID = tbl_Wedstrijden.Wed_TeamB) LEFT JOIN qry_Scheidsrechters ON tbl_Wedstrijden.Wed_Scheids_1 = qry_Scheidsrechters.Scheids_ID) LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_1 ON tbl_Wedstrijden.Wed_Scheids_2 = qry_Scheidsrechters_1.Scheids_ID) LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_2 ON tbl_Wedstrijden.Wed_Scheids_3 = qry_Scheidsrechters_2.Scheids_ID) ON tbl_IJsbanen.IJS_ID = tbl_Wedstrijden.Wed_Plaats) LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_3 ON tbl_Wedstrijden.Wed_Scheids_4 = qry_Scheidsrechters_3.Scheids_ID) ON tbl_Seizoenen.SEI_ID = tbl_Wedstrijden.Wed_Seizoen_ID) ON tbl_Divisie.Divisie_ID = tbl_Wedstrijden.Wed_Divisie ORDER BY tbl_Seizoenen.SEI_ID DESC , tbl_Wedstrijden.Wed_Datum DESC , tbl_Wedstrijden.Wed_Tijd DESC

When running the SELECT-statement of that view it is invalid:
Query Error: no such column: tbl_Wedstrijden.Wed_ID Unable to execute statement

And with this I keep coming back to the problem that you can't address fields and tables defined in the INNER JOIN if that join isn't named.

Can you trace back what the exact VIEW-statement is in Embarcadero Delphi XE7 or provide us with a sample db3 database so we can check why it was working in your other environment?

(I'm puzzled as to why Lazarus/SQLite drivers did allow the creation of this VIEW while trying to create it manually it gives an error:
Query Error: no such column: tbl_Wedstrijden.Wed_ID Unable to execute statement)

Yeah, somehow it is possible to create "invalid" VIEWS with the SQLite driver. So the VIEW qry_Overzicht_Wedstrijden is created but will give an error on SELECT * FROM qry_Overzicht_Wedstrijden.
« Last Edit: March 24, 2015, 02:06:52 pm by rvk »

madref

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Can't create view (query)
« Reply #25 on: March 24, 2015, 03:07:13 pm »
How would you make this query then?
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

rvk

  • Hero Member
  • *****
  • Posts: 3842
Re: Can't create view (query)
« Reply #26 on: March 24, 2015, 03:31:14 pm »
How would you make this query then?
I would begin with unwrapping all those "enclosed" inner joins so their fields are directly accessible to the main SELECT-statement (which in your case they were not).

Below is one way. I don't have a database filled so I can't check if the result is exactly what you want but it will show all the games with fields from the other tables (like I assume you want). If this statement runs correctly for you, you can use it to make your view by putting "CREATE VIEW IF NOT EXISTS qry_Overzicht_Wedstrijden AS " before it.

This is the easiest way to just first check if the SELECT statement works correctly and then using it in a VIEW.

(Also not sure but maybe all your INNER JOIN could also be LEFT JOIN but if all those fields referenced in the INNER JOIN have values in the main tbl_Wedstrijden table it shouldn't mater that much.)

Code: [Select]
SELECT
  tbl_Wedstrijden.Wed_ID,
  tbl_Seizoenen.SEI_ID AS Seizoen,
  Wed_Datum AS Datum,
  Wed_Tijd AS Tijd,
  tbl_Divisie.Divisie_Tekst AS Divisie,
  qry_Team_A.Team AS [Team A],
  qry_Team_B.Team AS [Team B],
  qry_Scheidsrechters_1.Scheidsrechter AS [Scheidsrechter 1],
  qry_Scheidsrechters_2.Scheidsrechter AS [Scheidsrechter 2],
  qry_Scheidsrechters_3.Scheidsrechter AS [Scheidsrechter 3],
  qry_Scheidsrechters_4.Scheidsrechter AS [Scheidsrechter 4],
  CASE WHEN Wed_Begeleiding THEN "x" ELSE NULL END AS B,
  CASE WHEN Wed_Meetellen THEN "x" ELSE NULL END AS M,
  CASE WHEN Wed_Show THEN "x" ELSE NULL END AS W,
  CASE WHEN Wed_Outlook THEN "x" ELSE NULL END AS O,
  tbl_Wedstrijden.Wed_Scheids_1,
  tbl_Wedstrijden.Wed_Scheids_2,
  tbl_Wedstrijden.Wed_Scheids_3,
  tbl_Wedstrijden.Wed_Scheids_4,
  tbl_Wedstrijden.Wed_Divisie AS Div,
  tbl_Wedstrijden.Wed_Meetellen,
  tbl_Wedstrijden.Wed_TeamA,
  tbl_Wedstrijden.Wed_TeamB,
  tbl_Wedstrijden.Wed_Show,
  qry_Team_A.Team_Plaats AS Speelplaats,
  tbl_Wedstrijden.Wed_Plaats,
  tbl_IJsbanen.IJS_Plaats,
  tbl_Wedstrijden.Wed_Datum,
  tbl_Wedstrijden.Wed_Tijd,
  tbl_IJsbanen.IJS_Kilometers,
  tbl_Wedstrijden.Wed_Opmerkingen,
  2*[IJS_Kilometers]*0.28 AS Euro,
  tbl_Divisie.Divisie_Vergoeding,
  tbl_IJsbanen.IJS_Parkeren
FROM tbl_Wedstrijden
INNER JOIN tbl_Divisie ON tbl_Divisie.Divisie_ID = tbl_Wedstrijden.Wed_Divisie
INNER JOIN tbl_Seizoenen ON tbl_Seizoenen.SEI_ID = tbl_Wedstrijden.Wed_Seizoen_ID
INNER JOIN tbl_IJsbanen ON tbl_IJsbanen.IJS_ID = tbl_Wedstrijden.Wed_Plaats
INNER JOIN qry_Teams AS qry_Team_A ON qry_Team_A.Team_ID = tbl_Wedstrijden.Wed_TeamA
INNER JOIN qry_Teams AS qry_Team_B ON qry_Team_B.Team_ID = tbl_Wedstrijden.Wed_TeamB
LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_1 ON tbl_Wedstrijden.Wed_Scheids_1 = qry_Scheidsrechters_1.Scheids_ID
LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_2 ON tbl_Wedstrijden.Wed_Scheids_2 = qry_Scheidsrechters_2.Scheids_ID
LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_3 ON tbl_Wedstrijden.Wed_Scheids_3 = qry_Scheidsrechters_3.Scheids_ID
LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_4 ON tbl_Wedstrijden.Wed_Scheids_4 = qry_Scheidsrechters_4.Scheids_ID
ORDER BY
  tbl_Seizoenen.SEI_ID DESC,
  tbl_Wedstrijden.Wed_Datum DESC,
  tbl_Wedstrijden.Wed_Tijd DESC

(I'm still puzzled as to why this statement worked for you earlier in Delphi and SQLite on Mac OS X)

madref

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Can't create view (query)
« Reply #27 on: March 24, 2015, 06:49:14 pm »
Wow now that it is written this way it works.....

Can you help me rewrite this one too?
Code: [Select]
// qry_Overzicht_Linesman
cSQL := '';
cSQL := 'SELECT ' +
          'tbl_Evaluatie_Linesman.Evo_ID, tbl_Evaluatie_Linesman.Evo_Wed_ID, ' +
          'qry_Scheidsrechters.Scheidsrechter, tbl_Wedstrijden.Wed_Datum, ' +
          'tbl_Wedstrijden.Wed_Tijd, qry_Teams.Divisie, qry_Teams.Team AS [Team A], ' +
          'qry_Teams_1.Team AS [Team B], tbl_Evaluatie_Linesman.Evo_Systeem, ' +
          'tbl_Evaluatie_Linesman.Evo_SoortWedstrijd, tbl_Evaluatie_Linesman.Evo_Goal_TeamA_Per1, ' +
          'tbl_Evaluatie_Linesman.Evo_Goal_TeamA_Per2, tbl_Evaluatie_Linesman.Evo_Goal_TeamA_Per3, ' +
          'tbl_Evaluatie_Linesman.Evo_Goal_TeamA_Per4, [Evo_Goal_TeamA_Per1]+[Evo_Goal_TeamA_Per2]+' +
          '[Evo_Goal_TeamA_Per3]+[Evo_Goal_TeamA_Per4] AS Goals_A, ' +
          'tbl_Evaluatie_Linesman.Evo_Goal_TeamB_Per1, tbl_Evaluatie_Linesman.Evo_Goal_TeamB_Per2, ' +
          'tbl_Evaluatie_Linesman.Evo_Goal_TeamB_Per3, tbl_Evaluatie_Linesman.Evo_Goal_TeamB_Per4, ' +
          '[Evo_Goal_TeamB_Per1]+[Evo_Goal_TeamB_Per2]+[Evo_Goal_TeamB_Per3]+[Evo_Goal_TeamB_Per4] ' +
          'AS Goals_B, tbl_Evaluatie_Linesman.Evo_Straf_TeamA_Per1, ' +
          'tbl_Evaluatie_Linesman.Evo_Straf_TeamA_Per2, tbl_Evaluatie_Linesman.Evo_Straf_TeamA_Per3, ' +
          'tbl_Evaluatie_Linesman.Evo_Straf_TeamA_Per4, [Evo_Straf_TeamA_Per1]+[Evo_Straf_TeamA_Per2]+' +
          '[Evo_Straf_TeamA_Per3]+[Evo_Straf_TeamA_Per4] AS Straffen_A, ' +
          'tbl_Evaluatie_Linesman.Evo_Straf_TeamB_Per1, tbl_Evaluatie_Linesman.Evo_Straf_TeamB_Per2, ' +
          'tbl_Evaluatie_Linesman.Evo_Straf_TeamB_Per3, tbl_Evaluatie_Linesman.Evo_Straf_TeamB_Per4, ' +
          '[Evo_Straf_TeamB_Per1]+[Evo_Straf_TeamB_Per2]+[Evo_Straf_TeamB_Per3]+[Evo_Straf_TeamB_Per4] ' +
          'AS Straffen_B, tbl_Evaluatie_Linesman.Evo_Fitheid, tbl_Evaluatie_Linesman.Evo_Tekens, ' +
          'tbl_Evaluatie_Linesman.Evo_Positie_Eindvak, tbl_Evaluatie_Linesman.Evo_FaceOffs, ' +
          'tbl_Evaluatie_Linesman.Evo_GameManagement, tbl_Evaluatie_Linesman.Evo_Aanwezigheid, ' +
          'tbl_Evaluatie_Linesman.Evo_Oplettendheid, tbl_Evaluatie_Linesman.Evo_Communicatie, ' +
          'tbl_Evaluatie_Linesman.Evo_Teamwork, tbl_Evaluatie_Linesman.Evo_Standaard_Per1, ' +
          'tbl_Evaluatie_Linesman.Evo_Standaard_Per2, tbl_Evaluatie_Linesman.Evo_Standaard_Per3, ' +
          'tbl_Evaluatie_Linesman.Evo_Standaard_Per4, tbl_Evaluatie_Linesman.Evo_Straffen_Gemist, ' +
          'tbl_Evaluatie_Linesman.Evo_Straffen_Cheap, tbl_Evaluatie_Linesman.Evo_Icings_Gemist, ' +
          'tbl_Evaluatie_Linesman.Evo_Icings_Twijfel, tbl_Evaluatie_Linesman.Evo_SterkePunten, ' +
          'tbl_Evaluatie_Linesman.Evo_VerbeterPunten, tbl_Evaluatie_Linesman.Evo_Feedback, ' +
          'tbl_Supervisors.SPV_Naam ' +
        'FROM ' +
          'tbl_Supervisors INNER JOIN (((tbl_Wedstrijden INNER JOIN qry_Teams ON ' +
          'tbl_Wedstrijden.Wed_TeamA = qry_Teams.Team_ID) INNER JOIN qry_Teams AS qry_Teams_1 ' +
          'ON tbl_Wedstrijden.Wed_TeamB = qry_Teams_1.Team_ID) INNER JOIN ' +
          '(tbl_Evaluatie_Linesman INNER JOIN qry_Scheidsrechters ON ' +
          'tbl_Evaluatie_Linesman.Evo_Scheids_ID = qry_Scheidsrechters.Scheids_ID) ON ' +
          'tbl_Wedstrijden.Wed_ID = tbl_Evaluatie_Linesman.Evo_Wed_ID) ON ' +
          'tbl_Supervisors.SPV_ID = tbl_Wedstrijden.Wed_SPV_ID;';
It gives the same error: No such column tbl_Evaluatie_Linesman.Evo_ID.

You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

rvk

  • Hero Member
  • *****
  • Posts: 3842
Re: Can't create view (query)
« Reply #28 on: March 24, 2015, 08:36:53 pm »
Below is the new query (which you can use for creating a VIEW if you wish). How did you come to all those nested INNER JOIN's in your own statements??

See it like this:
I changed the "FROM tbl_Supervisors" to "FROM tbl_Wedstrijden" because I think it is what you take as starting point. (you want to list all games, I think). Then for every field which is a pointer to another table you just add "INNER JOIN table ON table.my_table_ID = wedstrijd.my_table_ID". Then you can add table.field1, table.field2 etc to you SELECT fields. You do that with all the tables you want info from. There is no need to use nested INNER JOIN's.

If all the fields are filled in properly then using INNER JOIN's wont give you a problem. If however for one game only 3 referees are present (for example ) and the 4th is left empty or is 0 then your INNER JOIN will result in no games in your result. If that's the case you should replace all the INNER JOIN with LEFT JOIN. But I expect that doesn't happen in your case.

Code: [Select]
SELECT
  tbl_Evaluatie_Linesman.Evo_ID,
  tbl_Evaluatie_Linesman.Evo_Wed_ID,
  qry_Scheidsrechters.Scheidsrechter,
  tbl_Wedstrijden.Wed_Datum,
  tbl_Wedstrijden.Wed_Tijd,
  qry_Team_A.Divisie,
  qry_Team_A.Team AS [Team A],
  qry_Team_B.Team AS [Team B],
  tbl_Evaluatie_Linesman.Evo_Systeem,
  tbl_Evaluatie_Linesman.Evo_SoortWedstrijd,
  tbl_Evaluatie_Linesman.Evo_Goal_TeamA_Per1,
  tbl_Evaluatie_Linesman.Evo_Goal_TeamA_Per2,
  tbl_Evaluatie_Linesman.Evo_Goal_TeamA_Per3,
  tbl_Evaluatie_Linesman.Evo_Goal_TeamA_Per4,
  [Evo_Goal_TeamA_Per1]+[Evo_Goal_TeamA_Per2]+[Evo_Goal_TeamA_Per3]+[Evo_Goal_TeamA_Per4] AS Goals_A,
  tbl_Evaluatie_Linesman.Evo_Goal_TeamB_Per1,
  tbl_Evaluatie_Linesman.Evo_Goal_TeamB_Per2,
  tbl_Evaluatie_Linesman.Evo_Goal_TeamB_Per3,
  tbl_Evaluatie_Linesman.Evo_Goal_TeamB_Per4,
  [Evo_Goal_TeamB_Per1]+[Evo_Goal_TeamB_Per2]+[Evo_Goal_TeamB_Per3]+[Evo_Goal_TeamB_Per4] AS Goals_B,
  tbl_Evaluatie_Linesman.Evo_Straf_TeamA_Per1,
  tbl_Evaluatie_Linesman.Evo_Straf_TeamA_Per2,
  tbl_Evaluatie_Linesman.Evo_Straf_TeamA_Per3,
  tbl_Evaluatie_Linesman.Evo_Straf_TeamA_Per4,
  [Evo_Straf_TeamA_Per1]+[Evo_Straf_TeamA_Per2]+[Evo_Straf_TeamA_Per3]+[Evo_Straf_TeamA_Per4] AS Straffen_A,
  tbl_Evaluatie_Linesman.Evo_Straf_TeamB_Per1,
  tbl_Evaluatie_Linesman.Evo_Straf_TeamB_Per2,
  tbl_Evaluatie_Linesman.Evo_Straf_TeamB_Per3,
  tbl_Evaluatie_Linesman.Evo_Straf_TeamB_Per4,
  [Evo_Straf_TeamB_Per1]+[Evo_Straf_TeamB_Per2]+[Evo_Straf_TeamB_Per3]+[Evo_Straf_TeamB_Per4] AS Straffen_B,
  tbl_Evaluatie_Linesman.Evo_Fitheid,
  tbl_Evaluatie_Linesman.Evo_Tekens,
  tbl_Evaluatie_Linesman.Evo_Positie_Eindvak,
  tbl_Evaluatie_Linesman.Evo_FaceOffs,
  tbl_Evaluatie_Linesman.Evo_GameManagement,
  tbl_Evaluatie_Linesman.Evo_Aanwezigheid,
  tbl_Evaluatie_Linesman.Evo_Oplettendheid,
  tbl_Evaluatie_Linesman.Evo_Communicatie,
  tbl_Evaluatie_Linesman.Evo_Teamwork,
  tbl_Evaluatie_Linesman.Evo_Standaard_Per1,
  tbl_Evaluatie_Linesman.Evo_Standaard_Per2,
  tbl_Evaluatie_Linesman.Evo_Standaard_Per3,
  tbl_Evaluatie_Linesman.Evo_Standaard_Per4,
  tbl_Evaluatie_Linesman.Evo_Straffen_Gemist,
  tbl_Evaluatie_Linesman.Evo_Straffen_Cheap,
  tbl_Evaluatie_Linesman.Evo_Icings_Gemist,
  tbl_Evaluatie_Linesman.Evo_Icings_Twijfel,
  tbl_Evaluatie_Linesman.Evo_SterkePunten,
  tbl_Evaluatie_Linesman.Evo_VerbeterPunten,
  tbl_Evaluatie_Linesman.Evo_Feedback,
  tbl_Supervisors.SPV_Naam
FROM tbl_Wedstrijden
INNER JOIN tbl_Supervisors ON tbl_Wedstrijden.Wed_SPV_ID = tbl_Supervisors.SPV_ID
INNER JOIN qry_Teams AS qry_Team_A ON tbl_Wedstrijden.Wed_TeamA = qry_Team_A.Team_ID
INNER JOIN qry_Teams AS qry_Team_B ON tbl_Wedstrijden.Wed_TeamB = qry_Team_B.Team_ID
INNER JOIN tbl_Evaluatie_Linesman ON tbl_Wedstrijden.Wed_ID = tbl_Evaluatie_Linesman.Evo_Wed_ID
INNER JOIN qry_Scheidsrechters ON tbl_Evaluatie_Linesman.Evo_Scheids_ID = qry_Scheidsrechters.Scheids_ID

(again, I couldn't test this because I don't have functional data in my test-database)

madref

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Can't create view (query)
« Reply #29 on: March 24, 2015, 09:44:13 pm »
It's a 1-on-1 from MS Access.

I will try it and let you know.
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave