Retrieve the next entry

[Guest]

Good morning

I have a two tables which have a 1-to-many relationshi

The first contain three field
1. ID - Autonumbe
2. Drawing Number -Tex
3. Drawing Issue - Tex

The Second Contains dozens of fields, amongst them are
1. ID -Autonumbe
2. Drawing ID - Linked to the first table's I
3
4
5...

The first table contains a unique listing of all my drawing issue. The second table permits me to track each drawing issue throughout the release process and allows mulitple entries should the process be refused and have to recommence. Thus, Drawing xyz at issue A00 may only appear once in the first table as ID # 207 but may appear dozens of time in he second table

Here the question. I currently have a query that retrieves certain information about the entries in the second table and returns a listing a Drawing IDs that meet the criteria that I require, however, I then need to retrieve the information for the next entry for that Drawing ID in the second table

Let me put in context a bit. Let say I have the following entry in my first table
ID Drawing Number drawing Issu
207 xyz A0

And I have these entries (amongst many others of course) in the second table
Id Drawing ID
154 20
687 20
1204 20
2456 20
4567 20
8762 20

If my query returns the ID 1204 from the second table I need it return information from the next entry for that Drawing ID, in this case, ID 2456. How can I get a query to retrive the next entry?! I hope this is clear?

Thanks

Daniel

 

[Gerald Stanley]

Try something along the lines of
SELECT etc
FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Id =
T2.drawingID, Table2 AS T2A
WHERE T2A.Id IN (SELECT Min(Id) FROM Table2 WHERE drawingId
= T2.drawingId AND Id > T2.Id)

In the SELECT statement, you would use the alias T2A to
qualify columns from the next table entry. Note that
nothing will be returned when there is no next table entry.

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
Good morning,

I have a two tables which have a 1-to-many relationship

The first contain three fields
1. ID - Autonumber
2. Drawing Number -Text
3. Drawing Issue - Text

The Second Contains dozens of fields, amongst them are:
1. ID -Autonumber
2. Drawing ID - Linked to the first table's ID
3.
4.
5....

The first table contains a unique listing of all my

drawing issue. The second table permits me to track each
drawing issue throughout the release process and allows
mulitple entries should the process be refused and have to
recommence. Thus, Drawing xyz at issue A00 may only appear
once in the first table as ID # 207 but may appear dozens
of time in he second table.

Here the question. I currently have a query that

retrieves certain information about the entries in the
second table and returns a listing a Drawing IDs that meet
the criteria that I require, however, I then need to
retrieve the information for the next entry for that
Drawing ID in the second table.

Let me put in context a bit. Let say I have the following entry in my first table:
ID Drawing Number drawing Issue
207 xyz A00

And I have these entries (amongst many others of course) in the second table
Id Drawing ID
154 207
687 207
1204 207
2456 207
4567 207
8762 207

If my query returns the ID 1204 from the second table I

need it return information from the next entry for that
Drawing ID, in this case, ID 2456. How can I get a query
to retrive the next entry?! I hope this is clear?!