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
|
|||
|
|||
Ranking query
I have a query that assigns row numbers to the recordset returned by another
query: SELECT Insp1.RepairDate, Insp1.DetailID, Insp1.UnitID (SELECT Count(*) FROM qryRepairs AS Insp2 WHERE Insp2.RepairDate = Insp1.RepairDate AND (Insp2.DetailID = Insp1.DetailID OR Insp2.RepairDate Insp1.RepairDate)) AS ListOrder FROM qryRepairs AS Insp1; This works up to a point, which is that I want the count to start over when there is a new I_UnitID value. This is what I get: ListOrder UnitID RepairDate DetailID 1 29 4/14/2008 42 2 29 4/16/2008 18 3 29 4/16/2008 19 4 30 4/16/2008 39 5 30 4/16/2008 40 However, I want ListOrder to start over at 1 with UnitID 30. This is an abridged version of the SQL, but the idea is that this shows a repair history for an individual piece of equipment (UnitID). One table lists types of equipment (e.g. clamping fixture), and a related table lists individual equipment items (Fixture 1, Fixture 2, etc.). These are brought together, along with repair records for individual equipment items, into qryRepairs. At the form level, the user navigates from one individual equipment record to another. At each record a subform displays the repairs that have been performed. For Fixture 1 (UnitID 29) there are three items on the list, numberd 1, 2, and 3. At the next record (for Fixture 2) there are two items on the list. They should be numbered 1 and 2, not 4 and 5. One way to do this, I suppose, is to load the SQL at run time (in the main form's Current event?) as I move to each record, so that it includes only one UnitID. I don't know if there is a performance hit by doing it this way (there could be tens of thousands of records eventually). I have not been able to discover a way to restart the numbering using SQL. A related question is whether there are any considerations one way or the other to using a named query in another query. I could replace the name of the query with the query's SQL in the example above, if there is a reason for doing so. |
#2
|
|||
|
|||
Ranking query
Bruce,
Try this. This uses a non equi-join, so you can only complete it in the SQL view. I generally set it up in the design view with equi-joins (which should result in all the ListOrders = 1. Then, jump over to the SQL view and modify the second join, which should get you what you are looking for. SELECT Count(I2.*) as ListOrder, I1.UnitID, I1.RepairDate, I1.DetailID FROM qryRepairs as I1 INNER JOIN qryReparis as I2 ON I2.UnitID = I1.UnitID AND I2.RepairDate = I1.RepairDate GROUP BY I1.UnitID, I1.RepairDate, I1.DetailID ORDER BY I1.UnitID, Count(I2.*) HTH Dale -- Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "BruceM" wrote: I have a query that assigns row numbers to the recordset returned by another query: SELECT Insp1.RepairDate, Insp1.DetailID, Insp1.UnitID (SELECT Count(*) FROM qryRepairs AS Insp2 WHERE Insp2.RepairDate = Insp1.RepairDate AND (Insp2.DetailID = Insp1.DetailID OR Insp2.RepairDate Insp1.RepairDate)) AS ListOrder FROM qryRepairs AS Insp1; This works up to a point, which is that I want the count to start over when there is a new I_UnitID value. This is what I get: ListOrder UnitID RepairDate DetailID 1 29 4/14/2008 42 2 29 4/16/2008 18 3 29 4/16/2008 19 4 30 4/16/2008 39 5 30 4/16/2008 40 However, I want ListOrder to start over at 1 with UnitID 30. This is an abridged version of the SQL, but the idea is that this shows a repair history for an individual piece of equipment (UnitID). One table lists types of equipment (e.g. clamping fixture), and a related table lists individual equipment items (Fixture 1, Fixture 2, etc.). These are brought together, along with repair records for individual equipment items, into qryRepairs. At the form level, the user navigates from one individual equipment record to another. At each record a subform displays the repairs that have been performed. For Fixture 1 (UnitID 29) there are three items on the list, numberd 1, 2, and 3. At the next record (for Fixture 2) there are two items on the list. They should be numbered 1 and 2, not 4 and 5. One way to do this, I suppose, is to load the SQL at run time (in the main form's Current event?) as I move to each record, so that it includes only one UnitID. I don't know if there is a performance hit by doing it this way (there could be tens of thousands of records eventually). I have not been able to discover a way to restart the numbering using SQL. A related question is whether there are any considerations one way or the other to using a named query in another query. I could replace the name of the query with the query's SQL in the example above, if there is a reason for doing so. |
#3
|
|||
|
|||
Ranking query
Thanks for the suggestion. As it turned out it did not do what I needed in
that the order became 1, 3, 3, 1. However, it got me going in a different direction than I had tried before, and I ended up with: SELECT Count(*) AS ListOrder, I1.UnitID, I1.RepairDate, I1.DetailID FROM qryRepairs AS I1 INNER JOIN qryRepairs AS I2 ON I2.UnitID = I1.UnitID WHERE (((I2.DetailID)= I1.DetailID) AND ((I2.RepairDate)= I1.RepairDate)) OR (((I2.RepairDate)=I1.RepairDate) AND ((I2.RepairDate)I1.RepairDate)) GROUP BY I1.UnitID, I1.RepairDate, I1.DetailID ORDER BY I1.UnitID, Count(*); This gives me the desired result, and incidentally it can be viewed in design view. I'm not exactly sure what you mean by non equi-join, but it is certainly true that it cannot be viewed in design view. I see that the inner join in your suggestion has an AND component, and that it is non-equal, so I expect that's what you mean, but I have to admit I don't see what it does. "Dale Fye" wrote in message ... Bruce, Try this. This uses a non equi-join, so you can only complete it in the SQL view. I generally set it up in the design view with equi-joins (which should result in all the ListOrders = 1. Then, jump over to the SQL view and modify the second join, which should get you what you are looking for. SELECT Count(I2.*) as ListOrder, I1.UnitID, I1.RepairDate, I1.DetailID FROM qryRepairs as I1 INNER JOIN qryReparis as I2 ON I2.UnitID = I1.UnitID AND I2.RepairDate = I1.RepairDate GROUP BY I1.UnitID, I1.RepairDate, I1.DetailID ORDER BY I1.UnitID, Count(I2.*) HTH Dale -- Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "BruceM" wrote: I have a query that assigns row numbers to the recordset returned by another query: SELECT Insp1.RepairDate, Insp1.DetailID, Insp1.UnitID (SELECT Count(*) FROM qryRepairs AS Insp2 WHERE Insp2.RepairDate = Insp1.RepairDate AND (Insp2.DetailID = Insp1.DetailID OR Insp2.RepairDate Insp1.RepairDate)) AS ListOrder FROM qryRepairs AS Insp1; This works up to a point, which is that I want the count to start over when there is a new I_UnitID value. This is what I get: ListOrder UnitID RepairDate DetailID 1 29 4/14/2008 42 2 29 4/16/2008 18 3 29 4/16/2008 19 4 30 4/16/2008 39 5 30 4/16/2008 40 However, I want ListOrder to start over at 1 with UnitID 30. This is an abridged version of the SQL, but the idea is that this shows a repair history for an individual piece of equipment (UnitID). One table lists types of equipment (e.g. clamping fixture), and a related table lists individual equipment items (Fixture 1, Fixture 2, etc.). These are brought together, along with repair records for individual equipment items, into qryRepairs. At the form level, the user navigates from one individual equipment record to another. At each record a subform displays the repairs that have been performed. For Fixture 1 (UnitID 29) there are three items on the list, numberd 1, 2, and 3. At the next record (for Fixture 2) there are two items on the list. They should be numbered 1 and 2, not 4 and 5. One way to do this, I suppose, is to load the SQL at run time (in the main form's Current event?) as I move to each record, so that it includes only one UnitID. I don't know if there is a performance hit by doing it this way (there could be tens of thousands of records eventually). I have not been able to discover a way to restart the numbering using SQL. A related question is whether there are any considerations one way or the other to using a named query in another query. I could replace the name of the query with the query's SQL in the example above, if there is a reason for doing so. |
#4
|
|||
|
|||
Ranking query
Based on your comments, and my reanalysis of your data, I should have joined
on UnitID and DetailID (instead of RepairDate), something like: ON T2.UnitID = T1.UnitID AND T2.UnitID = T1.UnitID Let me give you an example. Suppose you have a table that contains the values 1-10, but what you want is a qruery to list for each item in that table, all of the numbers less than or equal to it. Now you could do this as: Select T1.intNumber, T2.intNumber FROM yourTable as T1, yourTable as T2 WHERE T2.IntNumber = T1.intNumber ORDER BY T1.IntNumber, T2.intNumber You could also do this as: Select T1.intNumber, T2.intNumber FROM yourTable as T1 LEFT JOIN yourTable as T2 ON T2.intNumber = T2.intNumber ORDER BY T1.intNumber, T2.intNumber Now, if you wanted a count of the number of numbers in your table, that were less than or equal to a number, you might use: SELECT T1.intNumber, (SELECT Count(*) FROM yourTable WHERE intNumber = T1.intNumber) as FreqCount FROM yourTable as T1 But with this method, you have to run the subquery for each record in T1. Another way to do this would be: SELECT T1.intNumber, Count(T2.intNumber) as FreqCount FROM yourTable as T1 INNER JOIN yourTable as T2 ON T2.intNumber = T1.intNumber Group BY T1.intNumber In my admittedly limited testing, I've found that this latter query generally runs quicker. HTH Dale -- HTH Dale Don''''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "BruceM" wrote: Thanks for the suggestion. As it turned out it did not do what I needed in that the order became 1, 3, 3, 1. However, it got me going in a different direction than I had tried before, and I ended up with: SELECT Count(*) AS ListOrder, I1.UnitID, I1.RepairDate, I1.DetailID FROM qryRepairs AS I1 INNER JOIN qryRepairs AS I2 ON I2.UnitID = I1.UnitID WHERE (((I2.DetailID)= I1.DetailID) AND ((I2.RepairDate)= I1.RepairDate)) OR (((I2.RepairDate)=I1.RepairDate) AND ((I2.RepairDate)I1.RepairDate)) GROUP BY I1.UnitID, I1.RepairDate, I1.DetailID ORDER BY I1.UnitID, Count(*); This gives me the desired result, and incidentally it can be viewed in design view. I'm not exactly sure what you mean by non equi-join, but it is certainly true that it cannot be viewed in design view. I see that the inner join in your suggestion has an AND component, and that it is non-equal, so I expect that's what you mean, but I have to admit I don't see what it does. "Dale Fye" wrote in message ... Bruce, Try this. This uses a non equi-join, so you can only complete it in the SQL view. I generally set it up in the design view with equi-joins (which should result in all the ListOrders = 1. Then, jump over to the SQL view and modify the second join, which should get you what you are looking for. SELECT Count(I2.*) as ListOrder, I1.UnitID, I1.RepairDate, I1.DetailID FROM qryRepairs as I1 INNER JOIN qryReparis as I2 ON I2.UnitID = I1.UnitID AND I2.RepairDate = I1.RepairDate GROUP BY I1.UnitID, I1.RepairDate, I1.DetailID ORDER BY I1.UnitID, Count(I2.*) HTH Dale -- Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "BruceM" wrote: I have a query that assigns row numbers to the recordset returned by another query: SELECT Insp1.RepairDate, Insp1.DetailID, Insp1.UnitID (SELECT Count(*) FROM qryRepairs AS Insp2 WHERE Insp2.RepairDate = Insp1.RepairDate AND (Insp2.DetailID = Insp1.DetailID OR Insp2.RepairDate Insp1.RepairDate)) AS ListOrder FROM qryRepairs AS Insp1; This works up to a point, which is that I want the count to start over when there is a new I_UnitID value. This is what I get: ListOrder UnitID RepairDate DetailID 1 29 4/14/2008 42 2 29 4/16/2008 18 3 29 4/16/2008 19 4 30 4/16/2008 39 5 30 4/16/2008 40 However, I want ListOrder to start over at 1 with UnitID 30. This is an abridged version of the SQL, but the idea is that this shows a repair history for an individual piece of equipment (UnitID). One table lists types of equipment (e.g. clamping fixture), and a related table lists individual equipment items (Fixture 1, Fixture 2, etc.). These are brought together, along with repair records for individual equipment items, into qryRepairs. At the form level, the user navigates from one individual equipment record to another. At each record a subform displays the repairs that have been performed. For Fixture 1 (UnitID 29) there are three items on the list, numberd 1, 2, and 3. At the next record (for Fixture 2) there are two items on the list. They should be numbered 1 and 2, not 4 and 5. One way to do this, I suppose, is to load the SQL at run time (in the main form's Current event?) as I move to each record, so that it includes only one UnitID. I don't know if there is a performance hit by doing it this way (there could be tens of thousands of records eventually). I have not been able to discover a way to restart the numbering using SQL. A related question is whether there are any considerations one way or the other to using a named query in another query. I could replace the name of the query with the query's SQL in the example above, if there is a reason for doing so. |
#5
|
|||
|
|||
Ranking query
Thanks again for the information. I did some experiments, and saw how the
various queries handle the data. One thing I'm certainly seeing is that there may be several options for solving any problem. The observation about the subquery running for each record is something to keep in mind. I expect that limiting the recordset on which the subquery operates is the way to keep that from getting bogged down when a subquery is needed. In any case, I expect that using a named query (as opposed to its SQL) will be OK. I see that a non equi-join is something such as you demonstrated in your last example (and elsewhere) in which the join is on something other than Equal To. I'll have to keep an eye out for that. I can imagine places where it will be useful. Thanks again for your input and suggestions. "Dale Fye" wrote in message ... Based on your comments, and my reanalysis of your data, I should have joined on UnitID and DetailID (instead of RepairDate), something like: ON T2.UnitID = T1.UnitID AND T2.UnitID = T1.UnitID Let me give you an example. Suppose you have a table that contains the values 1-10, but what you want is a qruery to list for each item in that table, all of the numbers less than or equal to it. Now you could do this as: Select T1.intNumber, T2.intNumber FROM yourTable as T1, yourTable as T2 WHERE T2.IntNumber = T1.intNumber ORDER BY T1.IntNumber, T2.intNumber You could also do this as: Select T1.intNumber, T2.intNumber FROM yourTable as T1 LEFT JOIN yourTable as T2 ON T2.intNumber = T2.intNumber ORDER BY T1.intNumber, T2.intNumber Now, if you wanted a count of the number of numbers in your table, that were less than or equal to a number, you might use: SELECT T1.intNumber, (SELECT Count(*) FROM yourTable WHERE intNumber = T1.intNumber) as FreqCount FROM yourTable as T1 But with this method, you have to run the subquery for each record in T1. Another way to do this would be: SELECT T1.intNumber, Count(T2.intNumber) as FreqCount FROM yourTable as T1 INNER JOIN yourTable as T2 ON T2.intNumber = T1.intNumber Group BY T1.intNumber In my admittedly limited testing, I've found that this latter query generally runs quicker. HTH Dale -- HTH Dale Don''''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "BruceM" wrote: Thanks for the suggestion. As it turned out it did not do what I needed in that the order became 1, 3, 3, 1. However, it got me going in a different direction than I had tried before, and I ended up with: SELECT Count(*) AS ListOrder, I1.UnitID, I1.RepairDate, I1.DetailID FROM qryRepairs AS I1 INNER JOIN qryRepairs AS I2 ON I2.UnitID = I1.UnitID WHERE (((I2.DetailID)= I1.DetailID) AND ((I2.RepairDate)= I1.RepairDate)) OR (((I2.RepairDate)=I1.RepairDate) AND ((I2.RepairDate)I1.RepairDate)) GROUP BY I1.UnitID, I1.RepairDate, I1.DetailID ORDER BY I1.UnitID, Count(*); This gives me the desired result, and incidentally it can be viewed in design view. I'm not exactly sure what you mean by non equi-join, but it is certainly true that it cannot be viewed in design view. I see that the inner join in your suggestion has an AND component, and that it is non-equal, so I expect that's what you mean, but I have to admit I don't see what it does. "Dale Fye" wrote in message ... Bruce, Try this. This uses a non equi-join, so you can only complete it in the SQL view. I generally set it up in the design view with equi-joins (which should result in all the ListOrders = 1. Then, jump over to the SQL view and modify the second join, which should get you what you are looking for. SELECT Count(I2.*) as ListOrder, I1.UnitID, I1.RepairDate, I1.DetailID FROM qryRepairs as I1 INNER JOIN qryReparis as I2 ON I2.UnitID = I1.UnitID AND I2.RepairDate = I1.RepairDate GROUP BY I1.UnitID, I1.RepairDate, I1.DetailID ORDER BY I1.UnitID, Count(I2.*) HTH Dale -- Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "BruceM" wrote: I have a query that assigns row numbers to the recordset returned by another query: SELECT Insp1.RepairDate, Insp1.DetailID, Insp1.UnitID (SELECT Count(*) FROM qryRepairs AS Insp2 WHERE Insp2.RepairDate = Insp1.RepairDate AND (Insp2.DetailID = Insp1.DetailID OR Insp2.RepairDate Insp1.RepairDate)) AS ListOrder FROM qryRepairs AS Insp1; This works up to a point, which is that I want the count to start over when there is a new I_UnitID value. This is what I get: ListOrder UnitID RepairDate DetailID 1 29 4/14/2008 42 2 29 4/16/2008 18 3 29 4/16/2008 19 4 30 4/16/2008 39 5 30 4/16/2008 40 However, I want ListOrder to start over at 1 with UnitID 30. This is an abridged version of the SQL, but the idea is that this shows a repair history for an individual piece of equipment (UnitID). One table lists types of equipment (e.g. clamping fixture), and a related table lists individual equipment items (Fixture 1, Fixture 2, etc.). These are brought together, along with repair records for individual equipment items, into qryRepairs. At the form level, the user navigates from one individual equipment record to another. At each record a subform displays the repairs that have been performed. For Fixture 1 (UnitID 29) there are three items on the list, numberd 1, 2, and 3. At the next record (for Fixture 2) there are two items on the list. They should be numbered 1 and 2, not 4 and 5. One way to do this, I suppose, is to load the SQL at run time (in the main form's Current event?) as I move to each record, so that it includes only one UnitID. I don't know if there is a performance hit by doing it this way (there could be tens of thousands of records eventually). I have not been able to discover a way to restart the numbering using SQL. A related question is whether there are any considerations one way or the other to using a named query in another query. I could replace the name of the query with the query's SQL in the example above, if there is a reason for doing so. |
#6
|
|||
|
|||
Ranking query
Another place where it is useful is when you want to map a value in one table
to a range of values in another table. SELECT Students.Name, Students.GradePct, Grades.Grade FROM Students LEFT JOIN Grades ON Students.GradePct = Grades.MinPct AND Students.GradePct Grades.MaxPct This would give you the letter grade associated with each student. -- HTH Dale Don''''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "BruceM" wrote: Thanks again for the information. I did some experiments, and saw how the various queries handle the data. One thing I'm certainly seeing is that there may be several options for solving any problem. The observation about the subquery running for each record is something to keep in mind. I expect that limiting the recordset on which the subquery operates is the way to keep that from getting bogged down when a subquery is needed. In any case, I expect that using a named query (as opposed to its SQL) will be OK. I see that a non equi-join is something such as you demonstrated in your last example (and elsewhere) in which the join is on something other than Equal To. I'll have to keep an eye out for that. I can imagine places where it will be useful. Thanks again for your input and suggestions. "Dale Fye" wrote in message ... Based on your comments, and my reanalysis of your data, I should have joined on UnitID and DetailID (instead of RepairDate), something like: ON T2.UnitID = T1.UnitID AND T2.UnitID = T1.UnitID Let me give you an example. Suppose you have a table that contains the values 1-10, but what you want is a qruery to list for each item in that table, all of the numbers less than or equal to it. Now you could do this as: Select T1.intNumber, T2.intNumber FROM yourTable as T1, yourTable as T2 WHERE T2.IntNumber = T1.intNumber ORDER BY T1.IntNumber, T2.intNumber You could also do this as: Select T1.intNumber, T2.intNumber FROM yourTable as T1 LEFT JOIN yourTable as T2 ON T2.intNumber = T2.intNumber ORDER BY T1.intNumber, T2.intNumber Now, if you wanted a count of the number of numbers in your table, that were less than or equal to a number, you might use: SELECT T1.intNumber, (SELECT Count(*) FROM yourTable WHERE intNumber = T1.intNumber) as FreqCount FROM yourTable as T1 But with this method, you have to run the subquery for each record in T1. Another way to do this would be: SELECT T1.intNumber, Count(T2.intNumber) as FreqCount FROM yourTable as T1 INNER JOIN yourTable as T2 ON T2.intNumber = T1.intNumber Group BY T1.intNumber In my admittedly limited testing, I've found that this latter query generally runs quicker. HTH Dale -- HTH Dale Don''''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "BruceM" wrote: Thanks for the suggestion. As it turned out it did not do what I needed in that the order became 1, 3, 3, 1. However, it got me going in a different direction than I had tried before, and I ended up with: SELECT Count(*) AS ListOrder, I1.UnitID, I1.RepairDate, I1.DetailID FROM qryRepairs AS I1 INNER JOIN qryRepairs AS I2 ON I2.UnitID = I1.UnitID WHERE (((I2.DetailID)= I1.DetailID) AND ((I2.RepairDate)= I1.RepairDate)) OR (((I2.RepairDate)=I1.RepairDate) AND ((I2.RepairDate)I1.RepairDate)) GROUP BY I1.UnitID, I1.RepairDate, I1.DetailID ORDER BY I1.UnitID, Count(*); This gives me the desired result, and incidentally it can be viewed in design view. I'm not exactly sure what you mean by non equi-join, but it is certainly true that it cannot be viewed in design view. I see that the inner join in your suggestion has an AND component, and that it is non-equal, so I expect that's what you mean, but I have to admit I don't see what it does. "Dale Fye" wrote in message ... Bruce, Try this. This uses a non equi-join, so you can only complete it in the SQL view. I generally set it up in the design view with equi-joins (which should result in all the ListOrders = 1. Then, jump over to the SQL view and modify the second join, which should get you what you are looking for. SELECT Count(I2.*) as ListOrder, I1.UnitID, I1.RepairDate, I1.DetailID FROM qryRepairs as I1 INNER JOIN qryReparis as I2 ON I2.UnitID = I1.UnitID AND I2.RepairDate = I1.RepairDate GROUP BY I1.UnitID, I1.RepairDate, I1.DetailID ORDER BY I1.UnitID, Count(I2.*) HTH Dale -- Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "BruceM" wrote: I have a query that assigns row numbers to the recordset returned by another query: SELECT Insp1.RepairDate, Insp1.DetailID, Insp1.UnitID (SELECT Count(*) FROM qryRepairs AS Insp2 WHERE Insp2.RepairDate = Insp1.RepairDate AND (Insp2.DetailID = Insp1.DetailID OR Insp2.RepairDate Insp1.RepairDate)) AS ListOrder FROM qryRepairs AS Insp1; This works up to a point, which is that I want the count to start over when there is a new I_UnitID value. This is what I get: ListOrder UnitID RepairDate DetailID 1 29 4/14/2008 42 2 29 4/16/2008 18 3 29 4/16/2008 19 4 30 4/16/2008 39 5 30 4/16/2008 40 However, I want ListOrder to start over at 1 with UnitID 30. This is an abridged version of the SQL, but the idea is that this shows a repair history for an individual piece of equipment (UnitID). One table lists types of equipment (e.g. clamping fixture), and a related table lists individual equipment items (Fixture 1, Fixture 2, etc.). These are brought together, along with repair records for individual equipment items, into qryRepairs. At the form level, the user navigates from one individual equipment record to another. At each record a subform displays the repairs that have been performed. For Fixture 1 (UnitID 29) there are three items on the list, numberd 1, 2, and 3. At the next record (for Fixture 2) there are two items on the list. They should be numbered 1 and 2, not 4 and 5. One way to do this, I suppose, is to load the SQL at run time (in the main form's Current event?) as I move to each record, so that it includes only one UnitID. I don't know if there is a performance hit by doing it this way (there could be tens of thousands of records eventually). I have not been able to discover a way to restart the numbering using SQL. A related question is whether there are any considerations one way or the other to using a named query in another query. I could replace the name of the query with the query's SQL in the example above, if there is a reason for doing so. |
#7
|
|||
|
|||
Ranking query
I've gotten this to work for me, but the sequence is reversed. The entry with
the highest # primary key is listed 1st, then down to the oldest. What have I done wrong? SELECT FormalCharges.UniqueID, Count(FormalCharges_1.UniqueID) AS Sequence, FormalCharges.DefendantID FROM FormalCharges INNER JOIN FormalCharges AS FormalCharges_1 ON (FormalCharges.DefendantID = FormalCharges_1.DefendantID) AND (FormalCharges.UniqueID = FormalCharges_1.UniqueID) GROUP BY FormalCharges.UniqueID, FormalCharges.DefendantID HAVING (((FormalCharges.DefendantID)=[Forms]![AddForm]![DefendantID])) ORDER BY FormalCharges.UniqueID; |
#8
|
|||
|
|||
Ranking query
Try changing = to =
SELECT FormalCharges.UniqueID, Count(FormalCharges_1.UniqueID) AS Sequence, FormalCharges.DefendantID FROM FormalCharges INNER JOIN FormalCharges AS FormalCharges_1 ON (FormalCharges.DefendantID = FormalCharges_1.DefendantID) AND (FormalCharges.UniqueID = FormalCharges_1.UniqueID) GROUP BY FormalCharges.UniqueID, FormalCharges.DefendantID HAVING (((FormalCharges.DefendantID)=[Forms]![AddForm]![DefendantID])) ORDER BY FormalCharges.UniqueID; '================================================= === John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County '================================================= === Jim Murray wrote: I've gotten this to work for me, but the sequence is reversed. The entry with the highest # primary key is listed 1st, then down to the oldest. What have I done wrong? SELECT FormalCharges.UniqueID, Count(FormalCharges_1.UniqueID) AS Sequence, FormalCharges.DefendantID FROM FormalCharges INNER JOIN FormalCharges AS FormalCharges_1 ON (FormalCharges.DefendantID = FormalCharges_1.DefendantID) AND (FormalCharges.UniqueID = FormalCharges_1.UniqueID) GROUP BY FormalCharges.UniqueID, FormalCharges.DefendantID HAVING (((FormalCharges.DefendantID)=[Forms]![AddForm]![DefendantID])) ORDER BY FormalCharges.UniqueID; |
Thread Tools | |
Display Modes | |
|
|