Calculation on multiple dates

Table:
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
AbidAliAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JennyRoCommented:


SELECT SUM(1stPay) FROM Table

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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Garion789Commented:
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.

Thanks,
Greg
0
AbidAliAuthor Commented:
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
0
JennyRoCommented:
I think this is what you mean:

If you have a table like this:

PaymentsTable
-------------
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
GROUP BY PayDate

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!


0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.