Denormalize

  • Thread starter Thread starter EllenM
  • Start date Start date
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
 
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
 
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
 
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...
 
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...
 
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...
 
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...
 
Back
Top