A query isn't working in a MS Access database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

A query isn't working in a MS Access database

Author

Topic

neko2
Starting Member

3 Posts

Posted - 2008-03-01 : 13:24:43

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

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-01 : 14:00:10

That's a lot of subqueries...would be more prudent to do LEFT JOINS and do this differently,


  WHERE LI.NoLiv IN (SELECT MAX(LIV.PrixCons) AS [Prix Max Livre Equivalent]
		     FROM LIVRE AS LIV 
	              WHERE LIV.NoLiv=LI.NoLiv))

That part...you may need to clarify... does LI.NoLiv translate to the Max(LIV.PrixCons) portion of the subquery?

this is your query laid out with code tags


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 INNER JOIN LIVRE as LIV on  LIV.NoLiv=LI.NoLiv
										  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;

I think there are far better ways to structure a query than using daisy-chained WHERE IN (_) clauses

Poor planning on your part does not constitute an emergency on my part.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-01 : 14:25:08

Ah yes, the "nested where-in anti-pattern" demonstrated to perfection!

http://weblogs.sqlteam.com/jeffs/archive/2007/07/12/60254.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

neko2
Starting Member

3 Posts

Posted - 2008-03-01 : 14:40:42

thank you for your answer !
Can you show me how to organise the query whith left join?
NoLiv is the number of the book in the database.
I don't know how to turn [Prix Max Livre Equivalent] into sql

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-01 : 14:53:34

I can't exactly decipher what you need, but here is a sample syntax

Select a.[Column],b.[Column],c.[Column]
FROM Table1 a LEFT JOIN Table2 b on a.aID = b.bID
LEFT JOIN Table3 c on a.aID = c.cID
Where a.[ColumnSomething] is not null AND b.[ColumnSomethingtext] = 'Text' and c.[ColumnwithValue] > 0

You should be able to design this pretty quickly using the Access design view just link the tables appropriately, then add your filters where appropriate.

In terms of the [Prix Max Livre Equivalent]

Select a.[Column],Max(b.[Column]) as MaxColumnFromB
FROM Table1 a LEFT JOIN Table2 b on a.aID = b.bID
Where a.[ColumnSomething] is not null AND b.[ColumnSomethingtext] = 'Text'
Group BY a.[Column]
Having max(b.Column) = a.[ColumnSomething]

Poor planning on your part does not constitute an emergency on my part.

neko2
Starting Member

3 Posts

Posted - 2008-03-01 : 15:10:00

Thank you, I am going to try this

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources