Help with organizing a messy table

l KAUTION l · Aug 14, 2007

I have something like:
(F1, F2, ..., F7 are column names)

F1.......F2.......F3................F4.........F5..........F6................F7
Pol#...04...000003261.....John.....Wood.....S100568
.......................................2007.....Harley......................1HD5DG5D696
...................................................................................Error: Blah
Pol#...06...000005553.....James...Brown....F516D52
.......................................1996.....Plym........................55ADG6546D98
...................................................................................Error: Blah Blah
.......................................1968.....Chev........................55D6D4F1RE77
...................................................................................Error: Blah

I would like to get all of that on one line in case there is a certain criteria that I need or don't need. Such as:
F1......F2.......F3..............F4........F5...........F6............F7........F8.............F9..........................F10..............F11....F12.............F13..................F14
Pol#...04...000003261...John...Wood...S100568...2007...Harley...1HD5DG5D696
Pol#...06...000005553...James...Brown...F516D52...1996...Plym...55ADG6546D98...Error: Blah Blah...1968...Chev...55D6D4F1RE77...Error: Blah

Maybe there is a way to in column F8 if column F1 is Pol#- the next Pol# then F8 = 1, then 2 for the next set.

Hopefully I have explained myself somewhat clearly. Thanks for your help in advance mrexcel members!

l KAUTION l · Aug 15, 2007

Hopefully the vendor can send a cleaner file, that would we wonderful! But in the mean time I have 8 days to clean all of this up and create a report. I can't count on the vendor right now.

I have placed an autonumber column in there already. But to link everything back together don't I need the autonumber to be the same for everything pertaining to a specific record?

I queried for criteria with a policy number. The last field I have is "id". Is there a way to have a ending id? Say take the id from the next record and put it in the ending id field? That way I would have something like this:
F1...........F2...........F3........F4.........F5.............id...ending id
Pol#: 123456798 Mickey Mouse Blahblahblah 1 7
Pol#: 234567890 Minney Mouse Blahblahblah 7 20
Pol#: 345678901 Donald Duck Blahblahblah 20 30

Then link the other data back by saying if id is between abovetable.id and abovetable.ending id then =abovetable.id

I hope this makes some sort of sense.

Edit: I have tried EndingID: DLookUp("id","10","[id]=" & [id]+1). This does not return anything, its null. I thought this would return the id from the next record.
DLookUp("id","10","[id]=" & [id]) This returns the id in the same row.

giacomo · Aug 15, 2007

my solution is to fill in each record with the pol# so that when you normalize you can use that as the foreign key. Why won't that work for you? Having an endingID will not really help in my opinion.

did you try my query?

hth,
Giacomo

l KAUTION l · Aug 15, 2007

Sorry about that. I guess I skipped over your query. It does seem to work on the first few records, but then it just continues to copy down the second record...?

Like:
45000004561
45000004561
45000004561
45000008608
45000008608
45000008608
45000008608
08500004379
45000008608
07700173623
04500008608
04500008608
etc....

giacomo · Aug 15, 2007

Oh man, I am sorry about that. I guess I assumed the pol#'s were going to be in ascending order. Use the ID instead, I think that should work better for you...

Code:

UPDATE tblSource
SET F2 = DMax("ID","tblSource","[ID] <= " & [ID])
WHERE F2 Is Null;

hth,
Giacomo

l KAUTION l · Aug 15, 2007

That is not working correctly. First it could not append as it was an autonumber, I worked around that. Now it can not append, maybe because the Id field is a number field?

Edit: That doesn't seem to be the case. I changed the data value to text and it still give me the can not append error.

giacomo · Aug 15, 2007

can you paste in the SQL you're using, and the error you're getting?

l KAUTION l · Aug 16, 2007

SQL:
UPDATE tblSource
SET [tblSource].[Field 2] = DMax("ID","tblSource","[ID] <= " & [ID])
WHERE ((([tblSource].[Field 2]) Is Null));

Error:
Microsoft Access can't update all the records in the update query.

Microsoft Access didn't update 179 field(s) due to a type conversion failure, 0 record(s) due to key violations, 0 record(s) due to lock variations, and 0 record(s) due to validation rule violations.
...

Nothing seems to be happening.

giacomo · Aug 16, 2007

OK, since that doesn't seem to be working for you let's try something else. I tested this and it worked for me... of course me testing the other solutions didn't help much did it?

1. Just to make sure that we're on the same page, I am assuming that [Field 2] is only populated on those rows that contain the "header" data and that the information it is populated with is the Pol # (4 & 6 in your example)

2. Add a new field to you table called parentID and give it a number data-type.

3. Run this sql:

Code:

UPDATE tblSource SET tblSource.ParentID = DMax("ID","tblSource","[F2] is not null and [ID] <= " & [ID]);

4. The output should look like this:
<TABLE BORDER=1 BGCOLOR=#ffffff CELLSPACING=0><CAPTION>tblSource</CAPTION><THEAD><TR><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >ID</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >ParentID</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >F1</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >F2</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >F3</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >F4</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >F5</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >F6</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >F7</TH></TR></THEAD><TBODY><TR VALIGN=TOP><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>9</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>9</TD><TD BORDERCOLOR=#d0d7e5 >Pol#</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>4</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>3261</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>
</TD><TD BORDERCOLOR=#d0d7e5 >Wood</TD><TD BORDERCOLOR=#d0d7e5 >S100568</TD><TD BORDERCOLOR=#d0d7e5 >
</TD></TR><TR VALIGN=TOP><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>10</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>9</TD><TD BORDERCOLOR=#d0d7e5 >
</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>
</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>
</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>2007</TD><TD BORDERCOLOR=#d0d7e5 >Harley</TD><TD BORDERCOLOR=#d0d7e5 >
</TD><TD BORDERCOLOR=#d0d7e5 >1HD5DG5D696</TD></TR><TR VALIGN=TOP><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>11</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>9</TD><TD BORDERCOLOR=#d0d7e5 >
</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>
</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>
</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>
</TD><TD BORDERCOLOR=#d0d7e5 >
</TD><TD BORDERCOLOR=#d0d7e5 >
</TD><TD BORDERCOLOR=#d0d7e5 >Error: Blah</TD></TR><TR VALIGN=TOP><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>12</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>12</TD><TD BORDERCOLOR=#d0d7e5 >Pol#</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>6</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>5553</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>
</TD><TD BORDERCOLOR=#d0d7e5 >Brown</TD><TD BORDERCOLOR=#d0d7e5 >F516D52</TD><TD BORDERCOLOR=#d0d7e5 >
</TD></TR><TR VALIGN=TOP><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>13</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>12</TD><TD BORDERCOLOR=#d0d7e5 >
</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>
</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>
</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>1996</TD><TD BORDERCOLOR=#d0d7e5 >Plym</TD><TD BORDERCOLOR=#d0d7e5 >
</TD><TD BORDERCOLOR=#d0d7e5 >55ADG6546D98</TD></TR><TR VALIGN=TOP><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>14</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>12</TD><TD BORDERCOLOR=#d0d7e5 >
</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>
</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>
</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>
</TD><TD BORDERCOLOR=#d0d7e5 >
</TD><TD BORDERCOLOR=#d0d7e5 >
</TD><TD BORDERCOLOR=#d0d7e5 >Error: Blah Blah</TD></TR><TR VALIGN=TOP><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>15</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>12</TD><TD BORDERCOLOR=#d0d7e5 >
</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>
</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>
</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>1968</TD><TD BORDERCOLOR=#d0d7e5 >Chev</TD><TD BORDERCOLOR=#d0d7e5 >
</TD><TD BORDERCOLOR=#d0d7e5 >55D6D4F1RE77</TD></TR><TR VALIGN=TOP><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>16</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>12</TD><TD BORDERCOLOR=#d0d7e5 >
</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>
</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>
</TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT>
</TD><TD BORDERCOLOR=#d0d7e5 >
</TD><TD BORDERCOLOR=#d0d7e5 >
</TD><TD BORDERCOLOR=#d0d7e5 >Error: Blah</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

hth,
Giacomo

l KAUTION l · Aug 16, 2007

Thank you so much giacomo! It works wonderfully. Now I need to normalize this mess. Thanks again!!!!

Help with organizing a messy table

l KAUTION l

Board Regular

l KAUTION l

Board Regular

Excel Facts

giacomo

Well-known Member

l KAUTION l

Board Regular

giacomo

Well-known Member

l KAUTION l

Board Regular

giacomo

Well-known Member

l KAUTION l

Board Regular

giacomo

Well-known Member

l KAUTION l

Board Regular

Similar threads

Forum statistics

Share this page

Help with organizing a messy table

Board Regular

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock