This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(220.8, 15%, 31%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ELJ%3C/text%3E%3C/svg%3E)
I have three tables
tblShipperHead(ShipNum, ShipDate, Qty1)
tblMinMax(PTNO, Max, CustInv, LastQtyRecd, LastDateRecd, CustInv)
tblPartInv(OnHand)
Desired results set, but not showing the derived field. It is for data filtering.
Want to use the tables to arrive at the Last Shipment of each PTNO in tblMinMax.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(96, 26%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYK%3C/text%3E%3C/svg%3E)
While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(137.6, 31%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ENN%3C/text%3E%3C/svg%3E)
So what exactly is the issue? You can create a new computed column at the table level or you can add this column in the view (you can create view in code, right)?
Good questions, and I neglected to show the current statement.
Here is what I have and it does not return the proper quantity associated with the last shipment.
In this case, the SQL is within Access DB. A bit of the syntax was cleaned in the spreadsheet provided. Here is the raw statement. It pains me to share it as it is dismal.
SELECT Max(tblShipperHead.ShipNum) AS LastShipNum, Max(tblShipperHead.ShipDate) AS LastShipDate, Last([Qty1]) AS Qty, tblMinMax.PTNO, tblMinMax.BoeInv, tblMinMax.Max, tblMinMax.LastQtyRecd, tblMinMax.LastDateRecd, tblPartInv.OnHand
FROM (tblShipperHead INNER JOIN tblMinMax ON tblShipperHead.PrinciplePart = tblMinMax.PTNO) INNER JOIN tblPartInv ON tblMinMax.PTNO = tblPartInv.PTNO
GROUP BY tblMinMax.PTNO, tblMinMax.BoeInv, tblMinMax.Max, tblMinMax.LastQtyRecd, tblMinMax.LastDateRecd, tblPartInv.OnHand
HAVING (((tblMinMax.BoeInv)<[tblMinMax].[Max]))
ORDER BY tblMinMax.PTNO;
You should probably ask this in Access forum (if there is one - try adding appropriate tag). The SQL syntax for Access is different than SQL Server and I'm afraid I cannot help :(
In addition, you help yourself a lot if you include CREATE TABLE statements for your tables and INSERT statements with sample data together with the expected result given the sample.
That makes it easy to copy and paste into a query window to develop a tested solution.
As it stands right now, I can't say what's wrong with your query, because I don't know your tables.
But as Naomi say, make sure you include a tag for Access. With an sql-server tag you will get SQL Server queries that may or may not run in Access.