A
anniecallaway
I have the following columns in my Query:
Column A - Field: STR_NBR, Table: PRTHD_STRSK_INV_SSTATS, Sort:
Ascending, Show: Checked
Column B - Field: SKU_NBR, Table: (Same Table), Show:
Checked
Column C - Field: OVR_SHRT_CE_AMT, Table: (Same Table), Sort:
Descending, Show: Checked
I am trying to get the Top 50 SKUs with the larget Shrink $ values for
each store listed. The table I am referencing has roughly 40,000 SKUs
for each Store Number. The table is huge as there are about 2,000
stores. I just want the largest 50 SKUs for each of the 2,000 stores
and their respective Shink $ values to display.
I am trying to duplicate the following posting my friend found but
have had no luck getting my query to run. Could someone please view
the message below and the query I have made to see if you can
determine what I am doing wrong.
My Query: (Please note I am using 1 store in the query (0121) but
would like to see all stores too.
In (Select Top 50 [OVR_SHRT_CE_AMT] From SKU_NBR where _
[0121]=[STR_NBR].[0121] Order By [OVR_SHRT_CE_AMT] Desc)
Posting I am Trying to Duplicate:
Method 1
The following example shows you how to create a query in the Northwind
sample database that displays the top three UnitsInStock per
CategoryID. The query uses a SQL subquery, which returns the top three
UnitsInStock given a specific CategoryID, and then uses the IN
operator to limit the records in the main query.
NOTE: In the criteria example in Step 5, an underscore (_) at the end
of a line is used as a line-continuation character. Remove the
underscore from the end of the line when re-creating the criteria.
Open the sample database Northwind.mdb.
Click the Queries tab, and then click New.
Click Design View, and then click OK.
In the Show Table dialog box, add the Categories and the Products
tables, and then click Close.
Add the following fields to the query grid:
Field: CategoryName
Sort: Ascending
Field: ProductName
Field: UnitsInStock
Sort: Descending
Criteria: In (Select Top 3 [UnitsInStock] From Products Where _
[CategoryID]=[Categories].[CategoryID] Order By [UnitsInStock] Desc)
Run the query. Note that the query returns the top three UnitsInStock
for each category.
Column A - Field: STR_NBR, Table: PRTHD_STRSK_INV_SSTATS, Sort:
Ascending, Show: Checked
Column B - Field: SKU_NBR, Table: (Same Table), Show:
Checked
Column C - Field: OVR_SHRT_CE_AMT, Table: (Same Table), Sort:
Descending, Show: Checked
I am trying to get the Top 50 SKUs with the larget Shrink $ values for
each store listed. The table I am referencing has roughly 40,000 SKUs
for each Store Number. The table is huge as there are about 2,000
stores. I just want the largest 50 SKUs for each of the 2,000 stores
and their respective Shink $ values to display.
I am trying to duplicate the following posting my friend found but
have had no luck getting my query to run. Could someone please view
the message below and the query I have made to see if you can
determine what I am doing wrong.
My Query: (Please note I am using 1 store in the query (0121) but
would like to see all stores too.
In (Select Top 50 [OVR_SHRT_CE_AMT] From SKU_NBR where _
[0121]=[STR_NBR].[0121] Order By [OVR_SHRT_CE_AMT] Desc)
Posting I am Trying to Duplicate:
Method 1
The following example shows you how to create a query in the Northwind
sample database that displays the top three UnitsInStock per
CategoryID. The query uses a SQL subquery, which returns the top three
UnitsInStock given a specific CategoryID, and then uses the IN
operator to limit the records in the main query.
NOTE: In the criteria example in Step 5, an underscore (_) at the end
of a line is used as a line-continuation character. Remove the
underscore from the end of the line when re-creating the criteria.
Open the sample database Northwind.mdb.
Click the Queries tab, and then click New.
Click Design View, and then click OK.
In the Show Table dialog box, add the Categories and the Products
tables, and then click Close.
Add the following fields to the query grid:
Field: CategoryName
Sort: Ascending
Field: ProductName
Field: UnitsInStock
Sort: Descending
Criteria: In (Select Top 3 [UnitsInStock] From Products Where _
[CategoryID]=[Categories].[CategoryID] Order By [UnitsInStock] Desc)
Run the query. Note that the query returns the top three UnitsInStock
for each category.