USING SQL STMT FOR QUERY

  • Thread starter Thread starter anamque
  • Start date Start date
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.
 
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".
 
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...
 
Back
Top