Hi all, It's been a while.
I’m trying to avoid Make Table queries. The result needed is a where records that match are grouped and totalled along with a list of the Refs (item references) for those records that are the same specification.
This is a simplified version, in reality it is, as always, more complicated
OrdID:Ref:Qty: Color:High:Wide
1001: “D1”: 2: Red: 100: 200
1001: “D2”: 1: Blue: 100: 200
1001: “D3”: 1: Red: 100: 200
Should become.
1001,3, Red, 100, 200, “D1, D3”
1001,1, Blue, 100, 200, “D2”
Allen Browne’s ConcatRelated() function is great (thanks Allen) but it puts all of the Refs in every record (the result is two records with “D1, D2, D3” in both) when I need only the Refs where the specifications are identical.
I have a solution that works but it feels very amateur (accurately reflecting my ability!) and the temp tables cause front end bloat.
1, Make table [WithRefs] using a Select query of all fields including the [Ref] field.
2, Make table [GroupedRefs] using a Totals query of the same fields but [Ref] is excluded and replaced by (AllRefs: “”) so that the result includes is an empty column.
3, Open both tables using DAO.
4, For each record in [GroupedRefs] loop thru [WithRefs].
5, For each record in [WithRefs], if all fields match with all fields in [GroupedRefs] then append WithRefs.Ref to GroupedRefs.AllRefs adding a trailing delimiter. (similar to Allrefs = AllRefs & Ref & “; “)
6, Run reports based on [GroupedRefs] which now holds all of the Refs for each unique specification and each Ref appears only once.
My problem is,
1, The nested If – End If’s in step 5 to compare well over 20 variables are long and ugly.
2, I can’t put this down, surely there is a better way.
Is this the only solution or should I be coming at it from a different direction. I’d be grateful for your comments either way and thanks in advance.
I’m trying to avoid Make Table queries. The result needed is a where records that match are grouped and totalled along with a list of the Refs (item references) for those records that are the same specification.
This is a simplified version, in reality it is, as always, more complicated
OrdID:Ref:Qty: Color:High:Wide
1001: “D1”: 2: Red: 100: 200
1001: “D2”: 1: Blue: 100: 200
1001: “D3”: 1: Red: 100: 200
Should become.
1001,3, Red, 100, 200, “D1, D3”
1001,1, Blue, 100, 200, “D2”
Allen Browne’s ConcatRelated() function is great (thanks Allen) but it puts all of the Refs in every record (the result is two records with “D1, D2, D3” in both) when I need only the Refs where the specifications are identical.
I have a solution that works but it feels very amateur (accurately reflecting my ability!) and the temp tables cause front end bloat.
1, Make table [WithRefs] using a Select query of all fields including the [Ref] field.
2, Make table [GroupedRefs] using a Totals query of the same fields but [Ref] is excluded and replaced by (AllRefs: “”) so that the result includes is an empty column.
3, Open both tables using DAO.
4, For each record in [GroupedRefs] loop thru [WithRefs].
5, For each record in [WithRefs], if all fields match with all fields in [GroupedRefs] then append WithRefs.Ref to GroupedRefs.AllRefs adding a trailing delimiter. (similar to Allrefs = AllRefs & Ref & “; “)
6, Run reports based on [GroupedRefs] which now holds all of the Refs for each unique specification and each Ref appears only once.
My problem is,
1, The nested If – End If’s in step 5 to compare well over 20 variables are long and ugly.
2, I can’t put this down, surely there is a better way.
Is this the only solution or should I be coming at it from a different direction. I’d be grateful for your comments either way and thanks in advance.
