Solved

How do I run this in MS Access

Posted on 2004-09-02
26
222 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
  • 4
  • +2
26 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 66

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 11967664
or

and Format(sn.entered_dt,"mm/dd/yyyy") < #07/31/2004#
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 66

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
 

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 66

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 66

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

726 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