newb join query

[C L Humphreys]

Hi,

When performing a large join, say on personaldetails against various other
tables where there is a one to many relationship, obviously the first table
will be repeated many times with the other's varying details.

I only want one occurence of the other tables to be shown (so every
personaldetails , but just any (random is ok) address/order/etc/).

Can this be done easily with sql?

Cheers,
Chris

[Peter Russell]

Re: newb join query

In principle you want to create GroupBy queries for your 'many' tables and
join these to the main table.
It is possible to do this in one sql statement but you may a massive
amount of sql code to handle.
I would be inclined to build saved queries for the groups and then treat
these as source table for a final query.
Regards

Peter Russell


C L Humphreys previously wrote:
[color=blue]
> Hi,
>
> When performing a large join, say on personaldetails against various
> other
> tables where there is a one to many relationship, obviously the first
> table
> will be repeated many times with the other's varying details.
>
> I only want one occurence of the other tables to be shown (so every
> personaldetails , but just any (random is ok) address/order/etc/).
>
> Can this be done easily with sql?
>
> Cheers,
> Chris
>
>
>[/color]

[Fletcher Arnold]

Re: newb join query

"C L Humphreys" <clhumphreys@to ofgib.moc> wrote in message
news:bm3fc9$4av $1@ucsnew1.ncl. ac.uk...[color=blue]
> Hi,
>
> When performing a large join, say on personaldetails against various other
> tables where there is a one to many relationship, obviously the first[/color]
table[color=blue]
> will be repeated many times with the other's varying details.
>
> I only want one occurence of the other tables to be shown (so every
> personaldetails , but just any (random is ok) address/order/etc/).
>
> Can this be done easily with sql?
>
> Cheers,
> Chris[/color]



....to be shown...? Where? On the screen you might use a form/subform
whereas on paper you could use either a report/subreport or a single report
with grouping. Any reason why you specifically wanted a query to do this?

Fletcher

[C L Humphreys]

Re: newb join query

"Fletcher Arnold" <fletch@home.co m> wrote in message
news:bm3m3h$d5k $1@titan.btinte rnet.com...[color=blue]
> "C L Humphreys" <clhumphreys@to ofgib.moc> wrote in message
> news:bm3fc9$4av $1@ucsnew1.ncl. ac.uk...[color=green]
> > When performing a large join, say on personaldetails against various[/color][/color]
other[color=blue][color=green]
> > tables where there is a one to many relationship, obviously the first[/color]
> table[color=green]
> > will be repeated many times with the other's varying details.
> > I only want one occurence of the other tables to be shown (so every
> > personaldetails , but just any (random is ok) address/order/etc/).[/color][/color]
[color=blue]
> ...to be shown...? Where? On the screen you might use a form/subform
> whereas on paper you could use either a report/subreport or a single[/color]
report[color=blue]
> with grouping. Any reason why you specifically wanted a query to do this?[/color]

Shown on screen, as a query. I just need the details of one table and a
segment of their involvement in other tables.

Chris

[C L Humphreys]

Re: newb join query

"Peter Russell" <rusty@127.0.0. 1> wrote in message
news:memo.20031 009135745.1212N @russellscott.b tinternet.com.. .[color=blue]
> C L Humphreys previously wrote:[color=green]
> > When performing a large join, say on personaldetails against various
> > other
> > tables where there is a one to many relationship, obviously the first
> > table
> > will be repeated many times with the other's varying details.
> > I only want one occurence of the other tables to be shown (so every
> > personaldetails , but just any (random is ok) address/order/etc/).[/color][/color]
[color=blue]
> In principle you want to create GroupBy queries for your 'many' tables and
> join these to the main table.
> It is possible to do this in one sql statement but you may a massive
> amount of sql code to handle.
> I would be inclined to build saved queries for the groups and then treat
> these as source table for a final query.[/color]

Thanks for that, sounds do-able :o)
Chris

[Fletcher Arnold]

Re: newb join query

"C L Humphreys" <clhumphreys@to ofgib.moc> wrote in message
news:bm3ou1$8pk $1@ucsnew1.ncl. ac.uk...[color=blue]
> "Fletcher Arnold" <fletch@home.co m> wrote in message
> news:bm3m3h$d5k $1@titan.btinte rnet.com...[color=green]
> > "C L Humphreys" <clhumphreys@to ofgib.moc> wrote in message
> > news:bm3fc9$4av $1@ucsnew1.ncl. ac.uk...[color=darkred]
> > > When performing a large join, say on personaldetails against various[/color][/color]
> other[color=green][color=darkred]
> > > tables where there is a one to many relationship, obviously the first[/color]
> > table[color=darkred]
> > > will be repeated many times with the other's varying details.
> > > I only want one occurence of the other tables to be shown (so every
> > > personaldetails , but just any (random is ok) address/order/etc/).[/color][/color]
>[color=green]
> > ...to be shown...? Where? On the screen you might use a form/subform
> > whereas on paper you could use either a report/subreport or a single[/color]
> report[color=green]
> > with grouping. Any reason why you specifically wanted a query to do[/color][/color]
this?[color=blue]
>
> Shown on screen, as a query. I just need the details of one table and a
> segment of their involvement in other tables.
>
> Chris[/color]


Then unless there is a sensible reason not to, I would use a form/subform
combination. Not only is it easier to do in this case, but is so much more
flexible because of the form's additional properties and methods. There's
not much you can do with a query.

Fletcher