Denormalizing a Table

  • Thread starter Thread starter Bre-x
  • Start date Start date
B

Bre-x

I know I posted this question time a go, but I have to keep trying
I am connecting MS Access to a Pervasive SQL Server with a very old table
structure design
The table in question is a Sales Order table.
Only God knows why to enter a comment for each part sold it adds a new
Recordset

Example:

SO Number, Line Number, Part Description
1234, 1, Potatoes
1234, 2, Commentary 1
1234, 3, Commentary 2
1234, 4, Tomatoes
1234, 5, My commentary for Tomatoes
1234, 6, My second commentary for Tomatoes

What I need to end up with is something like this:

SO Number, Line Number, Part Description, Line Description
1234, 1, Potatoes, Commentary 1 Commentary 2
1234, 2, Tomatoes, My commentary for Tomatoes My second commentary for
Tomatoes

I don't know if this problem has such a obvious solution, I am not
explaining myself properly, or has no solution at all.
Once again I hope someone can help me.

Regards,

Bre-x
 
I don't think there is a simple solution. The biggest problem I see is that
on lines 2 and 3, there is no indication that these comments go with the
item on line 1. Is there another field you can use? How do I know Potatoes
is an item with a part description and "Commentary 1" is not?

Once you get over that hurdle, the rest is doable - probably using a
combination of queries and code.

Are there *always* to comments?

Is there a table of items that can be used to filter the actual parts from
the comments?
 
After re-reading my post I think further clarification is necessary:
The biggest problem I see
is that on lines 2 and 3, there is no indication that these comments
go with the item on line 1.
Click to expand...

Other than the SO Number of course. I think the point I was really trying to
make is that I don't see a data point that would allow me to make a
distinction between lines 1-6 without having some additional information.
Are there *always* to comments?
Click to expand...

Meant to say are there *always* TWO comments? (I hate those kinds of
typos!!)
 
Hi Sandra,

Thank you for answering my post.

The only common denominator among the lines is the SO Number, other than the
SO Number is empty or does not have any date (SODaste Field)
The Line Number is an autonumber field so it goes 1,2,3,4,5 and so on
there could be no line comment at all or as many as the user wish to enter

Once again thanks for you help

Bre-x




Sandra Daigle said:
After re-reading my post I think further clarification is necessary:
The biggest problem I see
is that on lines 2 and 3, there is no indication that these comments
go with the item on line 1.
Click to expand...

Other than the SO Number of course. I think the point I was really trying to
make is that I don't see a data point that would allow me to make a
distinction between lines 1-6 without having some additional information.
Are there *always* to comments?
Click to expand...

Meant to say are there *always* TWO comments? (I hate those kinds of
typos!!)

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Sandra said:
I don't think there is a simple solution. The biggest problem I see
is that on lines 2 and 3, there is no indication that these comments
go with the item on line 1. Is there another field you can use? How
do I know Potatoes is an item with a part description and "Commentary
1" is not?

Once you get over that hurdle, the rest is doable - probably using a
combination of queries and code.

Are there *always* to comments?

Is there a table of items that can be used to filter the actual parts
from the comments?
Click to expand...
Click to expand...
 
Ouch - unless you can add fields I don't know how you'd do this except
manually. There isn't an Items table?

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Bre-x said:
Hi Sandra,

Thank you for answering my post.

The only common denominator among the lines is the SO Number, other
than the SO Number is empty or does not have any date (SODaste Field)
The Line Number is an autonumber field so it goes 1,2,3,4,5 and so on
there could be no line comment at all or as many as the user wish to
enter

Once again thanks for you help

Bre-x




Sandra Daigle said:
After re-reading my post I think further clarification is necessary:
The biggest problem I see
is that on lines 2 and 3, there is no indication that these comments
go with the item on line 1.
Click to expand...

Other than the SO Number of course. I think the point I was really
trying to make is that I don't see a data point that would allow me
to make a distinction between lines 1-6 without having some
additional information.
Are there *always* to comments?
Click to expand...

Meant to say are there *always* TWO comments? (I hate those kinds of
typos!!)

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Sandra said:
I don't think there is a simple solution. The biggest problem I see
is that on lines 2 and 3, there is no indication that these comments
go with the item on line 1. Is there another field you can use? How
do I know Potatoes is an item with a part description and
"Commentary 1" is not?

Once you get over that hurdle, the rest is doable - probably using a
combination of queries and code.

Are there *always* to comments?

Is there a table of items that can be used to filter the actual
parts from the comments?



Bre-x wrote:
I know I posted this question time a go, but I have to keep trying
I am connecting MS Access to a Pervasive SQL Server with a very old
table structure design
The table in question is a Sales Order table.
Only God knows why to enter a comment for each part sold it adds a
new Recordset

Example:

SO Number, Line Number, Part Description
1234, 1, Potatoes
1234, 2, Commentary 1
1234, 3, Commentary 2
1234, 4, Tomatoes
1234, 5, My commentary for Tomatoes
1234, 6, My second commentary for Tomatoes

What I need to end up with is something like this:

SO Number, Line Number, Part Description, Line Description
1234, 1, Potatoes, Commentary 1 Commentary 2
1234, 2, Tomatoes, My commentary for Tomatoes My second commentary
for Tomatoes

I don't know if this problem has such a obvious solution, I am not
explaining myself properly, or has no solution at all.
Once again I hope someone can help me.

Regards,

Bre-x
Click to expand...
Click to expand...
Click to expand...
 
Nup,

As I said it's a very old table structure. Anyways thanks for your help.

Regards,

Bre-x

Sandra Daigle said:
Ouch - unless you can add fields I don't know how you'd do this except
manually. There isn't an Items table?

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Bre-x said:
Hi Sandra,

Thank you for answering my post.

The only common denominator among the lines is the SO Number, other
than the SO Number is empty or does not have any date (SODaste Field)
The Line Number is an autonumber field so it goes 1,2,3,4,5 and so on
there could be no line comment at all or as many as the user wish to
enter

Once again thanks for you help

Bre-x




Sandra Daigle said:
After re-reading my post I think further clarification is necessary:

The biggest problem I see
is that on lines 2 and 3, there is no indication that these comments
go with the item on line 1.

Other than the SO Number of course. I think the point I was really
trying to make is that I don't see a data point that would allow me
to make a distinction between lines 1-6 without having some
additional information.

Are there *always* to comments?

Meant to say are there *always* TWO comments? (I hate those kinds of
typos!!)

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Sandra Daigle wrote:
I don't think there is a simple solution. The biggest problem I see
is that on lines 2 and 3, there is no indication that these comments
go with the item on line 1. Is there another field you can use? How
do I know Potatoes is an item with a part description and
"Commentary 1" is not?

Once you get over that hurdle, the rest is doable - probably using a
combination of queries and code.

Are there *always* to comments?

Is there a table of items that can be used to filter the actual
parts from the comments?



Bre-x wrote:
I know I posted this question time a go, but I have to keep trying
I am connecting MS Access to a Pervasive SQL Server with a very old
table structure design
The table in question is a Sales Order table.
Only God knows why to enter a comment for each part sold it adds a
new Recordset

Example:

SO Number, Line Number, Part Description
1234, 1, Potatoes
1234, 2, Commentary 1
1234, 3, Commentary 2
1234, 4, Tomatoes
1234, 5, My commentary for Tomatoes
1234, 6, My second commentary for Tomatoes

What I need to end up with is something like this:

SO Number, Line Number, Part Description, Line Description
1234, 1, Potatoes, Commentary 1 Commentary 2
1234, 2, Tomatoes, My commentary for Tomatoes My second commentary
for Tomatoes

I don't know if this problem has such a obvious solution, I am not
explaining myself properly, or has no solution at all.
Once again I hope someone can help me.

Regards,

Bre-x
Click to expand...
Click to expand...
Click to expand...
 
SO Number, Line Number, Part Description
1234, 1, Potatoes
1234, 2, Commentary 1
1234, 3, Commentary 2
1234, 4, Tomatoes
1234, 5, My commentary for Tomatoes
1234, 6, My second commentary for Tomatoes

What I need to end up with is something like this:

SO Number, Line Number, Part Description, Line Description
1234, 1, Potatoes, Commentary 1 Commentary 2
1234, 2, Tomatoes, My commentary for Tomatoes My second commentary for
Click to expand...

I have to agree with Sandra. There's readily available code to
concatenate fields for a given SO number - go to
http://www.mvps.org/access and search for "Concatenate" - but there is
a purely *logical* problem identifying when "potatoes" leaves off and
"tomatoes" begins. Just as an example, one could have data like

1234, 1, Potatoes
1234, 2, With potatoes one eats the tubers not the fruit,
1234, 3, even though botanically potatoes are relate to
1234, 4, tomatoes
1234, 5, Rutebagas

Here "tomatoes" is part of the commentary rather than the beginning of
a new entry.

Now I realize that this is hypothetical sample data - but is there
ANYTHING within the data, or the structure of the data, which could
reliably indicate the end of a commentary and the beginning of a new
entry? As Sandra suggests, one possibility would be if you had a list
of all of the part names (your "potatoes" and "tomatoes"); does such a
list exist, or could it be extracted? Might it be possible to extract
all (or most) of the "potatoes" and "tomatoes" by searching the field
for rows which contain only a single word (NOT LIKE "* *" as a
criterion)?
 
It looks as if records that contain (parts of) comments have no values
in ESD and PCODE. Does sorting on INVNM, CNTR produce the records in the
correct order?
 
Hi, John

I cound find any column that indicate the end of a commentary and the
beginning of a record.

Here is a copy with only 50 records (140K size), if you wish to look at it.

http://www.ircarequipa.com/Kelloggs/db1.zip
Click to expand...

Eeeuuuwwwww...

I am not sure that (without the help of someone familiar with the
business) that I'd be able to properly categorize all of these
manually. Many of the part names are multi-word phrases; many of the
comments are alphanumeric codes; there are *blank* rows (which may
just be there for doublespacing in a printout)...

Unless you make the assumption that every "1" is a partnumber and
higher numbers are comments (a dubious suggestion), I cannot imagine
any good way to automate this.

I must say - I've seen some snarky databases, but this one takes the
cake, the icing, and the platter as well! Good luck... you'll need it.
 
John said:
Eeeuuuwwwww...

I am not sure that (without the help of someone familiar with the
business) that I'd be able to properly categorize all of these
manually. Many of the part names are multi-word phrases; many of the
comments are alphanumeric codes; there are *blank* rows (which may
just be there for doublespacing in a printout)...

Unless you make the assumption that every "1" is a partnumber and
higher numbers are comments (a dubious suggestion), I cannot imagine
any good way to automate this.

I must say - I've seen some snarky databases, but this one takes the
cake, the icing, and the platter as well! Good luck... you'll need it.
Click to expand...

Looks like the column BKAR_INVL_OOQTY is 0 on comment records and <> 0 on
item records. If this is consistent then you might be able to use it.

"Snarky"??? I love this word and plan to use it several times today!
 
Back
Top