Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    Oct 2010
    Posts
    175

    Unanswered: Help with access query

    I don't know if anyone can look at this SQL code and tell me if they can see a problem??

    For some reason, it is returning results other than for the program id "171". It is pulling former employees' information....

    would be glad to provide further detail if someone is willing to help. Been stuck on this all day. Getting burned out. Thank you in advance. And BTW I know there shouldn't be spaces in the names, this was done a while back before I knew that was bad practice.

    SELECT DISTINCTROW [TABLE TO PULL FROM].LastName, [TABLE TO PULL FROM].FirstName, [TABLE TO PULL FROM].Address, [TABLE TO PULL FROM].City, [TABLE TO PULL FROM].State, [TABLE TO PULL FROM].Zip, [TABLE TO PULL FROM].[INSPECTION EXPIRATION], IIf([INSPECTION EXPIRATION]<Now()+30,"DUE","Not Due") AS INSPECTION, [TABLE TO PULL FROM].[REGISTRATION EXPIRATION], IIf([Registration Expiration]<Now()+30,"DUE","Not Due") AS Registration, [TABLE TO PULL FROM].[INSURANCE EXPIRATION], IIf([Insurance Expiration]<Now()+30,"DUE","Not Due") AS Insurance, [TABLE TO PULL FROM].[DRIVER LICENSE EXPIRATION], IIf([DRIVER LICENSE EXPIRATION]<Now()+30,"DUE","Not Due") AS [Driver License], [TABLE TO PULL FROM].COMMENTS1, [TABLE TO PULL FROM].COMMENTS2, [TABLE TO PULL FROM].COMMENTS3, [TABLE TO PULL FROM].COMMENTS4, [TABLE TO PULL FROM].COMMENTS5, [TABLE TO PULL FROM].AGE, [TABLE TO PULL FROM].[DRIVER LICENSE NUMBER], [TABLE TO PULL FROM].[USES PRIVATE VEHICLE], [TABLE TO PULL FROM].[USES AGENCY VEHICLE], [TABLE TO PULL FROM].[VEHICLE MAKE], [TABLE TO PULL FROM].[VEHICLE MODEL], [TABLE TO PULL FROM].VIN, [TABLE TO PULL FROM].[LICENSE PLATE], [TABLE TO PULL FROM].DATECOMMENTS1, [TABLE TO PULL FROM].DATECOMMENTS2, [TABLE TO PULL FROM].DATECOMMENTS3, [TABLE TO PULL FROM].DATECOMMENTS4, [TABLE TO PULL FROM].DATECOMMENTS5
    FROM [TABLE TO PULL FROM] INNER JOIN ([PROGRAM TABLE beginning 10-11] INNER JOIN [Transaction Table] ON [PROGRAM TABLE beginning 10-11].[PROGRAM ID] = [Transaction Table].[Program ID]) ON ([TABLE TO PULL FROM].[Individual ID] = [Transaction Table].[Individual ID]) AND ([TABLE TO PULL FROM].[Individual ID] = [Transaction Table].[Individual ID])
    WHERE ((([PROGRAM TABLE beginning 10-11].[PROGRAM ID])=171)) OR (((IIf([INSPECTION EXPIRATION]<Now()+30,"DUE","Not Due"))<>"Not Due")) OR (((IIf([Registration Expiration]<Now()+30,"DUE","Not Due"))<>"Not Due")) OR (((IIf([Insurance Expiration]<Now()+30,"DUE","Not Due"))<>"Not Due")) OR (((IIf([DRIVER LICENSE EXPIRATION]<Now()+30,"DUE","Not Due"))<>"Not Due"))
    ORDER BY [TABLE TO PULL FROM].LastName;

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Well your where clause has or sub clauses. So if you are seeing rows where the programid is not 171 then its because those rows satisfy some other element(s) of your where clause.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2010
    Posts
    175
    Yes. I don't understand, though, why when I specified in the criteria row of the programid only to display 171

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Putting the other conditions on separate rows in the design grid created tests using "OR" rather than "AND", as you can see in your SQL. That means records with a program ID of 171 OR one of the other conditions will be returned. Perhaps you want to change the OR's to AND's?
    Paul

  5. #5
    Join Date
    Oct 2010
    Posts
    175
    Thanks, Paul. I figured it was that. But how, in design view, can I make that happen?

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    By putting all the criteria on the same line.

  7. #7
    Join Date
    Oct 2010
    Posts
    175
    okay but I want the query to return 171 AND the other criteria should be OR. Does that make sense? There are four criteria in which I need to monitor from program id 171 only.

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I would just do it in SQL view with the appropriate parentheses. In design view I think you have to repeat the 171 on each line.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    okay but I want the query to return 171 AND the other criteria should be OR. Does that make sense? There are four criteria in which I need to monitor from program id 171 only.
    nope it doesn't make (logical) sense to me

    your where clause should evaluate to a simple boolean value.
    those rows that meet the criteria (ie = true) are processed, those that don't wont

    mind you you coudl tidy up the where clause to make it easier to read
    Code:
    WHERE [PROGRAM TABLE beginning 10-11].[PROGRAM ID]=171
    OR
    (
    [INSPECTION EXPIRATION]<Now()+30
    OR [Registration Expiration]<Now()+30
    OR [Insurance Expiration]<Now()+30
    OR [DRIVER LICENSE EXPIRATION]<Now()+30
    )
    I can understand the program_id = 171 bit but I don't understand the logic behind the 4 other elements.
    do you mean program id = 171 and one or more of the following.....


    WHERE A [PROGRAM TABLE beginning 10-11].[PROGRAM ID]=171
    OR
    (
    B[INSPECTION EXPIRATION]<Now()+30
    OR C[Registration Expiration]<Now()+30
    OR D[Insurance Expiration]<Now()+30
    OR E[DRIVER LICENSE EXPIRATION]<Now()+30
    )

    effectivley at present you have
    where A OR B OR C OR D OR E

    so that could be
    where A AND (B OR C OR D OR E)
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Healdem, I think the first needs to be AND like your last:

    171 AND (X OR Y OR Z)
    Paul

  11. #11
    Join Date
    Oct 2010
    Posts
    175
    To explain a bit further, I want the query to return those criteria from program 171 AND where either the inspection date, drivers license expiration, insurance expiration, OR registration is "due". I have expressions built to determine whether those criteria return a "due" result. Does that help?

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Did you try the design view solution in post 8? Or the SQL solutions, using healdem's last (using AND after the 171)?
    Paul

  13. #13
    Join Date
    Oct 2010
    Posts
    175
    I just put the "171" on each line under the program ID, and I THINK it worked. I'll know for sure later when I actually compare the data. THANK YOU FOLKS!

  14. #14
    Join Date
    Oct 2010
    Posts
    175
    Okay, so it appeared that it had worked, but after further review, I noticed that now the query pulls the programID correctly, but it also shows everyone in that program. It doesn't pull out only those with information that is "due".

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So is it working or not
    Analysing the results what is the fault
    Having worked out what isnt working apply that to your query.

    To he honest with queries its best to see the actual SQL (as you did with your first post). The query designer is ok but personally i find it limiting except when sorting out the JOIN syntax.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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