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
|
|||
|
|||
Help with criteria
I need a criteria or formula in a query to look at two fields, STATUS_CODE
and STATUS_DATE, what I need is to look at status code for 25-assgn and 70-wrkcomp, if the assgn date is greater than wrkcomp date, in the status date field, I need to show that data. So in the example below the proposal 0730874 will show up when I run the query, how can this be done? The SQL code is also below if that helps Using Access 2003 Thanks qselWorkOrdersOpenedBackUp PROPOSAL SHOP STATUS_CODE STATUS_DATE LOGIN SHOP_PERSON 0730874 56 10-OPEN 11/29/06 3:02:06 PM DWISE KLW056363 0730874 56 70-WRKCOMP 1/5/07 3:44:04 PM DAGNEW KLW056363 0730874 56 25-ASSGN 11/29/06 3:26:32 PM PAULB KLW056363 0730874 56 25-ASSGN 1/8/07 8:09:32 AM PAULB KLW056363 0730700 56 10-OPEN 11/28/06 2:33:11 PM DAGNEW DLH060473 0730700 56 70-WRKCOMP 12/12/06 4:00:20 PM DAGNEW DLH060473 0730700 56 25-ASSGN 11/28/06 2:33:30 PM DAGNEW DLH060473 SELECT ROOT_AE_P_PHS_E.PROPOSAL, ROOT_AE_P_PHS_E.SHOP, ROOT_AE_P_PST_E.STATUS_CODE, ROOT_AE_P_PST_E.STATUS_DATE, ROOT_AE_P_PST_E.LOGIN, ROOT_AE_P_PRO_S.SHOP_PERSON FROM (ROOT_AE_P_PHS_E INNER JOIN ROOT_AE_P_PST_E ON ROOT_AE_P_PHS_E.PROPOSAL = ROOT_AE_P_PST_E.PROPOSAL) INNER JOIN ROOT_AE_P_PRO_S ON (ROOT_AE_P_PHS_E.SORT_CODE = ROOT_AE_P_PRO_S.SORT_CODE) AND (ROOT_AE_P_PHS_E.PROPOSAL = ROOT_AE_P_PRO_S.PROPOSAL) WHERE (((ROOT_AE_P_PHS_E.SHOP)="56")); |
#2
|
|||
|
|||
Help with criteria
"Paul B" wrote in message ... I need a criteria or formula in a query to look at two fields, STATUS_CODE and STATUS_DATE, what I need is to look at status code for 25-assgn and 70-wrkcomp, if the assgn date is greater than wrkcomp date, in the status date field, I need to show that data. So in the example below the proposal 0730874 will show up when I run the query, how can this be done? The SQL code is also below if that helps Using Access 2003 Thanks qselWorkOrdersOpenedBackUp PROPOSAL SHOP STATUS_CODE STATUS_DATE LOGIN SHOP_PERSON 0730874 56 10-OPEN 11/29/06 3:02:06 PM DWISE KLW056363 0730874 56 70-WRKCOMP 1/5/07 3:44:04 PM DAGNEW KLW056363 0730874 56 25-ASSGN 11/29/06 3:26:32 PM PAULB KLW056363 0730874 56 25-ASSGN 1/8/07 8:09:32 AM PAULB KLW056363 0730700 56 10-OPEN 11/28/06 2:33:11 PM DAGNEW DLH060473 0730700 56 70-WRKCOMP 12/12/06 4:00:20 PM DAGNEW DLH060473 0730700 56 25-ASSGN 11/28/06 2:33:30 PM DAGNEW DLH060473 SELECT ROOT_AE_P_PHS_E.PROPOSAL, ROOT_AE_P_PHS_E.SHOP, ROOT_AE_P_PST_E.STATUS_CODE, ROOT_AE_P_PST_E.STATUS_DATE, ROOT_AE_P_PST_E.LOGIN, ROOT_AE_P_PRO_S.SHOP_PERSON FROM (ROOT_AE_P_PHS_E INNER JOIN ROOT_AE_P_PST_E ON ROOT_AE_P_PHS_E.PROPOSAL = ROOT_AE_P_PST_E.PROPOSAL) INNER JOIN ROOT_AE_P_PRO_S ON (ROOT_AE_P_PHS_E.SORT_CODE = ROOT_AE_P_PRO_S.SORT_CODE) AND (ROOT_AE_P_PHS_E.PROPOSAL = ROOT_AE_P_PRO_S.PROPOSAL) WHERE (((ROOT_AE_P_PHS_E.SHOP)="56")); One way might be to save the following query, then filter it for SHOP="56" and 25Date 70Date... SELECT PHS.PROPOSAL, PHS.SHOP, PST.STATUS_CODE, PST.STATUS_DATE, PST.LOGIN, PRO.SHOP_PERSON (SELECT Max(ST.STATUS_DATE) FROM ROOT_AE_P_PST_E As ST WHERE ST.PROPOSAL = PHS.PROPOSOL AND ST.STATUS_CODE = "25-ASSGN") As 25Date, (SELECT Max(ST.STATUS_DATE) FROM ROOT_AE_P_PST_E As ST WHERE ST.PROPOSAL = PHS.PROPOSOL AND ST.STATUS_CODE = "70-WRKCOMP") As 70Date FROM (ROOT_AE_P_PHS_E As PHS INNER JOIN ROOT_AE_P_PST_E As PST ON PHS.PROPOSAL = PST.PROPOSAL) INNER JOIN ROOT_AE_P_PRO_S As PRO ON (PHS.SORT_CODE = PRO.SORT_CODE) AND (PHS.PROPOSAL = PRO.PROPOSAL); |
#3
|
|||
|
|||
Help with criteria
Gary, I don't understand the 25Date 70Date... part, in design view what
would it look like and field would I put it? "Gary Walter" wrote in message ... "Paul B" wrote in message ... I need a criteria or formula in a query to look at two fields, STATUS_CODE and STATUS_DATE, what I need is to look at status code for 25-assgn and 70-wrkcomp, if the assgn date is greater than wrkcomp date, in the status date field, I need to show that data. So in the example below the proposal 0730874 will show up when I run the query, how can this be done? The SQL code is also below if that helps Using Access 2003 Thanks qselWorkOrdersOpenedBackUp PROPOSAL SHOP STATUS_CODE STATUS_DATE LOGIN SHOP_PERSON 0730874 56 10-OPEN 11/29/06 3:02:06 PM DWISE KLW056363 0730874 56 70-WRKCOMP 1/5/07 3:44:04 PM DAGNEW KLW056363 0730874 56 25-ASSGN 11/29/06 3:26:32 PM PAULB KLW056363 0730874 56 25-ASSGN 1/8/07 8:09:32 AM PAULB KLW056363 0730700 56 10-OPEN 11/28/06 2:33:11 PM DAGNEW DLH060473 0730700 56 70-WRKCOMP 12/12/06 4:00:20 PM DAGNEW DLH060473 0730700 56 25-ASSGN 11/28/06 2:33:30 PM DAGNEW DLH060473 SELECT ROOT_AE_P_PHS_E.PROPOSAL, ROOT_AE_P_PHS_E.SHOP, ROOT_AE_P_PST_E.STATUS_CODE, ROOT_AE_P_PST_E.STATUS_DATE, ROOT_AE_P_PST_E.LOGIN, ROOT_AE_P_PRO_S.SHOP_PERSON FROM (ROOT_AE_P_PHS_E INNER JOIN ROOT_AE_P_PST_E ON ROOT_AE_P_PHS_E.PROPOSAL = ROOT_AE_P_PST_E.PROPOSAL) INNER JOIN ROOT_AE_P_PRO_S ON (ROOT_AE_P_PHS_E.SORT_CODE = ROOT_AE_P_PRO_S.SORT_CODE) AND (ROOT_AE_P_PHS_E.PROPOSAL = ROOT_AE_P_PRO_S.PROPOSAL) WHERE (((ROOT_AE_P_PHS_E.SHOP)="56")); One way might be to save the following query, then filter it for SHOP="56" and 25Date 70Date... SELECT PHS.PROPOSAL, PHS.SHOP, PST.STATUS_CODE, PST.STATUS_DATE, PST.LOGIN, PRO.SHOP_PERSON (SELECT Max(ST.STATUS_DATE) FROM ROOT_AE_P_PST_E As ST WHERE ST.PROPOSAL = PHS.PROPOSOL AND ST.STATUS_CODE = "25-ASSGN") As 25Date, (SELECT Max(ST.STATUS_DATE) FROM ROOT_AE_P_PST_E As ST WHERE ST.PROPOSAL = PHS.PROPOSOL AND ST.STATUS_CODE = "70-WRKCOMP") As 70Date FROM (ROOT_AE_P_PHS_E As PHS INNER JOIN ROOT_AE_P_PST_E As PST ON PHS.PROPOSAL = PST.PROPOSAL) INNER JOIN ROOT_AE_P_PRO_S As PRO ON (PHS.SORT_CODE = PRO.SORT_CODE) AND (PHS.PROPOSAL = PRO.PROPOSAL); |
#4
|
|||
|
|||
Help with criteria
"Paul B" wrote: Gary, I don't understand the 25Date 70Date... part, in design view what would it look like and field would I put it? I was hoping you would start a new query and in SQL View, replace anything there with the following: SELECT PHS.PROPOSAL, PHS.SHOP, PST.STATUS_CODE, PST.STATUS_DATE, PST.LOGIN, PRO.SHOP_PERSON, (SELECT Max(ST.STATUS_DATE) FROM ROOT_AE_P_PST_E As ST WHERE ST.PROPOSAL = PHS.PROPOSAL AND ST.STATUS_CODE = "25-ASSGN") As 25Date, (SELECT Max(ST.STATUS_DATE) FROM ROOT_AE_P_PST_E As ST WHERE ST.PROPOSAL = PHS.PROPOSAL AND ST.STATUS_CODE = "70-WRKCOMP") As 70Date FROM (ROOT_AE_P_PHS_E As PHS INNER JOIN ROOT_AE_P_PST_E As PST ON PHS.PROPOSAL = PST.PROPOSAL) INNER JOIN ROOT_AE_P_PRO_S As PRO ON (PHS.SORT_CODE = PRO.SORT_CODE) AND (PHS.PROPOSAL = PRO.PROPOSAL); Then save the query giving it some name. If it works correctly, you should get a [25Date] and [70Date] field in the results for each PROPOSAL (especially since I spelled it correctly this time). Then start a new query using this query as a starting point where you apply criteria for [SHOP] and [25Date] [70Date] |
#5
|
|||
|
|||
Help with criteria
Gary, will give it a try at work tomorrow
"Gary Walter" wrote in message ... "Paul B" wrote: Gary, I don't understand the 25Date 70Date... part, in design view what would it look like and field would I put it? I was hoping you would start a new query and in SQL View, replace anything there with the following: SELECT PHS.PROPOSAL, PHS.SHOP, PST.STATUS_CODE, PST.STATUS_DATE, PST.LOGIN, PRO.SHOP_PERSON, (SELECT Max(ST.STATUS_DATE) FROM ROOT_AE_P_PST_E As ST WHERE ST.PROPOSAL = PHS.PROPOSAL AND ST.STATUS_CODE = "25-ASSGN") As 25Date, (SELECT Max(ST.STATUS_DATE) FROM ROOT_AE_P_PST_E As ST WHERE ST.PROPOSAL = PHS.PROPOSAL AND ST.STATUS_CODE = "70-WRKCOMP") As 70Date FROM (ROOT_AE_P_PHS_E As PHS INNER JOIN ROOT_AE_P_PST_E As PST ON PHS.PROPOSAL = PST.PROPOSAL) INNER JOIN ROOT_AE_P_PRO_S As PRO ON (PHS.SORT_CODE = PRO.SORT_CODE) AND (PHS.PROPOSAL = PRO.PROPOSAL); Then save the query giving it some name. If it works correctly, you should get a [25Date] and [70Date] field in the results for each PROPOSAL (especially since I spelled it correctly this time). Then start a new query using this query as a starting point where you apply criteria for [SHOP] and [25Date] [70Date] |
#6
|
|||
|
|||
Help with criteria
Gary, this is what I ended up with, it shows a 0 or -1 based on the dates,
but even when I filter it down to the last 30 days and the STATUS_CODE it takes about 5 min. to run and that long any time a change is made, do you see anything that could speed it up? Thanks SELECT PHS.PROPOSAL, PHS.SHOP, PST.STATUS_CODE, PST.STATUS_DATE, PST.LOGIN, PRO.SHOP_PERSON, (SELECT Max(ST.STATUS_DATE) FROM ROOT_AE_P_PST_E As ST WHERE ST.PROPOSAL = PHS.PROPOSAL AND ST.STATUS_CODE = "25-ASSGN") AS 25Date, (SELECT Max(ST.STATUS_DATE) FROM ROOT_AE_P_PST_E As ST WHERE ST.PROPOSAL = PHS.PROPOSAL AND ST.STATUS_CODE = "70-WRKCOMP") AS 70Date, [25Date][70Date] AS Expr1 FROM (ROOT_AE_P_PHS_E AS PHS INNER JOIN ROOT_AE_P_PST_E AS PST ON PHS.PROPOSAL = PST.PROPOSAL) INNER JOIN ROOT_AE_P_PRO_S AS PRO ON (PHS.PROPOSAL = PRO.PROPOSAL) AND (PHS.SORT_CODE = PRO.SORT_CODE) WHERE (((PHS.SHOP)="56") AND ((PST.STATUS_CODE)="70-WRKCOMP" Or (PST.STATUS_CODE)="25-ASSGN") AND ((PST.STATUS_DATE) Between Date()-30 And Date()+1)); "Gary Walter" wrote in message ... "Paul B" wrote: Gary, I don't understand the 25Date 70Date... part, in design view what would it look like and field would I put it? I was hoping you would start a new query and in SQL View, replace anything there with the following: SELECT PHS.PROPOSAL, PHS.SHOP, PST.STATUS_CODE, PST.STATUS_DATE, PST.LOGIN, PRO.SHOP_PERSON, (SELECT Max(ST.STATUS_DATE) FROM ROOT_AE_P_PST_E As ST WHERE ST.PROPOSAL = PHS.PROPOSAL AND ST.STATUS_CODE = "25-ASSGN") As 25Date, (SELECT Max(ST.STATUS_DATE) FROM ROOT_AE_P_PST_E As ST WHERE ST.PROPOSAL = PHS.PROPOSAL AND ST.STATUS_CODE = "70-WRKCOMP") As 70Date FROM (ROOT_AE_P_PHS_E As PHS INNER JOIN ROOT_AE_P_PST_E As PST ON PHS.PROPOSAL = PST.PROPOSAL) INNER JOIN ROOT_AE_P_PRO_S As PRO ON (PHS.SORT_CODE = PRO.SORT_CODE) AND (PHS.PROPOSAL = PRO.PROPOSAL); Then save the query giving it some name. If it works correctly, you should get a [25Date] and [70Date] field in the results for each PROPOSAL (especially since I spelled it correctly this time). Then start a new query using this query as a starting point where you apply criteria for [SHOP] and [25Date] [70Date] |
#7
|
|||
|
|||
Help with criteria
"Paul B" wrote: Gary, this is what I ended up with, it shows a 0 or -1 based on the dates, but even when I filter it down to the last 30 days and the STATUS_CODE it takes about 5 min. to run and that long any time a change is made, do you see anything that could speed it up? Thanks SELECT PHS.PROPOSAL, PHS.SHOP, PST.STATUS_CODE, PST.STATUS_DATE, PST.LOGIN, PRO.SHOP_PERSON, (SELECT Max(ST.STATUS_DATE) FROM ROOT_AE_P_PST_E As ST WHERE ST.PROPOSAL = PHS.PROPOSAL AND ST.STATUS_CODE = "25-ASSGN") AS 25Date, (SELECT Max(ST.STATUS_DATE) FROM ROOT_AE_P_PST_E As ST WHERE ST.PROPOSAL = PHS.PROPOSAL AND ST.STATUS_CODE = "70-WRKCOMP") AS 70Date, [25Date][70Date] AS Expr1 FROM (ROOT_AE_P_PHS_E AS PHS INNER JOIN ROOT_AE_P_PST_E AS PST ON PHS.PROPOSAL = PST.PROPOSAL) INNER JOIN ROOT_AE_P_PRO_S AS PRO ON (PHS.PROPOSAL = PRO.PROPOSAL) AND (PHS.SORT_CODE = PRO.SORT_CODE) WHERE (((PHS.SHOP)="56") AND ((PST.STATUS_CODE)="70-WRKCOMP" Or (PST.STATUS_CODE)="25-ASSGN") AND ((PST.STATUS_DATE) Between Date()-30 And Date()+1)); First...are these simply 3 tables in an one mdb backend? -- not tables or views in SQL Server, Oracle, MySQL, IBM DB -- not queries If so, what fields are indexed? PHS -- PROPOSAL -- SORT_CODE -- SHOP PST -- PROPOSAL -- STATUS_DATE -- STATUS_CODE PRO -- PROPOSAL -- SORT_CODE At the least, PROPOSAL should be indexed in all 3 tables. Indexes can be "expensive," but optimally all fields above should be indexed depending upon your data. If these are mdb tables and the fields are optimally indexed, and performance is still slow,then the next strategy would be to rethink the subqueries. Correlated subqueries (where we found [25Date] and [70Date]) can affect performance. Often it helps to "divide-and-conquer." How do these 2 queries perform? qry25Date SELECT ST.PROPOSAL, Max(ST.STATUS_DATE) As Max25Date FROM ROOT_AE_P_PST_E As ST WHERE ST.STATUS_CODE = "25-ASSGN" GROUP BY ST.PROPOSAL; qry70Date SELECT ST.PROPOSAL, Max(ST.STATUS_DATE) As Max70Date FROM ROOT_AE_P_PST_E As ST WHERE ST.STATUS_CODE = "70-WRKCOMP" GROUP BY ST.PROPOSAL; If acceptable, then get rid of correlated queries, and you could try just adding these 2 queries to your query design...or, you could "divide" even further... qryProposalsOpenedBackOpen SELECT q1.PROPOSAL, q1.Max25Date, q2.Max70Date, FROM qry25Date As q1 INNER JOIN qry70Date As q2 ON q1.PROPOSAL = q2.PROPOSAL WHERE q1.[Max25Date] q2.[Max70Date]; That ought to slice-and-dice down to some good performance I would think. How does it perform? Do you think you get the correct records? I really just guessed that you will only be concerned with the max dates for each PROPOSAL. Further "divide" might involve bringing only specific SHOP to the final query... qrySHOP SELECT DISTINCT PHS.PROPOSAL, PHS.SHOP, PRO.SHOP_PERSON FROM ROOT_AE_P_PHS_E AS PHS INNER JOIN ROOT_AE_P_PRO_S AS PRO ON (PHS.PROPOSAL = PRO.PROPOSAL) AND (PHS.SORT_CODE = PRO.SORT_CODE) WHERE PHS.SHOP ="56"; How does this perform? I am not sure about your data so maybe the "DISTINCT" is not necessary? For the final query, it makes sense to me that this query bring a single record for each distinct PROPOSAL/SHOP/SHOP_PERSON. I could be wrong... So...your final query... It appears you want to return PHS.PROPOSAL, --- qryProposalsOpenedBackOpen As qP PHS.SHOP, --- qrySHOP As qS PST.STATUS_CODE, --- ROOT_AE_P_PST_E AS PST PST.STATUS_DATE, --- ROOT_AE_P_PST_E AS PST PST.LOGIN, --- ROOT_AE_P_PST_E AS PST PRO.SHOP_PERSON --- qrySHOP As qS Start a new query and add qryProposalsOpenedBackOpen qryShop ROOT_AE_P_PST_E Right-mouse click on the table qryProposalsOpenedBackOpen, choose "Properties," and set "Alias" to "qP" Right-mouse click on the table qrySHOP, choose "Properties," and set "Alias" to "qS" Right-mouse click on the table ROOT_AE_P_PST_E, choose "Properties," and set "Alias" to "PST" Join qP.PROPOSAL to qS.PROPOSAL Join qP.PROPOSAL to PST.PROPOSAL Double-click on the fields in the tables that you want (as referenced above) to send them down to the grid. Save the query. We've indexed, divided-and-conquered, and sliced-and-diced.... If that does not help (and if I did not misunderstand), I am out of ideas for the moment... |
#8
|
|||
|
|||
Help with criteria
Gary, first off thanks for the help, I think I am getting in deep here, not
to good with Access, the data is coming from a linked table from an ODBC data base that I have no control over, just trying to get some data out of it. I have a lot more experience with excel and have managed to import the data into Excel and get the information I need with a few helper columns and some formulas, but I would like to keep it all in Access, if I can, so I will try and follow the instructions you gave and see it I can get it to work that way. It will be Monday before I can try it out at work. Thanks again for all your help Paul -- "Gary Walter" wrote in message ... "Paul B" wrote: Gary, this is what I ended up with, it shows a 0 or -1 based on the dates, but even when I filter it down to the last 30 days and the STATUS_CODE it takes about 5 min. to run and that long any time a change is made, do you see anything that could speed it up? Thanks SELECT PHS.PROPOSAL, PHS.SHOP, PST.STATUS_CODE, PST.STATUS_DATE, PST.LOGIN, PRO.SHOP_PERSON, (SELECT Max(ST.STATUS_DATE) FROM ROOT_AE_P_PST_E As ST WHERE ST.PROPOSAL = PHS.PROPOSAL AND ST.STATUS_CODE = "25-ASSGN") AS 25Date, (SELECT Max(ST.STATUS_DATE) FROM ROOT_AE_P_PST_E As ST WHERE ST.PROPOSAL = PHS.PROPOSAL AND ST.STATUS_CODE = "70-WRKCOMP") AS 70Date, [25Date][70Date] AS Expr1 FROM (ROOT_AE_P_PHS_E AS PHS INNER JOIN ROOT_AE_P_PST_E AS PST ON PHS.PROPOSAL = PST.PROPOSAL) INNER JOIN ROOT_AE_P_PRO_S AS PRO ON (PHS.PROPOSAL = PRO.PROPOSAL) AND (PHS.SORT_CODE = PRO.SORT_CODE) WHERE (((PHS.SHOP)="56") AND ((PST.STATUS_CODE)="70-WRKCOMP" Or (PST.STATUS_CODE)="25-ASSGN") AND ((PST.STATUS_DATE) Between Date()-30 And Date()+1)); First...are these simply 3 tables in an one mdb backend? -- not tables or views in SQL Server, Oracle, MySQL, IBM DB -- not queries If so, what fields are indexed? PHS -- PROPOSAL -- SORT_CODE -- SHOP PST -- PROPOSAL -- STATUS_DATE -- STATUS_CODE PRO -- PROPOSAL -- SORT_CODE At the least, PROPOSAL should be indexed in all 3 tables. Indexes can be "expensive," but optimally all fields above should be indexed depending upon your data. If these are mdb tables and the fields are optimally indexed, and performance is still slow,then the next strategy would be to rethink the subqueries. Correlated subqueries (where we found [25Date] and [70Date]) can affect performance. Often it helps to "divide-and-conquer." How do these 2 queries perform? qry25Date SELECT ST.PROPOSAL, Max(ST.STATUS_DATE) As Max25Date FROM ROOT_AE_P_PST_E As ST WHERE ST.STATUS_CODE = "25-ASSGN" GROUP BY ST.PROPOSAL; qry70Date SELECT ST.PROPOSAL, Max(ST.STATUS_DATE) As Max70Date FROM ROOT_AE_P_PST_E As ST WHERE ST.STATUS_CODE = "70-WRKCOMP" GROUP BY ST.PROPOSAL; If acceptable, then get rid of correlated queries, and you could try just adding these 2 queries to your query design...or, you could "divide" even further... qryProposalsOpenedBackOpen SELECT q1.PROPOSAL, q1.Max25Date, q2.Max70Date, FROM qry25Date As q1 INNER JOIN qry70Date As q2 ON q1.PROPOSAL = q2.PROPOSAL WHERE q1.[Max25Date] q2.[Max70Date]; That ought to slice-and-dice down to some good performance I would think. How does it perform? Do you think you get the correct records? I really just guessed that you will only be concerned with the max dates for each PROPOSAL. Further "divide" might involve bringing only specific SHOP to the final query... qrySHOP SELECT DISTINCT PHS.PROPOSAL, PHS.SHOP, PRO.SHOP_PERSON FROM ROOT_AE_P_PHS_E AS PHS INNER JOIN ROOT_AE_P_PRO_S AS PRO ON (PHS.PROPOSAL = PRO.PROPOSAL) AND (PHS.SORT_CODE = PRO.SORT_CODE) WHERE PHS.SHOP ="56"; How does this perform? I am not sure about your data so maybe the "DISTINCT" is not necessary? For the final query, it makes sense to me that this query bring a single record for each distinct PROPOSAL/SHOP/SHOP_PERSON. I could be wrong... So...your final query... It appears you want to return PHS.PROPOSAL, --- qryProposalsOpenedBackOpen As qP PHS.SHOP, --- qrySHOP As qS PST.STATUS_CODE, --- ROOT_AE_P_PST_E AS PST PST.STATUS_DATE, --- ROOT_AE_P_PST_E AS PST PST.LOGIN, --- ROOT_AE_P_PST_E AS PST PRO.SHOP_PERSON --- qrySHOP As qS Start a new query and add qryProposalsOpenedBackOpen qryShop ROOT_AE_P_PST_E Right-mouse click on the table qryProposalsOpenedBackOpen, choose "Properties," and set "Alias" to "qP" Right-mouse click on the table qrySHOP, choose "Properties," and set "Alias" to "qS" Right-mouse click on the table ROOT_AE_P_PST_E, choose "Properties," and set "Alias" to "PST" Join qP.PROPOSAL to qS.PROPOSAL Join qP.PROPOSAL to PST.PROPOSAL Double-click on the fields in the tables that you want (as referenced above) to send them down to the grid. Save the query. We've indexed, divided-and-conquered, and sliced-and-diced.... If that does not help (and if I did not misunderstand), I am out of ideas for the moment... |
#9
|
|||
|
|||
Help with criteria
"Paul B" wrote: Gary, first off thanks for the help, I think I am getting in deep here, not to good with Access, the data is coming from a linked table from an ODBC data base that I have no control over, just trying to get some data out of it. I have a lot more experience with excel and have managed to import the data into Excel and get the information I need with a few helper columns and some formulas, but I would like to keep it all in Access, if I can, so I will try and follow the instructions you gave and see it I can get it to work that way. It will be Monday before I can try it out at work. Are *all 3 tables* linked tables from an ODBC database? If so...it may then help to just get simple data from them, pump it into an Access table, then run a query on this table to sort out "[25Date][70Date]." Start with the following query: SELECT PHS.PROPOSAL, PHS.SHOP, PST.STATUS_CODE, PST.STATUS_DATE, PST.LOGIN, PRO.SHOP_PERSON FROM (ROOT_AE_P_PHS_E AS PHS INNER JOIN ROOT_AE_P_PST_E AS PST ON PHS.PROPOSAL = PST.PROPOSAL) INNER JOIN ROOT_AE_P_PRO_S AS PRO ON (PHS.PROPOSAL = PRO.PROPOSAL) AND (PHS.SORT_CODE = PRO.SORT_CODE) WHERE (PHS.SHOP = "56") AND ((PST.STATUS_CODE = "70-WRKCOMP") OR (PST.STATUS_CODE = "25-ASSGN")) AND (PST.STATUS_DATE Between Date()-30 And Date()+1); How does that perform suitably? If so, then change it into a make table query. In top menu, click on Query/Make Table. When it asks for table name, type in something like "tblCurrentData" Click on the red exclamation mark to run it. Does that perform adequately? If so, change it to an append query. Save it as something like "qryapptblCurrentData" Then, test "divide-and-conquer" on tblCurrentData. qry25Date SELECT T.PROPOSAL, Max(T.STATUS_DATE) As Max25Date FROM tblCurrentData As T WHERE T.STATUS_CODE = "25-ASSGN" GROUP BY T.PROPOSAL; qry70Date SELECT T.PROPOSAL, Max(T.STATUS_DATE) As Max70Date FROM tblCurrentData As T WHERE T.STATUS_CODE = "70-WRKCOMP" GROUP BY T.PROPOSAL; qryProposalsOpenedBackOpen SELECT q1.PROPOSAL, q1.Max25Date, q2.Max70Date, FROM qry25Date As q1 INNER JOIN qry70Date As q2 ON q1.PROPOSAL = q2.PROPOSAL WHERE q1.[Max25Date] q2.[Max70Date]; These should zip. All that is left is the final query... Start a new query and add qryProposalsOpenedBackOpen tblCurrentData Right-mouse click on the table qryProposalsOpenedBackOpen, choose "Properties," and set "Alias" to "qP" Right-mouse click on the table tblCurrentData, choose "Properties," and set "Alias" to "T" Join qP.PROPOSAL to T.PROPOSAL Double-click on the fields in the tables that you want to send them down to the grid. Save the query. ////////////////////////////////// In code you can rewrite the SQL of "qryapptblCurrentData" for a different SHOP and/or time frame. Then empty "tblCurrentData" Run "qryapptblCurrentData" Then open report based on your final query. I can imagine a form where user sets SHOP and/or time frame. A command button on the form does the above in its Click Event code. |
#10
|
|||
|
|||
Help with criteria
Gary, Yes all 3 tables are linked tables from an ODBC database, I will try
what you have posted on Monday, Thanks -- Paul B "Gary Walter" wrote in message ... "Paul B" wrote: Gary, first off thanks for the help, I think I am getting in deep here, not to good with Access, the data is coming from a linked table from an ODBC data base that I have no control over, just trying to get some data out of it. I have a lot more experience with excel and have managed to import the data into Excel and get the information I need with a few helper columns and some formulas, but I would like to keep it all in Access, if I can, so I will try and follow the instructions you gave and see it I can get it to work that way. It will be Monday before I can try it out at work. Are *all 3 tables* linked tables from an ODBC database? If so...it may then help to just get simple data from them, pump it into an Access table, then run a query on this table to sort out "[25Date][70Date]." Start with the following query: SELECT PHS.PROPOSAL, PHS.SHOP, PST.STATUS_CODE, PST.STATUS_DATE, PST.LOGIN, PRO.SHOP_PERSON FROM (ROOT_AE_P_PHS_E AS PHS INNER JOIN ROOT_AE_P_PST_E AS PST ON PHS.PROPOSAL = PST.PROPOSAL) INNER JOIN ROOT_AE_P_PRO_S AS PRO ON (PHS.PROPOSAL = PRO.PROPOSAL) AND (PHS.SORT_CODE = PRO.SORT_CODE) WHERE (PHS.SHOP = "56") AND ((PST.STATUS_CODE = "70-WRKCOMP") OR (PST.STATUS_CODE = "25-ASSGN")) AND (PST.STATUS_DATE Between Date()-30 And Date()+1); How does that perform suitably? If so, then change it into a make table query. In top menu, click on Query/Make Table. When it asks for table name, type in something like "tblCurrentData" Click on the red exclamation mark to run it. Does that perform adequately? If so, change it to an append query. Save it as something like "qryapptblCurrentData" Then, test "divide-and-conquer" on tblCurrentData. qry25Date SELECT T.PROPOSAL, Max(T.STATUS_DATE) As Max25Date FROM tblCurrentData As T WHERE T.STATUS_CODE = "25-ASSGN" GROUP BY T.PROPOSAL; qry70Date SELECT T.PROPOSAL, Max(T.STATUS_DATE) As Max70Date FROM tblCurrentData As T WHERE T.STATUS_CODE = "70-WRKCOMP" GROUP BY T.PROPOSAL; qryProposalsOpenedBackOpen SELECT q1.PROPOSAL, q1.Max25Date, q2.Max70Date, FROM qry25Date As q1 INNER JOIN qry70Date As q2 ON q1.PROPOSAL = q2.PROPOSAL WHERE q1.[Max25Date] q2.[Max70Date]; These should zip. All that is left is the final query... Start a new query and add qryProposalsOpenedBackOpen tblCurrentData Right-mouse click on the table qryProposalsOpenedBackOpen, choose "Properties," and set "Alias" to "qP" Right-mouse click on the table tblCurrentData, choose "Properties," and set "Alias" to "T" Join qP.PROPOSAL to T.PROPOSAL Double-click on the fields in the tables that you want to send them down to the grid. Save the query. ////////////////////////////////// In code you can rewrite the SQL of "qryapptblCurrentData" for a different SHOP and/or time frame. Then empty "tblCurrentData" Run "qryapptblCurrentData" Then open report based on your final query. I can imagine a form where user sets SHOP and/or time frame. A command button on the form does the above in its Click Event code. |
Thread Tools | |
Display Modes | |
|
|