create query

N

nishkrish

i need to compare 2 fields USD AMT from Funding Table with Funding AMt from
my activity Tbl, unmached query doesnt return correct info. the thing is i
have transaction No. which are in increment order in activity Tbl but not in
same order in Funding Tbl one transaction can be processed Twice with same
Number with an "r" in the end eg:
Transaction No. Funding Amt USDaMT
1000018825 834.03 834.03
1000018826 1500 1503
1000018825R 834.03 280(THIS MIGHT SHOW
DOWNLOADED amt)

Funding Tbl has Agent Transaction No. and Activity File has Transaction
NO.Basically the thing is my Funding has to match their USD AMT it can be in
Topsy- turvy order
 
K

KARL DEWEY

No sure if this is what you had in mind or not.
SELECT [activity Tbl].[Transaction No], [activity Tbl].USDaMT, [Funding
Table].[Transaction No], [Funding Table].[Funding Amt]
FROM [activity Tbl], [Funding Table]
WHERE ((([Funding Table].[Transaction No])=Left([activity Tbl].[Transaction
No],10))) OR ((([activity Tbl].[Transaction No])=[Funding Table].[Transaction
No]) AND (([Funding Table].[Transaction No])=[activity Tbl].[Transaction
No]));
 
N

nishkrish

i tried to copy the query and entered as Expr1:SELECT [Tbl activity 07].[Trxn
#], [Tbl activity 07].[Funding], [Funding 07
Table].[AgentTxnRefNo
], [Funding07 Table].[Field13]
FROM [Tbl activity 07], [Funding07 Table]
WHERE ((([Funding07 Table].[AgentTxnRefNo])=Left([Tbl activity 07].[Trxn #
],10))) OR ((([Tbl activity 07].[Trxn #])=[Funding Table].[AgentTxnRefNo
]) AND (([Funding Table].[AgentTxnRefNo])=[Tbl activity 07].[Trxn #]));
this is the fields i am having but it shows error

The Syntax of the Subquery in this expression is incorrect
ck the subquery syntax and enclose the subquery in parentheses


Please help
--
Nisha P


KARL DEWEY said:
No sure if this is what you had in mind or not.
SELECT [activity Tbl].[Transaction No], [activity Tbl].USDaMT, [Funding
Table].[Transaction No], [Funding Table].[Funding Amt]
FROM [activity Tbl], [Funding Table]
WHERE ((([Funding Table].[Transaction No])=Left([activity Tbl].[Transaction
No],10))) OR ((([activity Tbl].[Transaction No])=[Funding Table].[Transaction
No]) AND (([Funding Table].[Transaction No])=[activity Tbl].[Transaction
No]));

--
KARL DEWEY
Build a little - Test a little


nishkrish said:
i need to compare 2 fields USD AMT from Funding Table with Funding AMt from
my activity Tbl, unmached query doesnt return correct info. the thing is i
have transaction No. which are in increment order in activity Tbl but not in
same order in Funding Tbl one transaction can be processed Twice with same
Number with an "r" in the end eg:
Transaction No. Funding Amt USDaMT
1000018825 834.03 834.03
1000018826 1500 1503
1000018825R 834.03 280(THIS MIGHT SHOW
DOWNLOADED amt)

Funding Tbl has Agent Transaction No. and Activity File has Transaction
NO.Basically the thing is my Funding has to match their USD AMT it can be in
Topsy- turvy order
Click to expand...
Click to expand...
 
K

KARL DEWEY

You pasted to the wrong place.
Create a new query opening in the design view. Click on menu VIEW - SQL
View and paste my post in the window. Remove any hard returns or spaces
added by the coping and pating actions.
Expand the pane to maximum and the first three lines will start like this ---
SELECT [activity Tbl].[Transaction No],
FROM [activity Tbl], [Funding Table]
WHERE ((([Funding Table].[Transaction No])

--
KARL DEWEY
Build a little - Test a little


nishkrish said:
i tried to copy the query and entered as Expr1:SELECT [Tbl activity 07].[Trxn
#], [Tbl activity 07].[Funding], [Funding 07
Table].[AgentTxnRefNo
], [Funding07 Table].[Field13]
FROM [Tbl activity 07], [Funding07 Table]
WHERE ((([Funding07 Table].[AgentTxnRefNo])=Left([Tbl activity 07].[Trxn #
],10))) OR ((([Tbl activity 07].[Trxn #])=[Funding Table].[AgentTxnRefNo
]) AND (([Funding Table].[AgentTxnRefNo])=[Tbl activity 07].[Trxn #]));
this is the fields i am having but it shows error

The Syntax of the Subquery in this expression is incorrect
ck the subquery syntax and enclose the subquery in parentheses


Please help
--
Nisha P


KARL DEWEY said:
No sure if this is what you had in mind or not.
SELECT [activity Tbl].[Transaction No], [activity Tbl].USDaMT, [Funding
Table].[Transaction No], [Funding Table].[Funding Amt]
FROM [activity Tbl], [Funding Table]
WHERE ((([Funding Table].[Transaction No])=Left([activity Tbl].[Transaction
No],10))) OR ((([activity Tbl].[Transaction No])=[Funding Table].[Transaction
No]) AND (([Funding Table].[Transaction No])=[activity Tbl].[Transaction
No]));

--
KARL DEWEY
Build a little - Test a little


nishkrish said:
i need to compare 2 fields USD AMT from Funding Table with Funding AMt from
my activity Tbl, unmached query doesnt return correct info. the thing is i
have transaction No. which are in increment order in activity Tbl but not in
same order in Funding Tbl one transaction can be processed Twice with same
Number with an "r" in the end eg:
Transaction No. Funding Amt USDaMT
1000018825 834.03 834.03
1000018826 1500 1503
1000018825R 834.03 280(THIS MIGHT SHOW
DOWNLOADED amt)

Funding Tbl has Agent Transaction No. and Activity File has Transaction
NO.Basically the thing is my Funding has to match their USD AMT it can be in
Topsy- turvy order
Click to expand...
Click to expand...
Click to expand...
 
N

nishkrish

No Karl its not working i typed in SQL View but the query returns Error
saying The Select Statement includes a reserved wordor an argument ..........
when i click ok the cursor blinks on FORM.
--
Nisha P


KARL DEWEY said:
You pasted to the wrong place.
Create a new query opening in the design view. Click on menu VIEW - SQL
View and paste my post in the window. Remove any hard returns or spaces
added by the coping and pating actions.
Expand the pane to maximum and the first three lines will start like this ---
SELECT [activity Tbl].[Transaction No],
FROM [activity Tbl], [Funding Table]
WHERE ((([Funding Table].[Transaction No])

--
KARL DEWEY
Build a little - Test a little


nishkrish said:
i tried to copy the query and entered as Expr1:SELECT [Tbl activity 07].[Trxn
#], [Tbl activity 07].[Funding], [Funding 07
Table].[AgentTxnRefNo
], [Funding07 Table].[Field13]
FROM [Tbl activity 07], [Funding07 Table]
WHERE ((([Funding07 Table].[AgentTxnRefNo])=Left([Tbl activity 07].[Trxn #
],10))) OR ((([Tbl activity 07].[Trxn #])=[Funding Table].[AgentTxnRefNo
]) AND (([Funding Table].[AgentTxnRefNo])=[Tbl activity 07].[Trxn #]));
this is the fields i am having but it shows error

The Syntax of the Subquery in this expression is incorrect
ck the subquery syntax and enclose the subquery in parentheses


Please help
--
Nisha P


KARL DEWEY said:
No sure if this is what you had in mind or not.
SELECT [activity Tbl].[Transaction No], [activity Tbl].USDaMT, [Funding
Table].[Transaction No], [Funding Table].[Funding Amt]
FROM [activity Tbl], [Funding Table]
WHERE ((([Funding Table].[Transaction No])=Left([activity Tbl].[Transaction
No],10))) OR ((([activity Tbl].[Transaction No])=[Funding Table].[Transaction
No]) AND (([Funding Table].[Transaction No])=[activity Tbl].[Transaction
No]));

--
KARL DEWEY
Build a little - Test a little


:

i need to compare 2 fields USD AMT from Funding Table with Funding AMt from
my activity Tbl, unmached query doesnt return correct info. the thing is i
have transaction No. which are in increment order in activity Tbl but not in
same order in Funding Tbl one transaction can be processed Twice with same
Number with an "r" in the end eg:
Transaction No. Funding Amt USDaMT
1000018825 834.03 834.03
1000018826 1500 1503
1000018825R 834.03 280(THIS MIGHT SHOW
DOWNLOADED amt)

Funding Tbl has Agent Transaction No. and Activity File has Transaction
NO.Basically the thing is my Funding has to match their USD AMT it can be in
Topsy- turvy order
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

No Karl its not working i typed in SQL View but the query returns Error
saying The Select Statement includes a reserved wordor an argument ..........
when i click ok the cursor blinks on FORM.
Click to expand...

If what you have in the SQL is in fact the word FORM, that looks like a
typographical error. It should be FROM.

Perhaps you could copy and paste the actual SQL that you're trying to use to a
message here.

John W. Vinson [MVP]
 
Top