Last Date when two tables involved

Hi! I am trying to get the last date on the table SIR which is linked to the
table SIR_D.

Table SIR
-----------
SIR_ID (primary key)
ENTERED_DATE

Table SIR_D
---------------
SIR_ID (primary key)
STK_NO (primary key)
LOC (primary key)
REQUESTOR

How do I obtain the last ENTERED DATE for each STK_NO & LOC as the following
result : (N.B. a STK_NO could be found in many LOC) The goal is to get the
very last information for each STK_NO & LOC.

SIR_ID STK_NO LOC ENTERED_DATE
003 123 MAG 23-MAY-2006
003 123 EXT 23-MAY-2006
001 124 MAG 01-MAR-2005
006 672 MAG 06-JUN-2006

Thank you for your help,
gmore

In SQL view both the following queries should work

One way (probably fastest)
SELECT SIR_D.SIR_ID
, STK_NO.
, LOC
, L.LastDate
FROM SIR_D INNER JOIN
( SELECT SIR_ID
, Max(ENTERED_DATE) as LastDate
FROM SIR
GROUP BY SIR_D) as L
ON SIR_D.SIR_ID = L.SIR_ID)

Another way
SELECT *
, (SELECT Max(ENTERED_DATE) FROM SIR WHERE SIR.SIR_ID = SIR_D.SIR_ID) as
LastDate
FROM SIR_D

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"gmore" wrote in message
...
Hi! I am trying to get the last date on the table SIR which is linked to
the
table SIR_D.

Table SIR
-----------
SIR_ID (primary key)
ENTERED_DATE

Table SIR_D
---------------
SIR_ID (primary key)
STK_NO (primary key)
LOC (primary key)
REQUESTOR

How do I obtain the last ENTERED DATE for each STK_NO & LOC as the
following
result : (N.B. a STK_NO could be found in many LOC) The goal is to get the
very last information for each STK_NO & LOC.

SIR_ID STK_NO LOC ENTERED_DATE
003 123 MAG 23-MAY-2006
003 123 EXT 23-MAY-2006
001 124 MAG 01-MAR-2005
006 672 MAG 06-JUN-2006

Thank you for your help,
gmore

I initially though exactly as John did, and if that works then that's
great, however, the max entered date is always going to be the ONLY
date for a given SIR_ID based on OP's description. SIR_ID is a PK in
SIR therefore there is only one ENTERED_DATE per SIR_ID.

I think he wants something more along the lines of:

SELECT Max(ENTERED_DATE), STK_NO, LOC
FROM SIR
INNER JOIN SIR_D
ON SIR.SIR_ID = SIR_D.SIR_ID
GROUP BY STK_NO, LOC

Cheers,
Jason Lepack

On Feb 16, 7:50 am, "John Spencer" wrote:
In SQL view both the following queries should work

One way (probably fastest)
SELECT SIR_D.SIR_ID
, STK_NO.
, LOC
, L.LastDate
FROM SIR_D INNER JOIN
( SELECT SIR_ID
, Max(ENTERED_DATE) as LastDate
FROM SIR
GROUP BY SIR_D) as L
ON SIR_D.SIR_ID = L.SIR_ID)

Another way
SELECT *
, (SELECT Max(ENTERED_DATE) FROM SIR WHERE SIR.SIR_ID = SIR_D.SIR_ID) as
LastDate
FROM SIR_D

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

"gmore" wrote in message

...



Hi! I am trying to get the last date on the table SIR which is linked to
the
table SIR_D.

Table SIR
-----------
SIR_ID (primary key)
ENTERED_DATE

Table SIR_D
---------------
SIR_ID (primary key)
STK_NO (primary key)
LOC (primary key)
REQUESTOR

How do I obtain the last ENTERED DATE for each STK_NO & LOC as the
following
result : (N.B. a STK_NO could be found in many LOC) The goal is to get the
very last information for each STK_NO & LOC.

SIR_ID STK_NO LOC ENTERED_DATE
003 123 MAG 23-MAY-2006
003 123 EXT 23-MAY-2006
001 124 MAG 01-MAR-2005
006 672 MAG 06-JUN-2006

Thank you for your help,
gmore- Hide quoted text -

- Show quoted text -

Actually, now that I look more closely at the table structure I see what you
mean.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Jason Lepack" wrote in message
oups.com...
I initially though exactly as John did, and if that works then that's
great, however, the max entered date is always going to be the ONLY
date for a given SIR_ID based on OP's description. SIR_ID is a PK in
SIR therefore there is only one ENTERED_DATE per SIR_ID.

I think he wants something more along the lines of:

SELECT Max(ENTERED_DATE), STK_NO, LOC
FROM SIR
INNER JOIN SIR_D
ON SIR.SIR_ID = SIR_D.SIR_ID
GROUP BY STK_NO, LOC

Cheers,
Jason Lepack

On Feb 16, 7:50 am, "John Spencer" wrote:
In SQL view both the following queries should work

One way (probably fastest)
SELECT SIR_D.SIR_ID
, STK_NO.
, LOC
, L.LastDate
FROM SIR_D INNER JOIN
( SELECT SIR_ID
, Max(ENTERED_DATE) as LastDate
FROM SIR
GROUP BY SIR_D) as L
ON SIR_D.SIR_ID = L.SIR_ID)

Another way
SELECT *
, (SELECT Max(ENTERED_DATE) FROM SIR WHERE SIR.SIR_ID = SIR_D.SIR_ID) as
LastDate
FROM SIR_D

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

"gmore" wrote in message

...



Hi! I am trying to get the last date on the table SIR which is linked
to
the
table SIR_D.

Table SIR
-----------
SIR_ID (primary key)
ENTERED_DATE

Table SIR_D
---------------
SIR_ID (primary key)
STK_NO (primary key)
LOC (primary key)
REQUESTOR

How do I obtain the last ENTERED DATE for each STK_NO & LOC as the
following
result : (N.B. a STK_NO could be found in many LOC) The goal is to get
the
very last information for each STK_NO & LOC.

SIR_ID STK_NO LOC ENTERED_DATE
003 123 MAG 23-MAY-2006
003 123 EXT 23-MAY-2006
001 124 MAG 01-MAR-2005
006 672 MAG 06-JUN-2006

Thank you for your help,
gmore- Hide quoted text -

- Show quoted text -

Thanks Jason, You've aimed exactly what I wanted, but there's something I
forgot to mention:

I've got two other fields: ISSUE_QTY from SIR_D table and REQUESTOR from SIR
table.

Is it still possible to get the really last date as already done before,
where ISSUE_QTY 0 and that also shows the REQUESTOR field for each last
result (I tried to do it, but it shows a lot of additional rows.)

Thanks again!

"Jason Lepack" wrote:

I initially though exactly as John did, and if that works then that's
great, however, the max entered date is always going to be the ONLY
date for a given SIR_ID based on OP's description. SIR_ID is a PK in
SIR therefore there is only one ENTERED_DATE per SIR_ID.

I think he wants something more along the lines of:

SELECT Max(ENTERED_DATE), STK_NO, LOC
FROM SIR
INNER JOIN SIR_D
ON SIR.SIR_ID = SIR_D.SIR_ID
GROUP BY STK_NO, LOC

Cheers,
Jason Lepack

On Feb 16, 7:50 am, "John Spencer" wrote:
In SQL view both the following queries should work

One way (probably fastest)
SELECT SIR_D.SIR_ID
, STK_NO.
, LOC
, L.LastDate
FROM SIR_D INNER JOIN
( SELECT SIR_ID
, Max(ENTERED_DATE) as LastDate
FROM SIR
GROUP BY SIR_D) as L
ON SIR_D.SIR_ID = L.SIR_ID)

Another way
SELECT *
, (SELECT Max(ENTERED_DATE) FROM SIR WHERE SIR.SIR_ID = SIR_D.SIR_ID) as
LastDate
FROM SIR_D

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

"gmore" wrote in message

...



Hi! I am trying to get the last date on the table SIR which is linked to
the
table SIR_D.

Table SIR
-----------
SIR_ID (primary key)
ENTERED_DATE

Table SIR_D
---------------
SIR_ID (primary key)
STK_NO (primary key)
LOC (primary key)
REQUESTOR

How do I obtain the last ENTERED DATE for each STK_NO & LOC as the
following
result : (N.B. a STK_NO could be found in many LOC) The goal is to get the
very last information for each STK_NO & LOC.

SIR_ID STK_NO LOC ENTERED_DATE
003 123 MAG 23-MAY-2006
003 123 EXT 23-MAY-2006
001 124 MAG 01-MAR-2005
006 672 MAG 06-JUN-2006

Thank you for your help,
gmore- Hide quoted text -

- Show quoted text -

1) Modifiy the original query.

(This query will select the latest date for each pairing of stk_no and
loc where the issue_qty is greater than 0. If there is no issue_qty
greater than 0 then that skt_no and loc will not show up.)

SELECT Max(SIR.ENTERED_DATE) AS LAST_DATE,
SIR_D.STK_NO, SIR_D.LOC
FROM SIR
INNER JOIN SIR_D
ON SIR.SIR_ID = SIR_D.SIR_ID
WHERE SIR_D.ISSUE_QTY0
GROUP BY SIR_D.STK_NO, SIR_D.LOC;


2) Create another query. Replace Query1 with what you called the
above query.

(This query pairs the results from the last query with it's
requestor.)

SELECT Q.LAST_DATE, Q.STK_NO, Q.LOC, SIR.REQUESTOR
FROM Query1 AS Q
INNER JOIN
(SIR INNER JOIN SIR_D ON SIR.SIR_ID = SIR_D.SIR_ID)
ON (Q.LOC = SIR_D.LOC) AND (Q.STK_NO = SIR_D.STK_NO)
AND (Q.LAST_DATE = SIR.ENTERED_DATE);


3) Examine these in design view so that you understand what is going
on. It's great that Google Groups is here for people to get help, but
it's always easier if you can help yourself If there's anything
you don't understand in this query then get right back on here and
post again. Plus, you could do all of this in one query by replacing
the Query1 with the actual SQL Definition of Query1 but it's more
difficult to see what's going on using the design view.

Cheers,
Jason Lepack

On Feb 16, 9:50 am, gmore wrote:
Thanks Jason, You've aimed exactly what I wanted, but there's something I
forgot to mention:

I've got two other fields: ISSUE_QTY from SIR_D table and REQUESTOR from SIR
table.

Is it still possible to get the really last date as already done before,
where ISSUE_QTY 0 and that also shows the REQUESTOR field for each last
result (I tried to do it, but it shows a lot of additional rows.)

Thanks again!



"Jason Lepack" wrote:
I initially though exactly as John did, and if that works then that's
great, however, the max entered date is always going to be the ONLY
date for a given SIR_ID based on OP's description. SIR_ID is a PK in
SIR therefore there is only one ENTERED_DATE per SIR_ID.

I think he wants something more along the lines of:

SELECT Max(ENTERED_DATE), STK_NO, LOC
FROM SIR
INNER JOIN SIR_D
ON SIR.SIR_ID = SIR_D.SIR_ID
GROUP BY STK_NO, LOC

Cheers,
Jason Lepack

On Feb 16, 7:50 am, "John Spencer" wrote:
In SQL view both the following queries should work

One way (probably fastest)
SELECT SIR_D.SIR_ID
, STK_NO.
, LOC
, L.LastDate
FROM SIR_D INNER JOIN
( SELECT SIR_ID
, Max(ENTERED_DATE) as LastDate
FROM SIR
GROUP BY SIR_D) as L
ON SIR_D.SIR_ID = L.SIR_ID)

Another way
SELECT *
, (SELECT Max(ENTERED_DATE) FROM SIR WHERE SIR.SIR_ID = SIR_D.SIR_ID) as
LastDate
FROM SIR_D

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

"gmore" wrote in message

...

Hi! I am trying to get the last date on the table SIR which is linked to
the
table SIR_D.

Table SIR
-----------
SIR_ID (primary key)
ENTERED_DATE

Table SIR_D
---------------
SIR_ID (primary key)
STK_NO (primary key)
LOC (primary key)
REQUESTOR

How do I obtain the last ENTERED DATE for each STK_NO & LOC as the
following
result : (N.B. a STK_NO could be found in many LOC) The goal is to get the
very last information for each STK_NO & LOC.

SIR_ID STK_NO LOC ENTERED_DATE
003 123 MAG 23-MAY-2006
003 123 EXT 23-MAY-2006
001 124 MAG 01-MAR-2005
006 672 MAG 06-JUN-2006

Thank you for your help,
gmore- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

I am sorry, I am new with Access. I appreciate your valuable help. You guys
are kings!

gmore

"Jason Lepack" wrote:

1) Modifiy the original query.

(This query will select the latest date for each pairing of stk_no and
loc where the issue_qty is greater than 0. If there is no issue_qty
greater than 0 then that skt_no and loc will not show up.)

SELECT Max(SIR.ENTERED_DATE) AS LAST_DATE,
SIR_D.STK_NO, SIR_D.LOC
FROM SIR
INNER JOIN SIR_D
ON SIR.SIR_ID = SIR_D.SIR_ID
WHERE SIR_D.ISSUE_QTY0
GROUP BY SIR_D.STK_NO, SIR_D.LOC;


2) Create another query. Replace Query1 with what you called the
above query.

(This query pairs the results from the last query with it's
requestor.)

SELECT Q.LAST_DATE, Q.STK_NO, Q.LOC, SIR.REQUESTOR
FROM Query1 AS Q
INNER JOIN
(SIR INNER JOIN SIR_D ON SIR.SIR_ID = SIR_D.SIR_ID)
ON (Q.LOC = SIR_D.LOC) AND (Q.STK_NO = SIR_D.STK_NO)
AND (Q.LAST_DATE = SIR.ENTERED_DATE);


3) Examine these in design view so that you understand what is going
on. It's great that Google Groups is here for people to get help, but
it's always easier if you can help yourself If there's anything
you don't understand in this query then get right back on here and
post again. Plus, you could do all of this in one query by replacing
the Query1 with the actual SQL Definition of Query1 but it's more
difficult to see what's going on using the design view.

Cheers,
Jason Lepack

On Feb 16, 9:50 am, gmore wrote:
Thanks Jason, You've aimed exactly what I wanted, but there's something I
forgot to mention:

I've got two other fields: ISSUE_QTY from SIR_D table and REQUESTOR from SIR
table.

Is it still possible to get the really last date as already done before,
where ISSUE_QTY 0 and that also shows the REQUESTOR field for each last
result (I tried to do it, but it shows a lot of additional rows.)

Thanks again!



"Jason Lepack" wrote:
I initially though exactly as John did, and if that works then that's
great, however, the max entered date is always going to be the ONLY
date for a given SIR_ID based on OP's description. SIR_ID is a PK in
SIR therefore there is only one ENTERED_DATE per SIR_ID.

I think he wants something more along the lines of:

SELECT Max(ENTERED_DATE), STK_NO, LOC
FROM SIR
INNER JOIN SIR_D
ON SIR.SIR_ID = SIR_D.SIR_ID
GROUP BY STK_NO, LOC

Cheers,
Jason Lepack

On Feb 16, 7:50 am, "John Spencer" wrote:
In SQL view both the following queries should work

One way (probably fastest)
SELECT SIR_D.SIR_ID
, STK_NO.
, LOC
, L.LastDate
FROM SIR_D INNER JOIN
( SELECT SIR_ID
, Max(ENTERED_DATE) as LastDate
FROM SIR
GROUP BY SIR_D) as L
ON SIR_D.SIR_ID = L.SIR_ID)

Another way
SELECT *
, (SELECT Max(ENTERED_DATE) FROM SIR WHERE SIR.SIR_ID = SIR_D.SIR_ID) as
LastDate
FROM SIR_D

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

"gmore" wrote in message

...

Hi! I am trying to get the last date on the table SIR which is linked to
the
table SIR_D.

Table SIR
-----------
SIR_ID (primary key)
ENTERED_DATE

Table SIR_D
---------------
SIR_ID (primary key)
STK_NO (primary key)
LOC (primary key)
REQUESTOR

How do I obtain the last ENTERED DATE for each STK_NO & LOC as the
following
result : (N.B. a STK_NO could be found in many LOC) The goal is to get the
very last information for each STK_NO & LOC.

SIR_ID STK_NO LOC ENTERED_DATE
003 123 MAG 23-MAY-2006
003 123 EXT 23-MAY-2006
001 124 MAG 01-MAR-2005
006 672 MAG 06-JUN-2006

Thank you for your help,
gmore- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

There's absolutely nothing to be sorry for! You've done very well.
You described your situations using tabale structures and expected
output. That's more than we can say for a lot of questions! Keep on
trying to learn and describing your problems well and we'll be able to
help you I'm sure.

Cheers,
Jason Lepack

On Feb 16, 11:15 am, gmore wrote:
I am sorry, I am new with Access. I appreciate your valuable help. You guys
are kings!

gmore



"Jason Lepack" wrote:
1) Modifiy the original query.

(This query will select the latest date for each pairing of stk_no and
loc where the issue_qty is greater than 0. If there is no issue_qty
greater than 0 then that skt_no and loc will not show up.)

SELECT Max(SIR.ENTERED_DATE) AS LAST_DATE,
SIR_D.STK_NO, SIR_D.LOC
FROM SIR
INNER JOIN SIR_D
ON SIR.SIR_ID = SIR_D.SIR_ID
WHERE SIR_D.ISSUE_QTY0
GROUP BY SIR_D.STK_NO, SIR_D.LOC;

2) Create another query. Replace Query1 with what you called the
above query.

(This query pairs the results from the last query with it's
requestor.)

SELECT Q.LAST_DATE, Q.STK_NO, Q.LOC, SIR.REQUESTOR
FROM Query1 AS Q
INNER JOIN
(SIR INNER JOIN SIR_D ON SIR.SIR_ID = SIR_D.SIR_ID)
ON (Q.LOC = SIR_D.LOC) AND (Q.STK_NO = SIR_D.STK_NO)
AND (Q.LAST_DATE = SIR.ENTERED_DATE);

3) Examine these in design view so that you understand what is going
on. It's great that Google Groups is here for people to get help, but
it's always easier if you can help yourself If there's anything
you don't understand in this query then get right back on here and
post again. Plus, you could do all of this in one query by replacing
the Query1 with the actual SQL Definition of Query1 but it's more
difficult to see what's going on using the design view.

Cheers,
Jason Lepack

On Feb 16, 9:50 am, gmore wrote:
Thanks Jason, You've aimed exactly what I wanted, but there's something I
forgot to mention:

I've got two other fields: ISSUE_QTY from SIR_D table and REQUESTOR from SIR
table.

Is it still possible to get the really last date as already done before,
where ISSUE_QTY 0 and that also shows the REQUESTOR field for each last
result (I tried to do it, but it shows a lot of additional rows.)

Thanks again!

"Jason Lepack" wrote:
I initially though exactly as John did, and if that works then that's
great, however, the max entered date is always going to be the ONLY
date for a given SIR_ID based on OP's description. SIR_ID is a PK in
SIR therefore there is only one ENTERED_DATE per SIR_ID.

I think he wants something more along the lines of:

SELECT Max(ENTERED_DATE), STK_NO, LOC
FROM SIR
INNER JOIN SIR_D
ON SIR.SIR_ID = SIR_D.SIR_ID
GROUP BY STK_NO, LOC

Cheers,
Jason Lepack

On Feb 16, 7:50 am, "John Spencer" wrote:
In SQL view both the following queries should work

One way (probably fastest)
SELECT SIR_D.SIR_ID
, STK_NO.
, LOC
, L.LastDate
FROM SIR_D INNER JOIN
( SELECT SIR_ID
, Max(ENTERED_DATE) as LastDate
FROM SIR
GROUP BY SIR_D) as L
ON SIR_D.SIR_ID = L.SIR_ID)

Another way
SELECT *
, (SELECT Max(ENTERED_DATE) FROM SIR WHERE SIR.SIR_ID = SIR_D.SIR_ID) as
LastDate
FROM SIR_D

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

"gmore" wrote in message

...

Hi! I am trying to get the last date on the table SIR which is linked to
the
table SIR_D.

Table SIR
-----------
SIR_ID (primary key)
ENTERED_DATE

Table SIR_D
---------------
SIR_ID (primary key)
STK_NO (primary key)
LOC (primary key)
REQUESTOR

How do I obtain the last ENTERED DATE for each STK_NO & LOC as the
following
result : (N.B. a STK_NO could be found in many LOC) The goal is to get the
very last information for each STK_NO & LOC.

SIR_ID STK_NO LOC ENTERED_DATE
003 123 MAG 23-MAY-2006
003 123 EXT 23-MAY-2006
001 124 MAG 01-MAR-2005
006 672 MAG 06-JUN-2006

Thank you for your help,
gmore- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -