How to supply a count of records to select for append

  • Thread starter Thread starter DevourU
  • Start date Start date
D

DevourU

I have a table with parts and a class for each part. I would like to have an
append query select next 5 classA parts, next 7 classC parts, etc.

get from mypartlist
if [partnodate] is null, select [countA] records [class]="A"
and so on for classB, classC

Possible? TIA!

-JS
 
Using SELECT TOP 5 [MyTable].partno works, but I need the TOP number as form
field input, and for 3 groups. I could use 3 queries and run them in a macro
if needed. TIA

-JS
 
DevourU said:
Using SELECT TOP 5 [MyTable].partno works, but I need the TOP number as form
field input
Click to expand...

I don't think the MS proprietary TOP N syntax is going to work e.g.

CREATE PROCEDURE TestCustomers
(:row_count INTEGER = 5)
AS
SELECT TOP :row_count CustomerID
FROM Customers
;

generates an error, 'The SELECT statement includes a reserved word or
an argument name that is misspelled or missing, or the punctuation is
incorrect.'

The following standard SQL construct works fine:

CREATE PROCEDURE TestCustomers
(:row_count INTEGER = 5)
AS
SELECT T1.CustomerID
FROM Customers AS T1
WHERE :row_count >= (
SELECT COUNT(*)
FROM Customers
WHERE T1.CustomerID <= CustomerID
)
;
EXECUTE TestCustomers
;
EXECUTE TestCustomers 3
;
EXECUTE TestCustomers 7
;
etc etc
 
Back
Top