Query to update fields with data from table rows above with the same dwelling type

AndyC1000
AndyC1000 used Ask the Experts™
on
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.

http://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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
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?

Author

Commented:
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.
Most Valuable Expert 2012
Top Expert 2014
Commented:
I pulled the code from the database in the other question and modified it here:

    Dim rst As DAO.Recordset
    Dim sglStoredCouple As Single
    Dim sglStoredFamily As Single
    Dim sglStoredOther As Single
    Dim sglStoredHCFMDCode As String

    Set rst = CurrentDb.OpenRecordset("TypolLFSF")

    rst.MoveFirst

    Do Until rst.EOF
        If rst!Couple = 0 And rst!Family = 0 And rst!Other = 0 Then
            '/ get the previous record with the same dwelling type
            Dim rstPrev As DAO.Recordset
            Set rstPrev = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM TypolFSF WHERE DwellingType='" & rst("DwellingType") & "' AND ID<" & rst("ID") & " ORDER BY ID")
            If Not (rstPrev.eod And rstPrev.BOF) Then
                rst.Edit
                rst!Couple = rstPrev("Couple")
                rst!Family = rstPrev("Family")
                rst!Other = rstPrev("Other")
                rst!HCFMDCode = "B"
                rst.Update
            End If
        End If

        rst.MoveNext

    Loop

    rst.Close
    Set rst = Nothing

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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 ...

Author

Commented:
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.
Andy CownieImplementation Specialist

Commented:
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.

Author

Commented:
Yes it needs to be the closest ID (from rows above) with the same dwelling type.
Most Valuable Expert 2012
Top Expert 2014
Commented:
Try a different sort order:

Set rstPrev = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM TypolFSF WHERE DwellingType='" & rst("DwellingType") & "' AND ID<" & rst("ID") & " ORDER BY ID DESC")

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial