Complex for me but may be a very simple query for you experts.

[knavlekar]

I have four tables. I have not included all the fields and all the tables
but the fields that I thought that are important for the query.

1) Orders:
Order ID (P.K)
Customer ID (F.K)
Order Date
Ship Address
Ship Date

2) Order Details:
Order Detail ID (PK)
Order ID (FK)
Product ID (FK)
Qty .
Unit Price

3) Payments:
Payment ID (PK)
Order ID (FK)
Payment Amount
Payment Date

4) Customers:
Customer ID (PK)
Company Name


I want to create a query which returns
Company Name, Product ID, Order ID, Ship Date, Payment ID, Payment Date,
Payment Amount
(Note: For some Order ID there are multiple records because in a single
order we sale more than one products)

At present when I create a query it returns duplicate values for Payment ID,
Payment Date, and Payment Amount. I want unique results for the fields from
payment tables.

I understand why it happens but I can not solve it.
Reason according to me:

Every Order ID in Payments table is present in Orders table.
But not every Order ID in Orders table is present in Payments table as there
may be some orders for which the payments are still pending.

In short
I want all the entries for order tables and unique values for payment table
and ultimately I want to create a report.

I hope I have explained the problem.
Thanks for the help in advance.

-Kedar.

 

[Allen Browne]

You have set this up so that:
- one customer can have many orders,
- one order can have many line item details,
- one order can have mulitple payments.

That's fine, but there is no direct relation between the order details and
the payments. There is therefore no way to list all the line items of the
orders, and include the payment details as well, without repeating the
payment information.

You can do it if you just show the order total, with the payment total
alongside. To create this query:
1. Create a query using Orders and Order Details.

2. Depress the Total button on the toolbar.
Access adds a Total row to the query design grid.

3. In the Total row under OrderID, accept Group By.

4. Type this expression into the Field row:
Amount: [Qty] * [Unit Price]
and in the Total row under this, choose Sum.

5. Save the query with a name such as qryOrderAmount.

6. Create a new query, using qryOrderAmount as an input table, as well as
your Payments table.

7. In the upper pane of table design, double click the line joining the 2
tables. Access offers a dialog with 3 options. Choose the one that says:
All records from qryOrderAmount, and any matches from Payments.

8. Depress the Total button.

9. Drag OrderID from qryOrderAmount into the grid.
Accept Group By under this field.

10. Drag Amount from qryOrderAmount into the grid.
Choose First under this field.

11. Drag PaymentAmount into the grid.
Choose Sum under this field.

The query now gives the order total, and the correct payment received per
order.

If you wanted to do this in a report, you could use a subreport for the
payments. Put this subreport into the OrderID footer, and it will show once
only for the order.

 

[knavlekar]

thanks for the help. still some doubt. can i send current report format (and
explain my further requirement for the report) and relationships of table to
you on allenbrowne at mvps dot org. so that you have better idea about what
exactly i want in my report.

i want to create a kind of statement to my customers with each purchase
details as well as payments details in a single report.

thanks.