Days=NextDate-CurrentDate but How in Access Query?

N

nasir

New member

Local time

Tomorrow, 03:12

Joined

Aug 8, 2007

Messages

5

• Aug 8, 2007

• #1

I have two tables
ACCOUNTS
with fields
(AccountNumber, Name, AccountTypeID, Address, Phone, Date A/C Opened)

And
TRANSACTIONS
(TransactionID, TransactionDate, Particular, ChequeNo,AccountNumber,AccountTypeID, Rate, WithdrawalAmount, DepositAmount)

I created a query based on these two tables
CUSTOMER
(AccountNumber, TransactionDate, Days)

In this query Days: (NextTransactionDate-TransactionDate)
How I access NextTransactionDate of a specific Account in the record?

 

I

Ian Mac

Registered User.

Local time

Today, 23:12

Joined

Mar 11, 2002

Messages

179

• Aug 8, 2007

• #2

If I have this correct, you don't need the Accounts Table in your query.

You can use:

SELECT
AccountNumber,
TransactionDate,
(SELECT TOP 1 TransactionDate
FROM Transactions
WHERE TransactionDate > Transactions1.TransactionDate And AccountNumber = Transactions1.AccountNumber
ORDER BY TransactionDate; )-[TransactionDate] AS Days
FROM Transactions AS Transactions1;

I can explain if needed (I hope)

 

N

nasir

New member

Local time

Tomorrow, 03:12

Joined

Aug 8, 2007

Messages

5

• Aug 9, 2007

• #3

Respected IAN MAC
Thank You so much for co operation.
May God help you in every difficulty and keep you under His Blessings.
I was struggling to solve problem for so many days. It work.
Really Thanks
Nasir
www.nasir.co.nr
nasiramd@hotmail.com

 

N

nasir

New member

Local time

Tomorrow, 03:12

Joined

Aug 8, 2007

Messages

5

• Aug 9, 2007

• #4

I couldn't create report of this query? I want to create report by AccountNumber when I tried I got this message "Multi-Level GROUP BY Clause is not allowed in a subquery"? What is the solution of this problem? Please Sir?

 

I

Ian Mac

Registered User.

Local time

Today, 23:12

Joined

Mar 11, 2002

Messages

179

• Aug 21, 2007

• #5

Sorry for taking a while to get back to you.

Can you describe what is happening please?

I have created a report the has Group by on the Account number from the orginal table, then a subreport using the query I gave you and linked Master/Child on Account Number and get a report.

Not sure what you are trying to do.
If you can send a screen print of the design of the report I can have a look.
I tried to attach one myself but our servers won't allow it.

Cheers,

 

N

nasir

New member

Local time

Tomorrow, 03:12

Joined

Aug 8, 2007

Messages

5

• Aug 31, 2007

• #6

Sorry for taking a while to get back to you.
Thank Ian its working Thanks a lot.

Nasir
___________________________
Love 4 All, Hatred 4 None

 

N

nasir

New member

Local time

Tomorrow, 03:12

Joined

Aug 8, 2007

Messages

5

• Sep 8, 2007

• #7

Sorry for disturbing you again.
I have the following Table:

Transactions(TransactionID, TransactionDate, Particulars, ChequeNo, AccountNumber, AccountTypeID, WithDrawalAmount, DepositAmount)

TransactionID is the primary key.

I want to calculate Running Balance of a specific AccountNumber by using query. Please help me.

A BEAUTIFUL BELIEF
If God answers your prayer, He is increasing your faith!
If He delays, He is increasing your patience!
If He doesn't answer, He knows you can handle.

 

neileg

AWF VIP

Local time

Today, 23:12

Joined

Dec 4, 2002

Messages

5,970

• Sep 10, 2007

• #8

Running balances aren't very easy in a query. The techniques have been covered in these forums, though, so do a search and come back if you still can't figure it out.