Re: Query Problem..

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.


.

Relevant Pages

  • Re: Query Problem..
    ... I am trying to execute the following query: ... FROM Sale s WHERE s.Company_ID = c.ID) AS ... LastSaleID) AS PackageID ... ON s.Company_ID = c.ID) INNER JOIN ...
    (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)