SQL Query

[Abdul]

Hello!,

This is my first post over here

I am doing an SQL Query to get data to access.
What I am looking for is to get all documents for transactions having a
specific account no. This is an accounting transaction table. For Eg.
let us say if the account number = 112200 then i want to get both debit
and credit transaction for this. in another way all the documents if
either debit or credit account number is 112200. some times on the
debit side for the document account number will be 112200 and there may
be many credit transaction for this. i need all these to get

Thanks

Abdul

 

[John Vinson]

Hello!,

This is my first post over here

I am doing an SQL Query to get data to access.
What I am looking for is to get all documents for transactions having a
specific account no. This is an accounting transaction table. For Eg.
let us say if the account number = 112200 then i want to get both debit
and credit transaction for this. in another way all the documents if
either debit or credit account number is 112200. some times on the
debit side for the document account number will be 112200 and there may
be many credit transaction for this. i need all these to get

Thanks

Abdul

It's not at all clear what you're asking. What have you tried? Do you
have the transactions table linked to Access? or are you trying to
create a SQL View and link to that?

I would expect that you could simply link to the table; create a new
Query based on the table; and use 112200 on the Criteria line under
the Account Number field.

John W. Vinson[MVP]

 

[Abdul]

This is what I use

SELECT T1.[Posting Date], T1.[Document No_], T1.[Account No_], T2.Name,
T1.Description, Round(T1.[Debit Amount],2) AS DebitAmount,
Round(T1.[Credit Amount],2) AS CreditAmount, T1.[Shortcut Dimension 1
Code], T1.[Shortcut Dimension 2 Code]
FROM Journal_Num AS T1 INNER JOIN T2 ON T1.[Account No_] = T2.No_
WHERE (([Account No_] Like "12345*"));

But it gives me only one side.

I need both side for this transaction, that is debit and credit. Since
on the credit side Account No_ will not be 12345*

how i can get both debit and credit if either debit account no or
credit account no is 12345*



Thanks

 

[John Vinson]

This is what I use

SELECT T1.[Posting Date], T1.[Document No_], T1.[Account No_], T2.Name,
T1.Description, Round(T1.[Debit Amount],2) AS DebitAmount,
Round(T1.[Credit Amount],2) AS CreditAmount, T1.[Shortcut Dimension 1
Code], T1.[Shortcut Dimension 2 Code]
FROM Journal_Num AS T1 INNER JOIN T2 ON T1.[Account No_] = T2.No_
WHERE (([Account No_] Like "12345*"));

But it gives me only one side.

I need both side for this transaction, that is debit and credit. Since
on the credit side Account No_ will not be 12345*
how i can get both debit and credit if either debit account no or
credit account no is 12345*

What IS the account number on the "credit side"? If it is not equal to
the account number on the debit side, how can Access (or anyone!)
identify which record contains the credit? What (if any) fields are
there which provide a link between a debit and the corresponding
credit?

Abdul, you can see your database. You know what the fields are, and
what the tables contain. We do not have that information.

John W. Vinson[MVP]

 

[Abdul]

Thanks for your reply

[Document No_] will be the same for both debit and credit side.

so if [Account No_] is like "12345*" on either credit side or debit
side I need those [Document No_] with both debit and credit side
transaction to belisted


Thanks

This is what I use

SELECT T1.[Posting Date], T1.[Document No_], T1.[Account No_], T2.Name,
T1.Description, Round(T1.[Debit Amount],2) AS DebitAmount,
Round(T1.[Credit Amount],2) AS CreditAmount, T1.[Shortcut Dimension 1
Code], T1.[Shortcut Dimension 2 Code]
FROM Journal_Num AS T1 INNER JOIN T2 ON T1.[Account No_] = T2.No_
WHERE (([Account No_] Like "12345*"));

But it gives me only one side.

I need both side for this transaction, that is debit and credit. Since
on the credit side Account No_ will not be 12345*
how i can get both debit and credit if either debit account no or
credit account no is 12345*

What IS the account number on the "credit side"? If it is not equal to
the account number on the debit side, how can Access (or anyone!)
identify which record contains the credit? What (if any) fields are
there which provide a link between a debit and the corresponding
credit?

Abdul, you can see your database. You know what the fields are, and
what the tables contain. We do not have that information.

John W. Vinson[MVP]

 

[John Vinson]

Thanks for your reply

[Document No_] will be the same for both debit and credit side.

so if [Account No_] is like "12345*" on either credit side or debit
side I need those [Document No_] with both debit and credit side
transaction to belisted

ok... guessing a bit here:

SELECT T1.[Posting Date], T1.[Document No_], T1.[Account No_],
T2.Name, T1.Description, Round(T1.[Debit Amount],2) AS DebitAmount,
Round(T1.[Credit Amount],2) AS CreditAmount, T1.[Shortcut Dimension 1
Code], T1.[Shortcut Dimension 2 Code]
FROM Journal_Num AS T1 INNER JOIN Journal_Num AS T2
ON T1.[Document No_] = T2.[Document No_]
WHERE ((T1.[Account No_] Like "12345*") OR (T2.[Account No_] LIKE
"12345*"));

You'll need to select other fields from both T1 (the credit
record...?) and T2 (the debit record???) - again, I don't know how
your table distinguishes credits from debits, what an account
represents, or what a document represents.

John W. Vinson[MVP]