By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,478 Members | 1,526 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,478 IT Pros & Developers. It's quick & easy.

Need to show top 10 results PER ID

100+
P: 365
Hello gang, i want a query to select the top 10 Dates from results, here is the SQL so far

Expand|Select|Wrap|Line Numbers
  1. SELECT tblShiftsNew.EntryID, tblShiftsNew.StaffID, tblShiftsNew.ActDate, tblShiftsNew.ShiftID, tblShiftsNew.DayMod, tblShiftsNew.AM, tblShiftsNew.PM
  2. FROM tblShiftsNew
  3. WHERE (((tblShiftsNew.StaffID)=75) AND ((tblShiftsNew.ActDate)<=#5/3/2009#))
  4. ORDER BY tblShiftsNew.EntryID DESC;
  5.  
The date is dynamic and i would like these results for every staffid in my DB, i cant see anyway of doing it other than in VB, but this would make the activity slow because the BE is on a network share, the above query gives these results

Expand|Select|Wrap|Line Numbers
  1. EntryID    StaffID    ActDate    ShiftID    DayMod    AM    PM
  2. 735    75    03/05/2009    2    3    0    0
  3. 734    75    02/05/2009    2    2    0    0
  4. 733    75    01/05/2009    2    1    0    0
  5. 732    75    30/04/2009    2    0    0    0
  6. 731    75    12/04/2009    1    11    225    225
  7. 730    75    11/04/2009    1    10    225    225
  8. 729    75    10/04/2009    1    9    225    225
  9. 728    75    09/04/2009    1    8    225    225
  10. 727    75    08/04/2009    1    7    225    225
  11. 726    75    05/04/2009    1    4    225    225
  12. 725    75    04/04/2009    1    3    225    225
  13. 724    75    03/04/2009    1    2    225    225
  14. 723    75    02/04/2009    1    1    225    225
  15. 722    75    01/04/2009    1    0    225    225
I want to show the DayMods 0,1,2,3,4,7,8,9,10,11 with the highest dates, as mensioned this is just one set of data but i want all (the rest of the data is similar) the number of results would also depend on the number of entries and the search date used.

i would settle with a query that shows a single line (per staffid) for the DayMod with the highest date value, using MAX doesnt work because the last few fields (can) have different data ammounts eg

Expand|Select|Wrap|Line Numbers
  1. SELECT tblShiftsNew.EntryID, tblShiftsNew.StaffID, tblShiftsNew.ActDate, tblShiftsNew.ShiftID, tblShiftsNew.DayMod, tblShiftsNew.AM, tblShiftsNew.PM
  2. FROM tblShiftsNew
  3. WHERE (((tblShiftsNew.StaffID)=75) AND ((tblShiftsNew.ActDate)<=#5/3/2009#) AND ((tblShiftsNew.DayMod)=3))
  4. ORDER BY tblShiftsNew.EntryID DESC;
  5.  
Expand|Select|Wrap|Line Numbers
  1. EntryID    StaffID    ActDate    ShiftID    DayMod    AM    PM
  2. 735    75    03/05/2009    2    3    0    0
  3. 725    75    04/04/2009    1    3    225    225
I would like to run this sql from VB as an APPEND query as few times as possible (LAN BE)

Any advice would be greatly appreciated...

Thanks, Dan
Apr 21 '09 #1
Share this Question
Share on Google+
18 Replies


NeoPa
Expert Mod 15k+
P: 31,709
I can't think of a way this can be done in straightforward SQL I'm afraid Dan.
Apr 21 '09 #2

100+
P: 365
@NeoPa
I was afraid of that, mayb i could create a temp. local table,
which would still need a loop no doubt,

is it possible to question a recordset? dlookup or otherwise?
Apr 21 '09 #3

100+
P: 365
I've had another idea; maybe i could query to give the max, per date per staff per daymod and string the EntryID, then query WHERE IN() from that? that i believe would involve only 2 queries, per date, maybe even total,

im telling you all so that i can read this, post sleep, before i try in the 'morrow

any suggestions in the meantime, keep em coming

cheers 4 the inspiration BYTES/NeoPA
Apr 21 '09 #4

100+
P: 365
@Dan2kx
Question still stands...
Apr 21 '09 #5

FishVal
Expert 2.5K+
P: 2,653
Hello, Dan.

Looking at your example I've got a several silly questions:
  • Do you need to retrieve top 10 records for a single StaffID as in the example?
  • Do [DayMod] field values always appear in 0-11 blocks along ascending [AcctDate] field values?
  • And the most silly question ... Well. Will ask it later.
Apr 21 '09 #6

100+
P: 365
@fishval,
the data is shift paterns, for 1 particular instance i need the 10 most current DayMods, they relate to a persons bi-weekly shift, if a person changes hours i need to reflect the most current (inluding the old) depending on the date.

DayMod is always 1-11 because it reflects the modulus 14 of the passed date variable, which shows the bi-weekly days (no weekend data) i could do a daily loop, my form shows 14 days, but my second example details that difficulty

this query shows the basic data structure and order i hoped would accomidate "top 10" which would be a 1 shot update

and the silly question......?
Apr 22 '09 #7

Uncle Dickie
P: 67
Something like this may be possible (I have not tried this on your data!)

Expand|Select|Wrap|Line Numbers
  1. select    myRanks.*
  2. from
  3. (
  4.     Select t.EntryID
  5.         ,t.StaffID
  6.         ,t.ActDate
  7.         ,t.ShiftID
  8.         ,t.DayMod
  9.         ,t.AM
  10.         ,t.PM
  11.         ,rank() over (partition by t.DayMod order by t.ActDate desc) as dRank
  12.     from    tblShiftsNew t
  13.     where    StaffID = 75 AND ActDate <= #5/3/2009#
  14. ) myRanks
  15. where
  16. myRanks.dRank = 1
The theory being that each entry is ranked by DayMod in the sub query and then you select only those with rank = 1
Apr 22 '09 #8

Delerna
Expert 100+
P: 1,134
Does access SQL have the "Rank over partition" function?
I was under the impression that it was DB2/oracle syntax! but I could be wrong
I couldn't find anything with google searching for "ms Access rank over partition" that actually deals with Access, but I didn't look too hard!


How about something like this ?

Expand|Select|Wrap|Line Numbers
  1. Select EntryID,a.StaffID,ActDate,ShiftID,a.DayMod,AM,PM 
  2. From
  3. (   select StaffID,DayMod,Max(ActDate) as Dte 
  4.     From tblShiftsNew 
  5.     group by StaffID,DayMod
  6. )a
  7. join tblShiftsNew b on a.StaffID=b.StaffID 
  8. and a.DayMod=b.DayMod
  9. and a.Dte=b.ActDate
  10.  
Apr 22 '09 #9

Uncle Dickie
P: 67
You're probably right Delerna - I got my forums mixed - I thought I was still on the SQL one!

Hopefully your solution will work!
Apr 22 '09 #10

100+
P: 365
Unfortunately i could not get your example to work for me Delerna even with some "modding"
Apr 22 '09 #11

FishVal
Expert 2.5K+
P: 2,653
Just a thought.

Since DayMod changes from record to record in sequential manner, you may use it as a kind of "record number".
  • Query to get max ActDate per StaffID.
  • Find what DayMod value was N records ago per StaffID.
  • Query to get max EntryID per StaffID where DayMod is that N records ago per StaffID
  • Get records where EntryID per StaffID is greater than that obtained in previous point

Not sure, whether it will perform sufficiently fast, but well worth trying.
Apr 22 '09 #12

100+
P: 365
I seem to be having trouble getting it to MAX per staffID, any tips? do i use DMax rather than query Max?
Apr 22 '09 #13

100+
P: 365
Just me being silly
Expand|Select|Wrap|Line Numbers
  1. SELECT Max(tblShiftsNew.EntryID) AS MaxOfEntryID, tblShiftsNew.StaffID
  2. FROM tblShiftsNew
  3. WHERE (((tblShiftsNew.ActDate)<=#5/3/2009#))
  4. GROUP BY tblShiftsNew.StaffID;
  5.  
Apr 22 '09 #14

NeoPa
Expert Mod 15k+
P: 31,709
@Dan2kx
If, by Recordset, you mean specifically a code object defined as DAO.Recordset or ADODB.Recordset, then generally not easily.

DAO & ADODB do provide some functions whereby you can enquire of the recordset, but most functions requiring a data source that you can use with tables or QueryDefs will only take those types as parameters.

All the Domain Aggregate functions for instance (DLookup(), DMin(), DAvg(), etc) will accept only saved QueryDefs or Tables as their Domain parameters (Not even SQL strings).
Apr 22 '09 #15

100+
P: 365
i Managed to acheive my goal this is the one function that is apparent to this OP (there are many other steps before and after....)
Expand|Select|Wrap|Line Numbers
  1. Function ShiftDump(Dept As String, sdt As Date, edt As Date)
  2. Dim rst As DAO.Recordset, EID As String
  3. Dim intdays As Long
  4.     intdays = DateDiff("d", sdt, edt)
  5.     Set tbl = CurrentDb.CreateTableDef("" & LogStaffID & "tblMyDates")
  6.     With tbl
  7.         .Fields.Append .CreateField("MyDates", dbDate)
  8.         .Fields.Append .CreateField("MOD1", dbInteger)
  9.     End With
  10.     CurrentDb.TableDefs.Append tbl
  11.         For i = 0 To intdays
  12.             DoCmd.RunSQL "INSERT INTO " & LogStaffID & "tblMyDates ( MyDates, MOD1 ) SELECT #" & Format(DateAdd("d", i, sdt), "mm/dd/yyyy") & "# AS MyDates, ([MyDates]-2) Mod 14 AS MOD1;"
  13.         Next i
  14.     Set rst = CurrentDb.OpenRecordset("SELECT Max([" & LogStaffID & "tblShiftsNew].[EntryID]) AS MaxEntryID, [" & LogStaffID & "tblMyDates].MyDates FROM " & LogStaffID & "tblMyDates, [" & LogStaffID & "tblShiftsNew] INNER JOIN tblStaff ON [" & LogStaffID & "tblShiftsNew].[StaffID] = tblStaff.StaffID WHERE ((([" & LogStaffID & "tblShiftsNew].ActDate) <= " & CLng(edt) & ") And (([" & LogStaffID & "tblShiftsNew].DayMod) = [MOD1])) GROUP BY tblStaff.Department, [" & LogStaffID & "tblMyDates].MyDates, [" & LogStaffID & "tblShiftsNew].StaffID HAVING (((tblStaff.Department)='" & Dept & "'));")
  15.     If rst.RecordCount = 0 Then Exit Function
  16.         rst.MoveFirst
  17.         Do Until rst.EOF
  18.             EID = EID & rst!MaxEntryID & ","
  19.         rst.MoveNext
  20.         Loop
  21.     EID = Left(EID, (Len(EID) - 1))
  22.     DoCmd.RunSQL "SELECT [" & LogStaffID & "tblShiftsNew].StaffID, [" & LogStaffID & "tblMyDates].MyDates, IIf([AM]=0,4,0)+IIf([PM]=0,7,0) AS Val INTO " & LogStaffID & "tbl_HolidaysSL FROM " & LogStaffID & "tblMyDates, [" & LogStaffID & "tblShiftsNew] INNER JOIN tblStaff ON [" & LogStaffID & "tblShiftsNew].StaffID = tblStaff.StaffID WHERE ((([" & LogStaffID & "tblShiftsNew].DayMod) = [MOD1])) GROUP BY [" & LogStaffID & "tblShiftsNew].StaffID, [" & LogStaffID & "tblMyDates].MyDates, IIf([AM]=0,4,0)+IIf([PM]=0,7,0) HAVING (((Max([" & LogStaffID & "tblShiftsNew].EntryID)) In (" & EID & ")));"
  23.     'Crosstab 'TRANSFORM DateCount(nz(Sum([2tbl_holidaysSL.Val]),0)) AS [Count] SELECT nz([ForeName] & ' ' & [Surname]) AS Name, nz([2tbl_HolidaysSL].[StaffID]) AS StaffID, MinsLeft2(nz([2tbl_HolidaysSL].[StaffID])) AS Minutes FROM (1tblMyDates LEFT JOIN 2tbl_HolidaysSL ON [1tblMyDates].MyDates = [2tbl_HolidaysSL].MyDates) LEFT JOIN tblStaff ON [2tbl_HolidaysSL].StaffID = tblStaff.StaffID WHERE (((Nz([Deleted]))=False)) GROUP BY nz([ForeName] & ' ' & [Surname]), nz([2tbl_HolidaysSL].[StaffID]), MinsLeft2(nz([2tbl_HolidaysSL].[StaffID])), tblStaff.Surname ORDER BY tblStaff.Surname PIVOT [1tblMyDates].MyDates;
  24. End Function
  25.  
Job Done
Apr 23 '09 #16

100+
P: 365
Regarding searching recordsets (NeoPa feel free to seperate the thread) as part of this function above function i prepare some data from a linked table into a local table to make it fast, i presume this would be faster if i could use a recordset....

here is a sample...
Expand|Select|Wrap|Line Numbers
  1.     DoCmd.RunSQL "SELECT tblShifts.* INTO " & LogStaffID & "tblShifts FROM tblStaff INNER JOIN tblShifts ON tblStaff.StaffID = tblShifts.StaffID WHERE (((tblStaff.Department)='" & Dept & "'));"
  2.  
the above code pulls down a shortend version (by department) and creates a local copy... i then create a recordset from this data that iterates through staffID, as such
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT tblStaff.StaffID FROM tblStaff WHERE (((tblStaff.Department)='" & Dept & "') AND ((tblStaff.Deleted)=False));"
  2. Set rst = CurrentDb.OpenRecordset(strSQL)
  3.     Do Until rst.EOF
  4.         strSQL2 = "SELECT [" & LogStaffID & "tblShifts].* FROM " & LogStaffID & "tblShifts WHERE ((([" & LogStaffID & "tblShifts].StaffID)=" & rst!StaffID & ") AND (([" & LogStaffID & "tblShifts].WeekNo)=1));"
  5.         Set rst2 = CurrentDb.OpenRecordset(strSQL2)
  6.         strSQL3 = "SELECT [" & LogStaffID & "tblShifts].* FROM " & LogStaffID & "tblShifts WHERE ((([" & LogStaffID & "tblShifts].StaffID)=" & rst!StaffID & ") AND (([" & LogStaffID & "tblShifts].WeekNo)=2));"
  7.         Set rst3 = CurrentDb.OpenRecordset(strSQL3)
  8.             Do Until rst2.EOF
  9.                 d = Nz(rst2!ChDate, rst3!ChDate)
  10.                 For p = 0 To 13
  11.                     i = i + 1
  12.                     If i = 5 Then i = 7
  13.                     dt = d + p
  14.                     Select Case ((d - 2) + p) Mod 14
  15.                         Case 0
  16.                             DoCmd.RunSQL "INSERT INTO " & LogStaffID & "tblShiftsNew ( StaffID, ActDate, ShiftID, DayMod, AM, PM ) SELECT " & rst2!StaffID & " AS A, " & dt & " AS b, " & rst2!CurrentID & " AS c, ([b]-2) Mod 14 AS d, " & rst2!MonAM & " AS e, " & rst2!MonPM & " AS f;"
  17.                         Case 1
  18.                             DoCmd.RunSQL "INSERT INTO " & LogStaffID & "tblShiftsNew ( StaffID, ActDate, ShiftID, DayMod, AM, PM ) SELECT " & rst2!StaffID & " AS A, " & dt & " AS b, " & rst2!CurrentID & " AS c, ([b]-2) Mod 14 AS d, " & rst2!TueAM & " AS e, " & rst2!TuePM & " AS f;"
  19.                         Case 2
  20.                             DoCmd.RunSQL "INSERT INTO " & LogStaffID & "tblShiftsNew ( StaffID, ActDate, ShiftID, DayMod, AM, PM ) SELECT " & rst2!StaffID & " AS A, " & dt & " AS b, " & rst2!CurrentID & " AS c, ([b]-2) Mod 14 AS d, " & rst2!WedAM & " AS e, " & rst2!WedPM & " AS f;"
  21.                         Case 3
  22.                             DoCmd.RunSQL "INSERT INTO " & LogStaffID & "tblShiftsNew ( StaffID, ActDate, ShiftID, DayMod, AM, PM ) SELECT " & rst2!StaffID & " AS A, " & dt & " AS b, " & rst2!CurrentID & " AS c, ([b]-2) Mod 14 AS d, " & rst2!ThuAM & " AS e, " & rst2!ThuPM & " AS f;"
  23. ................
  24.  
i would like create the first SQL as a rceordset and then search it using the other recordsets (to minimise on transfer to disk but maximise network transfer)

can that be done or should i just re-evaluate my loops?

Thanks for any advice
Apr 23 '09 #17

100+
P: 365
I have altered my code to solve the problem, this method only pulld data once (twice really) from the network and uses recordsets the rest of the way, if anyone is interested here is the code:
Expand|Select|Wrap|Line Numbers
  1. Function NewShifts(Dept As String)
  2. Dim tdf As DAO.TableDef, qdf As DAO.QueryDef
  3. Dim rst As DAO.Recordset, rst2 As DAO.Recordset, rst3 As DAO.Recordset
  4. Dim dt As Long, p As Byte, i As Byte, d As Long, md As Byte
  5.     For Each tdf In CurrentDb.TableDefs
  6.         If tdf.Name = "" & LogStaffID & "tblShiftsNew" Then DoCmd.DeleteObject acTable, "" & LogStaffID & "tblShiftsNew"
  7.     Next
  8.     DoCmd.RunSQL "SELECT tblShifts.EntryID, tblShifts.StaffID, tblShifts.ChDate AS ActDate, tblShifts.CurrentID AS ShiftID, tblShifts.WeekNo AS DayMod, tblShifts.MonAM AS AM, tblShifts.MonPM AS PM INTO [" & LogStaffID & "tblShiftsNew] FROM tblShifts WHERE (((tblShifts.StaffID)=-1));"
  9.     Set rst = CurrentDb.OpenRecordset("" & LogStaffID & "tblShiftsNew", dbOpenDynaset)
  10.     Set rst2 = CurrentDb.OpenRecordset("SELECT tblShifts.*, tblStaff.Deleted FROM tblStaff INNER JOIN tblShifts ON tblStaff.StaffID = tblShifts.StaffID WHERE (((tblStaff.Department)='" & Dept & "') AND ((tblStaff.Deleted)=False) AND ((tblShifts.ChDate) Is Not Null)) ORDER BY tblStaff.StaffID, tblShifts.EntryID;")
  11.     Set rst3 = CurrentDb.OpenRecordset("SELECT tblShifts.*, tblStaff.Deleted FROM tblStaff INNER JOIN tblShifts ON tblStaff.StaffID = tblShifts.StaffID WHERE (((tblStaff.Department)='" & Dept & "') AND ((tblStaff.Deleted)=False) AND ((tblShifts.ChDate) Is Null)) ORDER BY tblStaff.StaffID, tblShifts.EntryID;")
  12.     Do Until rst3.EOF
  13.         If rst2!StaffID <> rst3!StaffID Then MsgBox "There seems to be a data corruption, please inform the system administrator", vbCritical, "System Error... StaffID Mismatch"
  14.         d = rst2!ChDate
  15.         For p = 0 To 13
  16.             dt = d + p
  17.             md = (dt - 2) Mod 14
  18.                 If md < 5 Then
  19.                     rst.AddNew
  20.                     rst!StaffID = rst2!StaffID
  21.                     rst!ActDate = dt
  22.                     rst!ShiftID = rst2!CurrentID
  23.                     rst!DayMod = md
  24.                     rst!AM = Choose(md + 1, rst2!MonAM, rst2!TueAM, rst2!WedAM, rst2!ThuAM, rst2!FriAM)
  25.                     rst!PM = Choose(md + 1, rst2!MonPM, rst2!TuePM, rst2!WedPM, rst2!ThuPM, rst2!FriPM)
  26.                     rst.Update
  27.                 ElseIf md > 6 Then
  28.                     rst.AddNew
  29.                     rst!StaffID = rst3!StaffID
  30.                     rst!ActDate = dt
  31.                     rst!ShiftID = rst3!CurrentID
  32.                     rst!DayMod = md
  33.                     rst!AM = Choose(md - 6, rst3!MonAM, rst3!TueAM, rst3!WedAM, rst3!ThuAM, rst3!FriAM)
  34.                     rst!PM = Choose(md - 6, rst3!MonPM, rst3!TuePM, rst3!WedPM, rst3!ThuPM, rst3!FriPM)
  35.                     rst.Update
  36.                 End If
  37.         Next
  38.         rst2.MoveNext
  39.         rst3.MoveNext
  40.     Loop
  41.     rst.Close
  42.     rst2.Close
  43.     rst3.Close
  44. End Function
it currently meets my speed needs..

Cheers Chaps
Apr 23 '09 #18

NeoPa
Expert Mod 15k+
P: 31,709
Thanks for posting Dan.

I haven't yet had a chance to look into this one more deeply. Been kept quite busy tonight. I've kept the page open though, in case I managed to get around to it. I can close it now though, so well done :)
Apr 23 '09 #19

Post your reply

Sign in to post your reply or Sign up for a free account.