Complex Query (1 Viewer)

[gbnz]

I am trying to write a query from 2 tables. The first table is called Leases, holds the lease information. The second table is called Wells, holds the well information. The lease is the primary key in the first table and is joined in a one-to-many relationship to the lease in the Wells table.

What I am trying to do is set up two combo-boxes on a form. The first combo-box has a list of Leases from the first table...after you choose a lease I would like the second combo-box to display the wells from that perspective lease first, but then all of the rest of the wells after that that are present in the Wells table. Is there anyway to do that?

 

[vbaInet]

Search the forum for the term "Cascading Combo boxes".

Also perform a search on Google for the SQL keyword "DISTINCT".

 

[gbnz]

I'm sorry I should have said that I am good on cascading combo-boxes...

But I can't seem to be able to figure out what I can do with the word DISTINCT??

 

[jdraw]

I'm sorry I should have said that I am good on cascading combo-boxes...

But I can't seem to be able to figure out what I can do with the word DISTINCT??

Read thru this sample
http://www.w3schools.com/sql/sql_distinct.asp

 

[gbnz]

I am sorry maybe I am not explaining myself well enough...an example of what I am talking about follows:

Table "Lease"
Lease |
Mayburry |
Jonestown |

Table "Wells"
Well | Lease
Mayburry 1 | Mayburry
Mayburry 2 | Mayburry
Jonestown 1 | Jonestown
Howardsville 6 | Jonestown

Now when I select Mayburry in the first combo-box I would like the second combo-box to read:
Mayburry 1 | Mayburry
Mayburry 2 | Mayburry
Jonestown 1 | Jonestown
Howardsville 6 | Jonestown

But if I were to select Jonestown in the first combo-box the second should be in this order:
Howardsville 6 | Jonestown
Jonestown 1 | Jonestown
Mayburry 1 | Mayburry
Mayburry 2 | Mayburry

Is there a way to do this or am I asking Access to do to much?? I can get by with having a third hidden combo-box, but I think that it would be easier to have only two. Thanks

 

[jdraw]

I thought you said you understood comboboxes, you wanted help with DISTINCT

Here's a free video tutorial on combo boxes, see if that is helpful
http://www.datapigtechnologies.com/flashfiles/combobox2.html

 

[vbaInet]

But if I were to select Jonestown in the first combo-box the second should be in this order:
Howardsville 6 | Jonestown
Jonestown 1 | Jonestown
Mayburry 1 | Mayburry
Mayburry 2 | Mayburry

Is there a way to do this or am I asking Access to do to much?? I can get by with having a third hidden combo-box, but I think that it would be easier to have only two. Thanks

Add an extra column in the row source of the second combo box to something like:

Expr1: IIF([Field] = [Forms]![[COLOR=red]NameOfForm[/COLOR]]![[COLOR=red]ComboName[/COLOR]], 0, Null)

Sort this field first in DESC order, followed by any other fields you want to sort by. Of course you can change Expr1 to something meaningful.

So if you select Jonestown, you will end up with this:

Field1                |   Field2            |    Expr1: IIF(...)
Jonestown 1               Jonestown              0
Howardsville 6            Jonestown              0
Mayburry 1                Mayburry
Mayburry 2                Mayburry

 

[gbnz]

Perfect! That is exactly what I was looking for. Thanks so much vbaInet!!

 

[vbaInet]

You're welcome!