Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
  1. #1
    New to the CF scene
    Join Date
    Mar 2008
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MS Access - A query isn't working

    Hello !
    I need to make a SQL query in Microsoft Access.
    The database is in French but you will easily understand, if not please ask me to translate.
    You will find the MDB Access file inside this ZIP file : http://dl.free.fr/nL03O6I60/Study.zip


    The query I need to make is :


    We want for the next sent of books to the members of l’EST-SUD (WEST-SOUTH in English), to test the equivalences of the books.

    We want to send for each book from the “genre” (theme in English) “Animaux” the most expensive equivalent book, we want to give a list with the original book, the recommended price (PrixCons in the database) and the most expensive equivalent book with his price.


    -“NoLiv” is the book number in the database.
    -“TitreLiv” is the book name (title) in the database.
    -“NoGen” is the theme (genre) number in the database.
    -“NomGen” is the theme (genre) name in the database.
    -“PrixCons” is the recommended price for the book.





    This is the query I tried, but it isn’t working :

    SELECT DISTINCT *
    FROM LIVRE AS L, EQUIVALENCE AS E
    WHERE L.NoGen IN (SELECT G.NoGen
    FROM GENRE AS G
    WHERE G.NomGen IN ("ANIMAUX"))
    AND E.NoLivEq IN (SELECT LI.NoLiv
    FROM LIVRE AS LI
    WHERE LI.NoLiv IN (SELECT MAX(LIV.PrixCons) AS [Prix Max Livre Equivalent]
    FROM LIVRE AS LIV
    WHERE LIV.NoLiv=LI.NoLiv))
    ORDER BY 2, 3;





    SECTEUR (N°Sec, NomSec)
    ADHERENT (NoAdh, NomAdh, PrenomAdh, Sexe, DateNaissance, #N°AdhResp, #N°Sec)
    MEMBRE (NoMemb, PrenomMemb, Sexe, Age, #N°Adh)
    GENRE (NoGen, NomGen, Exemples)
    LIVRE (NoLiv, TitreLiv, ThemeLiv, PrixCons, #NoGenre)
    ENVOI (#NoMem, #NoLiv, DateEnvoi)
    ETUDE (#NoMem, #NoLiv, DateEtude, EvalEt, Commentaire)
    EQUIVALENCE (#NoLiv, # NoLivEq)
    Primary keys are underlined and foreign keys have a # before them
    Last edited by neko; 03-01-2008 at 06:18 PM. Reason: The file attachement was missing, I added the link !


 

Posting Permissions

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