USING SQL STMT FOR QUERY

A

anamque

I'm trying to have a sql stmt in my query where I can pull data for a certian
group of individuals. I have other expressions and criteria in it (that is
working fine). However when I goto add the stmt to query for individual names
- i'm getting an error that states there is a problem with my From Clause.

This is what I have now:

SELECT COMBINATION.Associate, COMBINATION.NBK, COMBINATION.Date,
COMBINATION.[SS#], [COMBINATION]![CA Blocked]+[COMBINATION]![CA
Reduced]+[COMBINATION]![CA NO ACTION]+[COMBINATION]![STAT T
CA]+[COMBINATION]![CA CLOSED]+[COMBINATION]![CA NOT RISK
ELIGIBLE]+[COMBINATION]![CA STRAT ACCT] AS Expr1, COMBINATION.[CA Blocked],
COMBINATION.[CA Blocked Dollars], COMBINATION.[CA Reduced], COMBINATION.[CA
Reduced Dollars], COMBINATION.[CA NO ACTION], COMBINATION.[STAT T CA],
COMBINATION.[CA Stat T Dollars], COMBINATION.[CA CLOSED], COMBINATION.[CA NOT
RISK ELIGIBLE], COMBINATION.[CA STRAT ACCT], [COMBINATION]![CA Blocked
Dollars]+[COMBINATION]![CA Reduced Dollars]+[COMBINATION]![CA Stat T Dollars]
AS Expr2, [COMBINATION]![HELOC Blocked]+[COMBINATION]![HELOC
Reduced]+[COMBINATION]![HELOC NO ACTION]+[COMBINATION]![HELOC
CLOSED]+[COMBINATION]![HELOC NOT RISK ELIGIBLE]+[COMBINATION]![HELOC STRAT
ACCT] AS Expr3, COMBINATION.[HELOC Blocked], COMBINATION.[HELOC Blocked
Dollars], COMBINATION.[HELOC Reduced], COMBINATION.[HELOC REduced Dollars],
COMBINATION.[HELOC NO ACTION], COMBINATION.[HELOC CLOSED], COMBINATION.[HELOC
NOT RISK ELIGIBLE], COMBINATION.[HELOC STRAT ACCT], [COMBINATION]![HELOC
Blocked Dollars]+[COMBINATION]![HELOC REduced Dollars] AS Expr4,
[COMBINATION]![UNSEC LOC Blocked]+[COMBINATION]![UNSEC LOC
Reduced]+[COMBINATION]![UNSEC LOC NO ACTION]+[COMBINATION]![UNSEC LOC
CLOSED]+[COMBINATION]![UNSEC LOC NOT RISK ELIGIBLE]+[COMBINATION]![UNSEC LOC
STRAT ACCT] AS Expr5, COMBINATION.[UNSEC LOC Blocked], COMBINATION.[UNSEC LOC
Blocked Dollars], COMBINATION.[UNSEC LOC Reduced], COMBINATION.[UNSEC LOC NO
ACTION], COMBINATION.[UNSEC LOC CLOSED], COMBINATION.[UNSEC LOC NOT RISK
ELIGIBLE], COMBINATION.[UNSEC LOC STRAT ACCT], [COMBINATION]![UNSEC LOC
Blocked Dollars] AS Expr6, [COMBINATION]![SBL Blocked]+[COMBINATION]![SBL
Reduced]+[COMBINATION]![SBL NO ACTION]+[COMBINATION]![STAT T
SBL]+[COMBINATION]![SBL CLOSED]+[COMBINATION]![SBL NOT RISK
ELIGIBLE]+[COMBINATION]![SBL STRAT ACCT] AS Expr7, COMBINATION.[SBL Blocked],
COMBINATION.[SBL Blocked Dollars], COMBINATION.[SBL Reduced],
COMBINATION.[SBL Reduced Dollars], COMBINATION.[SBL NO ACTION],
COMBINATION.[STAT T SBL], COMBINATION.[STAT T SBL Dollars], COMBINATION.[SBL
CLOSED], COMBINATION.[SBL NOT RISK ELIGIBLE], COMBINATION.[SBL STRAT ACCT],
[COMBINATION]![SBL Blocked Dollars]+[COMBINATION]![SBL Reduced
Dollars]+[COMBINATION]![STAT T SBL Dollars] AS Expr8, [COMBINATION]![GO
Blocked]+[COMBINATION]![GO Reduced]+[COMBINATION]![GO NO
ACTION]+[COMBINATION]![STAT T GO]+[COMBINATION]![GO CLOSED]+[COMBINATION]![GO
NOT RISK ELIGIBLE]+[COMBINATION]![GO STRAT ACCT] AS Expr9, COMBINATION.[GO
Blocked], COMBINATION.[GO Blocked Dollars], COMBINATION.[GO Reduced],
COMBINATION.[GO Reduced Dollars], COMBINATION.[GO NO ACTION],
COMBINATION.[STAT T GO], COMBINATION.[GO Stat T Dollars], COMBINATION.[GO
CLOSED], COMBINATION.[GO NOT RISK ELIGIBLE], COMBINATION.[GO STRAT ACCT],
[COMBINATION]![GO Blocked Dollars]+[COMBINATION]![GO Reduced
Dollars]+[COMBINATION]![GO Stat T Dollars] AS Expr10, COMBINATION.[STRAT ID]
SELECT *
FROM [ASSOCIATE]
WHERE [ASSOCIATE] = "Caldwell, Michael L";

The last 3 lines:
SELECT *
FROM [ASSOCIATE]
WHERE [ASSOCIATE] = "Caldwell, Michael L", "Quevedo,Ana";

this is what I am trying to add - (everything before works fine) please note
I will be adding multiple users (one query with 25 and a second with 19).

Please help.
 
J

John W. Vinson

I'm trying to have a sql stmt in my query where I can pull data for a certian
group of individuals. I have other expressions and criteria in it (that is
working fine). However when I goto add the stmt to query for individual names
- i'm getting an error that states there is a problem with my From Clause.

This is what I have now:

SELECT COMBINATION.Associate, COMBINATION.NBK, COMBINATION.Date,
COMBINATION.[SS#], [COMBINATION]![CA Blocked]+[COMBINATION]![CA
Reduced]+[COMBINATION]![CA NO ACTION]+[COMBINATION]![STAT T
CA]+[COMBINATION]![CA CLOSED]+[COMBINATION]![CA NOT RISK
ELIGIBLE]+[COMBINATION]![CA STRAT ACCT] AS Expr1, COMBINATION.[CA Blocked],
COMBINATION.[CA Blocked Dollars], COMBINATION.[CA Reduced], COMBINATION.[CA
Reduced Dollars], COMBINATION.[CA NO ACTION], COMBINATION.[STAT T CA],
COMBINATION.[CA Stat T Dollars], COMBINATION.[CA CLOSED], COMBINATION.[CA NOT
RISK ELIGIBLE], COMBINATION.[CA STRAT ACCT], [COMBINATION]![CA Blocked
Dollars]+[COMBINATION]![CA Reduced Dollars]+[COMBINATION]![CA Stat T Dollars]
AS Expr2, [COMBINATION]![HELOC Blocked]+[COMBINATION]![HELOC
Reduced]+[COMBINATION]![HELOC NO ACTION]+[COMBINATION]![HELOC
CLOSED]+[COMBINATION]![HELOC NOT RISK ELIGIBLE]+[COMBINATION]![HELOC STRAT
ACCT] AS Expr3, COMBINATION.[HELOC Blocked], COMBINATION.[HELOC Blocked
Dollars], COMBINATION.[HELOC Reduced], COMBINATION.[HELOC REduced Dollars],
COMBINATION.[HELOC NO ACTION], COMBINATION.[HELOC CLOSED], COMBINATION.[HELOC
NOT RISK ELIGIBLE], COMBINATION.[HELOC STRAT ACCT], [COMBINATION]![HELOC
Blocked Dollars]+[COMBINATION]![HELOC REduced Dollars] AS Expr4,
[COMBINATION]![UNSEC LOC Blocked]+[COMBINATION]![UNSEC LOC
Reduced]+[COMBINATION]![UNSEC LOC NO ACTION]+[COMBINATION]![UNSEC LOC
CLOSED]+[COMBINATION]![UNSEC LOC NOT RISK ELIGIBLE]+[COMBINATION]![UNSEC LOC
STRAT ACCT] AS Expr5, COMBINATION.[UNSEC LOC Blocked], COMBINATION.[UNSEC LOC
Blocked Dollars], COMBINATION.[UNSEC LOC Reduced], COMBINATION.[UNSEC LOC NO
ACTION], COMBINATION.[UNSEC LOC CLOSED], COMBINATION.[UNSEC LOC NOT RISK
ELIGIBLE], COMBINATION.[UNSEC LOC STRAT ACCT], [COMBINATION]![UNSEC LOC
Blocked Dollars] AS Expr6, [COMBINATION]![SBL Blocked]+[COMBINATION]![SBL
Reduced]+[COMBINATION]![SBL NO ACTION]+[COMBINATION]![STAT T
SBL]+[COMBINATION]![SBL CLOSED]+[COMBINATION]![SBL NOT RISK
ELIGIBLE]+[COMBINATION]![SBL STRAT ACCT] AS Expr7, COMBINATION.[SBL Blocked],
COMBINATION.[SBL Blocked Dollars], COMBINATION.[SBL Reduced],
COMBINATION.[SBL Reduced Dollars], COMBINATION.[SBL NO ACTION],
COMBINATION.[STAT T SBL], COMBINATION.[STAT T SBL Dollars], COMBINATION.[SBL
CLOSED], COMBINATION.[SBL NOT RISK ELIGIBLE], COMBINATION.[SBL STRAT ACCT],
[COMBINATION]![SBL Blocked Dollars]+[COMBINATION]![SBL Reduced
Dollars]+[COMBINATION]![STAT T SBL Dollars] AS Expr8, [COMBINATION]![GO
Blocked]+[COMBINATION]![GO Reduced]+[COMBINATION]![GO NO
ACTION]+[COMBINATION]![STAT T GO]+[COMBINATION]![GO CLOSED]+[COMBINATION]![GO
NOT RISK ELIGIBLE]+[COMBINATION]![GO STRAT ACCT] AS Expr9, COMBINATION.[GO
Blocked], COMBINATION.[GO Blocked Dollars], COMBINATION.[GO Reduced],
COMBINATION.[GO Reduced Dollars], COMBINATION.[GO NO ACTION],
COMBINATION.[STAT T GO], COMBINATION.[GO Stat T Dollars], COMBINATION.[GO
CLOSED], COMBINATION.[GO NOT RISK ELIGIBLE], COMBINATION.[GO STRAT ACCT],
[COMBINATION]![GO Blocked Dollars]+[COMBINATION]![GO Reduced
Dollars]+[COMBINATION]![GO Stat T Dollars] AS Expr10, COMBINATION.[STRAT ID]
SELECT *
FROM [ASSOCIATE]
WHERE [ASSOCIATE] = "Caldwell, Michael L";

The last 3 lines:
SELECT *
FROM [ASSOCIATE]
WHERE [ASSOCIATE] = "Caldwell, Michael L", "Quevedo,Ana";

this is what I am trying to add - (everything before works fine) please note
I will be adding multiple users (one query with 25 and a second with 19).

Please help.
Click to expand...

Drop the SELECT * right before the end of the query - a query can have only
one SELECT clause, and you're SELECTing everything but the kitchen sink
already.

For multiple criteria use

WHERE [ASSOCIATE] IN ("Caldwell, Michael L", "Quevedo,Ana");

You should also replace the ! delimiters with .

I would also be VERY concerned about your table structure. A thirty field
table is getting uncomfortably wide; I didn't count your fields and don't know
anything about your data model, but it really looks like you're "committing
spreadsheet". As many have said, "Fields are expensive, records are cheap".
 
J

Jerry Whittle

SELECT COMBINATION.Associate,
COMBINATION.NBK,
COMBINATION.Date,
COMBINATION.[SS#],
[COMBINATION]![CA Blocked]+[COMBINATION]![CA Reduced]+[COMBINATION]![CA NO
ACTION]+[COMBINATION]![STAT T CA]+[COMBINATION]![CA CLOSED]+[COMBINATION]![CA
NOT RISK ELIGIBLE]+[COMBINATION]![CA STRAT ACCT] AS Expr1,
COMBINATION.[CA Blocked],
COMBINATION.[CA Blocked Dollars],
COMBINATION.[CA Reduced],
COMBINATION.[CA Reduced Dollars],
COMBINATION.[CA NO ACTION],
COMBINATION.[STAT T CA],
COMBINATION.[CA Stat T Dollars],
COMBINATION.[CA CLOSED],
COMBINATION.[CA NOT RISK ELIGIBLE],
COMBINATION.[CA STRAT ACCT],
[COMBINATION]![CA Blocked Dollars]+[COMBINATION]![CA Reduced
Dollars]+[COMBINATION]![CA Stat T Dollars] AS Expr2,
[COMBINATION]![HELOC Blocked]+[COMBINATION]![HELOC
Reduced]+[COMBINATION]![HELOC NO ACTION]+[COMBINATION]![HELOC
CLOSED]+[COMBINATION]![HELOC NOT RISK ELIGIBLE]+[COMBINATION]![HELOC STRAT
ACCT] AS Expr3,
COMBINATION.[HELOC Blocked],
COMBINATION.[HELOC Blocked Dollars],
COMBINATION.[HELOC Reduced],
COMBINATION.[HELOC REduced Dollars],
COMBINATION.[HELOC NO ACTION],
COMBINATION.[HELOC CLOSED],
COMBINATION.[HELOC NOT RISK ELIGIBLE],
COMBINATION.[HELOC STRAT ACCT],
[COMBINATION]![HELOC Blocked Dollars]+[COMBINATION]![HELOC REduced
Dollars] AS Expr4,
[COMBINATION]![UNSEC LOC Blocked]+[COMBINATION]![UNSEC LOC
Reduced]+[COMBINATION]![UNSEC LOC NO ACTION]+[COMBINATION]![UNSEC LOC
CLOSED]+[COMBINATION]![UNSEC LOC NOT RISK ELIGIBLE]+[COMBINATION]![UNSEC LOC
STRAT ACCT] AS Expr5,
COMBINATION.[UNSEC LOC Blocked],
COMBINATION.[UNSEC LOC Blocked Dollars],
COMBINATION.[UNSEC LOC Reduced],
COMBINATION.[UNSEC LOC NO ACTION],
COMBINATION.[UNSEC LOC CLOSED],
COMBINATION.[UNSEC LOC NOT RISK ELIGIBLE],
COMBINATION.[UNSEC LOC STRAT ACCT],
[COMBINATION]![UNSEC LOC Blocked Dollars] AS Expr6,
[COMBINATION]![SBL Blocked]+[COMBINATION]![SBL Reduced]+[COMBINATION]![SBL
NO ACTION]+[COMBINATION]![STAT T SBL]+[COMBINATION]![SBL
CLOSED]+[COMBINATION]![SBL NOT RISK ELIGIBLE]+[COMBINATION]![SBL STRAT ACCT]
AS Expr7,
COMBINATION.[SBL Blocked],
COMBINATION.[SBL Blocked Dollars],
COMBINATION.[SBL Reduced],
COMBINATION.[SBL Reduced Dollars],
COMBINATION.[SBL NO ACTION],
COMBINATION.[STAT T SBL],
COMBINATION.[STAT T SBL Dollars],
COMBINATION.[SBL CLOSED],
COMBINATION.[SBL NOT RISK ELIGIBLE],
COMBINATION.[SBL STRAT ACCT],
[COMBINATION]![SBL Blocked Dollars]+[COMBINATION]![SBL Reduced
Dollars]+[COMBINATION]![STAT T SBL Dollars] AS Expr8,
[COMBINATION]![GO Blocked]+[COMBINATION]![GO Reduced]+[COMBINATION]![GO NO
ACTION]+[COMBINATION]![STAT T GO]+[COMBINATION]![GO CLOSED]+[COMBINATION]![GO
NOT RISK ELIGIBLE]+[COMBINATION]![GO STRAT ACCT] AS Expr9,
COMBINATION.[GO Blocked],
COMBINATION.[GO Blocked Dollars],
COMBINATION.[GO Reduced],
COMBINATION.[GO Reduced Dollars],
COMBINATION.[GO NO ACTION],
COMBINATION.[STAT T GO],
COMBINATION.[GO Stat T Dollars],
COMBINATION.[GO CLOSED],
COMBINATION.[GO NOT RISK ELIGIBLE],
COMBINATION.[GO STRAT ACCT],
[COMBINATION]![GO Blocked Dollars]+[COMBINATION]![GO Reduced
Dollars]+[COMBINATION]![GO Stat T Dollars] AS Expr10,
COMBINATION.[STRAT ID]
FROM COMBINATION
WHERE ASSOCIATE In ("Caldwell, Michael L", "Quevedo, Ana") ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


anamque said:
I'm trying to have a sql stmt in my query where I can pull data for a certian
group of individuals. I have other expressions and criteria in it (that is
working fine). However when I goto add the stmt to query for individual names
- i'm getting an error that states there is a problem with my From Clause.

This is what I have now:

SELECT COMBINATION.Associate, COMBINATION.NBK, COMBINATION.Date,
COMBINATION.[SS#], [COMBINATION]![CA Blocked]+[COMBINATION]![CA
Reduced]+[COMBINATION]![CA NO ACTION]+[COMBINATION]![STAT T
CA]+[COMBINATION]![CA CLOSED]+[COMBINATION]![CA NOT RISK
ELIGIBLE]+[COMBINATION]![CA STRAT ACCT] AS Expr1, COMBINATION.[CA Blocked],
COMBINATION.[CA Blocked Dollars], COMBINATION.[CA Reduced], COMBINATION.[CA
Reduced Dollars], COMBINATION.[CA NO ACTION], COMBINATION.[STAT T CA],
COMBINATION.[CA Stat T Dollars], COMBINATION.[CA CLOSED], COMBINATION.[CA NOT
RISK ELIGIBLE], COMBINATION.[CA STRAT ACCT], [COMBINATION]![CA Blocked
Dollars]+[COMBINATION]![CA Reduced Dollars]+[COMBINATION]![CA Stat T Dollars]
AS Expr2, [COMBINATION]![HELOC Blocked]+[COMBINATION]![HELOC
Reduced]+[COMBINATION]![HELOC NO ACTION]+[COMBINATION]![HELOC
CLOSED]+[COMBINATION]![HELOC NOT RISK ELIGIBLE]+[COMBINATION]![HELOC STRAT
ACCT] AS Expr3, COMBINATION.[HELOC Blocked], COMBINATION.[HELOC Blocked
Dollars], COMBINATION.[HELOC Reduced], COMBINATION.[HELOC REduced Dollars],
COMBINATION.[HELOC NO ACTION], COMBINATION.[HELOC CLOSED], COMBINATION.[HELOC
NOT RISK ELIGIBLE], COMBINATION.[HELOC STRAT ACCT], [COMBINATION]![HELOC
Blocked Dollars]+[COMBINATION]![HELOC REduced Dollars] AS Expr4,
[COMBINATION]![UNSEC LOC Blocked]+[COMBINATION]![UNSEC LOC
Reduced]+[COMBINATION]![UNSEC LOC NO ACTION]+[COMBINATION]![UNSEC LOC
CLOSED]+[COMBINATION]![UNSEC LOC NOT RISK ELIGIBLE]+[COMBINATION]![UNSEC LOC
STRAT ACCT] AS Expr5, COMBINATION.[UNSEC LOC Blocked], COMBINATION.[UNSEC LOC
Blocked Dollars], COMBINATION.[UNSEC LOC Reduced], COMBINATION.[UNSEC LOC NO
ACTION], COMBINATION.[UNSEC LOC CLOSED], COMBINATION.[UNSEC LOC NOT RISK
ELIGIBLE], COMBINATION.[UNSEC LOC STRAT ACCT], [COMBINATION]![UNSEC LOC
Blocked Dollars] AS Expr6, [COMBINATION]![SBL Blocked]+[COMBINATION]![SBL
Reduced]+[COMBINATION]![SBL NO ACTION]+[COMBINATION]![STAT T
SBL]+[COMBINATION]![SBL CLOSED]+[COMBINATION]![SBL NOT RISK
ELIGIBLE]+[COMBINATION]![SBL STRAT ACCT] AS Expr7, COMBINATION.[SBL Blocked],
COMBINATION.[SBL Blocked Dollars], COMBINATION.[SBL Reduced],
COMBINATION.[SBL Reduced Dollars], COMBINATION.[SBL NO ACTION],
COMBINATION.[STAT T SBL], COMBINATION.[STAT T SBL Dollars], COMBINATION.[SBL
CLOSED], COMBINATION.[SBL NOT RISK ELIGIBLE], COMBINATION.[SBL STRAT ACCT],
[COMBINATION]![SBL Blocked Dollars]+[COMBINATION]![SBL Reduced
Dollars]+[COMBINATION]![STAT T SBL Dollars] AS Expr8, [COMBINATION]![GO
Blocked]+[COMBINATION]![GO Reduced]+[COMBINATION]![GO NO
ACTION]+[COMBINATION]![STAT T GO]+[COMBINATION]![GO CLOSED]+[COMBINATION]![GO
NOT RISK ELIGIBLE]+[COMBINATION]![GO STRAT ACCT] AS Expr9, COMBINATION.[GO
Blocked], COMBINATION.[GO Blocked Dollars], COMBINATION.[GO Reduced],
COMBINATION.[GO Reduced Dollars], COMBINATION.[GO NO ACTION],
COMBINATION.[STAT T GO], COMBINATION.[GO Stat T Dollars], COMBINATION.[GO
CLOSED], COMBINATION.[GO NOT RISK ELIGIBLE], COMBINATION.[GO STRAT ACCT],
[COMBINATION]![GO Blocked Dollars]+[COMBINATION]![GO Reduced
Dollars]+[COMBINATION]![GO Stat T Dollars] AS Expr10, COMBINATION.[STRAT ID]
SELECT *
FROM [ASSOCIATE]
WHERE [ASSOCIATE] = "Caldwell, Michael L";

The last 3 lines:
SELECT *
FROM [ASSOCIATE]
WHERE [ASSOCIATE] = "Caldwell, Michael L", "Quevedo,Ana";

this is what I am trying to add - (everything before works fine) please note
I will be adding multiple users (one query with 25 and a second with 19).

Please help.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help setting up relationships and query for form source 3
Only 6.00 0
Append Table with Insert Query via form / subform 1
Query works in SQL fails in VB code !?! 2
the ultimate stock trading data model 0
Prompt to save changes to a query 4
Delete a Query, or Empty it and Reload With New SQL? 3
I think I need a query....not quite for sure 3

Top