searching where item has the same results

[ArchMichael]

i created database where it captures my team results but i need the database to search to see it item has passed twice consetuvicely and go to next level of test.

example of test results
item results inspection
item A pass Normal
item B pass Normal
item A pass Normal


the next inpsection is light for item A

any help would be greatful

[MMcCarthy]

Hi Michael

It is not clear from your question what table structure you have used to store your data. Your question is too general for anyone to reply. Can you post the meta data of your tables so this question can be viewed in a cleared manner. Also please give more detail of exactly what you are trying to achieve and what help you need.

Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Mary

[ArchMichael]

my table is

Table Name = [Inspection_tbl]

Field; Type; IndexInfo
InspectionID; AutoNumber; PK
Partnumber; String
Description; String
TQty; Numeric
PQty; Numeric
FQty; Numeric
Results;String
InpectionDate; Date/Time
Auditor; String

i am trying to improve my team effiency where the database decides what the next level of inspection to be whether it is normal, light, or critical. our rule of thumb is that two consecutive results promote or demotes the inspection type

thanks for any assistance

[NeoPa]

Michael, I'm afraid your explanation only makes sense to someone who ALREADY KNOWS what you're talking about.

Try to explain it in such a way that someone who's not familiar with your task can understand it.

[Stewart Ross]

Hi Michael. If I'm interpreting you correctly (which as NeoPa says is difficult for people who don't know your application at all) you need to identify consecutive or near-consecutive tests on the same item and effectively set a 'test status' value to one of the three you mention as a result.

This is not a trivial problem, as in SQL there is no concept of record position - so there is no concept of 'consecutive'. What can be relatively easy to do in a free-form table structure such as that of Excel can be surprisingly complicated in the set-based table structures of databases, where record position cannot be used to determine what is consecutive and what is not. This means that you need to redefine your problem in such a way that a solution can be implemented in SQL - and given that your table metadata shows that you have a date recorded you need to define the problem as one involving the test date in some way (for example, two tests on the same item within 7 days of each other being treated as 'consecutive').

Think of how you would define your problem in a more rigorous way and feel free to post back to us. Remember that we don't know anything at all about your application other than what you tell us!

Cheers

-Stewart

[ArchMichael]

ok thanks. it worth the shot to ask the question. thanks for ya help