Query using OR (AND) or both

[dcjones]

Hi all,

I have 2 tables, "tblJob" and "tblSupplers"

"tblJob" fields: job_no, suppler_no1, supplier_no2, supplier_no3.
"tblSuppers" fields: supplier_id, company_name.

Each job can have more than one suppler. I am trying to write a query that will do the following.

If job_no 1 has one supplier display the company_name. OR
if job_no 1 has 2 suppliers display both company_names. OR
if job_no 2 has 3 supplers display all three comany_names.

I have been playing with this all morning and getting no where. Can anyone help with this.

Kind regards

 

[Len Boorman]

Your tables are not normalised and therefore you will have a problem

Basically 3 tables are required.

tbl_Supplier
tbl_Job
tbl_Job_Supplier

Set up relationships between the tables and the join will look after the data extract

len

 

[mschwent]

I might be misunderstanding your problem but this worked for me:

"tblJob" fields: job_no, suppler_no1, supplier_no2, supplier_no3.
"tblSuppers" fields: supplier_id, company_name.

SELECT company_name
FROM tblJob, tblSuppliers
WHERE (job_no = [whatever condition])
AND (
(supplier_no1 = supplier_id)
OR (supplier_no2 = supplier_id)
OR (supplier_no3 = supplier_id)
)
ORDER BY job_no

 

[Len Boorman]

What happens when you have 4, 5 6 suppliers. Where do you stop.

Normalising the data will cover 1 to Many suppliers without having any code or having to amend code. Your table will be more compact with essentially no Null entries

Each Job can have many Suppliers
Each Supplier can supply to many Jobs

Many to Many relationship requiring decomposing into two 1 to many relationships


len

 

[mschwent]

You could always build the SQL query in a VBA for... next loop to the number of suppliers you have ;-)

Yes, I agree with you, normalizing the table is a much better way to tackle the problem, but in any case, to get the data with only a query and 3 suppliers, it should work.

 

[dcjones]

Hi Len,

Your not so far "warwick" to "Coventry" If the rain has not reach you yet, it's on it's way.

Thanks you for your reply. I have added a new table named "tblJob_numbers" so I now have a query of :-

SELECT tbljobs.job_no, tbljob_number.job_no, tblsuppliers.ID, suppliers.company
FROM (tbljob_number LEFT JOIN tblsuppliers ON tbljob_number.supplier_id_1 = tblsuppliers.ID) LEFT JOIN tbljobs ON jtblob_number.job_no = tbljobs.job_no;


If I run the query and enter a job number it returns the correct data. I must thank you for your input.

When I create my master form "frmMainClient" I have a subform "frmJob". The subform "frmJob" has a subfor "frmSuppliers. The "frmjob" form is bound to my "qryjobSupplier" form as is "frmSuppliers".

When I run the "frmMainClient" form and select a job (using navigation command buttons" the job changes in the "frmjob" form but all the suppliers are shown. Any ideas.

many thanks for your time on this.

 

[Len Boorman]

Okay

Do not think you can have a sub form within a subform because you cannot set up the Master Child link sub form to sub form (I think. Could be wrong)

What The subform Job lists all jobs for a client...Yes. Good

So have a pop up form that is activated by the on click event of the job number in the subform. The pop up has a record source of a query that has a parameter of the sub form job no field.

That way you can see the suppliers against a job within jobs for a client

Len

Bulkington NE of Coventry
Rain has arrived.

Normalisation is always the better way

 

[dcjones]

Hi Len.

I think your right in what you say. So I have create a popup for the supplier which work fine.

Thanks for looking at this for me. Two pairs of eyes are always better than one.

Many thanks

Dereck.