Tables and queries

[colonelswizz]

I have a table that has 2 columns. The first column (ID) is the relationship
key between another table. The 1st colomn has repeating entries and the
corresponding column has different entries that relate to the first column.

Example

Column 1 Column2
ID Place
1234 forestbrook1
1234 forestbrook2
1234 forest brook3
1234 forestbrook4

When I do a simple query with another table I get results that repeat the
first column data with the 2nd table data because ID is related.

Example
Type Agent ID Place
RA Mark 1234 forestbrook1
RA Mark 1234 forestbrook2
RA Mark 1234 forestbrook3
RA Mark 1234 forestbrook4

What I want the query to do is put the information from the "Place" column
in the same row.

Example
Type Agent ID Place Place
Place
RA Mark 1234 forestbrook1 forestbrook2 forestbrook3

Is this possible and how do I do it? Any help is much appreciated.

 

[Ken Sheridan]

If there is another column on which you can base a PIVOT clause to provide
the column headings you could use a crosstab query, but the data as you've
shown it doesn't allow that. Rather than a query I'd suggest using a report
with a multi-column subreport:

Create a report based on the table containing Type and Agent columns (or on
a query on that table if you need to restrict the results). Put the controls
bound to these in the left part of the detail section.

Create a report for use as the subreport based on the other table. In
report design view, from the File | Page Setup menu item set the report's
column layout to 'Across Then Down' and its number of columns and the column
widths to however many will fit in the remaining space to the right of the
controls in the first report's detail section.

Embed the second report as a subreport in the first immediately to the right
of the controls already in the first report. Link it on the ID columns by
setting this as the LinkMasterFields and LinkChildFields properties of the
subreport control.

Put labels for each column in the main report's Page Header section,
positioning them so that they are above the columns from the subreport when
the report is opened – you'll probably need to experiment to fine tune the
positions of the labels.

As it happens I did post a file illustrating this at:


http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=24271


The file was originally produced in answer to a reader's enquiry for a
magazine column written by a contact of mine and was for listing club members
per address horizontally, but the method is the same as would be the case
with your files. As well as the subreport solution it also includes a
solution using a single report where the layout is amended in code at
runtime. That was really only produced as an example of how code in a
report's module can be used; I wouldn't recommend it as a serious solution as
the subreport approach is far simpler and requires no code whatsoever.

One thing you'll see from the demo file is that if there are too many
members per address to fit the number of columns across the page then they
simply wrap to another row.

Ken Sheridan
Stafford, England

 

[John W. Vinson]

What I want the query to do is put the information from the "Place" column
in the same row.

Example
Type Agent ID Place Place
Place
RA Mark 1234 forestbrook1 forestbrook2 forestbrook3

Is this possible and how do I do it? Any help is much appreciated.

If this is just for display purposes, you can return a concatenated string of
related-record values (separated by blanks or commas or whatever you want) in
one field. You cannot have three fields with the same name (but for displaying
the related values you don't really need to). See the sample code at

http://www.mvps.org/access/modules/mdl0004.htm