SQL TOP Syntax issue from Ira

T

TheIMan

Hi, I've been having trouble getting this query to return the values I
need. I have two related tables that contain raw material purchase
information. Currently, the query is returning ALL the pruchases for a
particular item, but I want to limit it to return 10 most recent
purchases for a particular item. I tried using the SELECT TOP
statement, but I keep putting it in the wrong places and the query
returned only 10 records period. I want the LAST 10 for EACH item. Can
anyone help? Thank you. Below is the query as it stands returning all
the records: -- Ira

SELECT RawMaterialsInvtbl.RawMaterialID,
RawMaterialsInvtbl.RawMaterialPartNo, RawMaterialsInvtbl.Category,
RawMaterialsInvtbl.RawMaterialName, RawMaterialsInvtbl.LbsPerDrum,
RawMaterialsInvtbl.Quote1Date, RawMaterialsInvtbl.Quote1Supplier,
RawMaterialsInvtbl.Quote1PriceDrum,
RawMaterialsInvtbl.Quote1PriceTote, RawMaterialsInvtbl.Quote2Date,
RawMaterialsInvtbl.Quote2Supplier, RawMaterialsInvtbl.Quote2PriceDrum,
RawMaterialsInvtbl.Quote2PriceTote, RawMaterialsInvtbl.Quote3Date,
RawMaterialsInvtbl.Quote3Supplier, RawMaterialsInvtbl.Quote3PriceDrum,
RawMaterialsInvtbl.Quote3PriceTote,
RawMaterialPurchaseRecords.OrderDate,
RawMaterialPurchaseRecords.Supplier,
RawMaterialPurchaseRecords.Quantity,
RawMaterialPurchaseRecords.UnitOfMeasure,
RawMaterialPurchaseRecords.PriceWithFreight,
RawMaterialPurchaseRecords.PO,
RawMaterialPurchaseRecords.ReceivedDate,
RawMaterialPurchaseRecords.Price,
RawMaterialPurchaseRecords.PriceWithFreight,
RawMaterialPurchaseRecords.TotalPrice

FROM RawMaterialsInvtbl LEFT JOIN RawMaterialPurchaseRecords ON
RawMaterialsInvtbl.RawMaterialID =
RawMaterialPurchaseRecords.RawMaterialID

GROUP BY RawMaterialsInvtbl.RawMaterialID,
RawMaterialsInvtbl.RawMaterialPartNo, RawMaterialsInvtbl.Category,
RawMaterialsInvtbl.RawMaterialName, RawMaterialsInvtbl.LbsPerDrum,
RawMaterialsInvtbl.Quote1Date, RawMaterialsInvtbl.Quote1Supplier,
RawMaterialsInvtbl.Quote1PriceDrum,
RawMaterialsInvtbl.Quote1PriceTote, RawMaterialsInvtbl.Quote2Date,
RawMaterialsInvtbl.Quote2Supplier, RawMaterialsInvtbl.Quote2PriceDrum,
RawMaterialsInvtbl.Quote2PriceTote, RawMaterialsInvtbl.Quote3Date,
RawMaterialsInvtbl.Quote3Supplier, RawMaterialsInvtbl.Quote3PriceDrum,
RawMaterialsInvtbl.Quote3PriceTote,
RawMaterialPurchaseRecords.OrderDate,
RawMaterialPurchaseRecords.Supplier,
RawMaterialPurchaseRecords.Quantity,
RawMaterialPurchaseRecords.UnitOfMeasure,
RawMaterialPurchaseRecords.PriceWithFreight,
RawMaterialPurchaseRecords.PO,
RawMaterialPurchaseRecords.ReceivedDate,
RawMaterialPurchaseRecords.Price,
RawMaterialPurchaseRecords.PriceWithFreight,
RawMaterialPurchaseRecords.TotalPrice

ORDER BY RawMaterialsInvtbl.Category,
RawMaterialsInvtbl.RawMaterialName;
 
N

NetworkTrade

to return 10 most recent purchases - first you would need to have an orderby
involving the purchase date which I do not see below

ORDER BY Table.[DateFieldName] DESC

and your query should begin

SELECT TOP 10

hint; put into query design grid and there is a Top feature in the tool
bar.... easier to tweak a query using the grid and move it to SQL view for
the syntax
 
T

TheIMan

to return 10 most recent purchases - first you would need to have an orderby
involving the purchase date which I do not see below

ORDER BY Table.[DateFieldName] DESC

and your query should begin

SELECT TOP 10

hint; put into query design grid and there is a Top feature in the tool
bar.... easier to tweak a query using the grid and move it to SQL view for
the syntax

--
NTC



TheIMan said:
Hi, I've been having trouble getting this query to return the values I
need. I have two related tables that contain raw material purchase
information. Currently, the query is returning ALL the pruchases for a
particular item, but I want to limit it to return 10 most recent
purchases for a particular item. I tried using the SELECT TOP
statement, but I keep putting it in the wrong places and the query
returned only 10 records period. I want the LAST 10 for EACH item. Can
anyone help? Thank you.  Below is the query as it stands returning all
the records:  -- Ira
Click to expand...
SELECT RawMaterialsInvtbl.RawMaterialID,
RawMaterialsInvtbl.RawMaterialPartNo, RawMaterialsInvtbl.Category,
RawMaterialsInvtbl.RawMaterialName, RawMaterialsInvtbl.LbsPerDrum,
RawMaterialsInvtbl.Quote1Date, RawMaterialsInvtbl.Quote1Supplier,
RawMaterialsInvtbl.Quote1PriceDrum,
RawMaterialsInvtbl.Quote1PriceTote, RawMaterialsInvtbl.Quote2Date,
RawMaterialsInvtbl.Quote2Supplier, RawMaterialsInvtbl.Quote2PriceDrum,
RawMaterialsInvtbl.Quote2PriceTote, RawMaterialsInvtbl.Quote3Date,
RawMaterialsInvtbl.Quote3Supplier, RawMaterialsInvtbl.Quote3PriceDrum,
RawMaterialsInvtbl.Quote3PriceTote,
RawMaterialPurchaseRecords.OrderDate,
RawMaterialPurchaseRecords.Supplier,
RawMaterialPurchaseRecords.Quantity,
RawMaterialPurchaseRecords.UnitOfMeasure,
RawMaterialPurchaseRecords.PriceWithFreight,
RawMaterialPurchaseRecords.PO,
RawMaterialPurchaseRecords.ReceivedDate,
RawMaterialPurchaseRecords.Price,
RawMaterialPurchaseRecords.PriceWithFreight,
RawMaterialPurchaseRecords.TotalPrice
Click to expand...
FROM RawMaterialsInvtbl LEFT JOIN RawMaterialPurchaseRecords ON
RawMaterialsInvtbl.RawMaterialID =
RawMaterialPurchaseRecords.RawMaterialID
Click to expand...
GROUP BY RawMaterialsInvtbl.RawMaterialID,
RawMaterialsInvtbl.RawMaterialPartNo, RawMaterialsInvtbl.Category,
RawMaterialsInvtbl.RawMaterialName, RawMaterialsInvtbl.LbsPerDrum,
RawMaterialsInvtbl.Quote1Date, RawMaterialsInvtbl.Quote1Supplier,
RawMaterialsInvtbl.Quote1PriceDrum,
RawMaterialsInvtbl.Quote1PriceTote, RawMaterialsInvtbl.Quote2Date,
RawMaterialsInvtbl.Quote2Supplier, RawMaterialsInvtbl.Quote2PriceDrum,
RawMaterialsInvtbl.Quote2PriceTote, RawMaterialsInvtbl.Quote3Date,
RawMaterialsInvtbl.Quote3Supplier, RawMaterialsInvtbl.Quote3PriceDrum,
RawMaterialsInvtbl.Quote3PriceTote,
RawMaterialPurchaseRecords.OrderDate,
RawMaterialPurchaseRecords.Supplier,
RawMaterialPurchaseRecords.Quantity,
RawMaterialPurchaseRecords.UnitOfMeasure,
RawMaterialPurchaseRecords.PriceWithFreight,
RawMaterialPurchaseRecords.PO,
RawMaterialPurchaseRecords.ReceivedDate,
RawMaterialPurchaseRecords.Price,
RawMaterialPurchaseRecords.PriceWithFreight,
RawMaterialPurchaseRecords.TotalPrice
Click to expand...
ORDER BY RawMaterialsInvtbl.Category,
RawMaterialsInvtbl.RawMaterialName;- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

NTC,

This is not working. It only returns the TOP N records overall. I need
all the values in the materials table and only to last 10 purchases
for each material.
 
N

NetworkTrade

ah - I see - well that is a tricky query - I would have to look at my sql
info and may not get to; I don't have an answer to this one off the
sleeve...since old questions, particulary those with some dialog, are
generally not looked at - you should ask a streamlined version of this
question and post it new in the query section....
--
NTC


TheIMan said:
to return 10 most recent purchases - first you would need to have an orderby
involving the purchase date which I do not see below

ORDER BY Table.[DateFieldName] DESC

and your query should begin

SELECT TOP 10

hint; put into query design grid and there is a Top feature in the tool
bar.... easier to tweak a query using the grid and move it to SQL view for
the syntax

--
NTC



TheIMan said:
Hi, I've been having trouble getting this query to return the values I
need. I have two related tables that contain raw material purchase
information. Currently, the query is returning ALL the pruchases for a
particular item, but I want to limit it to return 10 most recent
purchases for a particular item. I tried using the SELECT TOP
statement, but I keep putting it in the wrong places and the query
returned only 10 records period. I want the LAST 10 for EACH item. Can
anyone help? Thank you. Below is the query as it stands returning all
the records: -- Ira
Click to expand...
SELECT RawMaterialsInvtbl.RawMaterialID,
RawMaterialsInvtbl.RawMaterialPartNo, RawMaterialsInvtbl.Category,
RawMaterialsInvtbl.RawMaterialName, RawMaterialsInvtbl.LbsPerDrum,
RawMaterialsInvtbl.Quote1Date, RawMaterialsInvtbl.Quote1Supplier,
RawMaterialsInvtbl.Quote1PriceDrum,
RawMaterialsInvtbl.Quote1PriceTote, RawMaterialsInvtbl.Quote2Date,
RawMaterialsInvtbl.Quote2Supplier, RawMaterialsInvtbl.Quote2PriceDrum,
RawMaterialsInvtbl.Quote2PriceTote, RawMaterialsInvtbl.Quote3Date,
RawMaterialsInvtbl.Quote3Supplier, RawMaterialsInvtbl.Quote3PriceDrum,
RawMaterialsInvtbl.Quote3PriceTote,
RawMaterialPurchaseRecords.OrderDate,
RawMaterialPurchaseRecords.Supplier,
RawMaterialPurchaseRecords.Quantity,
RawMaterialPurchaseRecords.UnitOfMeasure,
RawMaterialPurchaseRecords.PriceWithFreight,
RawMaterialPurchaseRecords.PO,
RawMaterialPurchaseRecords.ReceivedDate,
RawMaterialPurchaseRecords.Price,
RawMaterialPurchaseRecords.PriceWithFreight,
RawMaterialPurchaseRecords.TotalPrice
Click to expand...
FROM RawMaterialsInvtbl LEFT JOIN RawMaterialPurchaseRecords ON
RawMaterialsInvtbl.RawMaterialID =
RawMaterialPurchaseRecords.RawMaterialID
Click to expand...
GROUP BY RawMaterialsInvtbl.RawMaterialID,
RawMaterialsInvtbl.RawMaterialPartNo, RawMaterialsInvtbl.Category,
RawMaterialsInvtbl.RawMaterialName, RawMaterialsInvtbl.LbsPerDrum,
RawMaterialsInvtbl.Quote1Date, RawMaterialsInvtbl.Quote1Supplier,
RawMaterialsInvtbl.Quote1PriceDrum,
RawMaterialsInvtbl.Quote1PriceTote, RawMaterialsInvtbl.Quote2Date,
RawMaterialsInvtbl.Quote2Supplier, RawMaterialsInvtbl.Quote2PriceDrum,
RawMaterialsInvtbl.Quote2PriceTote, RawMaterialsInvtbl.Quote3Date,
RawMaterialsInvtbl.Quote3Supplier, RawMaterialsInvtbl.Quote3PriceDrum,
RawMaterialsInvtbl.Quote3PriceTote,
RawMaterialPurchaseRecords.OrderDate,
RawMaterialPurchaseRecords.Supplier,
RawMaterialPurchaseRecords.Quantity,
RawMaterialPurchaseRecords.UnitOfMeasure,
RawMaterialPurchaseRecords.PriceWithFreight,
RawMaterialPurchaseRecords.PO,
RawMaterialPurchaseRecords.ReceivedDate,
RawMaterialPurchaseRecords.Price,
RawMaterialPurchaseRecords.PriceWithFreight,
RawMaterialPurchaseRecords.TotalPrice
Click to expand...
ORDER BY RawMaterialsInvtbl.Category,
RawMaterialsInvtbl.RawMaterialName;- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

NTC,

This is not working. It only returns the TOP N records overall. I need
all the values in the materials table and only to last 10 purchases
for each material.
Click to expand...
 
N

NetworkTrade

actually - over in the Query section I just searched on Top 10 and found a
question almost identical to yours:

http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

I'm trying to design a query where I return the last 10 matches
for a given player. -------------------------------------

Assuming you have a [MatchDate] field, try something like:

Select T1.Player_ID, T1.Match_ID, Count(T2.Player_ID) as MatchOrder
FROM yourTable as T1
LEFT JOIN yourTable as T2
ON T1.[Player_ID] = T2.[Player_ID]
AND T2.[Match_Date] <= T1.[Match_Date]
WHERE Count(T2.Player_ID) <= 10
GROUP BY T1.Player_ID, T1.Match_ID

--


--
NTC


TheIMan said:
to return 10 most recent purchases - first you would need to have an orderby
involving the purchase date which I do not see below

ORDER BY Table.[DateFieldName] DESC

and your query should begin

SELECT TOP 10

hint; put into query design grid and there is a Top feature in the tool
bar.... easier to tweak a query using the grid and move it to SQL view for
the syntax

--
NTC



TheIMan said:
Hi, I've been having trouble getting this query to return the values I
need. I have two related tables that contain raw material purchase
information. Currently, the query is returning ALL the pruchases for a
particular item, but I want to limit it to return 10 most recent
purchases for a particular item. I tried using the SELECT TOP
statement, but I keep putting it in the wrong places and the query
returned only 10 records period. I want the LAST 10 for EACH item. Can
anyone help? Thank you. Below is the query as it stands returning all
the records: -- Ira
Click to expand...
SELECT RawMaterialsInvtbl.RawMaterialID,
RawMaterialsInvtbl.RawMaterialPartNo, RawMaterialsInvtbl.Category,
RawMaterialsInvtbl.RawMaterialName, RawMaterialsInvtbl.LbsPerDrum,
RawMaterialsInvtbl.Quote1Date, RawMaterialsInvtbl.Quote1Supplier,
RawMaterialsInvtbl.Quote1PriceDrum,
RawMaterialsInvtbl.Quote1PriceTote, RawMaterialsInvtbl.Quote2Date,
RawMaterialsInvtbl.Quote2Supplier, RawMaterialsInvtbl.Quote2PriceDrum,
RawMaterialsInvtbl.Quote2PriceTote, RawMaterialsInvtbl.Quote3Date,
RawMaterialsInvtbl.Quote3Supplier, RawMaterialsInvtbl.Quote3PriceDrum,
RawMaterialsInvtbl.Quote3PriceTote,
RawMaterialPurchaseRecords.OrderDate,
RawMaterialPurchaseRecords.Supplier,
RawMaterialPurchaseRecords.Quantity,
RawMaterialPurchaseRecords.UnitOfMeasure,
RawMaterialPurchaseRecords.PriceWithFreight,
RawMaterialPurchaseRecords.PO,
RawMaterialPurchaseRecords.ReceivedDate,
RawMaterialPurchaseRecords.Price,
RawMaterialPurchaseRecords.PriceWithFreight,
RawMaterialPurchaseRecords.TotalPrice
Click to expand...
FROM RawMaterialsInvtbl LEFT JOIN RawMaterialPurchaseRecords ON
RawMaterialsInvtbl.RawMaterialID =
RawMaterialPurchaseRecords.RawMaterialID
Click to expand...
GROUP BY RawMaterialsInvtbl.RawMaterialID,
RawMaterialsInvtbl.RawMaterialPartNo, RawMaterialsInvtbl.Category,
RawMaterialsInvtbl.RawMaterialName, RawMaterialsInvtbl.LbsPerDrum,
RawMaterialsInvtbl.Quote1Date, RawMaterialsInvtbl.Quote1Supplier,
RawMaterialsInvtbl.Quote1PriceDrum,
RawMaterialsInvtbl.Quote1PriceTote, RawMaterialsInvtbl.Quote2Date,
RawMaterialsInvtbl.Quote2Supplier, RawMaterialsInvtbl.Quote2PriceDrum,
RawMaterialsInvtbl.Quote2PriceTote, RawMaterialsInvtbl.Quote3Date,
RawMaterialsInvtbl.Quote3Supplier, RawMaterialsInvtbl.Quote3PriceDrum,
RawMaterialsInvtbl.Quote3PriceTote,
RawMaterialPurchaseRecords.OrderDate,
RawMaterialPurchaseRecords.Supplier,
RawMaterialPurchaseRecords.Quantity,
RawMaterialPurchaseRecords.UnitOfMeasure,
RawMaterialPurchaseRecords.PriceWithFreight,
RawMaterialPurchaseRecords.PO,
RawMaterialPurchaseRecords.ReceivedDate,
RawMaterialPurchaseRecords.Price,
RawMaterialPurchaseRecords.PriceWithFreight,
RawMaterialPurchaseRecords.TotalPrice
Click to expand...
ORDER BY RawMaterialsInvtbl.Category,
RawMaterialsInvtbl.RawMaterialName;- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

NTC,

This is not working. It only returns the TOP N records overall. I need
all the values in the materials table and only to last 10 purchases
for each material.
Click to expand...
 
A

AccessVandal via AccessMonster.com

Have you tried "Last"? TOP 10 still apply.

In the query grid editor, select the "Date" field of your query which you
want to filter. Change the "Group By" to "Last". You can also sort by "asc"
or "desc".
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Return TOP N in 2 table query - REAL ANSWER PLEASE 1

Top