Access Query - Query Help Is Greatly Appreciated!!!!

[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.

 

[anniecallaway]

Hi Annie

The problem you have is because your first sort is on STR_NBR, so this
determines that your results will be the lowest 50 store numbers.  To fix
this you must sort on OVR_SHRT_CE_AMT first.

Your SQL also has errors in the WHERE section.

Try this SQL;

SELECT TOP 50 OVR_SHRT_CE_AMT, STR_NBR, SKU_NBR
FROM PRTHD_STRSK_INV_SSTATS
ORDER BY OVR_SHRT_CE_AMT DESC, STR_NBR;

If you want to select a single store (assuming your store number is a text
field) try;

SELECT TOP 50 OVR_SHRT_CE_AMT, STR_NBR, SKU_NBR
FROM PRTHD_STRSK_INV_SSTATS
WHERE STR_NBR = '0121'
ORDER BY OVR_SHRT_CE_AMT DESC, STR_NBR;

Hope this helps

Richard

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.

--
Regards,

Richardwww.kardanconsulting.co.uk

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200902/1- Hide quoted text -

- Show quoted text -

Thanks Richard! I will try this!

One thing though - I said that this "In (Select Top 50
[OVR_SHRT_CE_AMT] From SKU_NBR where _
[0121]=[STR_NBR].[0121] Order By [OVR_SHRT_CE_AMT] Desc)" was my query
but it is actually the Criteria I was inserting in Column C. Should I
still follow the same instruction you provided above (i.e. Put the
information you provided in the Criteria field of Column C) or should
I put it someone else?

 

[anniecallaway]

Hi Annie

No problem.

The SQL you posted would not work for criteria either.  Try the optionsI
have suggested and then if you need any further refinement or help, post back
here.

Regards

Richard

[quoted text clipped - 93 lines]

- Show quoted text -

Thanks Richard!  I will try this!

One thing though - I said that this "In (Select Top 50
[OVR_SHRT_CE_AMT] From SKU_NBR where _
[0121]=[STR_NBR].[0121] Order By [OVR_SHRT_CE_AMT] Desc)" was my query
but it is actually the Criteria I was inserting in Column C.  Should I
still follow the same instruction you provided above (i.e. Put the
information you provided in the Criteria field of Column C) or should
I put it someone else?

--
Regards,

Richardwww.kardanconsulting.co.uk

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200902/1- Hide quoted text -

- Show quoted text -

Richard,

Please note what I got after trying the first suggestion below.

a) This SQL View Query give the the Top 50 SKU numbers and skrink $
amounts for store 121:

SELECT TOP 50 PRTHD_STRSK_INV_SSTATS.STR_NBR,
PRTHD_STRSK_INV_SSTATS.SKU_NBR, PRTHD_STRSK_INV_SSTATS.OVR_SHRT_CE_AMT
FROM PRTHD_STRSK_INV_SSTATS
WHERE (((PRTHD_STRSK_INV_SSTATS.FSCL_YR)=2008) AND
((PRTHD_STRSK_INV_SSTATS.STR_NBR)=121))
ORDER BY PRTHD_STRSK_INV_SSTATS.OVR_SHRT_CE_AMT,
PRTHD_STRSK_INV_SSTATS.STR_NBR;

b) However this query gives me the Top 50 SKU numbers and skrink $
amounts for store 121 and 105 combined:

SELECT TOP 50 PRTHD_STRSK_INV_SSTATS.STR_NBR,
PRTHD_STRSK_INV_SSTATS.SKU_NBR, PRTHD_STRSK_INV_SSTATS.OVR_SHRT_CE_AMT
FROM PRTHD_STRSK_INV_SSTATS
WHERE (((PRTHD_STRSK_INV_SSTATS.FSCL_YR)=2008) AND
((PRTHD_STRSK_INV_SSTATS.STR_NBR)=121)) OR
(((PRTHD_STRSK_INV_SSTATS.FSCL_YR)=2008) AND
((PRTHD_STRSK_INV_SSTATS.STR_NBR)=105))
ORDER BY PRTHD_STRSK_INV_SSTATS.OVR_SHRT_CE_AMT,
PRTHD_STRSK_INV_SSTATS.STR_NBR;


I am looking for the top 50 SKU numbers and skrink $ amounts for store
121 &
the top 50 SKU numbers and skrink $ amounts for store 105. Which is
100 SKU numbers and shrink $ amounts total.

Could you please look at query and tell me what I might be doing
wrong?

Thanks,
Annie