Combo box problem (or maybe not)

[Frankenoid]

I am a very occasional designer of databases, so my knowledge is limited.

What I need is a smarter combo box, that will use the autocomplete function to compare strings against two columns, not one. I'm doing a database to track our lawfirm's files as they go in-and-out of storage.

We have several clients for whom we've handled many litigation matters. For example, client Trout has had 5 cases: Fly Fishing, Boat Litigation, RV Dispute, Worm Farm Purchase, and Traffic Incident. The tables are set for a one-to-many with client on the one side, and case on the many side.

What I need is a combo box that will look to both the one AND the many side of the relationship; if the user types in RV, the correct case will come up, or if the user types Trout, the box will go to the list where the Trout cases are. If any of you have used QuickBooks, the Customer:Job relationship works this way.

Then, of course, we have the clients who have only one case, with nothing on the many side of the equation.

I've banged my head against this for 3 days now, and the best, inelegant, solution I've come up with is to create two queries, then combine them into a union query. The first query concantenates Client/Case into a string with client first, named Client the 2nd query into a string with Case first, also named Client, and the union combines them. However, that means that each case is listed twice -- as I said, not elegant, but it at least does the job.

There must be a better way. Can you help? Thanks

 

[Fizzio]

Do you have to use a combo? Can you not use a txtbox then use the value of the txtbox to populate 2 criteria in the combo / form underlying query using 'OR'

 

[Frankenoid]

I'd prefer a combo box for 2 reasons: the lookup function, and the autocomplete function. When you're dealing with a few hundred client case names you can't always remember how a client was referred to in the filing system (was it the company name? the company owner's name? what was the opposition called?) And, for data entry purposes, a combo box is faster. -- Franki

 

[RichMorrison]

As I understand this, you have 2 primary tables; Client and Case. A Client may have one or more cases. (May a Case belong to more than one Client ?)

You wish to find all Cases based on 2 criteria; Client name or Case keyword. (sounds like WestLaw !)

Based on those facts, a combo box will NOT do the job.

Briefly, you need a query that joins Client to Case(s) based on a common field. In this query you need 2 criteria in an "Or" relationship; Client Name = "Trout" Or Case Name Contains "RV".

Unfortunately, I just made up "Contains". You will have to use some sort of expression that searches the full Case Name for a keyword.

It is possible but not quick or easy.

RichM

 

[Frankenoid]

Yes, a case can have many clients -- and leave it to lawyers to all have a different way of naming a case -- which is why I'd like to have the combo box for efficiency.

As noted in my first post, I HAVE done an inelegant work-around: two queries, joined into a union query, but it gives me a double-listing for each case.

The SQL statement for the 1st query puts client first:

SELECT [tblCaseClient].[fldClient] & IIf([tblCaseClient].[fldCase]="","",':' & [tblCaseClient].[fldCase]) AS Client, tblCaseClient.fldCaseID
FROM tblCaseClient;

The SQL statement for the 2nd query reverses the process, putting the case reference first:

SELECT IIf([tblCaseClient].[fldCase]="","",[tblCaseClient].[fldCase] & ':') & [tblCaseClient].[fldClient] AS Client, [tblCaseClient].[fldCaseID]
FROM tblCaseClient;

A union query joins the two:

Select [Client], fldCaseID
From qryCaseName

UNION Select [Client], fldCaseID
from qryCaseName2
ORDER BY qryCaseName.Client;

The combo box on the form relies on the case ID number to identify the case.

This may be the best that I can do, but I'm such a perfectionist I'll probably keep bashing my head against it until I find someone who can help, or stumble across the answer on my own -- Franki