Query Criteria (1 Viewer)

P

paulcherianc

Registered User.
Local time
Today, 02:48
Joined
Aug 15, 2013
Messages
86
Dear Experts!

I am trying to develop a simple software and stuck with criteria to calculate some fields. I have attached a sample excel file for your kind review.

Can anyone help me in this regards without SQL.:banghead:
 

Attachments

  • Sample File.xlsx
    11.2 KB · Views: 62
Last edited:
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:48
Joined
May 7, 2009
Messages
19,233
select [Inv ID], [Advance Payment],[Works Certified], [Advance 10%], (SELECT Sum(NZ([Advance Payment],0)-NZ([Works Certified],0)) FROM yourTable WHERE [Inv ID]<=T1.[Inv ID]) As [Advance Payment Balance] FROM YourTable AS T1


Replace YourTable with the correct table name.
 
P

paulcherianc

Registered User.
Local time
Today, 02:48
Joined
Aug 15, 2013
Messages
86
arnelgp said:
select [Inv ID], [Advance Payment],[Works Certified], [Advance 10%], (SELECT Sum(NZ([Advance Payment],0)-NZ([Works Certified],0)) FROM yourTable WHERE [Inv ID]<=T1.[Inv ID]) As [Advance Payment Balance] FROM YourTable AS T1


Replace YourTable with the correct table name.
Click to expand...



Dear Arnel,

Actually, my query already contains the following SQL. How can i integrate the above with that? I have edited your SQL with correct field names and Table name.

SELECT InvoiceMaster.ID,[AdvancePayment],[GrossInv-WorksCertified-61110], [Advance 409130], (SELECT Sum(NZ([AdvancePayment],0)-NZ([GrossInv-WorksCertified-61110],0)) FROM [SubCon-BasicInfo] WHERE [Inv ID]<=T1.[Inv ID]) As [Advance Payment Balance] FROM [SubCon-BasicInfo] AS T1;

Can you please integrate the above code with the below code!!


SELECT InvoiceMaster.ID, InvoiceMaster.SubConName, InvoiceMaster.Phase, InvoiceMaster.InvDate, InvoiceMaster.ReceiptDate, InvoiceMaster.InvNo, InvoiceMaster.ElodieDocNo, InvoiceMaster.Description, InvoiceMaster.PaymentCertiNo, InvoiceMaster.ProgressMonth, InvoiceMaster.ContractType, InvoiceMaster.AdvancePayment, InvoiceMaster.[GrossInv-WorksCertified-61110], InvoiceMaster.PayDueDate, InvoiceMaster.DatePaid, InvoiceMaster.TRANo, InvoiceMaster.Amount, ([GrossInv-WorksCertified-61110]*[SubCon-BasicInfo].[Advance-409130-%]/100) AS [Advance 409130], ([GrossInv-WorksCertified-61110]*[SubCon-BasicInfo].[Retension-401800%]/100) AS [Retension 401800], ([GrossInv-WorksCertified-61110]*[SubCon-BasicInfo].[SafetyRetention-401600%]/100) AS [Safety Retention 401600], ([GrossInv-WorksCertified-61110]*[SubCon-BasicInfo].[Performance-401700%]/100) AS [Performance 401700], Nz([Advance 409130])+Nz([Retension 401800])+Nz([Safety Retention 401600])+Nz([Performance 401700]) AS DeductionsTotal, IIf(([AdvancePayment])>1 And [DeductionsTotal]<0,([AdvancePayment]),Nz([AdvancePayment])+Nz([GrossInv-WorksCertified-61110])-Nz([DeductionsTotal])) AS [Net Payable]
FROM [SubCon-BasicInfo] INNER JOIN InvoiceMaster ON [SubCon-BasicInfo].SubConName = InvoiceMaster.SubConName;
 
P

paulcherianc

Registered User.
Local time
Today, 02:48
Joined
Aug 15, 2013
Messages
86
arnelgp said:
select [Inv ID], [Advance Payment],[Works Certified], [Advance 10%], (SELECT Sum(NZ([Advance Payment],0)-NZ([Works Certified],0)) FROM yourTable WHERE [Inv ID]<=T1.[Inv ID]) As [Advance Payment Balance] FROM YourTable AS T1


Replace YourTable with the correct table name.
Click to expand...

Dear Arnel,

I have attached the sample access file. Can you please look into it. (My Query)

Best Regards,
Paul C
 

Attachments

  • Sample.accdb
    1.7 MB · Views: 55
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:48
Joined
May 7, 2009
Messages
19,233
here take a look at the attached.
 

Attachments

  • Asample.zip
    55 KB · Views: 67
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:48
Joined
May 7, 2009
Messages
19,233
mr. paul, what does LRT2C3/C2 means,
don't tell me your working in the PH.
 
P

paulcherianc

Registered User.
Local time
Today, 02:48
Joined
Aug 15, 2013
Messages
86
arnelgp said:
here take a look at the attached.
Click to expand...

Dear Arnel,

Thanks! But the formula should deduct from the field "Advance 409130" not "GrossInv-WorksCertified-61110". But when I renamed to the other the query is not working. Can you please help. I have attached the excel for your better understanding.


Advance Payment Balance: (SELECT SUM(NZ(T1.AdvancePayment,0)-NZ(T1.[GrossInv-WorksCertified-61110],0)) FROM [InvoiceMaster] AS T1 WHERE T1.[SubConName]=[InvoiceMaster].[SubconName] AND T1.[ID]<=[InvoiceMaster].[ID])
 

Attachments

  • Subcon sample.xlsx
    32 KB · Views: 54
P

paulcherianc

Registered User.
Local time
Today, 02:48
Joined
Aug 15, 2013
Messages
86
arnelgp said:
here take a look at the attached.
Click to expand...

Dear Arnel,

Really sorry to bother you. My initial formula provided on the excel was wrong.

I have attached the correct one.

Instead of Cell "D"I mentioned "C". Below you may see the correct formula.

Different Formula from here (=+E2+B3-D3)

So the reference in the query should be "Advance 409130" field.

Sorry for the inconvenience.
 

Attachments

  • Intial Sample Correction.xlsx
    11.2 KB · Views: 57
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:48
Joined
May 7, 2009
Messages
19,233
here i made the correction.
 

Attachments

  • Asample.zip
    56.7 KB · Views: 56
P

paulcherianc

Registered User.
Local time
Today, 02:48
Joined
Aug 15, 2013
Messages
86
arnelgp said:
here i made the correction.
Click to expand...

Perfect!!!

I have integrated your coding to my main database. However, I am not able to add any records on the form. it's locked. Can you please look into it. I have attached my main database for your kind review.

Thanks a ton!
 

Attachments

  • SubconPro.accdb
    1.3 MB · Views: 73
Last edited:
P

paulcherianc

Registered User.
Local time
Today, 02:48
Joined
Aug 15, 2013
Messages
86
Dear Arnel,

Instead of keeping inside the query, can I use the same on the subform design view?:banghead:

Someone suggested me the following: With that, I could remove the data entry lock but I am not able to get the result.

Using calculations in a query make the underlying recordset not updateable. See this question for an excellent list why queries (and forms based on them) sometimes are not updateable.

The proper way to include calculations on a datasheet form is to open it in design view, add a new text box control to them, and set the controls control source equal to an = equal sign followed by your calculation.

You also will need to rewrite the subquery to a domain aggregate or use a combo box to use a query.

Rewrite example: =DSUM("NZ(InvoiceMaster.AdvancePayment,0)- NZ(([GrossInv-WorksCertified-61110]*[SubCon-BasicInfo].[Advance-409130-%]/100),0)", "InvoiceMaster", "[SubConName] = """ & [InvoiceMaster].[SubconName] & """ AND ID = " & [InvoiceMaster].[ID])

Can you please help me .
 
Last edited:
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:48
Joined
May 7, 2009
Messages
19,233
i added the fields which we calculate on the query.
the subform's recordsource is qryInvoiceMaster.
see the code behind the subform.
 

Attachments

  • aaSubconPro.zip
    73 KB · Views: 72
P

paulcherianc

Registered User.
Local time
Today, 02:48
Joined
Aug 15, 2013
Messages
86
Dear Arnel,

I checked your file. I have the following issues:

1. Advance Payment Balance is not updated on updating the "Work Certified" field. It is only updating upon creating a new invoice now. Actually, it should calculate as when I update the work certified or advance amount.

2. After updating deduction % ( on the main form, the queries are not refreshing.

I have attached the data updated file for your review.

Many thanks for your continuous support and guidance!

Thanks in Advance :)

Paul Cherian.
 

Attachments

  • SubconPro Test.zip
    89.9 KB · Views: 56
  • deduction %.JPG
    deduction %.JPG
    17.7 KB · Views: 68
Last edited:
P

paulcherianc

Registered User.
Local time
Today, 02:48
Joined
Aug 15, 2013
Messages
86
arnelgp said:
i added the fields which we calculate on the query.
the subform's recordsource is qryInvoiceMaster.
see the code behind the subform.
Click to expand...

Dear Arnel,

I could see the current record's "Advance Payment Balance" is changing only once when I change the Previous records "Advance Payment" or " WorkCertified" field.

Can you please help me to fix this.

Regards,
Paul C
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:48
Joined
May 7, 2009
Messages
19,233
i have to use AfterUpdate event of the subform
and do away with AfterUpdate of control since
we are updating multiple records.
 

Attachments

  • aaSubconPro11.zip
    70.9 KB · Views: 54
You must log in or register to reply here.

Similar threads

M
Query for multiple "not like" criteria on long text fields operating as "or", not "and".
Replies
9
Views
347
mike60smart
mike60smart
P
Invisible Query
Replies
5
Views
413
PeteB
P
L
Meeting schedule with Date Range
Replies
2
Views
259
MajP
MajP
K
Solved Why adding criteria to a column makes a query asking for parameters?
Replies
11
Views
586
The_Doc_Man
The_Doc_Man
T
Using multiple criteria in DCOUNT function using a comparison
Replies
1
Views
129
June7
June7

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom