Listing subdetails side by side???

[ransom511]

I will be forever in debt to whomever can help me with this issue. I'm sure
I'm probably making it out to be more difficult than it really is, but here's
the basics: I've created a master list with subdetails in my table. I am
creating a report in which I want to list a particular column (3 entries) of
that subdetail side by side. Access 2003 by default places it in a new
detail listing underneath the original with all columns, since they are the
same one I am using in my detail above. There has to be a way to make this
happen. Any help is is MUCH appreciated!

I've listed a link to a photo that may help. Let me know if it does not work.
Thanks again!
http://advenet.com/photos/ransom511/images/1936/original.aspx

 

[Sylvain Lafontaine]

Is 3 the maximum number of entries and do you want to have them displayed in
3 separate columns or in the same column using something like a comma as the
separator? Also, is there always 3 columns or if sometime you only have 1
or 2?

In the first case (3 separate columns), what you are trying to achieve is
some kind of pivot. There are many ways of pivoting a table but one easy way
for you would be to add a new column with the values 0, 1 and 2 for the
first, second and third columns. You can then make two Left Join (in the
case that there will be fewer than 3 columns):

Select T1.Id, T1.LastName, T1.Company, T1.Code as Code1, T2.Code as Code2,
T3.Code as Code3

From (TheTable as T1 left join TheTable as T2 on (T1.Id = T2.Id and
T2.Number = 1)) Left Join TheTable as T3 on (T1.Id = T3.Id and T3.Number =
2)

Where T1.Number = 0

The above code is for T-SQL. For Access, I'm not sure if the parenthesis
are OK but by using the Query Designer, you should be OK for getting the
right syntaxe. If you always have three columns, then you can drop the Left
Join and use regular Inner Join instead.

Another possibility would be to use DLookup fields to retrieve the second
and third columns but this will be a lot more slower than using a query with
two (Left) Join.

The case where you want all 3 fields in the same column is similar, all you
have to do is to concatenate the three fields Code1, Code2 and Code3 into a
single field. In this case, instead of using Join, you could also use
subqueries or a VBA function in the Select part to retrieve the data and
concatenate them but this will be more complicated to do. This is usually
done when you might have a great number of columns to concatenate than just
3.