1

Hi Guys Its Great to be here. I am stuck with a problem in making sequenced serial with condition in MS Access. Let's say i have number of machines working for 2 shifts. Every shift i receive a report of downtime for each machine and the same machine in the same shift could have more than one problem. My goal is to count those problems and make a sequenced serial for it. For Example i made it in Excel with countifs formula and working good - see the picture. 4 Conditions (PoNumber-Shift-MachineNumber-Zdate) Count Result

I tried everything I know in Access and got stuck. I tried Count(IIf()) but did not get the desired result and the way to do it is a recordset function and I don’t have the ability to do it. Any Help Appreciated Guys.

Edit #1 : Guys i have tried the following before

SELECT TblDownTime.PONumber, TblDownTime.Zdate, TblDownTime.Shift, TblDownTime.MachineNumber AS MachineNum, Count(TblDownTime.Shift) AS CountOfShift
FROM TblDownTime
GROUP BY TblDownTime.PONumber, TblDownTime.Zdate, TblDownTime.Shift, TblDownTime.MachineNumber
HAVING (((TblDownTime.PONumber)=[Forms]![FrmDownTime]![PONum]) AND ((TblDownTime.Zdate)=[Forms]![FrmDownTime]![Zzdate]) AND ((TblDownTime.Shift)=[Forms]![FrmDownTime]![Shift]) AND ((TblDownTime.MachineNumber)=[Forms]![FrmDownTime]![MachineNumber]));

This is a query which apply my conditions and return the last serial of each downtime reason As variable (Sr) in the form. Then after update in MachineNumber field in the form Me.Serial = Sr+1. This way works fine with one by one record but when i try to paste 20 records at the time it miscalculates and giving me duplicates sometimes. So it is not the best way to go. I need a way which work fine with pasting multiple records at the time. Thanks again.

Edit #2 i use the generated serial to be formatted like this "Num"&Serial to be like Num1,Num2 to use in the following query

SELECT TblDownTime.PONumber, TblDownTime.Zdate, TblDownTime.Shift, TblDownTime.MachineNumber, 
IIf([SerialFormat]="Num1" And [MachineNumber]=[MachineNumber],[ReasonCode],"") AS Code1, 
IIf([SerialFormat]="Num1" And [MachineNumber]=[MachineNumber],[Reason],"") AS Reason1, 
IIf([SerialFormat]="Num1" And [MachineNumber]=[MachineNumber],[DepartmentResponsible],"") AS Dept1, 
IIf([SerialFormat]="Num1" And [MachineNumber]=[MachineNumber],[StoppedFrom],"") AS From1, 
IIf([SerialFormat]="Num1" And [MachineNumber]=[MachineNumber],[StoppedTo],"") AS To1, 
IIf([SerialFormat]="Num1" And [MachineNumber]=[MachineNumber],[TotalDownTime/Min],"") AS TotalDownTime1
FROM TblDownTime
WHERE (((IIf([SerialFormat]="Num1" And [MachineNumber]=[MachineNumber],[ReasonCode],""))<>""));

i will try the other way in comments tomorrow, And i will be grateful if someone helps me with a recordset function that operates directly on the table and i will but it on OnClose Event in my form .. thanks a lot

June7
  • 19,874
  • 8
  • 24
  • 34
Csharp Newbie
  • 303
  • 1
  • 10
  • Does this answer your question? [MS Access Restart Number Sequence](https://stackoverflow.com/questions/64565228/ms-access-restart-number-sequence) – June7 Feb 24 '21 at 19:45
  • No It Doesn’t because of two reasons : First i have 4 variable conditions Second i tried a similar idea in the form after update MachineNumber it calculates well if i am typing one record at the time , But if i tried to copy and paste 30 records for example it miscalculates and giving me some duplicates sometimes not always , So I cant go with this . Thanks for your Suggestion – Csharp Newbie Feb 24 '21 at 19:54
  • Expand the SQL to use 4 linking fields instead of the 1 in example. Is there an autonumber field in table? Suggested query syntax definitely works with your data. What code is in event? Record(s) need to be committed to table and then possibly form refreshed. A form is not best vehicle for what you want. Reports are best for this sort of calculation. – June7 Feb 24 '21 at 20:05
  • Calculating this sequence and saving to table are different issues. Advise NOT to save to table, especially if you want to use copy/paste as that will require a completely different approach to resolve (VBA opening recordset and looping records to save an incremented value) and NOT in the MachineNumber AfterUpdate event. – June7 Feb 24 '21 at 20:12
  • Please read my Edit #1 in the question , About the autonumber field it does exist in the table As ID , Report maybe good idea if i do not use the serial in some other queries in my case i use the generated sequence and pair with "Num" to be like Num1,Num2,Num3 etc.. at last i use this New shape in other query as a column to have all the problems in one line For example MachineNumber M202 have columns with the same number as problems column 1 will be like Num1 problem with start time and end time and total down time all in one single line i wish i could explain more – Csharp Newbie Feb 24 '21 at 20:13
  • "will require a completely different approach to resolve (VBA opening recordset and looping records to save an incremented value)" Thanks God you got my point thats what i am talking about i need this recordset function with my 4 conditions as i am not able to do it myself , My Approach was the best i came up with , Thanks in advance – Csharp Newbie Feb 24 '21 at 20:16
  • I did read your edit. Doesn't change my comments. Can concatenate a string with the calculated sequence number. If you are talking about a CROSSTAB to pivot data so Num1, Num2, etc are column headers, that can also be done with sequence calculation https://stackoverflow.com/questions/64400911/pivot-query-in-ms-access – June7 Feb 24 '21 at 20:18
  • I did not try this way please could you give me some query with my 4 conditions ? I am not sure i could pull it , About my way it is a Sum(IIF()) depend on Num1,Num2 sequence to get the downtime start and end and total downtime for each machine its not just headers – Csharp Newbie Feb 24 '21 at 20:23
  • Which query, just sequence calc or with pivot? Make an attempt and edit your question. As I said, just expand query to link 4 field pairs instead of 1, use AND operator between them. Also, apostrophe delimiters with text fields. IIf() is not needed. Use all 4 fields in GROUP BY of CROSSTAB. – June7 Feb 24 '21 at 20:26
  • If you want multiple aggregations in CROSSTAB, that gets more complicated. http://allenbrowne.com/ser-67.html#MultipleValues. CROSSTAB query can be emulated with expressions like Sum(IIf()) but that also gets very complicated. If you still want a VBA function to save sequence to table, you need to open another question. Questions are not supposed to be multi-topic. This question seems to concern calculating a sequence in query. – June7 Feb 24 '21 at 20:36
  • Okay, this may be a case of too much info and my misunderstanding requirement. If this question was intended to request a VBA function then that should have been the focus, not all the query stuff that doesn't work. The VBA procedure itself is not terribly difficult. The real trick is figuring out what event to put code into - obviously not the MachineNumber AfterUpdate. Do you want to rely on a user to remember to push a button or automate trigger perhaps with form Close event? In which case user would not even see the calculated sequence on form. – June7 Feb 24 '21 at 20:47
  • Yes my bad my friend , about the function i will trigger it on form close and no need to the user to see it just need it to be correct and saved to the table "TblDownTime" in the field "ReasonSerial" , So Sorry For inconvenient – Csharp Newbie Feb 24 '21 at 20:51

1 Answers1

0

Here is a procedure to consider. Call from whatever event best meets your needs.

Sub MakeNum()
Dim rs As DAO.Recordset, intS As Integer, strG As String
Set rs = CurrentDb.OpenRecordset("SELECT PONumber & MachineNumber & ZDate & Shift AS Grp, ReasonSerial " & _
                 "FROM tblDowntime WHERE ReasonSerial Is Null ORDER BY PONumber, MachineNumber, ZDate, Shift, ID")
strG = rs!grp
While Not rs.EOF
    If strG = rs!grp Then
        intS = intS + 1
        rs.Edit
        rs!ReasonSerial = intS
        rs.Update
        rs.MoveNext
    Else
        intS = 0
        strG = rs!grp
    End If
Wend
End Sub

It only edits new records where ReasonSerial is Null. If you need to recalculate sequence for all records then include an UPDATE at beginning of procedure:
CurrentDb.Execute "UPDATE tblDowntime SET ReasonSerial=Null"

June7
  • 19,874
  • 8
  • 24
  • 34