>Hi Cetin! Thanks for the note.
>
>Let's see.. I don't know where to begin. I guess I'll start by saying I'm still lost, which would be the completely honest thing to say. Either you "get it", when it comes to writing code, or you don't. There is very little middle ground. This is probably just a little molehill, and I'm making it seem like an expedition to the top of Mt. Everest. After I finally "get" something in FoxPro, I write any new proceedure I learn in a notebook, and it always seems so easy after you understand.

---

Jason,
I can understand, we all had the same thoughts I think. This is what I felt when I tried to understand what ASP is and getting it to work. All I knew was it existed and meant "Active Server Pages". After experimenting with changing .htm extension to .asp and getting nothing, believe me I even asked if it was a language, dll or what. I'm gratefull I should give credits to Bob Archer, Jose Marcerano for their help as well as Rick Strahl for supplying a valuable source http://www.west-wind.com/wwthreads. They showed me "mole hill" was only meters below Mt.Everest :)
I never want to hear you dropped VFP for the sake of less (should I say no) coding and switched to Access :( If you do just read my signature.

---

>
>
>I haven't created a SELECT statement from scratch before, so these questions may appear silly.
>
>1. Do I place the code you sent into the CLICK event of my cmd button on my form (which calls the specific office?). Is it that simple?

---

Yes and no. Suppose you created a form with OFFICES in DE. In init you add :

lparameters tuID
select offices
set order to tag id
set key to tuId

Form's datasession is set to private. First you try this form with id(s) that you know exists and you know that doesn't exist.

do form myTestForm with "KnownId"
do form myTestForm with "KnownId"

When you see this one is working as intended all you need is to create an interface that gets correct id and calls this form. SQL is the next step.

---

>
>2. The part of your code I didn't understand was the top part, which talked about the joins. I am a little unclear on your notations. The botton part of your code, starting with the WHERE statement was fine.
>
>select of.id ;
> from offices of ;
> join districts ds ;
> on ds.ofid = of.id ;
> join localities lo ;
> on lo.dsid = ds.id ;
> join counties co ;
> on co.loid = lo.id ;
> join states st ;
> on st.coid = co.id ;
> join countries cou ;
> on cou.stid = st.id ;
>
>**Can you walk me through this part of the code with explanations, b/c this is the one part of the code I am a little confused? I think that would be most helpful.
>
>If I had a better handle on this, I could create the code visually using the view designer and then copy the code.

---

Create your SQL with designer as you're comfortable (but at some point you should certainly switch to writing code b/c designer is not capable of what you can really get out from it).
After creating and testing (supply known values for testing and check results fit your expectations) SQL in query designer, study, cut, copy modify code and test etc. (in designer rightclick in upper window gives you chance to "view SQL" and copy).
Now telling joins here might not be understandable (my VFP code language is certainly better than my daily English). I'll try and also I advise top check ie: home()+"samples\data\testdata" which has a view using joins. You can see visually and check SQL code.
Step by step :

select of.id ;
 from offices of ;
   join districts ds ;
      on ds.ofid = of.id

Here there is some lazy coding. Instead of writing "offices" or "districts" everytime I used "of" and "ds".
"of.id" same as "offices.id"
"ds.ofid" same as "districts.ofid"
"of" and "ds" assignments are done in "from offices of" and "join districts ds". Original tablename followed by "alias" I want to assign.

   from offices of ;
    join districts ds ;
      on ds.ofid = of.id

Joins "of" and "ds" tables. It correspond to "inner join" ("left outer", "right outer" and "full" are others) and this was the only available option in old FP2x expressed as :

   from offices of, districts ds ;
    where ds.ofid = of.id

If you're comfortable with "where" then you could use this way. But remember this is only for "inner join". So what the hell are those ... joins :
inner join : Checked item must exist on both tables to return a row. ie:

table1   table2
t1id     t2id
------   ------
v1       v2
v2       v2
v3       v1
         v2
         v4
select * from table1 ;
	join table2 on table1.t1id = table2.t2id
* or	- "where" style could be used since this is "inner join"
select * from table1, table2 ;
	where table1.t1id = table2.t2id
* Would return
table1   table2
t1id     t2id
------   ------
v2       v2
v2       v2
v1       v1
v2       v2

As you can see you get results for v1, v2 and they "exist in both". Think this is customer,orders tables.
What if you want to get all customers regardless they have any order or not. In Fox2x you could do this with union (just know the name for now) + subquery.
Wow in VFP you code much less and understandable. This is "Left join" that means I want all from left side table :

select * from table1 ;
	left join table2 on table1.t1id = table2.t2id  && Left side is table1
* Would return
table1   table2
t1id     t2id
------   ------
v1       v1
v2       v2
v2       v2
v2       v2
v3       NULL

t2id is NULL for the unmatching record. So cool we didn't miss customer v3.
Now say we want to find matching customers + orphaned orders. Right side is orders (table2) so we use "right join" :

select * from table1 ;
	right join table2 on table1.t1id = table2.t2id && Right side is table2
* Would return
table1   table2
t1id     t2id
------   ------
v2       v2
v2       v2
v1       v1
v2       v2
NULL     v4

Now t1id is NULL for the unmatching record. Good just one orphaned, check would be easy :). Now for the last join, we want all customers + all orders whether or not they have match on other table. Good guess :) Full join :

select * from table1 ;
	full join table2 on table1.t1id = table2.t2id
* Would return
table1   table2
t1id     t2id
------   ------
v1       v1
v2       v2
v2       v2
v2       v2
v3       NULL
NULL     v4

So far so good. But wait we have four joins in our "offices" SQL. How would it be interpreted. In simple (wished if that was this simple all the time) each join section would return a set to next join.
That is :

table1   table2       table3
t1id     t2id   t2cid t3id
------   ------ ----- ------
v1       v2     c1    c4  
v2       v2     c2    c5
v3       v1     c3
         v2     c4
         v4     c5

select * from table1,table2,table3 ;
  where table3.t3id = table2.t2cid ;
   and table1.t1id = table2.t2id
* or - warning prev where implementation works b/c this is still "inner join"
select * from table1 ;
	join table2 on table1.t1id = table2.t2id ;
	join table3 on table3.t3id = table2.t2cid 
* Would return
table1   table2       table3
t1id     t2id   t2cid t3id
------   ------ ----- ------
v2       v2     c4    c4

Notice that if you use just "join" (w/o left, right or full) it defaults to "inner join".
Now since we only deal with t3id (that would be offices.id in our real data with more joins) we would write it as :

select table3.t3id from table1,table2,table3 ;
  where table3.t3id = table2.t2cid ;
   and table1.t1id = table2.t2id
* or - warning prev where implementation works b/c this is still "inner join"
select table3.t3id from table1 ;
	join table2 on table1.t1id = table2.t2id ;
	join table3 on table3.t3id = table2.t2cid

We only add one more join, test and step to next, OK ?
When "join" part is completed you would start to add "where" part, each time only one, test and step to next. At the end your SQL would look like the one I originally sent. But during adding and testing part use variables instead of "thisform.cmb..." so you could easily do it outside of the form.
When completed (hey nearly finished) add destination part (..into array..) and change variable names with the matching form combo names. Cool ! Now we have an SQL getting and id into array using form combos (and prepared OFFICES form in first step) :)
Do final cut and place "if type(...) ... do form ... endif" block :-) Here we go :)

---

>
>Am I correct in saying that the only table I would throw into my DE of my view designer is my OFFICES table? Then.... just select the fields I want to join.... and finish the code with the WHERE statement, if, else, endif...

---

Hmmm. I'm no good with the designer but you should have all tables in "query designer". BTW we are designing a "query" not "view". I don't think you would need a view (fairly acceptable though).
OTOH "OFFICES" would be the only table that you would throw in your OFFICES form's DE (created in first step).

---

>
>One last note: My OFFICES form only has the last 3 combo boxes represented on it. I saw in your reply that you thought I should have all six in my SELECT, but I only need my last three. I have a JURISDICTION AND 3 ASSIGNMENT tables that keep track of what state, county, & country an office belongs to... therefore, in order to call the OFFICE FORM, I only have to "match" the last three combo boxes (locality, district, office title). I have my database set up so you can only ASSIGN a specific office to a locality (city), nothing above that..

---

Well I thought each localty could have many districts and districts could have many offices. You know your tables much better than I do :) So SQL is shortened to less "join" and "where" :)

---

>
>
>Thanks for any additional input. I hope this message isn't to confusing
>
>Jason

---

Wait not finished :) We came a long way to get OFFICES.id via SQL and fire our (I feel it's also mine now:) OFFICES form.
Now a radical action though I'm not a radical. Throw SQL away !
As someone said "Something could be done in Fox at least 3 ways or cannot be done".
This was a way. But might be better, faster ways :)
On your main form where combos reside, you can go down to a particular office, right ? So why use SQL to find out what we already have. The last (or localties) combo knows the specific office. If its combo doesn't have a column containing "officeid" add it. Assuming now it's in "cmbOffice" combo's 3rd column :

* This code would go to "Launch OFFICES form" button's click instead of "SQL, if..do form Offices...endif" 
with thisform.cmbOffice   && With..endwith improves performance if the same object to be sought more than once
     myOffId = .List(.ListIndex,3)  && Get 3rd column's value
endwith
if !empty(myOffId)  && User selected a valid office
   * Warning combo returns char values
   * If Id is something else ie: Integer then do conversion first
   do form OFFICES with myOffId
endif

That's all :)
Tip: For the combo if you don't want the "Id" (3rd column) to be seen, go to PEM sheet and set columnwidths as 100, 100, 0.
(100,100 are current values for 1st and 2nd columns, do not touch them, set 3rd column width to 0 - our Id column).
Lastly if the steps were slower than should be I apologize for it.
I hope to have a "Working" reply :)
Cetin