Insert Into Query for 1:m Relationship

[silentwolf]

No Problem Eberhard,

got it to run with a normal date field and updated the code and it runs perfektly!

Many many thanks!

P.s.

Just got one more question regarding a query.

When I like to count who played against another player how would you create a query for that?

Example: Player A played againd Player B 10 times and won 5 games lost 2 and draw 3 games

I have tried to find an example but was not able to find any information on how to get this kind of query out of the database .

Would you have an example oder can point me to the right direction on that please?

Cheers

 

[ebs17]

who played against another player

The base query would look something like this:

SELECT
   P1.PlayerID AS Player1,
   P2.PlayerID AS Player2,
   P1.GameResID
FROM
   GamePlays AS P1
      INNER JOIN GamePlays AS P2
      ON P1.GameID = P2.GameID
WHERE
   P1.PlayerID < P2.PlayerID

This also shows the function of the seemingly superfluous Games table: It supplies the GameID in order to get the two records of a game into one record, so to speak the counterpart to a union query based on the proposed "simplified" data schema. However, the damage of a JOIN is significantly less than that of a UNION.

Example: Player A played againd Player B 10 times and won 5 games lost 2 and draw 3 games

SELECT
   P1.PlayerID AS Player1,
   P2.PlayerID AS Player2,
   Abs(SUM(P1.GameResID = 1)) AS won,
   Abs(SUM(P1.GameResID = 0.5)) AS draw,
   Abs(SUM(P1.GameResID = 0)) AS lost
FROM
   GamePlays AS P1
      INNER JOIN GamePlays AS P2
      ON P1.GameID = P2.GameID
WHERE
   P1.PlayerID < P2.PlayerID
GROUP BY
   P1.PlayerID,
   P2.PlayerID

 

[ebs17]

I guess you've never encountered a m-m relationship where both sides were the same table

You're guessing wrong.
If this is related to the direct result of a union query, then you are guessing correctly.

 

[silentwolf]

Thanks alot Eberhard,

I will try that and see how I will go!

Cheers!!

 

[silentwolf]

Hi guys,

I have been working on this database but came accross some issue I am not able to work out and what is behind the behaviour.

When I open the newEntryForm and change the date on the form to todays Date for example the first game Entry gets changed to that date.
All remaining Data keep the correct dates??

How can that be possible?
I set a break point in the code but it not even fireing anywhere.

Very confusing on how that could be possible?

Has someone a idea what that issue could be?

Cheers

 

[ebs17]

When I open the newEntryForm

If this is the form discussed above: Only new records are created - 3 records per transaction, one of which has a date - no changes are made.
So it should be clear to check where you got this date from.

 

[silentwolf]

No Problem,

got it working!
The issue was that the Date was bound to the form. I changed it to unbound and it is working now!

Sorry

 

[silentwolf]

If this is the form discussed above: Only new records are created - 3 records per transaction, one of which has a date - no changes are made.
So it should be clear to check where you got this date from.

@ebs17
you gave me some queries on #23, could you please tell me how to change it to the Nicknames of the Players rather then the ID's please?

I tried jut inserting the PlayerNicknames to your queries but not sure if that is the right way of doing it.

Many thanks

 

[ebs17]

How to get from PlayerID to PlayerNickname?
Link the necessary table Players and use its fields.

SELECT
   N1.PlayerNickName AS Nick1,
   N2.PlayerNickName AS Nick2,
   Abs(SUM(P1.GameResID = 1)) AS won,
   Abs(SUM(P1.GameResID = 0.5)) AS draw,
   Abs(SUM(P1.GameResID = 0)) AS lost
FROM
   (
      (GamePlays AS P1
         INNER JOIN GamePlays AS P2
         ON P1.GameID = P2.GameID
      )
      INNER JOIN Players AS N1
      ON P1.PlayerID = N1.PlayerID
   )
   INNER JOIN Players AS N2
   ON P2.PlayerID = N2.PlayerID
WHERE
   P1.PlayerID < P2.PlayerID
GROUP BY
   N1.PlayerNickName,
   N2.PlayerNickName

Grouping via PlayerNickName suggests setting an index on this field - if your users are busy and have a lot of games to evaluate.

 

[silentwolf]

Many thanks Eberhard!

I will do that cheers!

 

[silentwolf]

How to get from PlayerID to PlayerNickname?
Link the necessary table Players and use its fields.

SELECT
   N1.PlayerNickName AS Nick1,
   N2.PlayerNickName AS Nick2,
   Abs(SUM(P1.GameResID = 1)) AS won,
   Abs(SUM(P1.GameResID = 0.5)) AS draw,
   Abs(SUM(P1.GameResID = 0)) AS lost
FROM
   (
      (GamePlays AS P1
         INNER JOIN GamePlays AS P2
         ON P1.GameID = P2.GameID
      )
      INNER JOIN Players AS N1
      ON P1.PlayerID = N1.PlayerID
   )
   INNER JOIN Players AS N2
   ON P2.PlayerID = N2.PlayerID
WHERE
   P1.PlayerID < P2.PlayerID
GROUP BY
   N1.PlayerNickName,
   N2.PlayerNickName

Grouping via PlayerNickName suggests setting an index on this field - if your users are busy and have a lot of games to evaluate.

Hi Eberhard,

the query works great in MS Access thanks again for your Help!

I am not sure if I can asked that here on this forum but I like to use this Query in SQL Server..


I am getting Errors on the following statements
Abs(SUM(P1.GameResID = 1)) AS won,
Abs(SUM(P1.GameResID = 0.5)) AS draw,
Abs(SUM(P1.GameResID = 0)) AS lost

Incorrect Syntax Near ')' shown in red.. Expection AND or OR,

I did try to change it or find something on the net but without any luck..

Maybe you know how this syntax need to be changed in SQL Server?

Cheers

Albert

 

[ebs17]

I'm not familiar with T-SQL, so I waited. But no one else has stepped in yet.
Therefore my recommendation: disassemble the logic and test the individual steps.

(P1.GameResID = 1)

This is a logical expression in Jet and returns -1 if True. Summing the 1 contents gives the number, the ABS function only removes the negative sign.

If a logical expression returns 1 instead of -1 for True, the ABS function can be omitted.
But you could also multiply a negative result by -1.

 

[silentwolf]

Hi Eberhard,

many thanks for your reply!
I will try that out and see if I can manage.

Cheers

 

[Solo712]

The need for a union query indicates a structural error in the majority of cases, even more so if this is the very first step in the processing.
The union query performs a normalization step => SAME information into the SAME column. In addition to the player column, the result and other things also have to be taken into account. If further normalization has to be carried out in the first step of processing via query - what is the previous normalization worth?

Everything that is then based on the union query no longer offers index usage. This is how you specifically prevent possible performance. Is that your recommendation?

Eberhard,
Pat and plog are right. the union query would by the correct way to go in this case because the 1:m relationship btw the Games and Games Played tables is always 1:2. This makes the Color & Result tables quite unnecessary and silly. Note that a win by White is a loss for Black and if John is White in the game, his opponent has to be Black. So you generate unnecessary duplicate records for situations which are obverse of each other. The union structure is simply preferable given by the fact that there are two sides in the game and never more or less than that.

Best,
Jiri