Re: subquery

Let me explain it to you
i have a table that keeps records of Transerfers by dates, Start
Address and Destinatin Address, in this table there are item that can
be transfered from one place to another i want to select all the item
by group but with the recent dates
i made this SQL, For some reaon The Current address is not coresponding
it is giving me a nother address, it seems that it is sorting by
lastest date thank you

SELECT DISTINCT Last([transport de generatrice].ID) AS LastOfID,
[transport de generatrice].Nom, Last([transport de
generatrice].UpdateDate) AS LastOfUpdateDate, Last([BU
details_1].[Alpha name]) AS [Primary Address],
Last(CurrentAddress([transport de generatrice]![Destination_Add],[BU
details]![Alpha name],Nz([transport de
generatrice]![Destination_add_sec],""))) AS CurentADD
FROM (([transport de generatrice] INNER JOIN [BU details] ON [transport
de generatrice].Destination_Add = [BU details].BU) INNER JOIN
Generatrice ON [transport de generatrice].Nom = Generatrice.Nom) INNER
JOIN [BU details] AS [BU details_1] ON Generatrice.Primary_Location_ID
= [BU details_1].BU
GROUP BY [transport de generatrice].Nom
ORDER BY Last([transport de generatrice].ID);

Gary Walter wrote:
You can use a subquery (without an ending ";" and
an ORDER BY clause is superfluous) in a
SELECT clause,
FROM clause,
or WHERE clause.

In the SELECT clause, the subquery must provide
only "one result," i.e., only one field and only one value
for that one field.

You can "correlate" the subquery back to the main
query so that "one result" is different depending
on a field (or fields) in the main query.

What are you trying to return from the subquery?
How does the subquery relate back to the main query,
i.e., for every record in the main query, what "one result"
do you want to include from a subquery, and how is that
value arrived at in some relation between the subquery
and the main query?

Without seeing your data, I am at a loss to understand
what you are trying to do...sorry. So, my best *guess* is
that you want to save the first query, then add this query
to the second query "as a table" joining on "MaxOfID"
to return the Nom?

This "divide and conquer" method of saving queries to be
used in further processing will always prove more useful
(vs subqueries) until you understand SQL better. I think
I understand SQL well, but I am still comfortable in saving
queries with well-defined names to be used in this
"divide-and-conquer" manner.

"Rohan" wrote:
Hi this is my subquery that i have: which doesn't work :

SELECT(SELECT Max([transport de generatrice].ID) AS MaxOfID,
Generatrice.Nom
FROM (([BU details] INNER JOIN Generatrice ON [BU details].BU =
Generatrice.Primary_Location_ID) INNER JOIN [transport de generatrice]
ON Generatrice.Nom = [transport de generatrice].Nom) INNER JOIN [BU
details] AS [BU details_1] ON [transport de
generatrice].Destination_Add = [BU details_1].BU

GROUP BY Generatrice.Nom ORDER BY Max([transport de generatrice].ID)
DESC;) , [transport de generatrice].*, IIf([transport de
generatrice]![Destination_Add]="40000",[transport de
generatrice]![Destination_add_sec],[BU details]![Alpha name]) AS
Adresse
FROM ([Invent_Gen ollie] LEFT JOIN [transport de generatrice] ON
[Invent_Gen ollie].MaxOfID = [transport de generatrice].ID) LEFT JOIN
[BU details] ON [transport de generatrice].Destination_Add = [BU
details].BU;

I made this query by combining two queres
frist:
SELECT Max([transport de generatrice].ID) AS MaxOfID, Generatrice.Nom
FROM (([BU details] INNER JOIN Generatrice ON [BU details].BU =
Generatrice.Primary_Location_ID) INNER JOIN [transport de generatrice]
ON Generatrice.Nom = [transport de generatrice].Nom) INNER JOIN [BU
details] AS [BU details_1] ON [transport de
generatrice].Destination_Add = [BU details_1].BU
GROUP BY Generatrice.Nom
ORDER BY Max([transport de generatrice].ID) DESC;

Second :
SELECT
[transport de generatrice].*, IIf([transport de
generatrice]![Destination_Add]="40000",[transport de
generatrice]![Destination_add_sec],[BU details]![Alpha name]) AS
Adresse
FROM ([Invent_Gen ollie] LEFT JOIN [transport de generatrice] ON
[Invent_Gen ollie].MaxOfID = [transport de generatrice].ID) LEFT JOIN
[BU details] ON [transport de generatrice].Destination_Add = [BU
details].BU;

i am getting the fallowing error :
You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise
the SELECT statement of the subquery to request only one field. (Error
3306)

Thank you for your help !


.

Relevant Pages

  • Re: subquery
    ... Generatrice ON [transport de generatrice].Nom = Generatrice.Nom) INNER ... -- Then just join query to tables as above... ... You can "correlate" the subquery back to the main ...
    (microsoft.public.access.queries)
  • Re: How to alias a Join->Correlated Subquery
    ... I want a query to list: ... The date and name of the first programme submission planned by ... Fails with the error "You have written a subquery that can return more than ... Isn't that exactly what the "TOP 1" clause means? ...
    (microsoft.public.access.queries)
  • Re: access compact database changes results
    ... Is there any chance that the last recordyou expect in the subquery have ... reliably (compact, add more records, run query - get wrong results etc.). ... The Top 32 clause does indeed return variable numbers of records as it ...
    (microsoft.public.access.queries)
  • Re: MAX
    ... Stacked = Subquery in From Clause ... "John Spencer" wrote: ... since it does reference the main query. ...
    (microsoft.public.access.queries)
  • Re: query wont run in access 97
    ... It returns a syntax error FROM Clause. ... A Jet subquery used in a FROM clause will always ... it is wrapped in brackets with an ending period ... or the Jet query parser will choke on them. ...
    (microsoft.public.access.queries)