Insert Into Query for 1:m Relationship (1 Viewer)

[ebs17]

This date picker was not available for me (Acc2010), the reference was annoying. That's why I eliminated it, as well as your preliminary checks for completeness. These are things that you can add independently.

 

[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

 

[Pat Hartman]

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.

I guess you've never encountered a m-m relationship where both sides were the same table and which also included intersection data. A chess match which records the two players as white and black along with date, location, winner is one. Another is a marriage which records the two partners as husband and wife or if you're woke as partner1 and partner2 along with date and location also.

The OP is currently using a 6 table schema which makes data entry and analysis more difficult.

 

[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!!

 

[Pat Hartman]

The suggestion was to use a union query when you were doing analysis that didn't depend on whether the player used the white pieces or the black. Obviously if you are analyzing performance when the player is white or black specifically, you would not need the union.

PS
Short of code, using your current schema, I'm not sure what is keeping you from assigning a win or a loss to both sides. That was the other reason for the junction table with two links to the player table. That model gives you one spot for the winning ID. Winner either equals the white playerID or the black playerID. When Winner is null, it is a tie. If you need other options, you need an additional field.

 

[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