Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009
    Posts
    37

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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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 query
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •