How to combine top and count in ms access sql so that every record other than the 1st is retrieved?
How to combine top and count in ms access sql so that every record other than the 1st is retrieved?
How to combine top and count in ms access sql so that every record other than the 1st is retrieved? i.e. exclude the 1st record, but do not know the number of records that exists.
I imagine the sql would be something like:
SELECT TOP
(SELECT COUNT(*)
FROM Company LEFT JOIN CompanySecretary ON Company.[Company Number] = CompanySecretary.[Company Number]
WHERE Company.[Company Number]=[Forms]![Company]![Company Number]
- 1)
Company.[Company Number], CompanySecretary.[Name]
FROM Company LEFT JOIN CompanySecretary ON Company.[Company Number] = CompanySecretary.[Company Number]
WHERE Company.[Company Number]=[Forms]![Company]![Company Number]
ORDER BY Company.[Company Number] ASC, CompanySecretary.[Name] ASC;
E.g.
Company.[Company Number] | CompanySecretary.[Name]
---------------------------------------------------
002 | Mark
002 | Paul
002 | William
The expected result would be:
Company.[Company Number] | CompanySecretary.[Name]
---------------------------------------------------
002 | Paul
002 | William
Thank you so much.
Grateful.
ASC
AESC
Off-topic: change your name to something meaningful :)
– JohnyL
Aug 27 at 5:35
A small data sample and the expected result would help. See also meta.stackoverflow.com/questions/333952/…
– Koen
Aug 27 at 5:43
Welcome aboard, Do you have to use
TOP and COUNT? like an exercise in JET SQL course, or you really need the right answer whatever it is like in real life?– Sion.D.P
Aug 27 at 8:50
TOP
COUNT
Please provide sample data and desired results. The question doesn't make sense for a couple of reasons. First
AESC is quite ambiguous -- ASC or DESC. Second, you are ordering by the column used in the WHERE, so it has a constant value. SQL result sets (and tables) have no "first" row, unless a column specifies the ordering. If the values are all equal, then no column specifies the ordering.– Gordon Linoff
Aug 27 at 11:04
AESC
ASC
DESC
WHERE
1 Answer
1
Assuming no 2 secretaries in same company with same name
SELECT TOP 1 Company.[Company Number], CompanySecretary.[Name]
FROM Company LEFT JOIN CompanySecretary
ON Company.[Company Number] = CompanySecretary.[Company Number]
WHERE Company.[Company Number]=[Forms]![Company]![Company Number]
ORDER BY Company.[Company Number] ASC, CompanySecretary.[Name] ASC
These are the pair of values to exclude from the result set
Lets create this as qTop1 in Access queries
qTop1
Now the desired query will be
SELECT Company.[Company Number], CompanySecretary.[Name]
FROM Company LEFT JOIN CompanySecretary
ON Company.[Company Number] = CompanySecretary.[Company Number]
WHERE Company.[Company Number]=[Forms]![Company]![Company Number]
AND NOT EXISTS (SELECT 1 FROM qTop1
WHERE qTop1.[Company Number] = Company.[Company Number]
AND qTop1.Name = CompanySecretary.[Name])
ORDER BY Company.[Company Number] ASC, CompanySecretary.[Name] ASC
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
I guess
ASC- notAESC– JohnyL
Aug 27 at 5:30