?
Solved

How do I run this in MS Access

Posted on 2004-09-02
26
Medium Priority
?
225 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

777 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