Ok, this is deep...
I have a working query using ConcatRelated, which is working great with one small piece I want corrected.
This is a database I made for my nephews that play Pokemon. It allows them to enter all aspects to track, see what they need, candies, CP, etc. There are about a dozen and a half reports, such as this one, which lists all Pokemon "Starters" which then lists all other Pokemon that are evolved from that first starter. In this, Starters refers to the initial Pokemon which may evolve into others.
Now in the DB, the PK is the Pokemon ID and each Pokemon has a family ID and SubFamilyID. The Family ID keeps all related Pokemon Together, such as a family would be together (Mom, Dad and all children, grandchildren, etc.). The SubFamilyID keeps tabs on each sequential evolve (such as Mom1,kid2, kid3, kid4 - they are sequential in the same FamilyID.)
So, a piece of the DB would look like this.
(tblmain.jpg)
Not all can evolve, some evolve once or more.
The issue is that when using the Concatrelated in this query I am limiting only showing subfamily of 1, and then concatrelated all related members - the problem is it shows the 1 again in the concatrelated list as shown below.
(Spawns.JPG)
Basically, in the Spawns column, it should not list the Pokemons name (or ID) for the Pokemon under the column Starter.
This is the query I am using:
I suspect I need a subquery to capture only subfamilyid of 1, then in the concatrelated only include where it is not 1...? I just don't know how to show where subfamilyid <> 1 - it was luck and magic to get this working.
I neglected to mention this is in Access 2002.
I have a working query using ConcatRelated, which is working great with one small piece I want corrected.
This is a database I made for my nephews that play Pokemon. It allows them to enter all aspects to track, see what they need, candies, CP, etc. There are about a dozen and a half reports, such as this one, which lists all Pokemon "Starters" which then lists all other Pokemon that are evolved from that first starter. In this, Starters refers to the initial Pokemon which may evolve into others.
Now in the DB, the PK is the Pokemon ID and each Pokemon has a family ID and SubFamilyID. The Family ID keeps all related Pokemon Together, such as a family would be together (Mom, Dad and all children, grandchildren, etc.). The SubFamilyID keeps tabs on each sequential evolve (such as Mom1,kid2, kid3, kid4 - they are sequential in the same FamilyID.)
So, a piece of the DB would look like this.
(tblmain.jpg)
Not all can evolve, some evolve once or more.
The issue is that when using the Concatrelated in this query I am limiting only showing subfamily of 1, and then concatrelated all related members - the problem is it shows the 1 again in the concatrelated list as shown below.
(Spawns.JPG)
Basically, in the Spawns column, it should not list the Pokemons name (or ID) for the Pokemon under the column Starter.
This is the query I am using:
Code:
SELECT tblmain.Idnum, tblmain.PName AS Starter,
ConcatRelated("""#"" &format([idnum],""000" & " "") & Pname","[tblmain]","familyid=" & [FamilyID]) AS Spawns, tblmain.SubFamilyID
FROM tblmain
WHERE (((tblmain.SubFamilyID)=1));I suspect I need a subquery to capture only subfamilyid of 1, then in the concatrelated only include where it is not 1...? I just don't know how to show where subfamilyid <> 1 - it was luck and magic to get this working.
I neglected to mention this is in Access 2002.
Last edited:
