If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Complex for me but may be a very simple query for you experts.
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. |
#2
|
|||
|
|||
Complex for me but may be a very simple query for you experts.
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "knavlekar" wrote in message news 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. |
#3
|
|||
|
|||
Complex for me but may be a very simple query for you experts.
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. "knavlekar" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|