Re: Query Problem..

Tech-Archive recommends: Fix windows errors by optimizing your registry

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"


.

Relevant Pages

  • Re: Query Problem..
    ... I need to change the JOIN between company & sale from an 'INNER JOIN' ... I am trying to execute the following query: ... LastSaleID) AS PackageID ...
    (microsoft.public.inetserver.asp.db)
  • Re: Query Problem..
    ... I need to change the JOIN between company & sale from an 'INNER JOIN' ... I am trying to execute the following query: ... LastSaleID) AS PackageID ...
    (microsoft.public.inetserver.asp.db)
  • Query Calculation using a [Sum of ....] field
    ... a sale and gives me a total as deisred. ... When I run the query, Access treats the like a parameter. ... correct profit answer. ... FROM (tblListing INNER JOIN tblSale ON tblListing.tblListingID = ...
    (microsoft.public.access.queries)
  • Re: Summaries with zeros
    ... exist in the Sale table, i.e. if a product was not sold at a location, you ... Create a query containing the Product and Location tables. ... Total row to the grid. ... Drag the Quantity field into the grid. ...
    (microsoft.public.access.queries)
  • RE: Calculating expressions between two dates
    ... One approach would be to group a query by week, month, year etc. ... Product, SUMAS [Total Quantity] ... FROM [HTGL-HISTORY] ... Product, Sale Year, Sale Month and Sale Week. ...
    (microsoft.public.access.queries)