Need help with Views 
Author Message
 Need help with Views

I am having a problem with a view I am trying to set-up in VFP 5. I have a
file that hold driving test appointments, in this file amongst other things
are a person code, a examiner code and a instructor code. I wanted to create
a view with a couple of fields out of the Tests file and instead of showing
the codes I wanted to show the examiner name, instructor name and so on. I
tried using the view Wizard at first and I answered all the questions but
when it tried to build the view at the end I kept getting a error message
that one of the fields was missing. So I tried to use the view designer, but
I can't seem to get the four tables to join correctly, and show the proper
results. Maybe I am asking to much but I though this is what views where
for. Also when in the view designer and on the join page sometimes it will
not let me select the criteria it is although VFP doesn't know where the
mouse pointer is. When I finally get all the joins into the designer it will
run (although not the correct results) but then I try and save the view and
I get a error message saying one of the fields can't be found and VFP puts
me back into the SQL statement. I am running VFP 5.0 on Windows 98.

Kind Regards,

David Baxter



Mon, 23 Apr 2001 03:00:00 GMT  
 Need help with Views
You're not asking om uch at all. Well, maybe from the View Desinger which
mostly gets the order the query should the load the tables wrong if there
are more than three involved.
This is<the pattern to use
   FROM T1 ;
        JOIN T2 ;
            JOIN T3 ;
                JOIN T4 ;
                ON T4.key = T3.key ;
            ON T3.key = T2.key
        ON T2.key = T1.key
The query opens the tables starting with the innermost pair. Once a table
has beeen opened by the query its alias can be used in the following ON
clauses. You will get the error you saw if ytou try this
   FROM T1 ;
        JOIN T2 ;
            JOIN T3 ;
                JOIN T4 ;
                ON T4.key = T2.key ;  <<<<error
            ON T3.key = T1.key ; <<<< error
        ON T2.key = T1.key
because T2 is not in the innermost pair and hasn't been opened by the query
yet.
The fix is to reorder either the FROM clause or the ON clauses.
Another fix is to use the earlier SQL syntag without JOIN and ONB and set up
the links in the WHERE clause. Then order doesn't matter.
  FROM T1, T2, T3, T4 WHERE T1=T2 AND T3=T2 AND T4=T3
-Anders

-Anders


Quote:
>I am having a problem with a view I am trying to set-up in VFP 5. I have a
>file that hold driving test appointments, in this file amongst other things
>are a person code, a examiner code and a instructor code. I wanted to
create
>a view with a couple of fields out of the Tests file and instead of showing
>the codes I wanted to show the examiner name, instructor name and so on. I
>tried using the view Wizard at first and I answered all the questions but
>when it tried to build the view at the end I kept getting a error message
>that one of the fields was missing. So I tried to use the view designer,
but
>I can't seem to get the four tables to join correctly, and show the proper
>results. Maybe I am asking to much but I though this is what views where
>for. Also when in the view designer and on the join page sometimes it will
>not let me select the criteria it is although VFP doesn't know where the
>mouse pointer is. When I finally get all the joins into the designer it
will
>run (although not the correct results) but then I try and save the view and
>I get a error message saying one of the fields can't be found and VFP puts
>me back into the SQL statement. I am running VFP 5.0 on Windows 98.

>Kind Regards,

>David Baxter




Tue, 24 Apr 2001 03:00:00 GMT  
 Need help with Views
David,

Yes this is possible.  Unfortunately the View Designer is really only
a basic tool.  To do 4 tables, I would suggest writing it in code.
Something like the following:

CREATE SQL VIEW ViewName AS;
  SELECT ;
    CustTbl.*,;
    ;
    RegionTbl.Region,;
    ;
    CorpTbl.Dscr,;
    ;
    CodeTbl.Dscr;
  FROM ;
    CustTbl;
      INNER JOIN;
    RegionTbl;
      ON CustTbl.RegionCd = RegionTbl.RegionCd;
      INNER JOIN;
    CorpTbl;
      ON RegionTbl.CorpCd = CorpTbl.CorpCd;
      INNER JOIN;
    CodeTbl;
      ON CustTbl.AcctCd = CodeTbl.LkupCd;
  WHERE ;
        CustTbl.AcctNmbr = ?lcAcctNmbr

If you want it updatable you will have to set some view and
field properties.

-myron kirby-
Independent Consultant

===========================


Quote:
>I am having a problem with a view I am trying to set-up in VFP 5. I have a
>file that hold driving test appointments, in this file amongst other things
>are a person code, a examiner code and a instructor code. I wanted to
create
>a view with a couple of fields out of the Tests file and instead of showing
>the codes I wanted to show the examiner name, instructor name and so on. I
>tried using the view Wizard at first and I answered all the questions but
>when it tried to build the view at the end I kept getting a error message
>that one of the fields was missing. So I tried to use the view designer,
but
>I can't seem to get the four tables to join correctly, and show the proper
>results. Maybe I am asking to much but I though this is what views where
>for. Also when in the view designer and on the join page sometimes it will
>not let me select the criteria it is although VFP doesn't know where the
>mouse pointer is. When I finally get all the joins into the designer it
will
>run (although not the correct results) but then I try and save the view and
>I get a error message saying one of the fields can't be found and VFP puts
>me back into the SQL statement. I am running VFP 5.0 on Windows 98.

>Kind Regards,

>David Baxter




Tue, 24 Apr 2001 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Need help with views and grids

2. View help needed

3. Need more view help!

4. I NEED HELP I NEED HELP CUSTOMIZING EXCHANGE 5.0

5. HELP,HELP,HELP need help fast

6. I Need Help needed generating a special report!!!

7. Help needed with ONLINE HELP system inside .EXE file

8. Need Help For Customize Help In Foxpro for Dos 2.5

9. (*.HLP) : Need help to build Windows 3.x Help Files

10. Need more help with HELP!!!!!!

11. Help Newibe needs Basic help

12. HELP! NOVICE NEEDS VFP 5 REPORT HELP

 

 
Powered by phpBB® Forum Software