Query help

[lookieuk]

Hello Everyone,
I am looking for assistance with a query that involves multiple functions.
To begin with, I need to filter the data to show only the items dated between 2022 and 2025. After that, I would like to determine if there is a preferred supplier. If there isn't, I need to find the highest price among the remaining item codes.

[lookieuk]

Hello Everyone,


I am looking for assistance with a query that involves multiple functions.


To begin with, I need to filter the data to show only the items dated between 2022 and 2025. After that, I would like to determine if there is a preferred supplier. If there isn't, I need to find the highest price among the remaining item codes.


For the item code (AV025051) with the highest price, I would like to perform an inflation calculation set at 2% for future purchases. If a preferred supplier exists, I will apply the inflation calculation to that cost instead.


Furthermore, for items outside the specified date range, I would like to create a new table to include them for the next phase of the query. In this table, I want to select the most recent cost based on the negotiated date and perform inflation calculations for each year, starting from 2016 (3.5%), 2017 (2.2%), and continuing until we reach the future date of 2026 for upcoming purchases.


Please see the snippet below for the relevant data.


I hope this clarifies my request. Thank you!

[lookieuk]

Snip attached ( data)

[lookieuk]

SQL Code at the moment

SELECT Jobs.JobCode AS MachineNum, Jobs.Description AS MachineDesc, SalesOrderLines.WorkCentreCode AS WCCode, JobWorkCentres.Description AS WCDesc, Items.ItemCode, Items.Description AS PartDesc, Items.Details AS PartDetails, Items.ManufacturePartNo, Format([WipFlag],"True/False") AS WIP, SalesOrderLines.QuantityOrdered AS Qty, Items.Unit, Items.ManufactureDetails, CatalogueLines.PreferredSupplier, CatalogueLines.Unit, CatalogueLines.NegotiatedDate, CatalogueLines.CostRate
FROM (((Jobs INNER JOIN SalesOrderLines ON Jobs.JobCode = SalesOrderLines.JobCode) INNER JOIN Items ON SalesOrderLines.LineCode = Items.ItemCode) INNER JOIN JobWorkCentres ON (SalesOrderLines.JobCode = JobWorkCentres.JobCode) AND (SalesOrderLines.WorkCentreCode = JobWorkCentres.WorkCentreCode)) INNER JOIN CatalogueLines ON Items.ItemID = CatalogueLines.ItemID
WHERE (((Jobs.JobCode)="BM00400000"))
ORDER BY Jobs.JobCode, SalesOrderLines.WorkCentreCode, Items.ItemCode, CatalogueLines.NegotiatedDate;

[June7]

Provide sample data as well as desired output as formatted text tables (see the GoAdvanced post editor menu for table construction tools or copy/paste Access table or Excel cells), not image, or attach file (Excel or Access).

Can extract year part from date value with Year(NegotiatedDate) and use that in filter criteria.

Most likely your other filter criteria will involve nested subqueries. As an example for retrieving the "most recent" something for each group, review http://allenbrowne.com/subquery-01.html#TopN

[lookieuk]

Data.zip

with the tables format please keep the same has the format in the data sample please

[madpiet]

Which columns belong to what entity?

There are
Items or Products or something (best guess: ItemCode, PartDescription, PartDetails, Item_Unit)
Codes (WCCode, WCDescription)

and then there are contracts or something? (Isn't that where "Preferred Supplier" comes from? Or is that just a property of an Item?) But then what of the NegotiatedDate and CostRate columns?

Sounds like to me that "keeping the columns in the same format" is a huge mistake. That's not what relational databases are all about. =)

[June7]

You posted results of query? What output do you expect from this dataset?

For a start, filtering for year range is simply: Year(NegotiatedDate) BETWEEN 2022 AND 2025

Apply inflation calc only if product cost is preferred supplier?

Calculating inflation % based on year might need a custom VBA function.