Thread: Nested queries in SQL
07-07-09, 04:44 #1Registered User
- Join Date
- Mar 2009
Unanswered: Nested queries in SQL
Being completely new to writing sql in Access - is it possible to "nested" queries in sql?
Here is an example of what I want to do:
I have a table with customer numbers and the number of items purchased by these customers. There is more than one entry against each customer number.
I have a second table with my list of customer numbers processed to date.
I have a third table which lists all the customer numbers which have not yet been processed.
Every time i get new data in I want to check whether the customer numbers have already been processed and if not I want to make an entry of the numbers and the "total number of purchases" in the third table.
What I did in access originally is the following:
I wrote one query which checks whether there is an entry of the customer numbers of the first table in the second table. And sums the number of purchases against each customer number.
Then I wrote a second query and appended the result of the first query to the third table.
Now I am trying to put all this into a long bit of VB which I have attached to a button on a form. Effectively it is for our processing staff to run. I know I can just run the queries I have written them but I am wondering whether I can just make do without the queries and just put it all into vb as embedded sql? As far as I can see I can not do this in one step. There is my problem....
07-07-09, 06:13 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
presumably all that separates customers with orders processed from orders that are not processed is a simple flag? so they could be in the same table
it also sounds like you are storing derived data.. you can count the number of items ordered by a customer ont he fly using the count() verb/predicate
it sounds lke there may be a non normalised db out there.....
which in tuirn tends to make the SQL uneccessarily complicated and in some cases impossible to do
have a look at the join clause as this is the preferred SQL of retrieving data from more than one table in one queryI'd rather be riding on the Tiger 800 or the Norton