If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Last Date when two tables involved
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 |
#3
|
|||
|
|||
Last Date when two tables involved
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 - |
#4
|
|||
|
|||
Last Date when two tables involved
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 - |
#5
|
|||
|
|||
Last Date when two tables involved
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 - |
#6
|
|||
|
|||
Last Date when two tables involved
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 - |
#7
|
|||
|
|||
Last Date when two tables involved
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 - |
#8
|
|||
|
|||
Last Date when two tables involved
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 - |
Thread Tools | |
Display Modes | |
|
|