Query Help - Retrieve Top 100 records

[milodm]

I am probably a intermediate Access user, what I know is self taught or from a book and or Google research. The vast majority of what I do is working directly in Access via Query Design; I do a little in Access via SQL but not a lot, I do know how to interpret simple to intermediate SQL but struggle with complex stuff.

ISSUE - I am working on a project where I am creating a database that tracks open orders (old and new orders that still need to be billed but haven't been billed for one reason or another).

I have my "Master" table built and now I need to create 2 queries, one query retrieves the Top 20 orders based on value and that are older then 30 days and the second query is to retrieve the Top 100 orders based on the same criteria but the Top 100 query should not include the orders that appear in the Top 20 query.

My Top 20 query is below

SELECT TOP 20 MASTER.COMBO, MASTER.[Customer Name], MASTER.Status, MASTER.CCD, MASTER.Area, MASTER.[Order Type], MASTER.[Order Value], MASTER.AGE, MASTER.ANALYST, MASTER.COMMENTS
FROM MASTER
WHERE (((MASTER.AGE)>=30))
ORDER BY MASTER.[Order Value] DESC , MASTER.AGE;

I have tried multiple ways to do the Top 100 query and exclude what is returned in my Top 20 query but so far no success and my books/Google isn't helping either.

I am sure to a lot of the people this is a fairly easy thing but I am frustrated and out of ideas.

Any suggestions would be appreciated!
Thanks!

 

[Brianwarnock]

I don't know which is your primary key so cannot be specific, but if you take your top 20 query into your top 100 query and in the where clause add master.pk <> query.pk that should filter out the top 20

Brian

 

[milodm]

Brian -

Damn..I knew I forgot something. PK is "COMBO"

 

[Brianwarnock]

Well you have it in your query so try what I suggested

Brian

 

[Brianwarnock]

It should be something like

SELECT TOP 100 MASTER.COMBO, MASTER.[Customer Name], MASTER.Status, MASTER.CCD, MASTER.Area, MASTER.[Order Type], MASTER.[Order Value], MASTER.AGE, MASTER.ANALYST, MASTER.COMMENTS
FROM MASTER, yourqueryname
WHERE (((MASTER.AGE)>=30) and master.combo <> yourqueryname.combo
ORDER BY MASTER.[Order Value] DESC , MASTER.AGE;

Brian

 

[milodm]

Brian,

I am probably showing my stupidity here so please bare with me.

Okay, I am doing this in Access 2007 and I have the one MASTER table (Primary Key is "COMBO" field) and off that table I want to have 2 separate queries, one that returns the TOP20 records (ORDERS) based on ORDER VALUE and AGE and then a second query that returns the TOP100 records based on ORDER VALUE and AGE and the TOP100 query would exclude the records from the TOP20 query.

I have built 2 queries via Query Design in Access named "TOP 20" and "TOP 100" and the code for those are:

TOP 20
SELECT TOP 20 MASTER.COMBO, MASTER.[Customer Name], MASTER.Status, MASTER.CCD, MASTER.Area, MASTER.[Order Type], MASTER.[Order Value], MASTER.AGE, MASTER.ANALYST, MASTER.COMMENTS
FROM MASTER
WHERE (((MASTER.AGE)>=30))
ORDER BY MASTER.[Order Value] DESC , MASTER.AGE;

TOP 100
SELECT TOP 100 MASTER.COMBO, MASTER.[Customer Name], MASTER.Status, MASTER.CCD, MASTER.Area, MASTER.[Order Type], MASTER.[Order Value], MASTER.AGE, MASTER.ANALYST, MASTER.COMMENTS
FROM MASTER
WHERE (((MASTER.AGE)>=30))
ORDER BY MASTER.[Order Value] DESC , MASTER.AGE;


I saw your last reply in which you instructed me to add the following to my TOP 100 query

FROM MASTER, yourqueryname
WHERE (((MASTER.AGE)>=30) and master.combo <> yourqueryname.combo

The fields you referred to as "yourqueryname", should that be the TOP 20 query? I tried doing the following code and run it and get an error "Syntax error in FROM clause."

SELECT TOP 100 MASTER.COMBO, MASTER.[Customer Name], MASTER.Status, MASTER.CCD, MASTER.Area, MASTER.[Order Type], MASTER.[Order Value], MASTER.AGE, MASTER.ANALYST, MASTER.COMMENTS
FROM MASTER, TOP 20
WHERE (((MASTER.COMBO)<>[TOP20].[COMBO]) AND ((MASTER.AGE)>=30))
ORDER BY MASTER.[Order Value] DESC , MASTER.AGE;

I know I need to bring in my TOP 20 query into my TOP 100 query but when I do that the COMBO field in the MASTER creates a JOIN between the MASTER table and the TOP 20 query and any JOIN I try I get a prompt asking me to enter a value for TOP 20.COMBO.

I am probably confusing you and I apologize but I am just completely lost on how to get this completed. In theory it seems so simple but I am just not getting what/where I am doing something wrong.

Thank you again for your help!

 

[Brianwarnock]

I notice that you appear to have a space in Top 20 in the From clause, make sure that you have no spaces in your object names they lead to syntax problems/difficulties.

Yes in the design grid Access will attempt the join , delete it, this will force a Cartesian join , normally a bad idea but it should be controlled in this case by the Where clause.

Brian

 

[milodm]

Brian,

I went back and renamed my queries to remove spaces as you suggested and still no luck. Below is my SQL

SELECT TOP 100 MASTER.COMBO, MASTER.[Customer Name], MASTER.Status, MASTER.CCD, MASTER.Area, MASTER.[Order Type], MASTER.[Order Value], MASTER.AGE, MASTER.ANALYST, MASTER.COMMENTS
FROM MASTER, TOP20
WHERE (((MASTER.COMBO)<>[TOP20].[COMBO]) AND ((MASTER.AGE)>=30))
ORDER BY MASTER.[Order Value] DESC , MASTER.AGE;

I am attaching a .pdf file so I can show you my results from the TOP20 query, then it shows you in Query Design view the TOP100 query and then I give you the results of that TOP100 query. The TOP100 query results appear to be a portion of the same records from the TOP20 query I am trying to exclude and the values are duplicated.

So again, I am a little unsure of what's wrong?

 

[Brianwarnock]

OOPs sorry you are correct it does not work

Go to plan B which actually was my first reaction but then I had that crazy idea that didn't work, normally I test but this time I hadn't , apologies.

queryTop20
queryTop120
use unmatched query wizard to construct query that finds all records in top120 not in top20 , hence you have your top100 after top20

Brian

 

[milodm]

Brian,

Here I thought this was going to be some elaborate query and come to find out it was as simple as using the Query Wizard and the Unmatched Query like you suggested!

I did the unmatched query and it worked...success!!

Thank you so much for all your help and being patient with me!!

Thanks!

 

[Brianwarnock]

Glad it's working ok, I can only apologies for wasting time trying to be clever without testing, I guess the Cartesian join created when no join is specified caused it to mess up big time.
That'll teach me.

Brian