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.


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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
AndyC1000Author 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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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")


    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!Couple = rstPrev("Couple")
                rst!Family = rstPrev("Family")
                rst!Other = rstPrev("Other")
                rst!HCFMDCode = "B"
            End If
        End If



    Set rst = Nothing

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

AndyC1000Author 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 ...
AndyC1000Author 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 SpecialistCommented:
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.
AndyC1000Author Commented:
Yes it needs to be the closest ID (from rows above) with the same dwelling type.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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")
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.