Solved

How do I run this in MS Access

Posted on 2004-09-02
26
182 Views
Last Modified: 2008-03-17
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
Comment
Question by:Ingx_Sub
  • 10
  • 8
  • 4
  • +2
26 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11967645
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 11967651
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11967664
or

and Format(sn.entered_dt,"mm/dd/yyyy") < #07/31/2004#
0
 

Author Comment

by:Ingx_Sub
ID: 11967665
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
 
LVL 2

Expert Comment

by:wblakely
ID: 11967689
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11967700
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
 

Author Comment

by:Ingx_Sub
ID: 11967709
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
 

Author Comment

by:Ingx_Sub
ID: 11967733
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
 

Author Comment

by:Ingx_Sub
ID: 11967765
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 11967769
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11967777
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
 

Author Comment

by:Ingx_Sub
ID: 11967840
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11967882
Hold on a second - you have this:
SUBRO.SUBRO_CASE

What does the SUBRO. refer to in this case?
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Ingx_Sub
ID: 11968008
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11968034
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 11968095
Shane:  Wouldn't he need JOINs between the three tables, since he's got WHERE clauses covering all three?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11968187
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
 

Author Comment

by:Ingx_Sub
ID: 11968203
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 11968209
oic thanks
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11968255
So do you actually have a table called SUBRO_CASE in your database?
0
 

Author Comment

by:Ingx_Sub
ID: 11968273
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11968303
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
 

Author Comment

by:Ingx_Sub
ID: 11968345
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
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 500 total points
ID: 11968548
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
 

Author Comment

by:Ingx_Sub
ID: 11972461
GENIUS!!!!  That's it.  Thanks a bajillion!!
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11972493
No problem :)
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now