Query Problem..

Hi all,

I am trying to execute the following query:

SELECT
c.ID, c.Company_Name,
(SELECT Max(ID) FROM Sale s WHERE s.Company_ID = c.ID) AS LastSaleID,
(SELECT s.Package_ID FROM Sale s WHERE s.ID = LastSaleID) AS PackageID
FROM
Company AS c

My problem is, 'LastSaleID' isn't picked up as a column in of the main query
in the second sub query. If i was to use another column in the main query
this problem dissappears.

I am unfortunatel using Access; any help appreciated to get this to work as
needed.

Cheers,
Adam

Re: Query Problem..

AJ wrote:
> Hi all,
>
> I am trying to execute the following query:
>
> SELECT
> c.ID, c.Company_Name,
> (SELECT Max(ID) FROM Sale s WHERE s.Company_ID = c.ID) AS
> LastSaleID, (SELECT s.Package_ID FROM Sale s WHERE s.ID =
> LastSaleID) AS PackageID
> FROM
> Company AS c
>
> My problem is, 'LastSaleID' isn't picked up as a column in of the
> main query in the second sub query. If i was to use another column in
> the main query this problem dissappears.
>
> I am unfortunatel using Access; any help appreciated to get this to
> work as needed.
>
I can't test it, but try this:

SELECT
c.ID, c.Company_Name,
s.LastSaleID,
p.PackageID
FROM
(Company AS c INNER JOIN
(SELECT Company_ID, Max(ID) AS LastSaleID FROM Sale
GROUP BY Company_ID) As s
ON s.Company_ID = c.ID) INNER JOIN
Sale p ON p.ID = s.LastSaleID

If Access complains about the subquery in thre FROM clause, create a saved
query called MaxIDPerCompany with this sql:
SELECT Company_ID, Max(ID) AS LastSaleID FROM Sale
GROUP BY Company_ID

And change the above query to:
SELECT
c.ID, c.Company_Name,
s.LastSaleID,
p.PackageID
FROM
(Company AS c INNER JOIN
MaxIDPerCompany As s
ON s.Company_ID = c.ID) INNER JOIN
Sale p ON p.ID = s.LastSaleID



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Query Problem..

Hi Bob,

Thanks for the reply...!

Another quick question though...

I need to change the JOIN between company & sale from an 'INNER JOIN'
to a 'LEFT JOIN'. Access won't support it. Is their another way to construct
this query to acheive this???

Regards,
Adam

"Bob Barrows [MVP]" wrote:

> AJ wrote:
> > Hi all,
> >
> > I am trying to execute the following query:
> >
> > SELECT
> > c.ID, c.Company_Name,
> > (SELECT Max(ID) FROM Sale s WHERE s.Company_ID = c.ID) AS
> > LastSaleID, (SELECT s.Package_ID FROM Sale s WHERE s.ID =
> > LastSaleID) AS PackageID
> > FROM
> > Company AS c
> >
> > My problem is, 'LastSaleID' isn't picked up as a column in of the
> > main query in the second sub query. If i was to use another column in
> > the main query this problem dissappears.
> >
> > I am unfortunatel using Access; any help appreciated to get this to
> > work as needed.
> >
> I can't test it, but try this:
>
> SELECT
> c.ID, c.Company_Name,
> s.LastSaleID,
> p.PackageID
> FROM
> (Company AS c INNER JOIN
> (SELECT Company_ID, Max(ID) AS LastSaleID FROM Sale
> GROUP BY Company_ID) As s
> ON s.Company_ID = c.ID) INNER JOIN
> Sale p ON p.ID = s.LastSaleID
>
> If Access complains about the subquery in thre FROM clause, create a saved
> query called MaxIDPerCompany with this sql:
> SELECT Company_ID, Max(ID) AS LastSaleID FROM Sale
> GROUP BY Company_ID
>
> And change the above query to:
> SELECT
> c.ID, c.Company_Name,
> s.LastSaleID,
> p.PackageID
> FROM
> (Company AS c INNER JOIN
> MaxIDPerCompany As s
> ON s.Company_ID = c.ID) INNER JOIN
> Sale p ON p.ID = s.LastSaleID
>
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

Re: Query Problem..

You need to change both joins to LEFT.

AJ wrote:
> Hi Bob,
>
> Thanks for the reply...!
>
> Another quick question though...
>
> I need to change the JOIN between company & sale from an 'INNER JOIN'
> to a 'LEFT JOIN'. Access won't support it. Is their another way to
> construct this query to acheive this???
>
> Regards,
> Adam
>
> "Bob Barrows [MVP]" wrote:
>
>> AJ wrote:
>>> Hi all,
>>>
>>> I am trying to execute the following query:
>>>
>>> SELECT
>>> c.ID, c.Company_Name,
>>> (SELECT Max(ID) FROM Sale s WHERE s.Company_ID = c.ID) AS
>>> LastSaleID, (SELECT s.Package_ID FROM Sale s WHERE s.ID =
>>> LastSaleID) AS PackageID
>>> FROM
>>> Company AS c
>>>
>>> My problem is, 'LastSaleID' isn't picked up as a column in of the
>>> main query in the second sub query. If i was to use another column
>>> in the main query this problem dissappears.
>>>
>>> I am unfortunatel using Access; any help appreciated to get this to
>>> work as needed.
>>>
>> I can't test it, but try this:
>>
>> SELECT
>> c.ID, c.Company_Name,
>> s.LastSaleID,
>> p.PackageID
>> FROM
>> (Company AS c INNER JOIN
>> (SELECT Company_ID, Max(ID) AS LastSaleID FROM Sale
>> GROUP BY Company_ID) As s
>> ON s.Company_ID = c.ID) INNER JOIN
>> Sale p ON p.ID = s.LastSaleID
>>
>> If Access complains about the subquery in thre FROM clause, create a
>> saved query called MaxIDPerCompany with this sql:
>> SELECT Company_ID, Max(ID) AS LastSaleID FROM Sale
>> GROUP BY Company_ID
>>
>> And change the above query to:
>> SELECT
>> c.ID, c.Company_Name,
>> s.LastSaleID,
>> p.PackageID
>> FROM
>> (Company AS c INNER JOIN
>> MaxIDPerCompany As s
>> ON s.Company_ID = c.ID) INNER JOIN
>> Sale p ON p.ID = s.LastSaleID
>>
>>
>>
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so
>> I don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.