Find First and Last in Sequential Numbers (1 Viewer)

tl mike · Feb 4, 2016

I am in need of some help. I tried looking for coding that will evaluate a query and show the First and Last Numbers based on the data in a sequential order but was not able to find anything.

For example I have created a query with car models and parts that fit those models listed as:
Manufacturer,Model,Year,Part Number,
Honda,Civic,2000,P-1,
Honda,Civic,2001,P-1,
Honda,Civic,2002,P-1,
Honda,Civic,2003,P-2,
Honda,Civic,2004,P-2,
Ford,Ranger,2000,P-4,
Ford,Ranger,2001,P-4,
Ford,Ranger,2002,P-5,
Ford,Ranger,2003,P-4,

And need it to show like:
Manufacturer,Model,Part Number,Beginning Year,End Year
Honda,Civic,P-1,2000,2002
Honda,Civic,P-2,2003,2004
Ford,Ranger,P-4,2000,2001
Ford,Ranger,P-5,2002,2002
Ford,Ranger,P-4,2003,2003

Thanks in advance!!!

plog · Feb 4, 2016

No VBA coding needed. You can do this with a simple aggregate query:

https://www.599cd.com/tips/access/aggregate-query/

You would bring in all the fields you want to show and choose the appropriate totals value.

Hint: Group By for all the values striaght from the underlying table/query and Max/Min for those fields you want the largest/smallest of. Give it a shot and post back here any issues you have.

tl mike · Feb 4, 2016

Hi Plog,

Thanks for the reply unfortunately I thought the same thing however there are the anomalies where either the model is not produced for a year or for a year it uses something different and then reverts back to the original part.

For the example I gave the anomaly that I is with the Ford,Ranger,2002,P-5,
If I use the Min Max aggregate query it will show:
Ford,Ranger,P-4,2000,2003
Ford,Ranger,P-5,2002,2002

When it should be:
Ford,Ranger,P-4,2000,2001
Ford,Ranger,P-5,2002,2002
Ford,Ranger,P-4,2003,2003

Manufacturer,Model,Year,Part Number,
Honda,Civic,2000,P-1,
Honda,Civic,2001,P-1,
Honda,Civic,2002,P-1,
Honda,Civic,2003,P-2,
Honda,Civic,2004,P-2,
Ford,Ranger,2000,P-4,
Ford,Ranger,2001,P-4,
Ford,Ranger,2002,P-5,
Ford,Ranger,2003,P-4,

And need it to show like:
Manufacturer,Model,Part Number,Beginning Year,End Year
Honda,Civic,P-1,2000,2002
Honda,Civic,P-2,2003,2004
Ford,Ranger,P-4,2000,2001
Ford,Ranger,P-5,2002,2002
Ford,Ranger,P-4,2003,2003

sneuberg · Feb 4, 2016

I think plog's suggestion would work except I believe this sequence

Ford,Ranger,P-4,2000,2001
Ford,Ranger,P-5,2002,2002
Ford,Ranger,P-4,2003,2003

Would come out

Ford,Ranger,P-4,2000,2003
Ford,Ranger,P-5,2002,2002

Let's see first if anyone can come up with a query, but I bet this is going required VBA and a temporary table. If it comes to that, I'd be willing to write the code for this if you want. It's actually fairly simple. You just loop through a sorted query and form the output the way you would on paper.

sneuberg · Feb 4, 2016

While I was editing my response you had already responding. I hate it when that happen. Makes me look stupid.

tl mike · Feb 4, 2016

Hahaha, no worries that is the down fall to forums and discussion boards you cannot see if anyone is in the middle of replying or not.

sneuberg · Feb 6, 2016

It doesn't look like anyone is going to come up with a query. Do you want me to write some code that will do this? If so please let me know and give me the actually table and field names of the Manufacturer,Model,Year, and Part Number or better yet upload a copy of the database with at least the relevant table(s).

tl mike · Feb 9, 2016

sneuberg,

Thanks for the offer I will get something together and post it here shortly.

tl mike · Feb 9, 2016

Attached is a very basic sample of the database.

There are what I would call "2" sections to the database.
1 section is for the Model information I.E. Make, Model and Year
2nd section is for the Product. Which I have a "CatalogProduct" which is a simplified Part Number that does not have the attributes the different Part Numbers would have like color.

For instance the P-1_ Catalog Product is a simplified Part Number fore the series P-1A, P-1B and P-1C which each Part Number the only difference is color.

I am able to read code and make simple changes but have very little ability to write it so any help will be greatly appreciated.

If you design it like you previously mentioned about having the data go into a table I can loop the data back through a query and base on the Primary Keys to pick up other relevant information if needed like the part description.

Thanks again for the offer.

sneuberg · Feb 9, 2016

I'll get you something as soon as I can.

sneuberg · Feb 9, 2016

I believe your requirement is to sort the data where the input data looks like:

Honda,Civic,2000,P-1,
Honda,Civic,2001,P-1,
Honda,Civic,2002,P-1,
Honda,Civic,2003,P-2,
Honda,Civic,2004,P-2,
Ford,Ranger,2000,P-4,
Ford,Ranger,2001,P-4,

In the tables you provided I see the tblModel would give me the Model and Year but I see no way of connecting this data to the manufacturer. One would think the tblModelManfactures would do that but that just has the Manfacturer's names. Also if I join the tblModel to the tblCatalogModel I don't get a CatalogProduct (P-1, P-2, etc) for all of the models making me wonder if that relationship is the one I'm looking for.

I can write the code in a generic fashion and let you adapt it to your situation, but it might be easier for you if I know the environment in which this is to be used. If you can provide me the tables and/or queries that will give me the input for this code, I can provide you with code that's ready to go.

Cronk · Feb 10, 2016

I'd say the database should be properly normalized before writing code to get around normalization deficiencies.

sneuberg · Feb 10, 2016

Cronk said:
I'd say the database should be properly normalized before writing code to get around normalization deficiencies.

@Cronk I'm not see anything that's not in normal form. What are you seeing? How would you change the structure?

sneuberg · Feb 10, 2016

I've attached my first stab at this code. In the database your will find a form that will allow you input data to test this code. I will provide instructions on how to adapt this to your system later. I'm hoping you can provide me with the additional information requested in my previous post. That would make it easier for me to create instructions.

plog · Feb 10, 2016

I played around with what you initially posted and I as was able to achieve what you want via SQL. You didn't provide your query's name that is the source of your data, so I used YourQueryNameHere for that. Replace all instances in the following code with the name of your actual query.

It's going to take 3 query objects to achieve (and those even have subqueries). Here's the first:

Code:

SELECT Sub1.Manufacturer, Sub1.Model, Sub1.[Part Number] AS PartNum, Sub1.Year AS PartYear, (SELECT MAX([Year]) FROM YourQueryNameHere WHERE Sub1.Manufacturer=YourQueryNameHere.Manufacturer AND Sub1.Model=YourQueryNameHere.Model AND Sub1.[Part Number]=YourQueryNameHere.[Part Number] AND (Sub1.[Year]-1)=YourQueryNameHere.[Year]) AS LastYear, (SELECT MAX(YourQueryNameHere.[Year]) FROM YourQueryNameHere WHERE Sub1.Manufacturer=YourQueryNameHere.Manufacturer AND Sub1.Model=YourQueryNameHere.Model AND Sub1.[Part Number]=YourQueryNameHere.[Part Number] AND (Sub1.[Year]+1)=YourQueryNameHere.[Year]) AS NextYear, (SELECT COUNT(YourQueryNameHere.[Year]) FROM YourQueryNameHere WHERE Sub1.Manufacturer=YourQueryNameHere.Manufacturer AND Sub1.Model=YourQueryNameHere.Model AND Sub1.[Part Number]=YourQueryNameHere.[Part Number] AND Sub1.[Year]>=YourQueryNameHere.[Year]) AS GroupNum
FROM YourQueryNameHere AS Sub1;

Paste the above code in, replace YourQueryNameHere appropriately and name it 'PartRanges_sub1'. It takes your query's data, makes better names out of your fields (you shouldn't use spaces [Part Number] or reserved words [Year] for names). It also looks to see if each record has a preceding/following record for the Year field and gets those values.

Code:

SELECT Sub2.Manufacturer, Sub2.Model, Sub2.PartNum, Sub2.PartYear, (SELECT Max(PartYear) FROM PartRanges_sub1 WHERE Sub2.Manufacturer=PartRanges_sub1.Manufacturer AND Sub2.Model=PartRanges_sub1.Model AND Sub2.PartNum=PartRanges_sub1.PartNum AND PartRanges_sub1.LastYear IS Null AND Sub2.PartYear>=PartRanges_sub1.PartYear) AS LowerYear, (SELECT Min(PartYear) FROM PartRanges_sub1 WHERE Sub2.Manufacturer=PartRanges_sub1.Manufacturer AND Sub2.Model=PartRanges_sub1.Model AND Sub2.PartNum=PartRanges_sub1.PartNum AND PartRanges_sub1.NextYear IS Null AND Sub2.PartYear<=PartRanges_sub1.PartYear) AS UpperYear
FROM PartRanges_sub1 AS Sub2;

Copy the above code in and name it 'PartRanges_sub2'. It helps group all consecutive records together. Then to get the data you want use this:

Code:

 SELECT PartRanges_sub2.Manufacturer, PartRanges_sub2.Model, PartRanges_sub2.PartNum, PartRanges_sub2.LowerYear, PartRanges_sub2.UpperYear
FROM PartRanges_sub2
GROUP BY PartRanges_sub2.Manufacturer, PartRanges_sub2.Model, PartRanges_sub2.PartNum, PartRanges_sub2.LowerYear, PartRanges_sub2.UpperYear;

sneuberg · Feb 10, 2016

@plog I got the first of your queries working (see attach), but the second is giving me an "Invalid argument to function error". Any idea of why I would be getting that?

plog · Feb 10, 2016

Looks like your Year field values are text, they need to be numbers.

sneuberg · Feb 10, 2016

I'm getting the same error with the Years as numbers.

sneuberg · Feb 10, 2016

I've attached a revised version of my solution. My previous version had the Part Number and Year interchanged in the output table.

This also includes plog's queries which I have not been able to get working.
@plog Would you take a look please?

plog · Feb 10, 2016

Using sneubergs database, change PartRanges_sub1 to this:

Code:

SELECT Sub1.Manufacturer, Sub1.Model, Sub1.[Part Number] AS PartNum, Sub1.[Model Year] AS PartYear, (SELECT MAX([Model Year]) FROM [Input Table] WHERE Sub1.Manufacturer=[Input Table].Manufacturer AND Sub1.Model=[Input Table].Model AND Sub1.[Part Number]=[Input Table].[Part Number] AND (Sub1.[Model Year]-1)=[Input Table].[Model Year]) AS LastYear, (SELECT MAX([Input Table].[Model Year]) FROM [Input Table] WHERE Sub1.Manufacturer=[Input Table].Manufacturer AND Sub1.Model=[Input Table].Model AND Sub1.[Part Number]=[Input Table].[Part Number] AND (Sub1.[Model Year]+1)=[Input Table].[Model Year]) AS NextYear, (SELECT COUNT([Input Table].[Model Year]) FROM [Input Table] WHERE Sub1.Manufacturer=[Input Table].Manufacturer AND Sub1.Model=[Input Table].Model AND Sub1.[Part Number]=[Input Table].[Part Number] AND Sub1.[Model Year]>=[Input Table].[Model Year]) AS GroupNum
FROM [Input Table] AS Sub1;

I got the same error initially, but then I changed the above query to use the Input Table instead of the query.

Find First and Last in Sequential Numbers (1 Viewer)

Registered User.

Banishment Pending

Registered User.

AWF VIP

AWF VIP

Registered User.

AWF VIP

Registered User.

Registered User.

Attachments

AWF VIP

AWF VIP

Registered User.

AWF VIP

AWF VIP

Attachments

Banishment Pending

AWF VIP

Attachments

Banishment Pending

AWF VIP

AWF VIP

Attachments

Banishment Pending

Similar threads

Users who are viewing this thread