Access query Row Count with Criteria for a Yes/No field
Have a table named "Machine Number"
That table has fields:
MachineID (PK, Autonumber)
Active (Yes/No)
CompanyID (FK)
In my query, so that I can get a list with a sequential numbering of just "Active" machines, I have the following code:
It is pulling all of the correct Items from the Table, however it is not creating the correct RowNum.
If I set the field for say MachineID 1 to be 0, then it is pulling MachineIDs 2, 3, 4 etc and starts the RowNum count at 2 instead of 1.
Any idea on where my code is missing starting its RowNum sequencing to include only the "Active" machinesid's?
That table has fields:
MachineID (PK, Autonumber)
Active (Yes/No)
CompanyID (FK)
In my query, so that I can get a list with a sequential numbering of just "Active" machines, I have the following code:
SELECT (SELECT Count([MachineID]) FROM [Machine Number] A2 WHERE A2.[MachineID] < A1.[MachineID])+1 AS RowNum, *
FROM [Machine Number] AS A1
WHERE (((A1.Active)=-1))
ORDER BY A1.MachineID;It is pulling all of the correct Items from the Table, however it is not creating the correct RowNum.
If I set the field for say MachineID 1 to be 0, then it is pulling MachineIDs 2, 3, 4 etc and starts the RowNum count at 2 instead of 1.
Any idea on where my code is missing starting its RowNum sequencing to include only the "Active" machinesid's?
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Glad to help :)
ASKER
Thanks! I was attempting Peter's suggestion when your answer came thru. I have allocated a portion of the points to Peter for indicating where my theory was flawed. Lion's share to you for the code also.