Excel to Access and Filling row with data

[rccline]

I have an excel spreadsheet with rows which contain missing data; the data needs to be filled-in from preceding rows:

Example 1:
Record CodeID SubID LastName FirstName Date DataNo
1 345 22 Jones John 1/23/2001 ASDFA
Jones James
Jones Ruth
2 334 20 Smith Mary 1/03/2001 GDASD
Smith John
Smith Harry
3 333 24 Polk John 1/22/2002 DFGHH
4 330 99 Randall Bill 2/11/2002 NNEWE


Record 1 has three rows and the data in the missing fields should not be blank, but should contain the data in the first row.

Example 2:
Record CodeID SubID LastName FirstName Date DataNo
1 345 22 Jones John 1/23/2001 ASDFA
1 345 22 Jones James 1/23/2001 ASDFA
1 345 22 Jones Ruth 1/23/2001 ASDFA
2 334 20 Smith Mary 1/03/2001 GDASD
2 334 20 Smith John 1/03/2001 GDASD
2 334 20 Smith Harry 1/03/2001 GDASD
3 333 24 Polk John 1/22/2002 DFGHH
4 330 99 Randall Bill 2/11/2002 NNEWE

How do I change Example 1 into Example 2 either before, or after it is imported into Access?


Thanks!


Robert
713-521-3805

 

[willir]

Robert
(From Texas??)

I would do this at the code level, but let's step back a bit.

I assume you want to import a spreadsheet into Access. Make sure it is sorted per your needs. ALSO, as a hint, change the description on your column headers so they do not have spaces -- you will save your self some work and frustation. And do not use any reserved words. For example, I would use "RecordID" and not "Record", UserDate instead of Date.

When you use the import wizard, use the autonumber feature for the import and use this for your primary key. (And I am assuming you are NOT importing into a database setup for replicas -- the autonumber uses a random number, and would defeat the purpose of what the autonumber will be used for in this case.)

When you go through the table, make sure the fields are setup properly. For example, the FirstNameDate field.

Using your data, when you complete the import, you should see...

tblImport
- Primary key is ImportID
ImportID Record CodeID SubID LastName FirstName UserDate DataNo
1 1 345 22 Jones John 1/23/2001 ASDFA
2 Jones James
3 Jones Ruth
4 2 334 20 Smith Mary 1/03/2001 GDASD

Note that the autonumber should have create a serial entry for your imported records.

Then create a code that performs something like a "reverse" "level break".

Logic is...

dim dbs as DAO.database()
dim rst as DAO.recordset
dim strSQL as String
dim lngRecordID as Long, lngCodeID as Long, lngSubID as Long
dim strLastName as String, strFirstName as String, strDateNo as String
dim dtUserDate as Date
dim booUpdate as Boolean

strSQL = "select * from tblImport order by ImportID"
booUpdate = False

Set dbs = CurrentDB()

Set rst = dbs.OpenRecordset(strSQL)

With rst
.MoveFirst

Do until .EOF

if not isnull(.RecordID) then
lngRecordID = .RecordID
else
boUpdate = True
end if

if not isnull(.CodeID) then
lngCodeID = .CodeID
else
boUpdate = True
end if

if not isnull(.SubID) then
lngSubID = .SubID
else
boUpdate = True
end if

if not isnull(.dtUserDate) then
UserDate = .dtUserDate
else
boUpdate = True
end if

if not isnull(.DateNo) then
strDateNo = .DateNo
else
boUpdate = True
end if

if booUpdate then
.Edit
me.RecordID = lngRecordID
me.CodeID = lngCodeID
me.SubID = lngSubID
me.dUserDate = dtUserDate
me.DateNo = strDateNo
.Update
booUpdate = False
end if
.MoveNext

Loop
End With

I am assuming that the previous record will have the required info. I am not promising the code will work first time -- I banged it out for you. You may have to tweak per typo's and a misunderstanding on my part in the interpretation of your data.

Remember to create a backup before starting.

Richard

 

[rccline]

Thank you for the hellpful hints and the code Richard. Your assumption is correct, the previous record does have the required info. I'll see how it goes!

Robert

 

[OhioSteve]

You realize that you could just drag down in Excel, and the values would appear?

 

[lespaul]

You're right steve, but if this is something that has to be done regularly, it would be more efficient to code the updates/changes rather than having to manually update the excel spreadsheet everytime the import is needed.

And yes Richard I believe Robert is from HOUSTON (who's team brought down the Miami Dolphins to the surprise of most NFL fans!).

Leslie