Page 1 of 2 12 LastLast
Results 1 to 15 of 21

Thread: Query Problem

  1. #1
    Join Date
    Aug 2003
    Posts
    160

    Unanswered: Query Problem

    Hi! I got one data base with 5 tables lets say db1.

    I got one more database called history database db2. Once the order in db1 is completed it will automatically send that order information into db2 and the order is deleted from the db1. In db2 I created the same table structure as db1.

    Here the problem is when I want to get a some customer information I need to query both the databases. I am not getting any Ideas how to do that.

    I linked the db2 tables to db1 for using append and delete queries for sending to db2 and for deleting from db1.

    Any help!

    Thanx In Advance.
    Vad

  2. #2
    Join Date
    Dec 2003
    Posts
    454
    The order information is deleted from db1 after the order is completed. How can you get the information from db1 again? Sorry I may not understand your question.

  3. #3
    Join Date
    Aug 2003
    Posts
    160
    What If the order was not completed. I want all the customer order information wether it is completed or not.

    IF it is completed it is in db2.If not it is in db1.

    Need some more information please ask me.

    thanks


    Originally posted by gyuan
    The order information is deleted from db1 after the order is completed. How can you get the information from db1 again? Sorry I may not understand your question.
    Vad

  4. #4
    Join Date
    Dec 2003
    Posts
    454
    In this case, you just list all order information from db1 and db2. it should not have duplicates there since the orders in db2 are completed.

  5. #5
    Join Date
    Dec 2003
    Posts
    454
    If you want to get a specific customer's information which you may not know if the order is completed, you can use a stored procedure SP1 running on the server. If the order information is not found in db2, you can execute another SP2 in SP1 to get the order from db1.

  6. #6
    Join Date
    Aug 2003
    Posts
    160
    I Didm't get u what u r trying to say.

    I created one query using db1 information and one with db2.
    Then I tried to make another query.This is where I got struck.

    Is this the one you are suggesting.

    making only one query to list all order information in db1 and db2. With this how can set a parameter value inorder to get specified customer information. Like order information based on customer lastname.

    Thanx

    Originally posted by gyuan
    In this case, you just list all order information from db1 and db2. it should not have duplicates there since the orders in db2 are completed.
    Vad

  7. #7
    Join Date
    Aug 2003
    Posts
    160
    Basically I am not good at coding in VBA. Could suggest me how to use that stored procedure.
    Can I get it in anyother way other than using stored procedure.

    Thanx

    Originally posted by vad
    I Didm't get u what u r trying to say.

    I created one query using db1 information and one with db2.
    Then I tried to make another query.This is where I got struck.

    Is this the one you are suggesting.

    making only one query to list all order information in db1 and db2. With this how can set a parameter value inorder to get specified customer information. Like order information based on customer lastname.

    Thanx
    Vad

  8. #8
    Join Date
    Dec 2003
    Posts
    454
    You just need one stored procedure on db2.

    CREATE PROCEDURE [dbo].GetOrderInfo
    -- input parameters
    AS

    SET NOCOUNT ON
    DECLARE @count int

    SELECT @count = Count(*) FROM Orders WHERE ......
    -- found in db2
    If @count >= 0 BEGIN
    SELECT * FROM Orders WHERE ......
    END
    -- not found in db2, go to db1
    ELSE BEGIN
    SELECT * FROM [db1].[dbo].Orders WHERE ......
    END
    SET NOCOUNT OFF

  9. #9
    Join Date
    Dec 2003
    Posts
    454
    Do you use Access or SQL server? The SP works on SQL server. If you are using Access, please forget the stored procedure. you need to have two queries.
    Last edited by gyuan; 01-15-04 at 15:33.

  10. #10
    Join Date
    Aug 2003
    Posts
    160
    I am using ms access. Could u suggest me how to get the result using two queries.

    I created one query using db1 information and one with db2.
    Then I tried to make another query. Because the table structure in both databases are same. In the query design grid it is showing the lastname two times. There I was bit confused how to set the parameter value.

    Could u please suggest me how to get rid of this problem.

    Thanks

    Originally posted by gyuan
    Do you use Access or SQL server? The SP works on SQL server. If you are using Access, please forget the stored procedure. you need to have two queries.
    Vad

  11. #11
    Join Date
    Dec 2003
    Posts
    454
    Do you want to get the single order information or multiply orders?

  12. #12
    Join Date
    Aug 2003
    Posts
    160
    Here is my database structure:

    CustomersTable...1--Many..OrdersTable

    OrdersTable...1---many...Orderdetailstable

    From this I can say if we get one customer information he is going to have multiple orders.

    Need more information!

    Thanks




    Originally posted by gyuan
    Do you want to get the single order information or multiply orders?
    Vad

  13. #13
    Join Date
    Dec 2003
    Posts
    454
    Do you want to get the orders' information under the specific costomer?

  14. #14
    Join Date
    Aug 2003
    Posts
    160
    That is what i need exactly.

    Thanks

    Originally posted by gyuan
    Do you want to get the orders' information under the specific costomer?
    Vad

  15. #15
    Join Date
    Dec 2003
    Posts
    454
    When you execute the query what is the input? i.e., customer name or others?

Posting Permissions

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