Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2009
    Posts
    2

    Help with a query

    I am facing a problem with how to create a query so that I have all the data within one data source and I can create a report easily. I am using 3 main tables - orders, credit and party.


    The structure of orders table is
    party_cd
    order_id
    order_date
    qty
    rate
    amount

    And another table "credit" has the structure
    party_cd
    credit_it
    credit_date
    credit_no
    credit_amount

    And the main table "party" has the structure
    party_cd
    party_name
    address

    Now, I wanted to make a report which shows the details between specified period of date and a particular customer with the page header of party code's address as follows:

    ------------------------------------------------------------------------------------------------------------------------------------------
    Order Id. Order No. Order_Date Qty Rate Order_Amount Credit Id. Credit No. Credit Date Credit_Amount
    ------------------------------------------------------------------------------------------------------------------------------------------

    The Credit Dates and Order Dates may vary and I want to select all the records of a particular party_code.

    So the query should have the data of
    order_id
    order_no
    order_date
    qty
    rate
    amount
    credit_id
    credit_no
    credit_date
    credit_amount

    Now, if I am using left outer join the records are coming in a single row but for each party code of credit table it is repeating all the orders records. I will show you the query which I had tried and do correct me or else give me a perfect soultion.

    SELECT DISTINCT orders.*, credit.*
    FROM orders LEFT JOIN credit ON orders.party_cd=credit.party_cd
    WHERE orders.party_cd in (select party_cd from party where party_type="Retail")
    And orders.party_cd=[Party Code] And orders.party_cd = credit.party_cd and orders.order_date Between [From Date] And [To Date]
    and credit.credit_date between [From Date] and [To Date];


    Please guide me in which way I should proceed. If any other information is required from my end, please let me know.

    Thanks in advance.

    Kiran.

  2. #2
    Join Date
    Feb 2009
    Location
    Cairo
    Posts
    7
    You should read about parameter and select queries , nobody will do ur work.

    Examples that may help:

    In the criteria of date in the query type this

    Between [enter start date] AND [enter end date]. Now when the user run the report a screen pops up telling him to enter the date range.

    You can also use combo boxes to feed parameters from forms to query criteria.

  3. #3
    Join Date
    Feb 2009
    Posts
    2

    Help with a query

    Hi Mr. finnegan bell,

    Thanks for your reply. I just wanted to correct my query, where I am failing, if this task can be done through strictly a query. If not I wanted to enquire how I can incorporate 2 detail sections in a report so that two different lists can be generated in the same report with 2 detail sections.

    Regards
    Kiran.

  4. #4
    Join Date
    Feb 2009
    Location
    Cairo
    Posts
    7
    Quote Originally Posted by kirang001 View Post
    Hi Mr. finnegan bell,

    Thanks for your reply. I just wanted to correct my query, where I am failing, if this task can be done through strictly a query. If not I wanted to enquire how I can incorporate 2 detail sections in a report so that two different lists can be generated in the same report with 2 detail sections.

    Regards
    Kiran.
    A report can include many fields from multiple queries/tables , add them manually.

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums