Hi
My query returns all the details of a hockey match, on a given date
e.g. Date, Place, Time, Team, Competition, etc,
The hockey club has 10 teams, so for any given date, the query returns 10 rows with the above info. All fine.
On the [FixtureDetails] form that is used to enter all this info about the match, I have now added a Subform [UmpireDetails].
This subform simply allows me to add from a drop-down combo, the names of the Umpires that are appointed to the match in question (called from an [UmpireDetails] table).
I've done it this way, because for any given match there could be anywhere between 1 and 5 umpires appointed to a game. It's not fixed and I, therefore, didn't want [Umpire1], [Umpire2], [Umpire3], etc fixed on my main form.
So - assuming I add 2 umpires to each of the 10 matches, - when I run the query, I no longer get 10 rows returned, I get 20 rows returned.
2 rows for each match with identical details, except for the UmpireName.
OK I get that, I understand that the tables can't show both umpires in one row.
But, I now want to 'denormalise'/group the records together.
i.e. my query returns:
Fixture1, 24/09/18, 13:00, Mens 1st XI, Home, School pitch, John Smith
Fixture1, 24/09/18, 13:00, Mens 1st XI, Home, School pitch, Davy Jones
How can I merge these to simply get one row:
Fixture1, 24/09/18, Mens 1st XI, Home, School pitch, John Smith, Davy Jones
Thanks for any advice!
My query returns all the details of a hockey match, on a given date
e.g. Date, Place, Time, Team, Competition, etc,
The hockey club has 10 teams, so for any given date, the query returns 10 rows with the above info. All fine.
On the [FixtureDetails] form that is used to enter all this info about the match, I have now added a Subform [UmpireDetails].
This subform simply allows me to add from a drop-down combo, the names of the Umpires that are appointed to the match in question (called from an [UmpireDetails] table).
I've done it this way, because for any given match there could be anywhere between 1 and 5 umpires appointed to a game. It's not fixed and I, therefore, didn't want [Umpire1], [Umpire2], [Umpire3], etc fixed on my main form.
So - assuming I add 2 umpires to each of the 10 matches, - when I run the query, I no longer get 10 rows returned, I get 20 rows returned.
2 rows for each match with identical details, except for the UmpireName.
OK I get that, I understand that the tables can't show both umpires in one row.
But, I now want to 'denormalise'/group the records together.
i.e. my query returns:
Fixture1, 24/09/18, 13:00, Mens 1st XI, Home, School pitch, John Smith
Fixture1, 24/09/18, 13:00, Mens 1st XI, Home, School pitch, Davy Jones
How can I merge these to simply get one row:
Fixture1, 24/09/18, Mens 1st XI, Home, School pitch, John Smith, Davy Jones
Thanks for any advice!