'merging'/'grouping' records in a query (1 Viewer)

marvo

Registered User.
Local time
Today, 13:46
Joined
Sep 23, 2018
Messages
20
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!
 

marvo

Registered User.
Local time
Today, 13:46
Joined
Sep 23, 2018
Messages
20
Hi Minty - thanks for the quick and helpful reply!
I've had a read through the article (bearing in mind my VBA is not great at all) and looks like this process will indeed concatenate the 2 umpires names at the end of the row:
Fixture1, 24/09/18, Mens 1st XI, Home, School pitch, John Smith, Davy Jones

but if I understand correctly, "John Smith, Davy Jones" will be a single concatenated string.
I need these 2 names to be in separate fields, so I can then use those values individually, in a later process, e.g. in a report
Or did I misunderstand?!

------
Update:
I have added the ConcatRelated function that you pointed me to.
It seems to be OK, I saved the module as 'ConcatRelated', it compiled ok

In the query expression builder it appears as expected:
ConcatRelated(«strField», «strTable», «strWhere», «strOrderBy», «strSeparator»)

When I run the query, I get the unexpected error message:
"Undefined function 'ConcatRelated' in expression"

Which seems pretty odd, given that it showed up in the expression builder?!
Even if it had a bug, it should still not be 'unrecognised'.
 
Last edited:

Minty

AWF VIP
Local time
Today, 13:46
Joined
Jul 26, 2013
Messages
10,354
You can't call the module the same as the function name, Access will throw a hissy fit as you have discovered.

Create a new Module - call it modBasicFunc or similar. Copy and paste the code into that, delete the old module, then run a compact and repair. It should then work.
 

isladogs

MVP / VIP
Local time
Today, 13:46
Joined
Jan 14, 2017
Messages
18,186
You can't call the module the same as the function name, Access will throw a hissy fit as you have discovered.

Create a new Module - call it modBasicFunc or similar. Copy and paste the code into that, delete the old module, then run a compact and repair. It should then work.

It should work fine if you just rename the module then compact (or close / reopen)
 

marvo

Registered User.
Local time
Today, 13:46
Joined
Sep 23, 2018
Messages
20
OK the new module is now working, saved with a different name - thanks!

A number of issues to address now that it runs through:

1 - I haven't got it to work properly yet! -
The Fixtures table (holds main fixture info like FixtureID, time, date, place etc) is related to Fixtures/Umpires (which just stores keys FixtureID and UmpireID to pull together Umpires for certain Fixtures) which in turn is related to Umpires table (which has UmpireID, umpires name, email etc)
When I run the new ConcatRelated function - I can display and concat UmpireIDs, but I can't get down to the UmpireName level. I'm assuming this is because the 'Where' clause needs to be on FixtureID and FixtureID does not exist in the Umpire table - only in the intermediate FixturesUmpires table? Thoughts?

2 - This process doesn't solve the original problem of actually combining the records returned in the query.
I still get 20 records returned (10 games x 2 umpires) it's just that each row now has the concatenated umpire names at the end. Perhaps once I get 1. above working, I can remove Umpires from query so I only get the original 10 Fixture records?

3 - As per my previous - the umpire names that result from the ConcatRelated function that Minty showed me, are a single concatenated string, rather than having been parsed into separate fields - which I need

Gonna be a long night! Thank in advance!
 

Minty

AWF VIP
Local time
Today, 13:46
Joined
Jul 26, 2013
Messages
10,354
The first 1 is hopefully simple - Create a query with the fixture ID and the Concatenated Umpire name only in it. Save that and then now join that saved query to your fixtures table in a new query.

2. should be solved by 1.

3. This is trickier, and won't be solved by concatenating. However investigate a cross tab query, I'm sure this can achieved by that method. But it depends what your end result needs to be used for.
 

marvo

Registered User.
Local time
Today, 13:46
Joined
Sep 23, 2018
Messages
20
Hi Minty
I'm abandoning the ConcatRelated method. It's been an interesting exercise and I've learned something, but it's not going to give me the result I need.

The crosstab I investigated, but I can only get the umpire names as column headings, not as values in the table, which requires a sum/avg/count etc, like a pivot table in excel.

Back to square 1 but thanks for your help

I'm certain this must be doable, can't belive I'm unique in wanting to merge the values of 2 records!
Will report back...
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:46
Joined
Sep 21, 2011
Messages
14,041
How about using the concatrelated function to group the umpires and then have another function to split them, but with an indicator as to which element to return.? Long winded I know, but worth a try?
 

marvo

Registered User.
Local time
Today, 13:46
Joined
Sep 23, 2018
Messages
20
Yescv I've considered the concatenate and then spilt/parse approach... It's one of a few options on a list to try!
Currently trying to write VBA to step through the record set and for each umpire name it finds with the same fixtureID, do a SQL insert into a 'merged record' table.
The first UmpireName it finds should be inserted into field "UmpireName(1)"
Then with next row, increase counter by 1
Then (providing the fixture ID is the same), insert the next UmpireName found into field "UmpireName(2)"

great theory, but I can't seem to use a variable for the field name (or at least find the correct syntax) in the SQL INSERT statement...
INSERT INTO tablename, variablefieldname (ie. 'UmpireName1' then 'UmpireName2', etc

I realise the code isn't correct, just trying to explain the process/problem

Thanks for any ideas on how to use a variable for the field name in an INSERT INTO statement!
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:46
Joined
Sep 21, 2011
Messages
14,041
Investigate the EVAL function

TBH I'd just use the Concatenate/Split method, as I know it would work.
So in design view you'd have something along the lines of
Code:
GetUmpire(ID,0)     GetUmpire(ID,1)   etc
and then use the concatrelated function in the GetUmpire function?, or even in the call of the function GetUmpire(Concatrelated(),0)
 

Users who are viewing this thread

Top Bottom