problem query

[Hammy Hammy]

Hi all, I have an invoice table query that returns 10 records. ie. there
are 10 invoices.

When I try to join a subjects table to retrieve the subject name
associated with an invoice it returns 11 records.

I know this is because for each invoice, there might be multiple
subjects. So when I join the subject table to get the subject's name, it
will add an extra record.

How can I get the query to return only the 10 records, but for that
single record that has 2 subjects, to show both subjects in the same
field for that record?

This query gets 11 records:
*************** *************** *********
select invoiceid,
subject.name,
files.file_numb er
from invoices
inner join files on files.file_numb er = invoices.file_n umber
inner join subject on subject.file_nu mber = files.file_numb er
where invoices.invoic eID between 3173 and 3183
order by invoiceid
*************** *************** ************

So instead of having the results look like this:

invoiceID name file_number
3173 jon 22222
3173 jane 22222

I would like:

invoiceID name file_number
3173 jon and jane 22222

Thanks.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Rich Protzel]

Re: problem query

You have two options

1) recreate the record with the duplicate ID where you include both
names in the name field and delete the 2nd record

2) assign a different ID to the 2nd record with the duplicate ID. I
will guess that your ID column is numeric and not alpha numeric. If it
were/is alpha numeric then you could do something like

3173 - 1
3173 - 2

else the 2nd 3173 gets some totally different number.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bob Quintal]

Re: problem query

Hammy Hammy <chris@thehams. ca> wrote in
news:3f6769ef$0 $62080$75868355 @news.frii.net:
[color=blue]
> Hi all, I have an invoice table query that returns 10 records.
> ie. there are 10 invoices.
>
> When I try to join a subjects table to retrieve the subject
> name associated with an invoice it returns 11 records.
>
> I know this is because for each invoice, there might be
> multiple subjects. So when I join the subject table to get the
> subject's name, it will add an extra record.
>
> How can I get the query to return only the 10 records, but for
> that single record that has 2 subjects, to show both subjects
> in the same field for that record?
>[/color]

You can't do that with a query. There is no join that will merge
multiple rows into a single one.

What you /can/ do is create a form on the main query, which does
not have the subjects table and include the subjects information on
a subform control. Besides queries should never be used for user
interface. Always encapsulate them in a form or report.

Bob Q.

[color=blue]
> This query gets 11 records:
> *************** *************** *********
> select invoiceid,
> subject.name,
> files.file_numb er
> from invoices
> inner join files on files.file_numb er = invoices.file_n umber
> inner join subject on subject.file_nu mber = files.file_numb er
> where invoices.invoic eID between 3173 and 3183
> order by invoiceid
> *************** *************** ************
>
> So instead of having the results look like this:
>
> invoiceID name file_number
> 3173 jon 22222
> 3173 jane 22222
>
> I would like:
>
> invoiceID name file_number
> 3173 jon and jane 22222
>
> Thanks.
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>[/color]