Search results for query: *

It looks like it works! I love you guys.....

PH I think I just figured it out, but do not know how to correct. The below table shows all the order numbers for 4 DefectNums, #1, 515, 557 and 670. EvtDefid will define an assigned to action; 1 indicates assigned to. The problem with DefectNum 557 is when we use max or order num "4" that is...

Debug step by step. 1) Is this query working as expected ? SELECT [ParentID], Max(OrderNum) AS MaxOfOrderNum FROM tbl_DefectEvtsCarrie GROUP BY [ParentID] Above works correctly, result below. ParentID MaxOfOrderNum 7 2 521 13 563 4 676 4 2) And this ? SELECT E.[ParentID], E.OrderNum...

My 2nd email today is the correct data. The query works correctly if there is more than one order number. If there is only 1, it does not show this record. Sql as above. Can you help me?

Sorry wrong data in 1st table, s/b DEFECTNUM MaxOfORDERNUM LastName 1 2 Deview 1 1 Miller 515 1 Bennett 515 12 Sandahl 515 13 Smith 515 3 Tabora...

PHV You have been such a help for me, but I still am having trouble getting the record with only one order number. Below is the raw data, note defectnum 557 has maxofordernum of 1, name Harig. DEFECTNUM MaxOfORDERNUM LastName 1 1 Miller 515 1 Bennett...

SELECT D.DefectNum, M.MaxOfOrderNum, U.LastName FROM ((tbl_DefectsCarrie D INNER JOIN tbl_DefectEvtsCarrie E ON D.[IDRecord] = E.[ParentID]) INNER JOIN ( SELECT [ParentID], Max(OrderNum) AS MaxOfOrderNum FROM tbl_DefectEvtsCarrie GROUP BY [ParentID] ) M ON E.[ParentID] = M.[ParentID] AND...

Sorry, same as field above tbl_DefectsCarrie.IDRecord.

PH Defect Order tbl_Defects Asgnd tbl_CarrieUser LastNm Num Num Carrie.IDRec User .IDRecord 515 1 521 19 19 Jones 515 2 521 37 37 Smith 515 3 521 91 91 White 557 1...

One defect num = one id record and vice versa. This is what I am trying to get to: Def Num 1 Assign to: Jones Date Assigned: 1/1/03 Def Num 1 Assign to: Smith Date Assigned: 2/23/05 Above works correctly, I get 2nd record as that is the current assigned to. If we have...

DefectNum not indexed.

Some issues have only been assigned to one person (so far). Potential is unlimited to reassign to other people. I had to make a table to dump data into to play around with, currently as one user to many issues, actually s/b many to many, this is the way the external database was set up. (They...

PH Works great if there are multiple assigned to, however, if there is only one assigned to person that is not showing. C

Will check it out, so far looks correct. I will take a look at some of the records to verify....

OK, we have 3 tables: tbl_DefectsCarrie ID Record DefectNum tbl_DefectEvtsCarrie ID Record Parent ID (linked to tbl_DefectsCarrie ID Record) OrderNum Asgnduser tbl_CarrieUser ID Record (Linked to tbl_DefectEvtsCarrie (Asgnduser) Last Name Scenario: I have one Defect Number (670 for...

lespaul: All works correctly in the response from PHV, except "WHERE D.DEFECTNUM = 670" i need it to show all defectnums. If you need more info will gladly provide...

PHV: Can you help me in looping thru records to pull data as mentioned above? Thanks Carrie

Ok, this worked for record #670. The table tbl_DefectsCarrie (or "D") has hundreds of records. I need to know if we can loop through this table, pull out for each DefectNum the highest Ordernum from table tbl_DefectEvtsCarrie ("E") and give me the corresponding Last name associated with this...

Thanks This works. You guys are great. [rockband]

Thank you very much for responding. However, I get an error message "you tried to execute a query that does not include the specified expressono "DEFECTNUM' as part of an aggregate function.