I need to concat multiple records for SKU

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a main table called "dvd" with unique 105,000 records where DV-ID is
my field primary key. I have field called "V_ID" in my "dvd" table that
relate to another table called "VIDEO".

The "video" table contains 340,000 records where "V_ID" is the primary key.

I have other tables that relate to my main table "dvd" thru the "VIDEO" table.

For example, I have a table called "vtitle" which contains alternate titles
for each "V_ID". The table "vtitle" might contains more than one alternate
title for a "V_ID". So I can found 3 results for a single "V_ID". This mean
that this "V_ID" has 3 alternates titles.

I'd like to do a make table query where I have a concatenation of all
alternative titles per "V_ID" seperated by <br/>.

So for example, if I have 3 alternate titles for "V 221446", I'd like to
have a field that contains "Title1<br/>Title2<br/>Title3" .



Thanks in advance

uberblick
 
Duane Hookom said:
There is a generic concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This
might be very slow. It also works only in Access (not in an ASP page).
Click to expand...

Thanks Duane.

I tried to do it by importing the module in my *.mdb but I got a bunch of
errors.

How should I write my code ?

Here is what I wrote:

SELECT Dvd.DV_ID, Concatenate("SELECT fname FROM Dcovers WHERE dv_id=" &
[DV_ID]) AS FileNames
FROM Dvd;


Regards

uberblick
 
You haven't mention the table (or is it a query) named dcovers. Also, what is
the data type of DV_ID (or is it V_ID)? I am confused.

--
Duane Hookom
Microsoft Access MVP


uberblick said:
Duane Hookom said:
There is a generic concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This
might be very slow. It also works only in Access (not in an ASP page).
Click to expand...

Thanks Duane.

I tried to do it by importing the module in my *.mdb but I got a bunch of
errors.

How should I write my code ?

Here is what I wrote:

SELECT Dvd.DV_ID, Concatenate("SELECT fname FROM Dcovers WHERE dv_id=" &
[DV_ID]) AS FileNames
FROM Dvd;


Regards

uberblick
Click to expand...
 
Yes i understand.

I tried to apply your concat function and module to smallest tables so I can
understand more what I was doing.

The table1 is "dvd" and the second table is "Dcovers".

The primary key for "dvd" is "DV_ID" and "Dcovers" contains a field named
"dv_id" too. The "Dcovers" table might contains more than one occurence per
"dv_id" since, a product might have more than 1 picture.

So the goal is the same than the initial post. The "dcovers" table contain a
field name "fname" that I<d like to regroup into one string and each value
separated by a comma and a space like you're module is set up.

Data type is all text.

Also, do you give private course on access ?

Regards

uberblick






Duane Hookom said:
You haven't mention the table (or is it a query) named dcovers. Also, what is
the data type of DV_ID (or is it V_ID)? I am confused.

--
Duane Hookom
Microsoft Access MVP


uberblick said:
Duane Hookom said:
There is a generic concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This
might be very slow. It also works only in Access (not in an ASP page).
--
Duane Hookom
Microsoft Access MVP


:

Hi,

I have a main table called "dvd" with unique 105,000 records where DV-ID is
my field primary key. I have field called "V_ID" in my "dvd" table that
relate to another table called "VIDEO".

The "video" table contains 340,000 records where "V_ID" is the primary key.

I have other tables that relate to my main table "dvd" thru the "VIDEO" table.

For example, I have a table called "vtitle" which contains alternate titles
for each "V_ID". The table "vtitle" might contains more than one alternate
title for a "V_ID". So I can found 3 results for a single "V_ID". This mean
that this "V_ID" has 3 alternates titles.

I'd like to do a make table query where I have a concatenation of all
alternative titles per "V_ID" seperated by <br/>.

So for example, if I have 3 alternate titles for "V 221446", I'd like to
have a field that contains "Title1<br/>Title2<br/>Title3" .



Thanks in advance

uberblick
Click to expand...

Thanks Duane.

I tried to do it by importing the module in my *.mdb but I got a bunch of
errors.

How should I write my code ?

Here is what I wrote:

SELECT Dvd.DV_ID, Concatenate("SELECT fname FROM Dcovers WHERE dv_id=" &
[DV_ID]) AS FileNames
FROM Dvd;


Regards

uberblick
Click to expand...
Click to expand...
 
If DV_ID is text then you must delimit it with quotes:
SELECT Dvd.DV_ID, Concatenate("SELECT fname FROM Dcovers WHERE dv_id=""" &
[DV_ID] & """") AS FileNames FROM Dvd;

I have done some private mentoring with clients involving application
development. This works best when I can look over my client's shoulder. You
may be able to find someone in or near your location who would do this for
you.

--
Duane Hookom
Microsoft Access MVP


uberblick said:
Yes i understand.

I tried to apply your concat function and module to smallest tables so I can
understand more what I was doing.

The table1 is "dvd" and the second table is "Dcovers".

The primary key for "dvd" is "DV_ID" and "Dcovers" contains a field named
"dv_id" too. The "Dcovers" table might contains more than one occurence per
"dv_id" since, a product might have more than 1 picture.

So the goal is the same than the initial post. The "dcovers" table contain a
field name "fname" that I<d like to regroup into one string and each value
separated by a comma and a space like you're module is set up.

Data type is all text.

Also, do you give private course on access ?

Regards

uberblick






Duane Hookom said:
You haven't mention the table (or is it a query) named dcovers. Also, what is
the data type of DV_ID (or is it V_ID)? I am confused.

--
Duane Hookom
Microsoft Access MVP


uberblick said:
:

There is a generic concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This
might be very slow. It also works only in Access (not in an ASP page).
--
Duane Hookom
Microsoft Access MVP


:

Hi,

I have a main table called "dvd" with unique 105,000 records where DV-ID is
my field primary key. I have field called "V_ID" in my "dvd" table that
relate to another table called "VIDEO".

The "video" table contains 340,000 records where "V_ID" is the primary key.

I have other tables that relate to my main table "dvd" thru the "VIDEO" table.

For example, I have a table called "vtitle" which contains alternate titles
for each "V_ID". The table "vtitle" might contains more than one alternate
title for a "V_ID". So I can found 3 results for a single "V_ID". This mean
that this "V_ID" has 3 alternates titles.

I'd like to do a make table query where I have a concatenation of all
alternative titles per "V_ID" seperated by <br/>.

So for example, if I have 3 alternate titles for "V 221446", I'd like to
have a field that contains "Title1<br/>Title2<br/>Title3" .



Thanks in advance

uberblick





Thanks Duane.

I tried to do it by importing the module in my *.mdb but I got a bunch of
errors.

How should I write my code ?

Here is what I wrote:

SELECT Dvd.DV_ID, Concatenate("SELECT fname FROM Dcovers WHERE dv_id=" &
[DV_ID]) AS FileNames
FROM Dvd;


Regards

uberblick
Click to expand...
Click to expand...
Click to expand...
 
Thanks Duane.

It's worked. Fantastic !!!

To be honest, I have hired 3 firms/professional to build my DB and they all
screw up.

I finally decide to start from scratch and read some books, and improve my
skills. I have succeeded in obtaining some results in Access or MySQL using
GUI Navicat.

I am now at the point where I have complicated code to write for me that are
specific and don't need to be changed every month. So I'm looking for someone
who can write the code for me or assist me writing the code.

If you can refer me someone, that will be great.

Thanks for greatfull help.

Regards

uberblick

Duane Hookom said:
If DV_ID is text then you must delimit it with quotes:
SELECT Dvd.DV_ID, Concatenate("SELECT fname FROM Dcovers WHERE dv_id=""" &
[DV_ID] & """") AS FileNames FROM Dvd;

I have done some private mentoring with clients involving application
development. This works best when I can look over my client's shoulder. You
may be able to find someone in or near your location who would do this for
you.

--
Duane Hookom
Microsoft Access MVP


uberblick said:
Yes i understand.

I tried to apply your concat function and module to smallest tables so I can
understand more what I was doing.

The table1 is "dvd" and the second table is "Dcovers".

The primary key for "dvd" is "DV_ID" and "Dcovers" contains a field named
"dv_id" too. The "Dcovers" table might contains more than one occurence per
"dv_id" since, a product might have more than 1 picture.

So the goal is the same than the initial post. The "dcovers" table contain a
field name "fname" that I<d like to regroup into one string and each value
separated by a comma and a space like you're module is set up.

Data type is all text.

Also, do you give private course on access ?

Regards

uberblick






Duane Hookom said:
You haven't mention the table (or is it a query) named dcovers. Also, what is
the data type of DV_ID (or is it V_ID)? I am confused.

--
Duane Hookom
Microsoft Access MVP


:



:

There is a generic concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This
might be very slow. It also works only in Access (not in an ASP page).
--
Duane Hookom
Microsoft Access MVP


:

Hi,

I have a main table called "dvd" with unique 105,000 records where DV-ID is
my field primary key. I have field called "V_ID" in my "dvd" table that
relate to another table called "VIDEO".

The "video" table contains 340,000 records where "V_ID" is the primary key.

I have other tables that relate to my main table "dvd" thru the "VIDEO" table.

For example, I have a table called "vtitle" which contains alternate titles
for each "V_ID". The table "vtitle" might contains more than one alternate
title for a "V_ID". So I can found 3 results for a single "V_ID". This mean
that this "V_ID" has 3 alternates titles.

I'd like to do a make table query where I have a concatenation of all
alternative titles per "V_ID" seperated by <br/>.

So for example, if I have 3 alternate titles for "V 221446", I'd like to
have a field that contains "Title1<br/>Title2<br/>Title3" .



Thanks in advance

uberblick





Thanks Duane.

I tried to do it by importing the module in my *.mdb but I got a bunch of
errors.

How should I write my code ?

Here is what I wrote:

SELECT Dvd.DV_ID, Concatenate("SELECT fname FROM Dcovers WHERE dv_id=" &
[DV_ID]) AS FileNames
FROM Dvd;


Regards

uberblick
Click to expand...
Click to expand...
Click to expand...
 
Three firms that have screwed up isn't a good track record. Make sure you
always have very good specifications and a system of managing projects that
includes change control.

If you email privately with some specifics and your location, I could
probably make some recommendations. duane AT hookom DOT net. Make sure you
have a subject line like "Regarding Access Query News Group"
--
Duane Hookom
Microsoft Access MVP


uberblick said:
Thanks Duane.

It's worked. Fantastic !!!

To be honest, I have hired 3 firms/professional to build my DB and they all
screw up.

I finally decide to start from scratch and read some books, and improve my
skills. I have succeeded in obtaining some results in Access or MySQL using
GUI Navicat.

I am now at the point where I have complicated code to write for me that are
specific and don't need to be changed every month. So I'm looking for someone
who can write the code for me or assist me writing the code.

If you can refer me someone, that will be great.

Thanks for greatfull help.

Regards

uberblick

Duane Hookom said:
If DV_ID is text then you must delimit it with quotes:
SELECT Dvd.DV_ID, Concatenate("SELECT fname FROM Dcovers WHERE dv_id=""" &
[DV_ID] & """") AS FileNames FROM Dvd;

I have done some private mentoring with clients involving application
development. This works best when I can look over my client's shoulder. You
may be able to find someone in or near your location who would do this for
you.

--
Duane Hookom
Microsoft Access MVP


uberblick said:
Yes i understand.

I tried to apply your concat function and module to smallest tables so I can
understand more what I was doing.

The table1 is "dvd" and the second table is "Dcovers".

The primary key for "dvd" is "DV_ID" and "Dcovers" contains a field named
"dv_id" too. The "Dcovers" table might contains more than one occurence per
"dv_id" since, a product might have more than 1 picture.

So the goal is the same than the initial post. The "dcovers" table contain a
field name "fname" that I<d like to regroup into one string and each value
separated by a comma and a space like you're module is set up.

Data type is all text.

Also, do you give private course on access ?

Regards

uberblick






:

You haven't mention the table (or is it a query) named dcovers. Also, what is
the data type of DV_ID (or is it V_ID)? I am confused.

--
Duane Hookom
Microsoft Access MVP


:



:

There is a generic concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This
might be very slow. It also works only in Access (not in an ASP page).
--
Duane Hookom
Microsoft Access MVP


:

Hi,

I have a main table called "dvd" with unique 105,000 records where DV-ID is
my field primary key. I have field called "V_ID" in my "dvd" table that
relate to another table called "VIDEO".

The "video" table contains 340,000 records where "V_ID" is the primary key.

I have other tables that relate to my main table "dvd" thru the "VIDEO" table.

For example, I have a table called "vtitle" which contains alternate titles
for each "V_ID". The table "vtitle" might contains more than one alternate
title for a "V_ID". So I can found 3 results for a single "V_ID". This mean
that this "V_ID" has 3 alternates titles.

I'd like to do a make table query where I have a concatenation of all
alternative titles per "V_ID" seperated by <br/>.

So for example, if I have 3 alternate titles for "V 221446", I'd like to
have a field that contains "Title1<br/>Title2<br/>Title3" .



Thanks in advance

uberblick





Thanks Duane.

I tried to do it by importing the module in my *.mdb but I got a bunch of
errors.

How should I write my code ?

Here is what I wrote:

SELECT Dvd.DV_ID, Concatenate("SELECT fname FROM Dcovers WHERE dv_id=" &
[DV_ID]) AS FileNames
FROM Dvd;


Regards

uberblick
Click to expand...
Click to expand...
Click to expand...
 
Back
Top