Solved

How do I run this in MS Access

Posted on 2004-09-02
26
193 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

867 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

12 Experts available now in Live!

Get 1:1 Help Now