Barbara,

Boy have I been falling a lot lately ;)! (it's tough teaching yourself to ride that bike without training wheels or a parent to hold you til you're steady!)

After reading your message I've got a question. Maybe my Allcodes table isn't set up correctly. Again the structure is:
codetype - c12
codeno - c5
dispvalue - c80
And the contents are unique by a combination of the codetype *and* the codeno. That is there are dupes in the codeno. Here (I love showing examples):
Codetype Codeno Dispvalue
position 00001 CIO
position 00002 DB Manager
position 00003 Telecom Specialist
printer 00001 Laserjet
printer 00002 Inkjet
printer 00003 Dot Matrix
software 00001 Word Perfect
and so on......

Should the codeno's all be unique so I can relate them by themselves with my data tables or is there a way to relate them with a combo-key (codetype+codeno) ... although the data tables only contain the codeno... *I* have to know which codetype it refers to and was thinking I could include the specific codetype in my filters. I'm beginning to think this isn't ideal. Your thoughts???

Thanks as always,
Mandy


>Hi, Mandy. SQL is like bike riding - you have to fall off a lot, and then you find you're cruising along without thinking about it.
>
>Your first SELECT is fine, as you said. Lets try to get a position name into your contacts.
>
>SELECT contacts.persid, allcodes.dispvalue from contacts, allcodes;
>WHERE contacts.poscode = allcodes.codeno
>
>This will give you your persid field with the position name. Now lets combine this with the PERSON file to get a name & position
>
>SELECT Person.first, Person.middlei, Person.last, Person.phone, ;
> Person.email, allcodes.dispvalue FROM Person, contacts, allcodes;
> WHERE person.persid = contacts.persid;
> AND contacts.poscode = allcodes.codeno;
> INTO cursor MyCursor order by 3
>
>This links the person into contacts (by the 2 persid fields) and from there into Allcodes by the poscode & codeno fields. IF you have a contacts record for every person.persid AND an allcodes record for every contacts.poscode, you'll get all your data. If not, you need to do a join. I've ordered it by last name (field 3), just for fun.
>
>This SQL can be done in the view designer. Set up your 3 tables and link them, then just pull in the fields you want.
>
>HTH
>Barbara
>
>
>>I'm losing it here. I've been reading about SQL SELECT for the past 24 hours but can't get the syntax right for this one view I'm trying to create. Here's what I'm trying to do...
>>
>>The final view columns should be:
>>Position/Title.......First.........Middle........Last......Phone
>>
>>First, Middle, Last & Phone come from my Person.dbf
>>
>>Position/Title comes from the following:
>>
>>Person.persid related to Contacts.persid which also contains Contacts.poscode which then relates to Allcodes.codeno for Allcodes.codetype="Position". Here are partial table strucs to better illustrate.
>>
>>Person.dbf -|-----< Contacts.dbf >-----|- Allcodes.dbf
>> persid acode* codetype
>> first persid codeno
>> middle poscode dispvalue
>> etc.
>>
>>(*acode [agency code] relates to an agency table to indicate which agency a person belongs to ... another thing/filter to deal with later)
>>
>>I think I need to write a SQL Select statement with a Union Clause to create the view I need. So far I created this for the Person info which works:
>>
>> SELECT Person.first,Person.middlei,Person.last,Person.phone, ;
>> Person.email FROM Person
>>
>>and I tried this to get the person's position without any success (I think you call it a Cartesian Join ;^) ...):
>>
>> SELECT Contacts.persid,Contacts.poscode,Allcodes.dispval ;
>> FROM Contacts,Allcodes ;
>> WHERE Allcodes.codetype='position' ;
>> AND Contacts.poscode=Allcodes.codeno
>>
>>I was trying to get two SELECTs which would work individually then combine them with a UNION Clause but haven't got a clue as to how to do this especially how I tell VFP (v3.0 btw) to only relate the Allcodes.codeno's that are a particular codetype ("position" in this case).
>>
>>Any help (very detailed code if possible ;^)!) would be incredibly appreciated. Once I get this, I think I'll be on a roll for awhile with my forms.
>>
>>MTIA!
>>Mandy