Line by Line Transactional Data Queries

[defaultuser909]

Hi,

I am working with line by line transactional data. For example, for a single order, I have multiple rows (identified by an orderID column) with several columns of additional information. Specifically for my tasks, I will be dealing with the discount amount (number) and discount/promo code (text) columns. Thus, I have 3 objectives:

1. If there exists a discount amount in a row with a null or blank value in the adjacent discount/promo code column, insert a text value "Other Discount" to all rows within that same order (orderid). In other words, an order was placed with an unidentified discount code (text) provided.

1. Distribute a discount code (text) used in a purchase to all rows of the same order (with the same orderid). Currently, an order with 3 purchased items only includes the discount code on one of the rows. I need this text value replicated in each of the 3 line items or rows. (this is similar to my first task above)

2. Distribute the discount amount (number), which also only included in only 1 row for a multi-item rode, evenly across all ordered items so that when I aggregate all orders by OrderId, I will have a "net order revenue" number. For example, if a person orders 3 different items at $25 each, receives a $15 discount, I need each row to be reduced to $20.

This is my very first post on here so I hope I was clear in my explanations. Thank you in advance for your help!

[jzwp11]

Welcome to the forum!

It sounds like the discount is applicable to the entire order not to a line item of the order. If that is the case, the discount field should be in your order table not in the transaction table. To do the discount calculation whether it is in a subform or a query, you would use the DLookup() function to pull in the appropriate discount amount. What determines which order gets a discount or not?

BTW, you cannot do mathematical calculations using a text value, so I assume that you are converting the discount code (text) to a numerical value at some point.

[defaultuser909]

jzp,

Thanks!

I do actually have the discount code (text) in my "orders" table as well. However, I am providing this data to a 3rd party service provider so the data format I am trying to get to is a bit different than usual. (line by line transaction data)

However, I think you are on to something! If I had that second table available with 3 columns:
* OrderID (primary key)
* Discount Amount (number)
* Discount Code (text)

How would the dlookup expression calculate the amount across several line items in the transactions table? And the same expression to merely place that text value in all the rows for that order?

To answer your final question, an order with an amount (or value > 0) in the "Discount Amount" column within my tables constitutes a discount. Also, a Discount Code SOMETIMES identifies a discount (but not always since some of the Discount Code values are null or blank

[jzwp11]

Actually, you might need a DLookup at all since the order and transaction tables should be related, just create a query with both tables. Select the fields from the transaction table and the [Discount Amount] and [Discount Code] fields from the order table. You can then add any calculated fields you want to do the math part.

A typical query might look something like this (the table/field names are a little different but the principal is the same)

SELECT tblOrders.pkOrderID, tblOrders.[Discount Amount], tblOrders.[Discount Code], tblOrderDetail.fkItemID, tblOrderDetail.currCost, tblOrderDetail.QTY
FROM tblOrders INNER JOIN tblOrderDetail ON tblOrders.pkOrderID = tblOrderDetail.fkOrderID;

[defaultuser909]

JZWP,

This is awesome foundational help. I believe I can apply this to my tasks. I'll let you know how it goes regardless later tonight since I'll be working on this between now and 10pm EST.

Thanks again!

[jzwp11]

Please, keep me posted on your progress. I can't guarantee that I will be on-line tonight, but I should be able to take a look in the morning.

[defaultuser909]

JZP,

Thanks to your direction I got things taken care of! I actually don't need to distribute the discounts but I was able to include the discount code (text) in every line item for an order that has a discount.

Thank you very much for your willingness to follow up. From across the internet wires, I really appreciate it

[jzwp11]

You're welcome. Good luck with your project.