?
Solved

Nested SQL statements in ASP/Access - need help with syntax

Posted on 2005-04-20
8
Medium Priority
?
285 Views
Last Modified: 2006-11-18
All - I need help with the syntax for nesting SQL statements. I am using Access to hold the data and ASP to deliver it to our intranet users. We are about to run a contest for supervisors to get more folks on direct deposit.

tblEFT is the table that has the data: columns are Manager, Supervisor, Worker, EFT (where "E" = EFT and "P" = paper).
Problem is to get percents of the Supervisor's workers on EFT (Electronic Funds Transfer), sorted by percent (DESC).
Cheesy solution is to run the queries in Access and pull from the second one in line. I would prefer to use a nested SQL statement in my ASP page. (Hey, I'm trainable...)

First query:
     TRANSFORM Count(tblEFT.Worker) AS CountOfWorker
     SELECT tblEFT.Manager, tblEFT.Supervisor, Count(tblEFT.Worker) AS TotWorkers
     FROM tblEFT
     GROUP BY tblEFT.Manager, tblEFT.Supervisor
     PIVOT tblEFT.EFT;

Second query (which pulls data from the first):
     SELECT qctEFT.Manager, qctEFT.Supervisor, [E]/[TotWorkers] AS pctEFT
     FROM qctEFT
     ORDER BY [E]/[TotWorkers] DESC;

Can I nest these, and how would I do it?  For example, as:
     SELECT qctEFT.Manager, qctEFT.Supervisor, [E]/[TotWorkers] AS pctEFT
     FROM (
     TRANSFORM Count(tblEFT.Worker) AS CountOfWorker
     SELECT tblEFT.Manager, tblEFT.Supervisor, Count(tblEFT.Worker) AS TotWorkers
     FROM tblEFT
     GROUP BY tblEFT.Manager, tblEFT.Supervisor
     PIVOT tblEFT.EFT;
     )
     ORDER BY pctEFT DESC;

Specific help, or a representative sample that I can play with would be much appreciated.

Thanks!  -Tim



0
Comment
Question by:paymeister
7 Comments
 
LVL 23

Expert Comment

by:apresto
ID: 13829566
Hi

im sorry but its not clear to me what you are trying to accomplish, could you just tell us what you would like to see on the asp page and we'll help you construct the SQL for it

Do you know if your current sql works, if not, why not? :o)

Apresto
0
 

Author Comment

by:paymeister
ID: 13834063
The overall db/query is quite complex - far more than I need to burden you with (though I'll try to create a test db and statement for this purpose).

But the generic task is to duplicate (in one SQL statement) what I can easily do in Access with two queries. One query (for instance) is a Crosstab Query which summarizes and categorizes the table data. The other query selects information from the Crosstab Query, performs calculations, and restricts the results per certain criteria. I can't do it in one step in Access, or I would have swiped their SQL view, and the error message I get is "Do two queries" or something like that.

I am sure one can "nest" SQL statements, and if you could show me the simplest example I could probably puzzle out the answer to my particular question. Note that I don't want to just use the "nested" statement to match values (like the criteria 'if the value is IN (-----) or whatever the syntax is), but rather, I need the numbers from the first (or nested) query.

Hope that clarifies things a bit. Note that the crisis is over - I cheated and used a pair of Access queries. But I would like to know how to do this trick.
0
 
LVL 10

Accepted Solution

by:
avidya earned 750 total points
ID: 13849955
Hi,

you stated this as you problem:
Problem is to get percents of the Supervisor's workers on EFT (Electronic Funds Transfer), sorted by percent (DESC).

it's hard to explain without the database/ asp code you're using, but maybe this helps
====================
1) you need to know the total of workers and the total of supervisors.
- your first query takes care of that
Put the result your getting in the vars emp_tot and emp_supervisor_tot

2) now you need to know how may supervisors ar working on "e"
SELECT tblEFT.Supervisor
FROM tblEFT
GROUP BY tblEFT.Supervisor, tblEFT.EFT
HAVING (((tblEFT.Supervisor) Is Not Null) AND ((tblEFT.EFT)="e"));

the total ammount of records is the amount of supervisors working on "e".
fill the var e_supervisor_tot with the recordcount command

Now you can calculated the percentage of supervisors that are working on EFt/e.




0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:paymeister
ID: 13851110
Well, I can certainly use this information to assist me in other situations (so I'll be happy to send some points your way even if someone else answers my primary question) - Thanks!

But the REAL question that is bugging me is "How do I get a query to feed into another query?" or, looked at the other way, "How can I use a SQL query to pull information from another SQL query instead of a table?"

The way I see it (which may need correction) is that if Access can use a query as a data source for another query, one ought to be able to do the same thing using a SQL statement from within an ASP page.  For example, Access won't let me set criteria when using a crosstab query - to do that, I need a second, "Select" query, which pulls from the crosstab query. I am effectively "nesting" one query inside another.

If such a "nesting" IS possible using SQL, what does it look like? For example, "Select A, B, C From (Select A, B, C from tblSource WHERE (criteria for first query);) WHERE (criteria for second query) ORDER BY (order desired for second query);" I can't think of why one would WANT to do a query like this, but if I can get THIS to work, I could adapt it to all sorts of other problems I am working on.

I will try to build a small database (which doesn't contain real company info) and I'll post a link to it.

Thanks for your patience...
0
 
LVL 10

Expert Comment

by:avidya
ID: 13851761
ok, I 'll wait for the database, much easiar to discuss things ;-)

btw, you can set criteria in an crosstable query using the "where" component.
here's an example you can cut and paste in ms access sql view:

TRANSFORM Count(tblEFT.Supervisor AS CountOfSupervisor
SELECT tblEFT.Supervisor
FROM tblEFT
WHERE (((tblEFT.Supervisor ) Is Not Null) AND ((tblEFT.EFT)="e"))
GROUP BY tblEFT.Supervisor, tblEFT.EFT
PIVOT order_status__main.EFT;
0
 
LVL 4

Assisted Solution

by:Danielcmorris
Danielcmorris earned 750 total points
ID: 13930965
I believe you can also set the source table  to be a query

TRANSFORM Count(MyAliasTableName.Supervisor AS CountOfSupervisor
SELECT MyAliasTableName.Supervisor
FROM (select some_fields from tblEFT) MyAliasTableName

WHERE (((MyAliasTableName.Supervisor ) Is Not Null) AND ((MyAliasTableName.EFT)="e"))
GROUP BY MyAliasTableName.Supervisor, tblEFT.EFT
PIVOT order_status__main.EFT;

---I think... It's been a long time since I played with Transform statements.
0
 

Author Comment

by:paymeister
ID: 14087539
Apologies for my tardiness. The "B" grade was not awarded in any criticism of the quality of the advice, but rather because it didn't address my chief problem. When time permits, I will cobble up an illustrative database for the gang to dissect. For now, I just did the deed within Access and then used SQL to query the last Access query in line. -Tim
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.
Suggested Courses
Course of the Month14 days, 17 hours left to enroll

840 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