Hi,
I need to find out how many deliveries it took my company to complete an order. Each delivery has a different invoice number, but the customers order number remains the same. So, as per example 1 below, there are 2 invoice numbers against the 1 order number so it took 2 deliveries to complete the order.
Example 1
<tbody>
</tbody>
As you can see the order number and invoice numbers are repeated, this is because they both go down to the line item detail on the order and invoice as per example 2 below. i.e. these were the items ordered and supplied.
<tbody>
</tbody>
It does not matter for the purpose of my report what items were delivered and I have only shown it in example 2 so you can see why I have so many repeated order and invoice numbers.
So, I need to know how to look at the order number, see how many different invoice numbers are against that order number to calculate how many deliveries that equates to as per example 1 above. I don't know if this will make a difference to any formula but some of the orders have only 1 invoice so they were completed in 1 delivery.
I hope this makes sense and that someone out there can help. I can provide more data if required.
Thank you in antisipation.
Steve
I need to find out how many deliveries it took my company to complete an order. Each delivery has a different invoice number, but the customers order number remains the same. So, as per example 1 below, there are 2 invoice numbers against the 1 order number so it took 2 deliveries to complete the order.
Example 1
Order Number | Invoice Number |
00/001027/ | 365077 |
00/001027/ | 365077 |
00/001027/ | 365077 |
00/001027/ | 365149 |
00/001027/ | 365149 |
<tbody>
</tbody>
As you can see the order number and invoice numbers are repeated, this is because they both go down to the line item detail on the order and invoice as per example 2 below. i.e. these were the items ordered and supplied.
Example 2 00/001027/ | 365077 | socket |
00/001027/ | 365077 | plug |
00/001027/ | 365077 | light |
00/001027/ | 365149 | cable |
00/001027/ | 365149 | mcb |
<tbody>
</tbody>
It does not matter for the purpose of my report what items were delivered and I have only shown it in example 2 so you can see why I have so many repeated order and invoice numbers.
So, I need to know how to look at the order number, see how many different invoice numbers are against that order number to calculate how many deliveries that equates to as per example 1 above. I don't know if this will make a difference to any formula but some of the orders have only 1 invoice so they were completed in 1 delivery.
I hope this makes sense and that someone out there can help. I can provide more data if required.
Thank you in antisipation.
Steve