If you are familiar with Pokemon (Go) then read on, if not, start from Part II as this may be too much or irrelevant info.
Part I - If you are familiar with Pokemon (Go)
I have a growing functionality database for Pokemon Go that does quite a bit. Right now, Im trying to figure out how I can update candy values for all "Starter" Pokemon and later just update all related Pokemon to which I have acquired. For example, if I have Pichu with 5 candies I want to be able to filter by use of a single button, then scroll through and only see Pichu (not Pikachu or Raichu) and update its candy, as well as all other starter Pokemon. Once I complete the entire roster of starters I wanted this value to update for all related Pokemon (all non-Starters) that I have acquired. I set my own FamilyID and SubFamilyID values as I needed to assign values for all Pokemon to be able to group them as well as identify starters and children. See example on the bottom for a example of some of the records.
Part II - If you are not familiar with Pokemon (Go)
I have a Main Form that list the primary fields for each record. Each record can be related to another (up to five times), if related at all. I wanted to know a way I could update a single field with a new value and then it would update all related records with that same value for the same field.
For example, records Apple, Banana and Pear are all related. This is identified under the FamilyID field as 1. Cherry, Orange are related as 2. Coconut, sadly, is alone, but identified as 3. Additionally each record also has a subfamily ID, which simply increments by 1, so Apple, banana and Pear are 1,2,3 respectively and Coconut will be 1since its the only record in the family series and Cherry, Orange will be 1,2 respectively.
Since Coconut has no family members it will be excluded (on the update portion) because I won't include any subfamily of 1 since that is what will be updated bu the users, so eventually we will only update where subfamilyid <>1 and InPokedex =TRUE, well get to InPokedex later.
Two things need to happen.
1. We need to filter the form to only show where subfamilyID = 1 and where InPokedex = TRUE (Inpokedex True means we have it) - By enabling a filter we are only scrolling through records we have and that we want to update. For the users sake, Id like this to be a button that just says "Show Starters" and it will filter for records that have a SubFamilyID = 1 and display in the current form, if possible.
2. Once all updates are done on the Starters, we need to update the others OnHand values where their familyID = familyID of the record we updated with a subfamilyid =1 and where the InPokedex=True. It would be Update tblMain set OnHand = [OnHand] where familyID=[FamilyID] and subfamilyid <>1 and InPokedex = True.
For Part I or II, this is the data for a dozen records out of 251. Idnum is the PK. These are unfiltered.
Idnum OnHand FamilyID SubFamilyID InPokedex
1 93 1 1 TRUE
2 93 1 2 TRUE
3 93 1 3 FALSE
4 22 2 1 TRUE
5 22 2 2 TRUE
6 22 2 3 TRUE
7 105 3 1 TRUE
8 105 3 2 TRUE
9 105 3 3 FALSE
10 66 4 1 TRUE
11 66 4 2 TRUE
12 66 4 3 FALSE
Filtered:
Showing SubfamilyID = 1 and InPokedex = TRUE (These are the records the user will update OnHand values, once completed the onhand values will be updated to match these with with same FamilyID where the SubFamily <>1 and where InPokedex=TRUE.
Idnum OnHand FamilyID SubFamilyID InPokedex
1 93 1 1 TRUE
4 22 2 1 TRUE
7 105 3 1 TRUE
10 66 4 1 TRUE
I hope this makes sense. This is in Microsoft Access 2002.
Are you into Pokemon Go and want to see what this generated for reports as well as the main form, check the screen-shots out at: drive.google.com/drive/folders/0Bzml8R4b8NaweFNCSDBpR2pvLW8?usp=sharing
Part I - If you are familiar with Pokemon (Go)
I have a growing functionality database for Pokemon Go that does quite a bit. Right now, Im trying to figure out how I can update candy values for all "Starter" Pokemon and later just update all related Pokemon to which I have acquired. For example, if I have Pichu with 5 candies I want to be able to filter by use of a single button, then scroll through and only see Pichu (not Pikachu or Raichu) and update its candy, as well as all other starter Pokemon. Once I complete the entire roster of starters I wanted this value to update for all related Pokemon (all non-Starters) that I have acquired. I set my own FamilyID and SubFamilyID values as I needed to assign values for all Pokemon to be able to group them as well as identify starters and children. See example on the bottom for a example of some of the records.
Part II - If you are not familiar with Pokemon (Go)
I have a Main Form that list the primary fields for each record. Each record can be related to another (up to five times), if related at all. I wanted to know a way I could update a single field with a new value and then it would update all related records with that same value for the same field.
For example, records Apple, Banana and Pear are all related. This is identified under the FamilyID field as 1. Cherry, Orange are related as 2. Coconut, sadly, is alone, but identified as 3. Additionally each record also has a subfamily ID, which simply increments by 1, so Apple, banana and Pear are 1,2,3 respectively and Coconut will be 1since its the only record in the family series and Cherry, Orange will be 1,2 respectively.
Since Coconut has no family members it will be excluded (on the update portion) because I won't include any subfamily of 1 since that is what will be updated bu the users, so eventually we will only update where subfamilyid <>1 and InPokedex =TRUE, well get to InPokedex later.
Two things need to happen.
1. We need to filter the form to only show where subfamilyID = 1 and where InPokedex = TRUE (Inpokedex True means we have it) - By enabling a filter we are only scrolling through records we have and that we want to update. For the users sake, Id like this to be a button that just says "Show Starters" and it will filter for records that have a SubFamilyID = 1 and display in the current form, if possible.
2. Once all updates are done on the Starters, we need to update the others OnHand values where their familyID = familyID of the record we updated with a subfamilyid =1 and where the InPokedex=True. It would be Update tblMain set OnHand = [OnHand] where familyID=[FamilyID] and subfamilyid <>1 and InPokedex = True.
For Part I or II, this is the data for a dozen records out of 251. Idnum is the PK. These are unfiltered.
Idnum OnHand FamilyID SubFamilyID InPokedex
1 93 1 1 TRUE
2 93 1 2 TRUE
3 93 1 3 FALSE
4 22 2 1 TRUE
5 22 2 2 TRUE
6 22 2 3 TRUE
7 105 3 1 TRUE
8 105 3 2 TRUE
9 105 3 3 FALSE
10 66 4 1 TRUE
11 66 4 2 TRUE
12 66 4 3 FALSE
Filtered:
Showing SubfamilyID = 1 and InPokedex = TRUE (These are the records the user will update OnHand values, once completed the onhand values will be updated to match these with with same FamilyID where the SubFamily <>1 and where InPokedex=TRUE.
Idnum OnHand FamilyID SubFamilyID InPokedex
1 93 1 1 TRUE
4 22 2 1 TRUE
7 105 3 1 TRUE
10 66 4 1 TRUE
I hope this makes sense. This is in Microsoft Access 2002.
Are you into Pokemon Go and want to see what this generated for reports as well as the main form, check the screen-shots out at: drive.google.com/drive/folders/0Bzml8R4b8NaweFNCSDBpR2pvLW8?usp=sharing