Andrew,
I may not totally understand all of the specific requirements related to the input and output data sets, but here are some queries when used together will give you what I think you want. Further, if this were my problem to solve, I would write some logic to clean the data before the upload and then process the report. However you wanted a query.
Assuming:
1) the rows to be joined from the input dataset should only be joined if the ExpDate of the a record is one day prior to the EffDate of the following row and the part number and price are the same.
2) multiple contiguous rows they will be merged.
3) gaps will generate separate rows
4) there are no overlapping dates with the same part number and price

It turns out that the input data set can make this set of queries return interesting results. You should test very thoroughly.

There are three queries to be defined. And you will only need to run the last one (#3).
1) to find all of the rows that are contiguous, returning a set that is merged
2) to find all of the records not included in #1
3) a union to combine #1 and #2 above

The price data is contained in the table "Table1" and I used the same column names given above. In the query below you will see "Table1_1", I just included the same table twice in the query and MS Access gave it that name.

Query #2 (named "q2_non_contiguous")
SELECT Table1.PartNo, Table1.EffDate, Table1.ExpDate, Table1.Price
FROM Table1 LEFT JOIN q1_contiguous ON Table1.PartNo = q1_contiguous.PartNo
WHERE (((Table1.EffDate)<>[q1_contiguous].[effdate]) AND ((Table1.Price)<>[q1_contiguous].[price])) OR (((Table1.ExpDate)<>[q1_contiguous].[expdate]) AND ((Table1.Price)<>[q1_contiguous].[price])) OR (((Table1.EffDate)<[q1_contiguous].[effdate]) AND ((Table1.Price)=[q1_contiguous].[price])) OR (((Table1.ExpDate)>[q1_contiguous].[expdate]) AND ((Table1.Price)=[q1_contiguous].[price]))
ORDER BY Table1.PartNo, Table1.EffDate, Table1.ExpDate;

If you add the Distinct keyword to your query
Select Distinct PartNo, Price, Min(EffDate), Max(ExpDate) Order by PartNo, Price
you should get the result you're after though I'm puzzled by the replication. For each instance of a price you have two records and both have the same effective date and expiration date.
Did you just take a shortcut in creating your example or is this an accurate reflection of your source data?

The Min() and Max() functions are aggregate functions. They are intended to work on the set of values in a group of records (as defined by the GROUP BY clause). This will combine all the records that share the same part number and price, so that you can extract the date range.

However, the results will be misleading. You will be showing that a price applied over the entire date range, even though there was a period in the middle when a different price applied. Of course, that may not be important in your case, given that the examples you presented have multiple prices in effect simultaneously.

@Mike, The idea is to generate a price history. I need to know when the price changed and there are many occasions when the price stays the same as not all prices change at the same time. And the way we implement the price changes with a mass upload to JDE (and then a download to Access!) of all items, including those that do not change.

@Michael, the "blip" when the price changes and then returns to its previous value, is important and I need to capture those and it is those that are giving me the problem. (BTW, I did remember the GROUP BY in my code but not when I typed it in my note.) I am confused about your comments about having multiple prices at the same time. My dates are in dd/mm/yyyy format and I do not see any overlapping date periods. However, after I have done this for the base prices, I have to do the same for the contract prices and there can be overlapping date periods as the price can lower if the customers buys more at the same time.

Let me make sure I'm understanding.
You have a pricing table (history) and you need to generate a pricing history that takes into account overlapping periods and ignores overlapping periods where the price is constant? In other words you want a complete history of pricing variation showing the effective and expiration dates and the price for each item?

Some effort should be made on the front end to avoid creating new and overlapping pricing records, possibly by allowing the expiration date to be modified. Since you may not have any control there, I'll try to find a solution in whatever time I have available. I'm thinking this is going to take two (nested) queries.

You seem to have understood the problem. As for extending the date ranges when the price does not change, it is possible. However the people who do the mass uploading have only two tools to use: one that extends all the dates for a division and the other that creates new records for a division. A tool th

You seem to have understood the problem. As for extending the date ranges when the price does not change, it is possible. However the people who do the mass uploading have only two tools to use: one that extends all the dates for a division and the other that creates new records for a division. We do not have a tool that extends the dates if the price is the same and adds a new record if the price is different and, unfortunately, it is out of my control.

However, for reporting, they want to know the date the price changed and not when the last record was added.

Try creating a "temporary" table that has key fields of part no. and price. Then, run an append query from your main table to the temporary table . This should eliminate any duplicates in the temporary table. Then use the temporary table as the recordset of your report. Set your warnings to false before running the append query to avoid message boxes from appearing during the append query.

Copyright 1998-2015 Ziff Davis, LLC (Toolbox.com). All rights reserved. All product names are trademarks of their respective companies. Toolbox.com is not
affiliated with or endorsed by any company listed at this site.