[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

How do I run this in MS Access

I want to run this query in MS Access.  How can I get it to run?

SELECT Count(SN.CASE_ID), Sum(sn.AMOUNT)
FROM SUBRO.SUBRO_CASE sc, SUBRO.SUBRO_NEGOTIATION SN, SUBRO.SUBRO_CASE_CLIENT sccl
Where sccl.CLIENT_ID < 76
and sccl.client_id Not In (49, 56, 58, 59, 69, 71, 72)
AND sn.NEGOTIATION_TYPE_ID=4
AND sc.CASE_STATUS_ID In (2,6)
AND sn.CASE_ID = sc.case_id
And sn.CASE_ID Not In (select case_id from subro.subro_payment where active_ind = 'Y')
AND sccl.CASE_ID = sn.case_id
and sn.entered_dt < '31-jul-04'

I want that last line (sn.entered_dt < '31-jul-04') to pull the date from a form, and not need to be manually entered.  I've been dinking with this for a few days now, and I can never get Access to give me the same results that I get with the above SQL.

HELP!!!  Please :)
0
Ingx_Sub
Asked:
Ingx_Sub
  • 10
  • 8
  • 4
  • +2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try


SELECT Count(SN.CASE_ID), Sum(sn.AMOUNT)
FROM [SUBRO.SUBRO_CASE sc], [SUBRO.SUBRO_NEGOTIATION SN], [SUBRO.SUBRO_CASE_CLIENT sccl]
Where sccl.CLIENT_ID < 76
and sccl.client_id Not In (49, 56, 58, 59, 69, 71, 72)
AND sn.NEGOTIATION_TYPE_ID=4
AND sc.CASE_STATUS_ID In (2,6)
AND sn.CASE_ID = sc.case_id
And sn.CASE_ID Not In (select case_id from subro.subro_payment where active_ind = 'Y')
AND sccl.CASE_ID = sn.case_id
and sn.entered_dt < #31-jul-04#
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
and sn.entered_dt < '31-jul-04'

should be

and sn.entered_dt < #07/31/2004#

Everything else looks ok at first glance

Hope this helps.
-Jim
0
 
Rey Obrero (Capricorn1)Commented:
or

and Format(sn.entered_dt,"mm/dd/yyyy") < #07/31/2004#
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ingx_SubAuthor Commented:
Didn't work.  I don't think MS ACCESS likes that table naming convention of abreviation.  It's like you have to type the whole long name out.  I'll try the whole name.
0
 
wblakelyCommented:
If you want to pull the date from a form, change

and sn.entered_dt < #07/31/2004#

to

and sn.entered_dt < [forms]![formname]![controlname]

(replace formname and controlname with your form name and control name).
0
 
Rey Obrero (Capricorn1)Commented:
try this

SELECT Count(SN.CASE_ID), Sum(sn.AMOUNT)
FROM SUBRO.[SUBRO_CASE sc], SUBRO.[SUBRO_NEGOTIATION SN], SUBRO.[SUBRO_CASE_CLIENT sccl]
Where sccl.CLIENT_ID < 76
and sccl.client_id Not In (49, 56, 58, 59, 69, 71, 72)
AND sn.NEGOTIATION_TYPE_ID=4
AND sc.CASE_STATUS_ID In (2,6)
AND sn.CASE_ID = sc.case_id
And sn.CASE_ID Not In (select case_id from subro.subro_payment where active_ind = 'Y')
AND sccl.CASE_ID = sn.case_id
and Format(sn.entered_dt,"mm/dd/yyyy") < #07/31/2004#
0
 
Ingx_SubAuthor Commented:
I tried this SQL:

SELECT Count(SUBRO_SUBRO_NEGOTIATION_CASE_ID), Sum(SUBRO_SUBRO_NEGOTIATION_AMOUNT)
FROM [SUBRO_SUBRO_CASE], [SUBRO_SUBRO_NEGOTIATION], [SUBRO_SUBRO_CASE_CLIENT]
Where SUBRO_SUBRO_CASE_CLIENT_CLIENT_ID < 76
and SUBRO_SUBRO_CASE_CLIENT_client_id Not In (49, 56, 58, 59, 69, 71, 72)
AND SUBRO_SUBRO_NEGOTIATION_NEGOTIATION_TYPE_ID=4
AND SUBRO_SUBRO_CASE_CASE_STATUS_ID In (2,6)
AND SUBRO_SUBRO_NEGOTIATION_CASE_ID = SUBRO_SUBRO_CASE_case_id
And SUBRO_SUBRO_NEGOTIATION_CASE_ID Not In (select case_id from subro_subro_payment where active_ind = 'Y')
AND SUBRO_SUBRO_CASE_CLIENT_CASE_ID = SUBRO_SUBRO_NEGOTIATION_case_id
and SUBRO_SUBRO_NEGOTIATION_entered_dt < #31-jul-04#

And it propmts me for a value for SUBRO_SUBRO_NEGOTIATON_CASE_ID

:(
0
 
Ingx_SubAuthor Commented:
This one didn't work either:

SELECT Count(SN.CASE_ID), Sum(sn.AMOUNT)
FROM SUBRO.[SUBRO_CASE sc], SUBRO.[SUBRO_NEGOTIATION SN], SUBRO.[SUBRO_CASE_CLIENT sccl]
Where sccl.CLIENT_ID < 76
and sccl.client_id Not In (49, 56, 58, 59, 69, 71, 72)
AND sn.NEGOTIATION_TYPE_ID=4
AND sc.CASE_STATUS_ID In (2,6)
AND sn.CASE_ID = sc.case_id
And sn.CASE_ID Not In (select case_id from subro.subro_payment where active_ind = 'Y')
AND sccl.CASE_ID = sn.case_id
and Format(sn.entered_dt,"mm/dd/yyyy") < #07/31/2004#


Errored out while trying to look for a SUBRO.mdb in My Documents
0
 
Ingx_SubAuthor Commented:
I don't know if this matters....I probably should have told you this earlier.  Those "SUBRO" tables are from an Oracle database, and I'm using the Access DB to link to them.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
FROM SUBRO.[SUBRO_CASE sc], SUBRO.[SUBRO_NEGOTIATION SN], SUBRO.[SUBRO_CASE_CLIENT sccl

In Access, this would read..

FROM SUBRO INNER JOIN ... other joins ....

Having the SUBRO_CASE sc identified as sc works though.
0
 
shanesuebsahakarnCommented:
SUBRO_SUBRO_NEGOTIATON_CASE_ID

should be:
SUBRO_SUBRO_NEGOTIATON.CASE_ID

and likewise with your other tables and fields. However, your original query, with table aliases should work. Try this:
SELECT Count(SN.CASE_ID), Sum(sn.AMOUNT)
FROM SUBRO.SUBRO_CASE AS sc, SUBRO.SUBRO_NEGOTIATION AS SN, SUBRO.SUBRO_CASE_CLIENT AS sccl
Where sccl.CLIENT_ID < 76
and sccl.client_id Not In (49, 56, 58, 59, 69, 71, 72)
AND sn.NEGOTIATION_TYPE_ID=4
AND sc.CASE_STATUS_ID In (2,6)
AND sn.CASE_ID = sc.case_id
And sn.CASE_ID Not In (select case_id from subro.subro_payment where active_ind = 'Y')
AND sccl.CASE_ID = sn.case_id
and sn.entered_dt < #31-jul-04#

However, as far as I can see, all of the above suggestions should work.
0
 
Ingx_SubAuthor Commented:
This one errored out trying to find the SUBRO.mdb on My Documents as well.

SELECT Count(SN.CASE_ID), Sum(sn.AMOUNT)
FROM SUBRO.SUBRO_CASE AS sc, SUBRO.SUBRO_NEGOTIATION AS SN, SUBRO.SUBRO_CASE_CLIENT AS sccl
Where sccl.CLIENT_ID < 76
and sccl.client_id Not In (49, 56, 58, 59, 69, 71, 72)
AND sn.NEGOTIATION_TYPE_ID=4
AND sc.CASE_STATUS_ID In (2,6)
AND sn.CASE_ID = sc.case_id
And sn.CASE_ID Not In (select case_id from subro.subro_payment where active_ind = 'Y')
AND sccl.CASE_ID = sn.case_id
and sn.entered_dt < #31-jul-04#


Why is it looking for a SUBRO.mdb in My Documents
0
 
shanesuebsahakarnCommented:
Hold on a second - you have this:
SUBRO.SUBRO_CASE

What does the SUBRO. refer to in this case?
0
 
Ingx_SubAuthor Commented:
I believe SUBRO. would be the database name.

Ahh...that's probably why it's looking for the SUBRO.mdb in My Documents.  I have all those SUBRO. tables linked from an Oracle database on the network.
0
 
shanesuebsahakarnCommented:
Omit that part, i.e.:
SELECT Count(SN.CASE_ID), Sum(sn.AMOUNT)
FROM SUBRO_CASE AS sc, SUBRO_NEGOTIATION AS SN, SUBRO_CASE_CLIENT AS sccl
Where sccl.CLIENT_ID < 76
and sccl.client_id Not In (49, 56, 58, 59, 69, 71, 72)
AND sn.NEGOTIATION_TYPE_ID=4
AND sc.CASE_STATUS_ID In (2,6)
AND sn.CASE_ID = sc.case_id
And sn.CASE_ID Not In (select case_id from subro.subro_payment where active_ind = 'Y')
AND sccl.CASE_ID = sn.case_id
and sn.entered_dt < #31-jul-04#
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Shane:  Wouldn't he need JOINs between the three tables, since he's got WHERE clauses covering all three?
0
 
shanesuebsahakarnCommented:
Jim, no, you can do joins using this syntax. This:
SELECT A.*,B.* FROM A INNER JOIN B ON A.ID=B.AID
is equivalent to:
SELECT A.*, B.* FROM A,B WHERE A.ID=B.ID

Not sure as to speed differences, although I have a sneaking suspicion that the latter will be slower, although don't quote me on that. Jet does some pretty good optimisation on indexed fields.
0
 
Ingx_SubAuthor Commented:
No Go.  I got this error:

The Microsoft Jet database engine cannot find the input table or query 'SUBRO_CASE'.  Make sure it exists and that it's name is spelled correctly.

Here is an SQL sample of a query that actually runs in Access with this tables:

SELECT Sum(SUBRO_SUBRO_CLAIM.PAID_ON_CLAIM) AS SumOfPAID_ON_CLAIM
FROM SUBRO_SUBRO_CLAIM INNER JOIN SUBRO_SUBRO_PAYMENT ON SUBRO_SUBRO_CLAIM.CASE_ID = SUBRO_SUBRO_PAYMENT.CASE_ID
WHERE (((SUBRO_SUBRO_CLAIM.RECOVERED_ON_CLAIM) Is Not Null) AND ((SUBRO_SUBRO_CLAIM.INCLUDE_IN_TOTAL_IND)="Y") AND ((SUBRO_SUBRO_PAYMENT.ACTIVE_IND)="Y") AND ((SUBRO_SUBRO_PAYMENT.RPT_MONTH_DT)=[Forms]![SubroTrackReports]![LastDayofMonth]));


Granted...it's not the same query that we're trying to run...but it will show you how the table naming works.  I created this query in Design view.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
oic thanks
0
 
shanesuebsahakarnCommented:
So do you actually have a table called SUBRO_CASE in your database?
0
 
Ingx_SubAuthor Commented:
It's a linked table, SUBRO_SUBRO_CASE is the name of the linked table.  Dumb...I know...but I didn't do the naming of these things.
0
 
shanesuebsahakarnCommented:
I see. Try this:
SELECT Count(SN.CASE_ID), Sum(sn.AMOUNT)
FROM SUBRO_SUBRO_CASE AS sc, SUBRO_SUBRO_NEGOTIATION AS SN, SUBRO_SUBRO_CASE_CLIENT AS sccl
Where sccl.CLIENT_ID < 76
and sccl.client_id Not In (49, 56, 58, 59, 69, 71, 72)
AND sn.NEGOTIATION_TYPE_ID=4
AND sc.CASE_STATUS_ID In (2,6)
AND sn.CASE_ID = sc.case_id
And sn.CASE_ID Not In (select case_id from subro.subro_payment where active_ind = 'Y')
AND sccl.CASE_ID = sn.case_id
and sn.entered_dt < #31-jul-04#
0
 
Ingx_SubAuthor Commented:
Nope....once again it was looking for a SUBRO.mdb on my harddrive.  What the heck?  I can run this query directly against the Oracle database using an application called TOAD.  The SQL that I posted in the original question is exactly what I use.  

I'm just trying to automate this process a little bit, and I was hoping I could duplicate the query in MS Access, and then create a macro to run it.

Doesn't look like that will happen :(  Bummer.
0
 
shanesuebsahakarnCommented:
I missed one . in the expression:

SELECT Count(SN.CASE_ID), Sum(sn.AMOUNT)
FROM SUBRO_SUBRO_CASE AS sc, SUBRO_SUBRO_NEGOTIATION AS SN, SUBRO_SUBRO_CASE_CLIENT AS sccl
Where sccl.CLIENT_ID < 76
and sccl.client_id Not In (49, 56, 58, 59, 69, 71, 72)
AND sn.NEGOTIATION_TYPE_ID=4
AND sc.CASE_STATUS_ID In (2,6)
AND sn.CASE_ID = sc.case_id
And sn.CASE_ID Not In (select case_id from subro_subro_payment where active_ind = 'Y')
AND sccl.CASE_ID = sn.case_id
and sn.entered_dt < #31-jul-04#
0
 
Ingx_SubAuthor Commented:
GENIUS!!!!  That's it.  Thanks a bajillion!!
0
 
shanesuebsahakarnCommented:
No problem :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 8
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now