Hi All:

I have created a query based on 3 tables.

tblPotential_Partner (PK- CompanyID)
tblOpportunity_Team( PK- CompanyID,Opportunity_ID)
tblBusinessOppurtunity (PK- Opportunity_ID)

As you can see that I have a many-to-many relationship.
I want to create a search engine based on this query but the problem is the search displays no records if Opportunity table has no record associated in the Potential Partner table and vice versa. I fixed the problem for Potential Partner table by using the LEFT join but cannot get it to work for Opportunity table.

Any help would be appreciated.

Thanx
Ekta

If you dont need to return data for those records without a match in the other tbl, could you stipulate in the query criteria Is Not Null for the fields?

Thanx for replying jeremie.

I think you misunderstood me. I want to return data for the records without a match in the other table.
I am attaching my db. The search form that opens up. There you enter MASS. It is a opportunity but it wont find it because it does not have a match in the other table. If on the other hand you type TRAFFIC it will show you the result b'coz this opportunity has matching records in the Potential Partner table.

Thx again
Ekta

Anyone????

Hi

You seem to think that the problem is with your Joins but it is possible to write a simple query looking for Like "MASS*" in the criteria of the opportunity_name field and return the two records expected with blanks in such fields as company name etc.

This suggests that the fault is in your VBA which is somewhat beyond me.

Sorry if what I've written is dumb but atleast you know somebody looked at it!!:)

Brian

Hi Brian:

Thanx for taking the time to look at it and replying..:)
You are right. Previously I had 2 search engines- one for Potential Partner table and the other for BusinessOpportunity. It worked great. But now the managers here don't want to seperate search engines. They want a general search for the entire database. So I need to include both the tables in the same query. That's where I am having this problem.


Ekta

Unfortunately, you can't create a query which joins these three tables. You will have to create separate queries with each of your data tables using left outer joins on your link table. Then you can use a union query to display all the rows from the data tables, including the nulls from both sides.

Thanx AncientOne:

It makes sense. I will try it.

Thanx
Ekta

Hello AncientOne:

I have created the two queries using left outer joins.

How do I create a union query to display all the rows from the data tables, including the nulls from both sides?

Thanx
Ekta

This is not all that straightforward. Because there a differing numbers of columns in the two queries, dummy fields have to be stipulated. Rather than go through a complex explanation, I'll stitch the two queries together and post it later. Meanwhile, you might get a better insight if you trawl the forum for posts on UNION QUERIES.

Thanx so much.

Ekta

Please see attached db. The separate queries qryOpp & qryPot are used in the SQL of the union query to return all companies and all opportunities. Since a union Query must return equal numbers of columns for each query, and one of your queries has more columns than the other, you can bolt any further columns on using a further query qryAllColumns
HTH

Thanx for your help and time AncientOne.
I have one extra column "Capabilities" in qryPot. I am not sure as to how I should add it using a further query.

Ekta

Ekta

Capabilities and all other unmatched columns are invoked in qryAllColumns. Delete any columns you don't need from this query.

AncientOne:

Sorry, didn't see that :p.

I have another problem. I added my expressions in qryAllColumn.
But now if I go to the search form and search for any string using "Any Words", I din't set it up for other search options, it gives me an error

Error #3079: The specified field '[CompanyName]' could refer to more than one table listed in the FROM clause of your SQL statement.

Can you please tell me why I am getting this?
I am attaching the db.

Thanx n Regards
Ekta

Ekta
If you look at the tables in the query, you will see that CompanyName does appear in each of them. When the query runs, your expressions don't know which of these fields to use.


You have two options:

Replace the table from which you get Capabilities with a query that only uses companyID and capabilities, so the problem doesn't arise

OR

Make specific reference to the table in your expressions

e.g: Expr2: IIf(IsNull(quniOppPot.CompanyName]),False,AllWordsExist(CStr(quniOppPot.[CompanyName]),Forms!frmKeyword!txtKeyword))

The first option probably being simpler.


Your query still doesn't work: one or more of the expressions is faulty. You can eliminate them one by one to find out which are in error.

Thanx AncientOne:

Works purrrrfect...:D


Ekta

Would it be to much trouble to get that file on Access 97?

Thanks, got myself unto a little jam like that. I Think it might be useful.


l8ter...

Here you go...

Ekta

Muchas Gracias Ekta :cool: