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_number
from invoices
inner join files on files.file_number = invoices.file_number
inner join subject on subject.file_number = files.file_number
where invoices.invoiceID 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]

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]

Hammy Hammy <ch***@thehams.ca> wrote in
news:3f***********************@news.frii.net:

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?

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.

This query gets 11 records:
***************************************
select invoiceid,
subject.name,
files.file_number
from invoices
inner join files on files.file_number = invoices.file_number
inner join subject on subject.file_number = files.file_number
where invoices.invoiceID 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!