Calculation on multiple dates

Posted on 2003-03-01
Medium Priority
Last Modified: 2011-10-03
Inv_No. , Inv_Date, Client, Type, Discount, 1st Pay, 1st Date, 2nd Pay, 2nd Date, 3rd Pay, 3rd Date

Now I want to calculate the amount according to dates. Suppose :

I have Invoice # 001 of 30 Dollar.
I received amount 10 on 1st Date(2/2/2003)
I received amount 10 on 2nd date(5/2/2003)

Another invoice # 002 of 50 Dollar
I received amount 25 on 1st Date(2/2/2003)
I received amount 10 on 2nd date(5/2/2003)

Now i want to calculate amount according to datewise, like
I have date 2/2/2003 and that total should be 25+10 = 35
I have date 5/2/2003 and that total should be 10+10 = 20

I dont know how to solve it, please help me.
Thanking you in advance
Question by:AbidAli
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Accepted Solution

JennyRo earned 300 total points
ID: 8050979


where 1stPay is the name of the column you're wanting to add up and Table is the name of your table.

If 1stPay is always 2/2/2003 as it seems to be with your table, then you don't need to specify date.

But if you had a column called Date with different dates then it would be:

SELECT SUM(1stPay) FROM Table WHERE Date=2/2/2003

Expert Comment

ID: 8051039
Actually, if you have time, it almost seems that your Invoice_No and Invoice_Date should be in one table and then Pay_Amount and Pay_Date should be in a second table.  Think of the second table like a history table.  For example, what happens if it takes your customers 4 or 5 times to pay?  Anyway, let me know if you would like some assistance in setting up the relationships.  It truly would appear to be a much more efficient way of setting up your system.  Let me know your thoughts.


Author Comment

ID: 8051092
Thank you for your answer and comment:
but how can i give the criteria of one input date and give the result of three same dates. I dont want to specify any date like : WHERE Date=2/2/2003

Expert Comment

ID: 8051291
I think this is what you mean:

If you have a table like this:

PayDate   Amount
-------   ------
2/2/2003  20
2/2/2003  50
5/2/2003  10
5/2/2003  10

Then this SQL:

SELECT PayDate,SUM(Amount) FROM PaymentsTable

would return this:

PayDate   Amount
-------   ------
2/2/2003  70
5/2/2003  20

Thus no need for any actual dates given in code.

Hope this helps a bit more!


Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question