Values from multipule records in 1 record

[Vics]

Hello

I'm looking to create a querey that will take mulitple records from a table
and put them in 1 record in my query
For example, Lets say I have 2 tables, one with orders and order details,
these tables have a 1 to many relationship.
The first order has 3 items on it
so there is 1 record in the orders table and 3 related records in the order
details, in my query i would like 1 record with the order number and either
an extra column for each order item, or 1 field with multipule values in it

I'm not even sure if you can do it, I've been trying to figure it out for
while, if any one has any ideas that would be great

Thanks
Vic

 

[Jerry Whittle]

If a column for each order item is OK, check out crosstab queries in Help.

A couple of tips: First create a query that joins the two tables and returns
the records and fields that you need.

Next use the above query as the record source for the crosstab.

 

[Vics]

Hi Jerry,

I don't think a crosstab will work, I've created a query with all the data
in it, which only has 2 columns (not enough for a crosstab). OrderId and
ProductName. Each OrderId will occur in several records depeding on how many
products are on that order. So I have found the data I need, however It is
not in the correct format. I would like 1 record for each order, with the
different products listed in feilds across rather than down, or have all the
products names in 1 field
E.G.
OrderNo, ProductName, ProductName, ProductName

 

[John Spencer]

I can think of two ways to do this using queries, plus an additional way if
all you want this for is to print out a report. Printing the data in a report
is simplest.

For a query to put all the product names in one field (calculated) of the
query you can use a vba function to concatenate all the values.
Here are links (url) to three examples.

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

Allen Browne
http://allenbrowne.com/func-concat.html

The Access Web
http://www.mvps.org/access/modules/mdl0004.htm

If you want to put the products in separate fields in a query, you would need
to use a ranking query and then use the ranking query as the source for the
crosstab.

Simplest ranking query (not the fastest) to use with small sets of data

SELECT OrderNo, ProductName
, 1 + DCount("*","YourTable","OrderNo=""" & OrderNo & """ AND ProductName<"""
& ProductName & """") as Rank
FROM YourTable

A bit faster would be to use a correlated sub-query to get the rank
SELECT OrderNo, ProductName
, DCount(Select Count(ProductName) FROM YourTable as Temp WHERE Temp.OrderNo =
YourTable.ProductNo AND Temp.ProductName < YourTable.ProductName ) as Rank
FROM YourTable

Now the crosstab can be based on one of the above queries. Use Rank for the
column, First(ProductName) for the value, and OrderNo for row.

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

 

[Vics]

Thank you, that works perfectly, I had a liitle trouble with a type mismatch,
but that was easily fixed
Vicky