"Simple" Query

[Nip351]

There more I think about this, or the conditions, the more complicated it gets.

I've attached a partial data set of the fields that are needed. Basically, there are records which may be related (family and subfamily fields). Many are related in sets of three. Some have no (family) relationships. I previously created a form filter to show all Starters/Parent records for each family. They are identified as follows:

FamilyID = 1 and SubfamlyID = 1
FamilyID = 1 and SubfamlyID = 2
FamilyID = 1 and SubfamlyID = 3
FamilyID = 2 and SubfamlyID = 1
FamilyID = 3 and SubfamlyID = 1
FamilyID = 3 and SubfamlyID = 2

Notice Family 1 has three children, family 2 has none and family 3 has two children. As mentioned before I made a filter to show all Parent records as Subfamilyid = 1. Now the user would update a OnHand field with a numeric value for all records where SubfamiulyID = 1. Once completed we need to update all records that ARE NOT subfamilyID of 1 under the same groups (familyid) where we previously updated the onhand value.

So, to identify the OnHand value we want to apply to other related records we would use:
select onhand, familyid from tblmain
where inpokedex = true and subfamilyid = 1;

We would get the attached records. I don't believe I need the pk, Idnum. I just included it in the txt example to show these are unique records.

Now, to prevent unnecessary updates we can exclude records that have only 1 record in a familyid set, such as IDnum 83, 95 and 108 to name a few. This can be done in the Update WHERE clause as ...'where SubFanilyID > 1' since the user updated all records that are SubFamilyID of 1. We are only including records that are Active (InPokedex = True) at all times.

My question is how do I update a table and refer back to records in the same table, I just cant seem to get the logic correct, although I know what I need to do, I just can create the SQL. Here is what I have:

UPDATE TBLMAIN
SET ONHAND = ( SELECT ONHAND
FROM TBLMAIN
WHERE INPOKEDEX = TRUE
AND SUBFAMILYID = 1)
WHERE SUBFAMILY >1 AND INPOKEDEX = TRUE

This can't be correct because whose ONHAND values am I updating? I almost think I need to include PK (Idnum field) because I seem to need to update individual records. I just don't see how to correlate the subquery to the main query to keep the OnHand values correct.

This is a query I will eventually apply to a command button in Access 2002.

I'm sorry this was so long, I think I needed to explain at least this much. Am I overthinking this to death?

 

[plog]

You lost me, and before you write another chapter, it would be better if you demonstrate your issue with sample data.

Post 2 sets of data:

A. Starting sample data. Include table and field names and enough data to cover all cases.

B. Expected results using A. Show me what you hope to end up with when you use the data in A.

 

[Nip351]

I only have limited time to post and/or review. I try to include everything I can in one go, so I apologize if you think its too much.

Ive added a second zip of a Start and Finish csv of about 20 or so records before and after their expected update.

Basically, the results for each familyID group will have the same OnHand value as the entered Onhand where subfamilyid = 1.

 

[plog]

Basically, the results for each familyID group will have the same OnHand value as the entered Onhand where subfamilyid = 1

That's not what your data presents. ID=25 uses the Onhand for subfamilyID=2.

So, to accomodate that you will need a subquery to determine the "first" subfamilyid to use. Below is that SQL:

SELECT TBLMAIN.FamilyID, Min(TBLMAIN.SubFamilyID) AS FirstSubFamilyID
FROM TBLMAIN
GROUP BY TBLMAIN.FamilyID;

Paste that into a new query object and name it "sub1". Then to obtain the data you want, use this query:

SELECT TBLMAIN.Idnum, TBLMAIN_1.OnHand, sub1.FamilyID, TBLMAIN.SubFamilyID
FROM TBLMAIN INNER JOIN (sub1 INNER JOIN TBLMAIN AS TBLMAIN_1 ON (sub1.FirstSubFamilyID = TBLMAIN_1.SubFamilyID) AND (sub1.FamilyID = TBLMAIN_1.FamilyID)) ON TBLMAIN.FamilyID = sub1.FamilyID;

 

[Nip351]

In reference to "That's not what your data presents. ID=25 uses the Onhand for subfamilyID=2."

Thanks for verifying the sample data. It is possible to have a higher Idnum with a lower subfamilyid for the same familyID. Such as:
Idnum FamilyID SubFamilyID
154 10 1

In other words, this record (product) exists, but is not yet available.

This worked fine, now I need to see about automation.