Help with a query

[kirang001]

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.

[finnegan bell]

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.

[kirang001]

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.

[finnegan bell]

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.