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!

giacomo · Aug 14, 2007

how many rows of data do you have? it might be easier to do this in excel.

l KAUTION l · Aug 14, 2007

Is there anyway to do this in access. This file is quite large and I want to avoid the manual tasks every month. Thanks for your reply however.

giacomo · Aug 14, 2007

I wouldn't advise doing it in access because the end result you are looking for is not normalized. Sorry I couldn't be more help.

Giacomo

l KAUTION l · Aug 14, 2007

Is there a way to make column or field 21 say something like:

..F1.......F21
Pol#:.......1
null..........1
null..........1
Pol#:.......2
null..........2
Pol#:.......3
null..........3
null..........3

Basically Increment the counter in F21 if F1 = "Pol#:"

NumbersMax · Aug 14, 2007

Could this problem be tackled at the source? What I mean is, I've run into problems where the input I'm trying to work with is impossible, but when I've worked with the people putting in the source data, together we've come to a compromise that benefits both parties. Usually, they are more open to change when a change lightens their data entry or redundant task, so I try to think of ways I can automate their process and still get what I need.

That being said, it looks like your data might be imported from a spreadsheet, database or something else rather than direct entry into the db you are using. Is this the case?

Can you create a form they could input the info directly into your db?
Can you pull the information differently?
Could they be convinced to copy down the POL# across all the entries or put all the entries on the same line somehow?

I've been trying to think of a way to get it all on one line, but it has escaped me so far...

I would suggest you do a search on Normalizing your data just to make sure you are changing the data into a format that will best serve you down the road. Check this link:

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Basically an example would be that you wouldn't want the 2 car models on one line and probably not in the same table with the pol# (policy number) anyway.

Maybe something like this:

PolicyTable:
Policy #
Customer #
Veh ID #

CustomerTable:
Customer #
Customer Name
Address
etc

VehicleTable:
Veh ID #
Make
Model
Year
etc

In the Tools/Relationships menu option, you could link the tables by their common fields Veh ID # or Customer #. This would allow you to make all sorts of cool queries that give you the answers you want instead of constant headaches with duplicate results.

Please answer the questions about the source of your data...maybe someone can give better advice on how to pull it so you don't have the messy table from the start!

Max

l KAUTION l · Aug 15, 2007

I am guessing that the data comes from another database and is exported as a text file. In some cases it is exported as a rpt file. Which I need to manually convert to a txt file before I can import that, unless there is a way to import rpt file.

I'm not sure I can contact the source of the data. I am thinking that the data comes from an outside the company source.

I am trying to figure out a way to normalize this database. There aren't any common "keys" between any of the fileds. That's why I'm trying to figure out if there is a way to put a 1 in all of the rows of the first record, then a 2 for the second records different rows and so on. I am going to try to find out where exactly we get this data, but I have a strong feeling that it is the way it is.

I hope that answers your questions. Thanks for your help thus far!

l KAUTION l · Aug 15, 2007

I just found out that the data comes from a third party vendor. The vendor has been asked to change things before and was unable to. I just need to get a common field in there somehow. Is there a way to copy the last field 2 the was not null to the field 2's that are null until there is a new field 2 and the copy that field 2 and so on?

There has to be a way. I am going to keep messing around with this until I can figure it out or someone has some sort of solution. I am not very good at vba but maybe that is the way that I need to go...?

NumbersMax · Aug 15, 2007

I would continue to pursue the vendor for a solution. Of course, you can still try to work it out in the meantime. It is good that it's a vendor, because at least your company may have some ability to set requirements with them. If it was a customer, you'd probably be completely out of luck.

It may be worth the time to investigate if they could simply run the query behind the report (assuming that is the way it's setup) and send you a spreadsheet of the query results or maybe a delimited text file of some sort. They could send it along with the reports they currently send or instead depanding on the needs of others in your organization. You don't want the report because it gives you exactly what you have...discorganization.

A few years back, my Materials Manager required our primary freight carriers (Con-Way and FedEx Freight) to provide us with spreadsheets of our shipments via e-mail. We have integrated that into our current system by having one of our better Excel people create macros to manipulate the data in Excel to line it up right for import into Access, where I created macros to handle the consolidation, saving and e-mailing of the data to different people in the organization.

It can definitely be done if someone at your organization is willing to lean on them just a little

giacomo · Aug 15, 2007

I think NumbersMax has given you some pretty good advice so far. However, if you are stuck with the data as is you could normalize it with a bunch of clean-up queries. The first thing you need to do is to add an auto-number field to your source table. The auto-number will be crucial in later steps for keeping everything in order, and for relating back to the source.

The query below will populate F2 with the pol# from above where it's null in the source. After you've done this it should be pretty easy to split up this table into a normalized table structure.

Going into this you should have your existing fields F1 - F7 plus an auto-number field named ID. I will call your existing table tblSource in the example, I am assuming it's laid out 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 >F11</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 >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 >
</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 >
</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 >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 >
</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 >
</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 >
</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 >
</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>

Update SQL:

Code:

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

hth,
Giacomo

Help with organizing a messy table

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

NumbersMax

Board Regular

l KAUTION l

Board Regular

l KAUTION l

Board Regular

NumbersMax

Board Regular

giacomo

Well-known Member

Similar threads

Forum statistics

Share this page

Help with organizing a messy table

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Well-known Member

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