Merge Data in diffrent way

[Rahul Gupta]

Hello All,

I have a database such that i have a list of cereals and related products.

Cereals
_______
Wheat
Rice


Products Procedure
________ _________
Bread ...
Biscuits ...
Flour ...

I want to fire a SQL query such that they are merged as shown below:


Wheat Bread (New Line) Biscuits (New Line) Flour ...(New Line)...(New
Line)...

Visually they will be displayed as

Cereals Products Procedure
_______ ________ _________
Wheat Bread ...
Biscuits ...
Flour ...

How can i do it? I am using access 2003. is it possible through functions in
access?

Please guide me.

Rahul.

 

[storrboy]

Hello All,

I have a database such that i have a list of cereals and related products.

Cereals
_______
Wheat
Rice

Products Procedure
________ _________
Bread ...
Biscuits ...
Flour ...

I want to fire a SQL query such that they are merged as shown below:

Wheat Bread (New Line) Biscuits (New Line) Flour ...(New Line)...(New
Line)...

Visually they will be displayed as

Cereals Products Procedure
_______ ________ _________
Wheat Bread ...
Biscuits ...
Flour ...

How can i do it? I am using access 2003. is it possible through functions in
access?

Please guide me.

Rahul.

Not without knowing how to relate them and I assume you mean to
actually add a field to one of the tables. You'd have to define in
some way what products relate to what cereals. Depending on the number
of products, I would probably do it in a function after manually
adding the field to the table.

 

[Rahul Gupta]

Both are related fields. CerealsID is key that links both data.

Can you please explain how can it be done it through function.

Rahul.

 

[storrboy]

Both are related fields. CerealsID is key that links both data.

Can you please explain how can it be done it through function.

Rahul.

What I mean by relating them is what products are Wheat, and what are
Rice. If your tables look like your original example, then there is
nothing currently in the products table saying Flour=Wheat. If you
have 500 Wheat products and 2 Rice, then it would easier to manually
fill in the Rice and use an Update query to fill in the remaining
records.
In bare bones terms the function would look like this..

Sub UpdateCreals
Dim db As Database
Dim rst As Recordset

Set db=CurrentDb
Set rst=db.OpenRecordSet("[ProductsTableName]",dbOpenDynaset)

Do Until rst.EOF
rst.Edit
Select Case rst![ProductsIDName]
Case "something","something","something"
rst!CerealsID = "Wheat"
Case "somethingElse","somethingElse","somethingElse"
rst!CerealsID = "Rice"
Case Else
rst!CerealsID = Null
End Select
rst.Update
Loop

rst.Close
Set rst=Nothing
Set db=Nothing

Without knowing more specifics about products, related cereals and how
many variations there are, I can't be more specific.
See if you can make something out of the above. Look through the help
files for more.

 

[Rahul Gupta]

This is something i know.

Let me explain it in a diffrent way.

There are two tables as provided, with provided an additional primary key.

I want a ROW which returns in normal procedure as below:

Cereals Products Procedure
_______ ________ _________
Wheat Bread ...
Wheat Biscuits ...
Wheat Flour ...

Above are the three rows returned. now i do not want this. I want only one
row to be returned as

Cereals Products Procedure
_______ ________ _________

Wheat Bread (New Line) Biscuits (New Line) Flour ...(New Line)...(New
Line)...

which can be visualised as below:

Cereals Products Procedure
_______ ________ _________
Wheat Bread ...
Biscuits ...
Flour ...

Now this is just one row of data returned.

I want this...

Hope you get me this time.

Regards,
Rahul.

Both are related fields. CerealsID is key that links both data.

Can you please explain how can it be done it through function.

Rahul.

What I mean by relating them is what products are Wheat, and what are
Rice. If your tables look like your original example, then there is
nothing currently in the products table saying Flour=Wheat. If you
have 500 Wheat products and 2 Rice, then it would easier to manually
fill in the Rice and use an Update query to fill in the remaining
records.
In bare bones terms the function would look like this..

Sub UpdateCreals
Dim db As Database
Dim rst As Recordset

Set db=CurrentDb
Set rst=db.OpenRecordSet("[ProductsTableName]",dbOpenDynaset)

Do Until rst.EOF
rst.Edit
Select Case rst![ProductsIDName]
Case "something","something","something"
rst!CerealsID = "Wheat"
Case "somethingElse","somethingElse","somethingElse"
rst!CerealsID = "Rice"
Case Else
rst!CerealsID = Null
End Select
rst.Update
Loop

rst.Close
Set rst=Nothing
Set db=Nothing

Without knowing more specifics about products, related cereals and how
many variations there are, I can't be more specific.
See if you can make something out of the above. Look through the help
files for more.

 

[John W. Vinson]

Cereals Products Procedure
_______ ________ _________
Wheat Bread ...
Wheat Biscuits ...
Wheat Flour ...

Above are the three rows returned. now i do not want this. I want only one
row to be returned as

Cereals Products Procedure
_______ ________ _________

Wheat Bread (New Line) Biscuits (New Line) Flour ...(New Line)...(New
Line)...

which can be visualised as below:

Cereals Products Procedure
_______ ________ _________
Wheat Bread ...
Biscuits ...
Flour ...

This cannot easily be done in a Query - but it can be done in a
Report. Set the "Hide Duplicates" property of the report textbox bound
to Cereals to Yes.

John W. Vinson [MVP]