Insert Into Query for 1:m Relationship

[silentwolf]

Hi guys,

I have created a little Database for our Chess Club with below shown Table Structure.

This is all working well and I get all relevant Information out of that database using Queries, i.e. who played on what date, who played and who won with what color played.

However at present I record those games with ChessEntryForm01
where I need to enter Player White in one line and Player Black in another with the corresponding win or loss or draw value.

But I would like to speed the entry up and use a Form like EntryForm02. where I did create a value List of 1-0, 0-1 or 0-0 for either White won, Black won or a draw representing a 0-0 value.

To save that game played I would need a Insert Into Statement I would imagine.

So this Insert Into Statement would need to enter Location and Date into the Games Table
and for White Player and Black Player Into the GamesPlays table with the relevant GameID for those two Players.

Could someone help me with this please?

Many thanks

Albert

 

[theDBguy]

Perhaps you could also achieve that without using code by using multiple subforms. Just a thought...

 

[silentwolf]

Hi,
thanks for your input!
But isn't that what I have with ChessEntryForm01?
Or do you have a different thought about that?

Further I think if I have to insert two players with each of their results wrong inputs can be entered.
Like both players have won or lost.. as nothing is preventing it with that kind of EntryForm.

But I believe you think of a different type of subforms as I have it currently shown.

 

[ebs17]

Is Formular1 an unbound form? That would be practical here.

Then you need three append queries, ideally encapsulated in one transaction.
1) LocationID and date in Games, determining the generated GameID, see

Insert Records with VBA and get new autonum value - different variants and possible problems

Inspired by https://www.access-programmers.co.uk/forums/threads/how-to-avoid-the-domain-functions-in-ms-access.330010/ I am trying to compare insert variants in VBA in this thread. First of all: I only discuss the variants that can be used with Jet/ACE (e.g. executing procedures in an active...

www.access-programmers.co.uk

2) Add first player in GamePlays
3) Add the second player with the color and result reversed

 

[plog]

I'm the guy who tells people to normalize their table structure when their question is "How do I change the background color of my form?". So really listen when I say:

I think you have overnormalized your data.

I think you need one table that resembles your form. Definitely not 3 tables for Games/GamePlays/GameResults, all that data should go into one table. What exactly is the difference between Games and GamePlays? Further, I'm not abreast of current events in chess but is 'red' and 'blue' being discussed as 3rd and 4th possibilities as colors? I think Games/GamePlays/GameResults/Colors should all go away and be replaced by a new table like so:

Games
game_ID, autonumber, primary key
ID_Location, number, foreign key to Locations
game_Date, date, same as GameDate currently
game_White, number, foreign key to Players table
game_Black, number, foreign key to Players table
game_Result, ?, same as GameResult currently.

I think that 1 table replaces your 4 and lends itself to an easy form like the one you want. Again, I'm the strongest advocate for normalization on this board, but I think you have overdone it and with just 2 options for players this is a case where it is ok to technically break normalization and hardcode white/black as prefixes onto field names.

 

[ebs17]

@plog
In chess there are only the playing colors white and black. You can know the "Game of Kings".

Your suggestion would make input easier. However, this would make evaluation extremely complicated, and evaluation would then have to run across many records.
For example, determine a player's points. It doesn't make sense to have to look for the values from several columns.

 

[plog]

Thanks for trying to explain my post to me. I'm sorry you were unable to understand it.

this would make evaluation extremely complicated,

I extremely disagree.

 

[silentwolf]

Hi guys,

thanks for your inputs!

@plog
I did some research on how others create such a Database
and it was mentioned that with that type of structure queries would be a bit easier then self referencing to Player Table.

https://stackoverflow.com/questions/21174138/designing-a-database-games-played-table

I am not saying that this is the case or doubt your opinion on this but I found that with some sample Data I was able to query Data pretty nicecly.
The difference of Games and GamePlays is just the linked table between Games and Players.

@ebs17
thanks for the Link!
I will look into it but will need to do it tomorrow as it is getting late at my end for today.

For now I thank you both and will look into it more!

Cheers

Albert

 

[Pat Hartman]

I agree with Plog, the games table should include the white playerID and the black playerID. This does not complicate the analysis since you use a union query to make one set of data for both black and white so it doesn't matter which position a player played in a game. This leaves you with 4 tables rather than 6.

 

[silentwolf]

Hi Pat,
thanks also to you! I will try it with some sample data and see how I go!

Cheers

 

[ebs17]

since you use a union query

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?

 

[ebs17]

I will try it with some sample data

Maybe you can provide an example file. Some things you have to see and actually do. Executing in one's own abstraction has its limits.

 

[silentwolf]

Hi,

hiere is a sample Database with some dummy data and some queries.

Cheers

 

[silentwolf]

Hi,
I have forgotten to insert Forms Sorry!
Also I did update the crosstab Query with Nz.


Cheers

Albert

 

[ebs17]

cboGameResult should have something to do with the GameResults table.

Note: In tournament-style games, points are awarded (1 - won, 0.5 - draw, 0 - lost), with the highest point scorer being the winner.

If you want to take something like this into account, I would use the values mentioned as speaking keys for GameResID instead of the autonum values. You could sum the key directly in GamePlays and do without an extra join to GameResults.

Taking the points mentioned into account influences the insertion of the records.

 

[silentwolf]

Hi Eberhard,

thanks for your answer!

So that would mean I create a GameResID as double field?
And then in the GamePlays table as the FK also as double right?
Then I would only need one field in the GameResult table insead of two fields?

Furthermore I was not able to work out the link you did send me and I can not quite figure out how to implemant that into the frmEntryNew.

Maybe you could explain what I need to to .. In the meantime I will read over it again maybe I will get it.

Cheers

 

[ebs17]

as double field

Better Currency (without currency format) because it is a fixed point number.

Entry in GameResults, for example
1 -- 1-0 -- won
0 -- 0-1 -- lost
0.5 -- 0-0 -- draw
This means you can keep the previous user-friendly display in the form.

If this is taken into account, you can then continue with the insertion action.

 

[silentwolf]

I have changed it as you mentioned.
But I should keep the PK on GameResID right?

And could you please help me with the frmEntryNew "Form"

Cheers

P.S. I think you are also a chess player? knowing about the scoring points?

 

[ebs17]

Knowledge of chess is part of general education. As a student I also played a little more intensively, but that was decades ago.
It's only fun when you're playing against an opponent who's roughly equal, but then you need a lot of time for a game.

 

[silentwolf]

Hallo Eberhard,

vielen lieben Dank!
Leider sehe ich das Datumsfeld nicht mehr ist da etwas beim entpacken daneben gegangen?

LG
Albert