Select Query for Access 2003

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that contains job numbers which associate to a specific
request message. The request often change throughout the job and these
changes are sent to the field as a change message to the original request.
So, each job number can have many request associated with it.

Job 1
- original message
- change message one
Job 2
- original message
- change message one
- change message two
Job 3
- original message

I need to keep all the messages in the databse for history purposes, but for
immediate uses I just want to query a Job # and only the Last message
entered, not all of the messages. Dates messages are entered are also
included in the table.

Job 1 change message one
Job 2 change message two
Job 3 original message

not

Job 1 original message
Job 1 change message one
Job 2 original message
Job 2 change message one etc.

Any suggestions on how to query this table?

Thank-you
 
First, you'd be better off using 2 tables having a one-to-many relationship,
with Job Number as primary key in the one table and Change Number as the
primary key in the many table. I'm guessing your data is not currently set
up this way based on your post. Is this correct?

Randall Arnold
 
Stan said:
I have a table that contains job numbers which associate to a specific
request message. The request often change throughout the job and these
changes are sent to the field as a change message to the original request.
So, each job number can have many request associated with it.

Job 1
- original message
- change message one
Job 2
- original message
- change message one
- change message two
Job 3
- original message

I need to keep all the messages in the databse for history purposes, but for
immediate uses I just want to query a Job # and only the Last message
entered, not all of the messages. Dates messages are entered are also
included in the table.

Job 1 change message one
Job 2 change message two
Job 3 original message

not

Job 1 original message
Job 1 change message one
Job 2 original message
Job 2 change message one etc.
Click to expand...


A relational database does not keep track of when records
were created. If you want to do this kind of thing, you
**must** have a data field in each record that can be used
for this purpose. Most common is a date field that defaults
to Date() so it is filled in automatically when a record is
first created.

Assuming you already have a changedate field for this
purpose, the query would be something like:

SELECT jobno, msgtext
FROM thetable
WHERE changedate = (SELECT Max(changedate)
FROM thetable As X
WHERE X.jobno = thetable.jobno)
 
Back
Top