sql question

[pmpmpm1]

Hello,

I've a problem with SQL, can you help me?

My database has 2 tables (concerning the problem)
1. = invoices (invoicenr, name, date, etc..)
2. = invoice lines (unique_linenr, invoicenr, article, price, etc..)

the relation is one to many (invoice -->> invoice_lines)

i want to select all the invoices (or the numbers) where the lines
contains 2 articles, e.g. "give me all the invoices that contains a
line with "article 1" and a line with "article 2" and maybee more
lines.

how can i obtain this in Access??

regards

Pascal

[lesperancer]

something like
select invoicenbr from tblInvoiceLines where count(*) > 1 group by
invoicenbr

I assume line #1 on an invoice could be deleted, so it's possible for a
invoice to have multiple lines and no line 1

[pmpmpm1]

thats is not what i need,

i want to select all invoices with the article "car" and the article
"radio"
or
i want to select all invoices with the article "cow" and the article
"dog"

....

[lesperancer]

select a.* from tblInvoiceLines as a inner join
tblInvoiceLines as b on a.invoicenbr =
b.invoicenbr where a.article = "car" and b.article = "radio"

join the table to itself, looking for 2 articles on the same invoice #

[pmpmpm1]

thx,

but..... in ms access it gives me the error that a.article is a
variable, so i have a pop-up window to fill in the value of a.article.

who can help???

regards

Pascal

[pmpmpm1]

Please,

if you read this have a look at the question and if you can help, give
me a reaction!

regards

Pascal

[lesperancer]

to confirm...

'article' is a field in tblInvoiceLines ?

cause I just create a tblInvoiceLines table with fields invoiceNbr and
article (access 97)
and it works fine

does this work ?
select a.* from tblInvoiceLines as a inner join
tblInvoiceLines as b on a.invoicenbr =
b.invoicenbr where a.[article] = "car" and b.[article] = "radio"

[pmpmpm1]

Thanks !!

this works fine

Regards

Pascal