AndyC1000
asked on
Query to update fields with data from table rows above with the same dwelling type
Dear all,
I posted a similar question a while ago see the link below. The vb code populates the record where all values are 0 with the populated record above. The issue is now I need to check the dwelling type of the record with all 0's and populate with the record above with the same dwelling type. I'm unsure of how to make this change. I'm happy with the vb solution although if there is another approach I would also be interested.
https://www.experts-exchange.com/questions/28083252/Query-to-update-fields-with-data-from-table-rows-above.html
I've created two tables below with the original dataset and the new results I'm trying to achieve. The dwelling types aren't grouped so the code might need to search many records above before it finds the correct dwelling type. The HCFMDCode is updated to B when a change is made.
The original table looks like this:
ID DwellingType Couple Family Other HCFMDCode
1 House 0.433 0.567 0 A
2 House 0.223 0.388 0.388 A
3 House 0 0 0 A
4 House 0 0 0 A
5 Unit 0.355 0.332 0.332 A
6 Unit 0 0 0 A
The final results should produce:
ID DwellingType Couple Family Other HCFMDCode
1 House 0.433 0.567 0 A
2 House 0.223 0.388 0.388 A
3 House 0.223 0.388 0.388 B
4 House 0.223 0.388 0.388 B
5 Unit 0.355 0.332 0.332 B
6 Unit 0.355 0.332 0.332 B
Thanks
I posted a similar question a while ago see the link below. The vb code populates the record where all values are 0 with the populated record above. The issue is now I need to check the dwelling type of the record with all 0's and populate with the record above with the same dwelling type. I'm unsure of how to make this change. I'm happy with the vb solution although if there is another approach I would also be interested.
https://www.experts-exchange.com/questions/28083252/Query-to-update-fields-with-data-from-table-rows-above.html
I've created two tables below with the original dataset and the new results I'm trying to achieve. The dwelling types aren't grouped so the code might need to search many records above before it finds the correct dwelling type. The HCFMDCode is updated to B when a change is made.
The original table looks like this:
ID DwellingType Couple Family Other HCFMDCode
1 House 0.433 0.567 0 A
2 House 0.223 0.388 0.388 A
3 House 0 0 0 A
4 House 0 0 0 A
5 Unit 0.355 0.332 0.332 A
6 Unit 0 0 0 A
The final results should produce:
ID DwellingType Couple Family Other HCFMDCode
1 House 0.433 0.567 0 A
2 House 0.223 0.388 0.388 A
3 House 0.223 0.388 0.388 B
4 House 0.223 0.388 0.388 B
5 Unit 0.355 0.332 0.332 B
6 Unit 0.355 0.332 0.332 B
Thanks
Scott McDaniel (EE MVE )
Do you have some type of Sort Order on the dataset? Access has no knowledge of "before" or "after" records unless you can sort them in some manner. For example, if you sort by the ID value, would that give you the correct sort order?
ASKER
Yes its sorted by ID, then by dwelling type. I used the ID together with the dwelling type as the primary key when querying. I just realised the table in the question is incorrect.
The input should be:
ID DwellingType Couple Family Other HCFMDCode
1 House 0.433 0.567 0 A
1 Unit 0.223 0.388 0.388 A
2 House 0 0 0 A
2 Unit 0 0 0 A
3 House 0.355 0.332 0.332 A
3 Unit 0 0 0 A
The final output:
ID DwellingType Couple Family Other HCFMDCode
1 House 0.433 0.567 0 A
1 Unit 0.223 0.388 0.388 A
2 House 0.433 0.567 0 B
2 Unit 0.223 0.388 0.388 B
3 House 0.355 0.332 0.332 A
3 Unit 0.223 0.388 0.388 B
In some cases for an ID, only 1 of the 2 dwelling types exists. This is due to a previous step where I filtered out the results where on the number of dwellings for an ID is 0. The number of dwelling field isn't used here. If this will cause an issue I can perform the filtering at the end.
The input should be:
ID DwellingType Couple Family Other HCFMDCode
1 House 0.433 0.567 0 A
1 Unit 0.223 0.388 0.388 A
2 House 0 0 0 A
2 Unit 0 0 0 A
3 House 0.355 0.332 0.332 A
3 Unit 0 0 0 A
The final output:
ID DwellingType Couple Family Other HCFMDCode
1 House 0.433 0.567 0 A
1 Unit 0.223 0.388 0.388 A
2 House 0.433 0.567 0 B
2 Unit 0.223 0.388 0.388 B
3 House 0.355 0.332 0.332 A
3 Unit 0.223 0.388 0.388 B
In some cases for an ID, only 1 of the 2 dwelling types exists. This is due to a previous step where I filtered out the results where on the number of dwellings for an ID is 0. The number of dwelling field isn't used here. If this will cause an issue I can perform the filtering at the end.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The code stops in the imbedded if statement at rstPrev.eod, the compile error is Method or data member not found.
If Not (rstPrev.eod And rstPrev.BOF) Then ...
If Not (rstPrev.eod And rstPrev.BOF) Then ...
ASKER
I realised it should of been rstPrev.EOF.
The code now runs however the rows (with 0's) aren't being updated with values. Only the HCFMDCode.
The code now runs however the rows (with 0's) aren't being updated with values. Only the HCFMDCode.
Does it need to be the row above with the same dwelling type, or just the last (non 0) with the same dwelling type?
If not you can just sort desc by id, filter by dwelling type (and by non 0) and get the values from the first record.
If not you can just sort desc by id, filter by dwelling type (and by non 0) and get the values from the first record.
ASKER
Yes it needs to be the closest ID (from rows above) with the same dwelling type.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.