Top Value Conditional Query

[leadpro]

I have clients recieving leads...records. Sometimes, their number of records delivered from my suppliers exceeds thier order, leaving a balance due of (-).

How would I query clients [ClientID] that have exceeded thier [OrderAmount], and only pull up the number of records equal to their [OrderAmount], so I can append only these records to another table, thus leaving the number of records exceeding their order in the original table.

Example: [OrderAmount]=10
[LeadsDelivered]=12
[Balance]= -2

Need to query [ClientID] Balances that are negative, but only up to the [OrderAmount], in this case 10 records.

 

[lupins46]

'Need to query [ClientID] Balances that are negative, but only up to the [OrderAmount], in this case 10 records.'

I don't follow the second bit here.

Using your example, you appear to be saying that you want to know of any -ve balances from -1 to -10 but not for -11 or -30 etc.

However, if that's what you want then the query would be somathing like:

Select * from orders where Balance <0 and Abs(balance)<= Orderamount

 

[leadpro]

Thanks, actually the negative balance could be anything, just so happen to be -10 in the example.

 

[lupins46]

OK so if you don't care what the negative balance is then you can append records to another table like this (this assumes the field names in the second table are exactly the same as in the main table):

Insert Into [othertable]
Select orders.* from Orders
Where orders.Balance <0

If you want to delete these records from the main table (be absolutely sure of this before doing so)
then you run another query immediately after the Append query:
Delete * from Orders
Where Balance <0