Denormalize

E

EllenM

Hello,
I'm using Access 2003 and have data that looks like this:
DOCNUM REGNUM
5001 176.170
5002 175.105
5003 175.105
5004
5005 175.105
5006 175.105
5006 175.300
5006 175.320
5006 178.3910
5006 181.27
5007 175.105
5007 176.170

Docnums can be numbers or text. Regnums are text fields.

I'd like it to look like this:
DOCNUM REGNUM
5001 176.170
5002 175.105
5003 175.105
5004
5005 175.105
5006 175.105<br />175.300<br />175.320<br />178.3910<br />181.27
5007 175.105<br />176.170

In the second table, the REGNUMs need to be a memo field as they can be well
over 255 characters.

Thanks in advance for your help.

Ellen
 
V

vanderghast

A varchar field can only accept a maximum of 255 characters.

So, if you need the stuff in a table, you will have to go for a memo, and
there isn't much you can do with it in SQL.

Alternatively, if that is only required for a Report, then you can GROUP on
Docnum (in the report, NOT in a query), then, in VBA, concatenate the regnum
value you see passing by each time your report would normally have to print
a line of detail, you concatenate the value into a VBA variable, and NOT
printing the detail value in the detail section, but rather you will print
the content of the VBA variable at the group footer (which means that
records making the group have all been visited). You initialize that VBA
variable to a zero length string at the start of the group.


Hoping it makes sense,
Vanderghast, Access MVP
 
J

John Spencer

Quoting Duane Hookom

I use a generic Concatenate() function. The code is listed below with both ADO
and DAO. There are comments regarding which lines to comment or uncomment
based on which library you prefer. Access 97 is mostly DAO while the default
for 2000 and newer is ADO.

See:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

IF you download the example and copy the code into your database application
you should be able to construct a query like the following assuming DocNum is
a text field. If DocNum is a number field then you need to remove the '
(apostrophes) from the concatenate string.

SELECT DISTINCT DocNum,
Concatenate("SELECT RegNum FROM [YourTable] WHERE DocNum='" & [DocNum] & "'"
, "<br />") as TheList
FROM [YourTable]

It might be faster to use a subquery to get a unique list of DocNums (assuming
that your table name consists of only letters, numbers, and underscores - no
other characters allowed).

SELECT DocNum,
Concatenate("SELECT RegNum FROM YourTable WHERE DocNum='" & [DocNum] & "'" ,
"<br />") as TheList
FROM (SELECT DISTINCT DocNum FROM YourTable) as X


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
E

EllenM

John,

Thanks sooooooooooooo much! It worked beautifully!

John Spencer said:
Quoting Duane Hookom

I use a generic Concatenate() function. The code is listed below with both ADO
and DAO. There are comments regarding which lines to comment or uncomment
based on which library you prefer. Access 97 is mostly DAO while the default
for 2000 and newer is ADO.

See:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

IF you download the example and copy the code into your database application
you should be able to construct a query like the following assuming DocNum is
a text field. If DocNum is a number field then you need to remove the '
(apostrophes) from the concatenate string.

SELECT DISTINCT DocNum,
Concatenate("SELECT RegNum FROM [YourTable] WHERE DocNum='" & [DocNum] & "'"
, "<br />") as TheList
FROM [YourTable]

It might be faster to use a subquery to get a unique list of DocNums (assuming
that your table name consists of only letters, numbers, and underscores - no
other characters allowed).

SELECT DocNum,
Concatenate("SELECT RegNum FROM YourTable WHERE DocNum='" & [DocNum] & "'" ,
"<br />") as TheList
FROM (SELECT DISTINCT DocNum FROM YourTable) as X


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hello,
I'm using Access 2003 and have data that looks like this:
DOCNUM REGNUM
5001 176.170
5002 175.105
5003 175.105
5004
5005 175.105
5006 175.105
5006 175.300
5006 175.320
5006 178.3910
5006 181.27
5007 175.105
5007 176.170

Docnums can be numbers or text. Regnums are text fields.

I'd like it to look like this:
DOCNUM REGNUM
5001 176.170
5002 175.105
5003 175.105
5004
5005 175.105
5006 175.105<br />175.300<br />175.320<br />178.3910<br />181.27
5007 175.105<br />176.170

In the second table, the REGNUMs need to be a memo field as they can be well
over 255 characters.

Thanks in advance for your help.

Ellen
Click to expand...
.
Click to expand...
 
E

EllenM

Hi John,
I'm still very grateful to be able to generate the denormalized data. One
question, though, is there a way to prevent the expression from truncating?

Thankfully, in my case it only happened three times, so it was easy to fix.

Ellen

John Spencer said:
Quoting Duane Hookom

I use a generic Concatenate() function. The code is listed below with both ADO
and DAO. There are comments regarding which lines to comment or uncomment
based on which library you prefer. Access 97 is mostly DAO while the default
for 2000 and newer is ADO.

See:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

IF you download the example and copy the code into your database application
you should be able to construct a query like the following assuming DocNum is
a text field. If DocNum is a number field then you need to remove the '
(apostrophes) from the concatenate string.

SELECT DISTINCT DocNum,
Concatenate("SELECT RegNum FROM [YourTable] WHERE DocNum='" & [DocNum] & "'"
, "<br />") as TheList
FROM [YourTable]

It might be faster to use a subquery to get a unique list of DocNums (assuming
that your table name consists of only letters, numbers, and underscores - no
other characters allowed).

SELECT DocNum,
Concatenate("SELECT RegNum FROM YourTable WHERE DocNum='" & [DocNum] & "'" ,
"<br />") as TheList
FROM (SELECT DISTINCT DocNum FROM YourTable) as X


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hello,
I'm using Access 2003 and have data that looks like this:
DOCNUM REGNUM
5001 176.170
5002 175.105
5003 175.105
5004
5005 175.105
5006 175.105
5006 175.300
5006 175.320
5006 178.3910
5006 181.27
5007 175.105
5007 176.170

Docnums can be numbers or text. Regnums are text fields.

I'd like it to look like this:
DOCNUM REGNUM
5001 176.170
5002 175.105
5003 175.105
5004
5005 175.105
5006 175.105<br />175.300<br />175.320<br />178.3910<br />181.27
5007 175.105<br />176.170

In the second table, the REGNUMs need to be a memo field as they can be well
over 255 characters.

Thanks in advance for your help.

Ellen
Click to expand...
.
Click to expand...
 
J

John Spencer

As far as I know, it should not truncate --- whoops we are using DISTINCT in
the first query so you don't get multiples of the value. Distinct truncates
memo fields/columns to the first 255 characters so it can do its "magic"
efficiently.

If you use the second method that should not be a problem.

OR you can TRY the following to eliminate the duplicates. This may be slow also.

SELECT DocNum,
First(Concatenate("SELECT RegNum FROM [YourTable] WHERE DocNum='" & [DocNum]
& "'" , "<br />")) as TheList
FROM [YourTable]
GROUP BY DocNum

This query does the DISTINCT before we try doing the Concatenate so the query
does not need to truncate the result to 255 to handle the DISTINCT action.

SELECT DocNum,
Concatenate("SELECT RegNum FROM YourTable WHERE DocNum='" & [DocNum] & "'" ,
"<br />") as TheList
FROM (SELECT DISTINCT DocNum FROM YourTable) as X

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi John,
I'm still very grateful to be able to generate the denormalized data. One
question, though, is there a way to prevent the expression from truncating?

Thankfully, in my case it only happened three times, so it was easy to fix.

Ellen

John Spencer said:
Quoting Duane Hookom

I use a generic Concatenate() function. The code is listed below with both ADO
and DAO. There are comments regarding which lines to comment or uncomment
based on which library you prefer. Access 97 is mostly DAO while the default
for 2000 and newer is ADO.

See:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

IF you download the example and copy the code into your database application
you should be able to construct a query like the following assuming DocNum is
a text field. If DocNum is a number field then you need to remove the '
(apostrophes) from the concatenate string.

SELECT DISTINCT DocNum,
Concatenate("SELECT RegNum FROM [YourTable] WHERE DocNum='" & [DocNum] & "'"
, "<br />") as TheList
FROM [YourTable]

It might be faster to use a subquery to get a unique list of DocNums (assuming
that your table name consists of only letters, numbers, and underscores - no
other characters allowed).

SELECT DocNum,
Concatenate("SELECT RegNum FROM YourTable WHERE DocNum='" & [DocNum] & "'" ,
"<br />") as TheList
FROM (SELECT DISTINCT DocNum FROM YourTable) as X


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hello,
I'm using Access 2003 and have data that looks like this:
DOCNUM REGNUM
5001 176.170
5002 175.105
5003 175.105
5004
5005 175.105
5006 175.105
5006 175.300
5006 175.320
5006 178.3910
5006 181.27
5007 175.105
5007 176.170

Docnums can be numbers or text. Regnums are text fields.

I'd like it to look like this:
DOCNUM REGNUM
5001 176.170
5002 175.105
5003 175.105
5004
5005 175.105
5006 175.105<br />175.300<br />175.320<br />178.3910<br />181.27
5007 175.105<br />176.170

In the second table, the REGNUMs need to be a memo field as they can be well
over 255 characters.

Thanks in advance for your help.

Ellen
Click to expand...
.
Click to expand...
Click to expand...
 
E

EllenM

Hi John,
The second query works like a dream. I've prepared the DocNum table to be
unique values, so weeding out duplicates isn't a problem.

The query is VERY much appreciated as I'm going to be updating the inventory
from time to time.

Thanks ever so much,
Ellen

John Spencer said:
As far as I know, it should not truncate --- whoops we are using DISTINCT in
the first query so you don't get multiples of the value. Distinct truncates
memo fields/columns to the first 255 characters so it can do its "magic"
efficiently.

If you use the second method that should not be a problem.

OR you can TRY the following to eliminate the duplicates. This may be slow also.

SELECT DocNum,
First(Concatenate("SELECT RegNum FROM [YourTable] WHERE DocNum='" & [DocNum]
& "'" , "<br />")) as TheList
FROM [YourTable]
GROUP BY DocNum

This query does the DISTINCT before we try doing the Concatenate so the query
does not need to truncate the result to 255 to handle the DISTINCT action.

SELECT DocNum,
Concatenate("SELECT RegNum FROM YourTable WHERE DocNum='" & [DocNum] & "'" ,
"<br />") as TheList
FROM (SELECT DISTINCT DocNum FROM YourTable) as X

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi John,
I'm still very grateful to be able to generate the denormalized data. One
question, though, is there a way to prevent the expression from truncating?

Thankfully, in my case it only happened three times, so it was easy to fix.

Ellen

John Spencer said:
Quoting Duane Hookom

I use a generic Concatenate() function. The code is listed below with both ADO
and DAO. There are comments regarding which lines to comment or uncomment
based on which library you prefer. Access 97 is mostly DAO while the default
for 2000 and newer is ADO.

See:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

IF you download the example and copy the code into your database application
you should be able to construct a query like the following assuming DocNum is
a text field. If DocNum is a number field then you need to remove the '
(apostrophes) from the concatenate string.

SELECT DISTINCT DocNum,
Concatenate("SELECT RegNum FROM [YourTable] WHERE DocNum='" & [DocNum] & "'"
, "<br />") as TheList
FROM [YourTable]

It might be faster to use a subquery to get a unique list of DocNums (assuming
that your table name consists of only letters, numbers, and underscores - no
other characters allowed).

SELECT DocNum,
Concatenate("SELECT RegNum FROM YourTable WHERE DocNum='" & [DocNum] & "'" ,
"<br />") as TheList
FROM (SELECT DISTINCT DocNum FROM YourTable) as X


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

EllenM wrote:
Hello,
I'm using Access 2003 and have data that looks like this:
DOCNUM REGNUM
5001 176.170
5002 175.105
5003 175.105
5004
5005 175.105
5006 175.105
5006 175.300
5006 175.320
5006 178.3910
5006 181.27
5007 175.105
5007 176.170

Docnums can be numbers or text. Regnums are text fields.

I'd like it to look like this:
DOCNUM REGNUM
5001 176.170
5002 175.105
5003 175.105
5004
5005 175.105
5006 175.105<br />175.300<br />175.320<br />178.3910<br />181.27
5007 175.105<br />176.170

In the second table, the REGNUMs need to be a memo field as they can be well
over 255 characters.

Thanks in advance for your help.

Ellen
.
Click to expand...
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Normalize/concatenate 10

Top