Is the Date/Time Criteria Right?

[prabha_friend]

I have one more query too

 

[plog]

I would do this with a subquery because I do not trust the LAST() function. However, you may have gotten right.

I would make a test environment and throw all the test cases I could at it.

 

[prabha_friend]

SELECT Bills.Bill_ID, Sales.Sales_Item, Sales.Sales_Item_Unit, Last(Sales.Sales_Price) AS LastOfSales_Price
FROM Bills INNER JOIN Sales ON Bills.Bill_ID = Sales.Bill
GROUP BY Bills.Bill_ID, Sales.Sales_Item, Sales.Sales_Item_Unit
HAVING (((Bills.Bill_ID)=[Forms]![Bills]![SalesONBills].[Form]![Bill]) AND ((Sales.Sales_Item)=[Forms]![Bills]![SalesONBills].[Form]![Sales_Item]) AND ((Sales.Sales_Item_Unit)=[Forms]![Bills]![SalesONBills].[Form]![Sales_Item_Unit]));

I think the above code is enough. Please share your thoughts. Thank You.

 

[CJ_London]

Please share your thoughts.

if it works it works - if it does what you want, great. Since it is not clear what it is required to do, can't really comment further - I don't understand what this means - or at least don't understand how or why it might be different

If there is a repetition of order items in the same order then we have to sell on the same price the item entered the bill for the first time (Consistent item price across the bill for repeated items)

 

[Pat Hartman]

If Last() is working, it is a pure accident. Do not expect this function to work once your tables expand in the production environment. You can create a query and sort the data descending. Then you can use Top 1 to retrieve the "last" record. Otherwise, you will need to use the Max() function to get the ID of the most recent record for a particular item and then find the price for that Max() record.

You have been warned.