Is this a Querie or Sorting problem? (1 Viewer)

[bwrobel]

Is this a Query or Sorting problem?

Ok, new to this relm. First off I'm using Access 2002 working in a retooling company, writing a tool tracking DB. What I have is :

Liner ID # Inspected Date Dia 1 Dia 2 Dia 3 Dia 4 OD 1 ID 1


I have only 15 liners now but will add more in future, that we use and inspected and enter measuring data. I have tolerances in place, when the liner gets out of tolerance it's flagged and replaced. So when I put in measurments I have multi-number of liners in my records.

Question:

Is there a way to do a query to only show only the lastest or most currert by [inspected date], liners with all the measurements?

If there is a way.... Can there be a drop down box for the other dates bringing the other data with it?


Any help with my quest for knowledge

 

[Banana]

I'm thinking this is a design issue, not a query issue.

Your tables should not have repeating data, which it seems to have because you have dia1, dia2, etc.

Read up on normalization.

 

[Mile-O]

Liner ID # Inspected Date Dia 1 Dia 2 Dia 3 Dia 4 OD 1 ID 1

When you have a list appearing across the top (i.e. Dia1, Dia2, Dia--->) then this is a sure sign that you need a new table in your database. A database should grow down and not across.

I have only 15 liners now but will add more in future

This is a very bad design practice. As Banana has said, do some reading up on Normalization (First Normal Form to Third Normal Form, at least). But, before you do that, consider the following example:

Say you want to query, then you will have the problem of looking for data in fifteen columns (it's almost hard coding). Then you will have fifteen textboxes in your form. Your report will have the same. And any code will be hard coding. When it's the future and you want to expand on your fifteen liners then you will have to edit the table to add them, edit the query to select them, edit the forms and reports to accommodate them, and any modules to reference them.

Ideally what you want is the liners in their own table that you can refer to in this inspection table. Then you can query on the one column. There will be no need to set limits of fifteen or whatever.

To be honest, I'm not the clearest on what your database does but it's certainly a design issue that is preventing you from getting further with it. If you are far gone I would advise you not to be stubborn and continue with it knowing that it will require extra hours maintenance every time you want to make a change. You should design it properly - you'll get all the help you need here - and you will not have to come back to make edits to it in future as you will have a robust design that does all the work for you. That's what you want.

 

[bwrobel]

Quote:
When you have a list appearing across the top (i.e. Dia1, Dia2, Dia--->) then this is a sure sign that you need a new table in your database. A database should grow down and not across.

Those are my set fields, and they grow down. My Data input (inspect date,dia 1 ...)

I do have a Liner ID # Table all by itself that I use as a drop down box.

I guess my only question is there a way in query to pull all single [Liner ID #] records by the most recent date

I know can sort by date, I just want the top record.

I could send my db to anyone that would like to analyze, but it is 10mb

 

[Banana]

I didn't understand your comments.

But to answer the question of getting only one record, you would use SELECT TOP 1 instead of SELECT.

But really, I'd be more concerned with your table design than the query.

 

[neileg]

I concurr with the comments about your design, but it may be we are misunderstanding.

Anyway, you need a different type of query. A Totals query, will also allow you to select the maximum of a field. This would allow you to get the most recent record. A Top Values query will allow you to specify the number of records for each liner, say Top 5 or Top 10.

Look these up in Access help.

 

[Mile-O]

When you have a list appearing across the top (i.e. Dia1, Dia2, Dia--->) then this is a sure sign that you need a new table in your database. A database should grow down and not across.

Those are my set fields, and they grow down. My Data input (inspect date,dia 1 ...)

What does DIA stand for/represent? Of course this data will grow down but these DIA have incremental values which means they have grown across.

I do have a Liner ID # Table all by itself that I use as a drop down box.

When bringing data through to a form it is safer and more flexible to base things off a query than a table.

 

[dsigner]

I think maybe Dia1, Dia2 etc are diameters at different points on the same die. If that is the case then it makes sense to have them in the same record. I would use them to store the maximum wear tolerance.

Then you have an inspection table which has same ID (so that you can link the two) and records the same dia info from the inspection Plus the date of inspection and also the number of pressings or whatever which it has made.

This will enable you to plot die wear against date or usage and calculate remaining life which is very useful. Oviously you can easily get the most recent figures in a report and make those which are getting close to limit turn red. This flags up critical dies at a glance.

Hope that this was the type of thing which you were trying to do.

 

[bwrobel]

Yes (Dia1 Dia2....) are just diameters which the maintenance person measures the die at that point. These dies will constantly grow within a group of tolerances. Group of tolerances = to diameter of product. They usually buy the dies with a small diameter then get bored to the next size up after out of tolerance.

I do have a inspection table that holds all the liner diameters. I have all the data, I just need a better way to sort it, while keep all old records to project wear life. I have set up a counted list of out of tolerance liners and Active liners, but are counting all newest dated and old dates.
I am still in the developing stages of this DB So any suggestion would help me out.

Would Distinct or DistinctRow help me? My book does not tell that much about those ways or have any examples.

 

[bwrobel]

I DID IT....it is Distinct

 

[bwrobel]

Well not exactly I did get a list of all Active, but the distinct only works on one field.

Is there anyway to filter all other thru most recent date? I need most recent date code

 

[Banana]

But, see-

this wouldn't be problem if the data were properly normalized

The table design should be something like this:

tblProduct
ProductID
ProductName

tblMeasurement
ProductID (Foreign key to Product)
ProductMeasurement
MeasureDate (? I'm not sure whether you measure product several different times or something like that so I'm not 100% sure if this belongs here)

That way you can easily get the answers you need.

 

[bwrobel]

That is what I'm try to say... Measure Date is going to be changing. This ITEM [LINER ID#] is going to be a changing item with changing Diameters.

As the tool is used the diameter changes. So the tool [liner ID #] will stay the same I want the most recent diameters and when it was inspected last. [inspected date, dia1, dia2...]

Is that help any??

 

[bwrobel]

Is there a query function that can do this or do I have a make All new tables for Each [Liner id #]
Might be 90+ tables just for each liner

 

[neileg]

Well not exactly I did get a list of all Active, but the distinct only works on one field.

Is there anyway to filter all other thru most recent date? I need most recent date code

You have already been advised that a Totals query using Max or a TOP value query will give you this.

Create a query that returns only the Liner ID and the inspected date and use Max on the date. This will give you a set of records with the latest inspection date for each liner. Create a second query. Add in the query you just made and your original table. Join the first query to the table on both the Liner ID and the date. That will give you what you want.

 

[bwrobel]

SELECT DISTINCT [Liner id #], Max[date inspected]

Is not working am I missing something?

 

[p595659]

what about from? This would be which table you are getting the fields from eg

Select ID, Name,PhoneNumber
from tblCustomer;

 

[bwrobel]

Ok here is my project.... Any Access Masters comments are greatly welcome.


Ok need help in Query [Active Liner A] or [Tot ACT Liner A]

Trying to just get list of liners with most recent date inspected. I just can't grasp the right way to query.

 

[neileg]

You have already been advised that a Totals query using Max or a TOP value query will give you this.

Create a query that returns only the Liner ID and the inspected date and use Max on the date. This will give you a set of records with the latest inspection date for each liner. Create a second query. Add in the query you just made and your original table. Join the first query to the table on both the Liner ID and the date. That will give you what you want.

I'm sorry but if you are going to ask questions you have to listen to the answers.

 

[bwrobel]

Originally Posted by neileg
You have already been advised that a Totals query using Max or a TOP value query will give you this.

Create a query that returns only the Liner ID and the inspected date and use Max on the date. This will give you a set of records with the latest inspection date for each liner. Create a second query. Add in the query you just made and your original table. Join the first query to the table on both the Liner ID and the date. That will give you what you want.

I'm sorry but if you are going to ask questions you have to listen to the answers.

I have tried these queries and can't get the results I want.

I have a list of Liners (ex... A-1-1, A-1-2, A-1-3 and so on...) with multiple dates and inspection data.

SELECT DISTINCT [Liner id #], Max[date inspected]

Is not working am I missing something?

There is something I'm not grasping... Is there a Where statement I can filter only the most recent date or a Having statement. And I don't I'm using my Max statement right.