Alpha Video Training
Results 1 to 9 of 9

Thread: Help with queries

  1. #1
    Jamin Dunivan
    Guest

    Default Help with queries

    I have enclosed the database I am working on. In the "Code" tab there is a script called "Query". For some reason when you run it the indx2.records_get() ends up as being zero. I am working with three queries on three seperate tables all in the same script. I dont know how they could be affectiing one another.

    Thanks Jamin

  2. #2
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default RE: Help with queries

    Jamin, the sample you uploaded contains no marked records in raw answers table. so the first query returns 0 records. Values from that query recordset are then used in the other queries. Since the raw answers recordset is empty I don't see how any of the others will work either.

    I also question the syntax in the second and third query filter expressions.

    I recommend you test them separately to verify that they work as you expect, before you combine them like this. In particular your use of the tbl.name_get() method is unusual and should be checked carefully. It's been my experience that if I write a filter expression that has no syntax errors but makes no sense Alpha Five will still run the query and return an empty recordset. My guess is that perhaps that's what's happening here.

    -- tom

  3. #3
    Jamin Dunivan
    Guest

    Default RE: Help with queries

    Tom I thought the sample I sent was up to date. The one I have at home is. What your seeing will not work because it was in the midst of a rewrite. I will post the database when I get home.

    I did check the other two queries before inserting them. They ran perfectly. In fact I think there is a button on one of the forms called unmarked that runs them both.

    Thanks
    Jamin

  4. #4
    Jamin Dunivan
    Guest

    Default RE: Help with queries

    I made the corrections to the one at work. The raw_answers table has records in it. Download this one. Look at raw_answers to see that there is indeed records in it. Then run the scipt "Query". When finished check raw answers. It will look like there are no recods in it. close the database down and reopen it. Check raw_answers. Take one of the queries out before the one that queries raw_answers. It seems to work fine. The first query orders the raw_answers table by ssn then by date_called. The second query takes the ssn from the quried raw_answers table and queries the patient table with it to find the patient record. The third query takes information from the queried raw_answers and patient tables and queries the answers table. The really long complex query for the anses table works fine. I have tried it numerous times as the query on the patient table.

    Thanks jamin

  5. #5
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default RE: Help with queries

    Jamin, the first query does not simply re-order the table. It filters out all which are not marked. Since no marked records were present, it filters them all out, which is why the table appears empty when you look at it. As I mentioned before, there are no marked records in Raw Answers, so the first query returns an empty recordset.

    -- tom

  6. #6
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default RE: Help with queries

    I see now that you've changed the first query filter so that the returned recordset is not limited to marked records.

    I suggest you check the third query filter expression. It seems to me that it will always return zero records because (if I'm reading it right!) it seems to be returning those records in Answers table which contain values in the date_called field which are both less than 'date_called' in Raw Answers table *and* greater than date_called in Raw Answers table. By definition any record which meets the first criteria will fail the second. Since both are required, none are returned.

    -- tom

  7. #7
    Jamin Dunivan
    Guest

    Default RE: Help with queries

    TOM

    THANK YOU EVER SO MUCH FOR TRYING BUT I AM A COMPLETE AND UTTER IDIOT!!!!!!! The first record queried in raw_answers is a patient record that is not filled in all the way. I thought it was and that makes me an IDIOT. Works just fine after that.

    Thanks again!
    Jamin

  8. #8
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default RE: Help with queries

    Great! I'm glad you got it figured out. I was swimming in the deep end of the pool trying to figure out your filter expressions...

    Thanks for letting me know it's ok to get out of the pool!

    -- tom

  9. #9
    Member
    Real Name
    Stephen Williams
    Join Date
    Apr 2000
    Location
    Oakland, CA
    Posts
    930

    Default RE: Help with queries

    Easy on there, Jamin. If this makes YOU an IDIOT I had better kill myself now and claim the Darwin Award. This programming stuff is so easy when you get it right. I refer to my learning technique as 'amoeboid' as I crash into blank walls till I find the answer. Since I found this board I have grown fins. What I dream about are the wings...

Similar Threads

  1. SQL Queries
    By Sonia Sidhwaney in forum Web Application Server v6
    Replies: 6
    Last Post: 07-15-2005, 06:44 AM
  2. queries
    By Mike Finnerty in forum Alpha Five Version 5
    Replies: 1
    Last Post: 07-18-2003, 02:07 PM
  3. Migrating Queries
    By William R Schone in forum Alpha Five Version 5
    Replies: 1
    Last Post: 07-03-2003, 05:50 AM
  4. Queries
    By John Gagnon in forum Alpha Five Version 4
    Replies: 8
    Last Post: 03-04-2002, 02:11 AM
  5. Queries
    By Frank Cozzi in forum Alpha Five Version 4
    Replies: 1
    Last Post: 01-20-2001, 12:53 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •