Macro to find and sort column in proper way on the basis of unique Ticket_ID number (1 Viewer)

jaryszek · Aug 26, 2016

Hi Guys,

I have 3 columns within Table1:
Number and time of Entry in Excel.

5000020 15:45 3 June user1
5000020 16:00 3 June user1
5000015 16:20 3 June user1
5000020 16:30 3 June user1

Im am passing Table1 to Access database from each user.
So in User2 Table1 he can have also numbers like this but with different time:

5000020 15:50 3 June user2
5000020 16:10 3 June user2
5000015 16:25 3 June user2
5000020 16:40 3 June user2

The number should be looking this after my macro:

5000020_1_2016 from 15:45 3 June user1
5000020_2_2016 from 15:50 3 June user2
5000020_3_2016 from 16:00 3 June user1
5000020_4_2016 from 16:10 3 June user2
5000015_1_2016 from 16:20 3 June user1
5000015_2_2016 from 16:25 3 June user2
5000020_5_2016 from 16:30 3 June user1
5000020_6_2016 from 16:40 3 June user2

So when user 1 is typing his Ticket and click button send in Excel - Excel is joining Access, Access is checking in Whole database from each user if specific number exists and give number properly in order to Ticket typed by User.

Please Help if you have any solutions for this,
Thank You in advance,
Jacek Antek

sneuberg · Aug 26, 2016

First please note that you can achieve the order you seem to want for the output by sorting on the time then the ticker number, i.e., the order by would be like ORDER BY Table1.TicketTime, Table1.TicketNo;

But I'll assume you want the combined output anyway. The sequence number in this output appears to be the number of the latest entry for the ticket number. It doesn't seem to have anything to do with the user. You could generate that sequence number with DCount the expression is:

Code:

SequenceNo: DCount("*","[qryTable1]","[TicketNo] = " & [TicketNo] & " And [TicketTime] <= #" & [TicketTime] & "#")

However this generates the same sequence number if the time is the same for any ticket number. If this isn't acceptable you need to specific how the numbering should be for the case where the times are equal. Let me know if this is the case and how you want it handled.

The output can be calculated and therefore there is no reason to store it. The expression for the output is:

Code:

Formatted Ticket: [TicketNo] & "_" & [SequenceNo] & "_" & Format([TicketTime],"yyyy") & " from " & Format([TicketTime],"hh::nn d mmmm") & " " & [User]

You can find a demonstration of this in the attached database. Information on the format function can be found here.

sneuberg · Aug 26, 2016

In case the equal times are a problem the following expression generates sequence number in order by the user in the cases where the times are equal

Code:

SequenceNo: DCount("*","[qryTable1]","[TicketNo] = " & [TicketNo] & " And ( [TicketTime] < #" & [TicketTime] & "#  Or [TicketTime] = #" & [TicketTime] & "# And [User] < '" & [User] & "')")+1

This is in the attached database.

jaryszek · Aug 27, 2016

Thank you sneuberg for your solution and help.

Function Dcount seems very useful for me and it is working like a charm.

I think that in the same time it will be no possibility to have inputs in equals times. I will be have times in format 15:45:04 so seconds will done the job

But Idea is to have this Formatted Ticket remembered in Access database and query should find the last record with the specific Ticket number and add this ticked after last record.

So we have already do qryTable1 but Users from Excel database are inputting new records (for example Ticket Number 50000020).
Our query should search in qryTable1 the lst record with number 5000020, see that there is already number 5000020_6_2016 from 16::40 3 czerwca user2 and add to this table record with 5000020_7_2016 from 17:00.

Please let me know if I explain this understandable.

Best Regards,
Jacek Antek

sneuberg · Aug 27, 2016

If I add Ticket No = 5000020, User = user2, and TicketTime = 6/3/2016 17:00 and refresh the query it has 5000020_7_2016 from 17::00 3 June user2 for a Formatted Ticket so I guess I don't understand.

I suggest you put more data in the database I sent you. Please add data that would cause the problems you are concerned with. Then upload the database and tell me where the problems are.

jaryszek · Aug 28, 2016

Thank you sneuberg!

I understand you, it will be working fine but I am wondering what if database will have above 100 000 hundreds records. It will be all the time counting again and again if user add new number 5000020.

Will this D count function will be not to slow in this case?

So I was wondering if I can loop only with exact number inputed by User.
So If User2 will input 50000020 the table at 17:00 will be filtered to only 50000020 numbers and looping only through them. Next function will add 5000020_7_2016 from 17::00 3 June user2 as last record. It will be quicker than refreshing all the table I suppose.

Why time format has "17::00" instead of "17:00" ?

Jacek Antek

sneuberg · Aug 28, 2016

If you look at the criteria in the DCount you will see that the first part is

[TicketNo] = " & [TicketNo]

so the DCount selects records with the same ticket number and counts them. It wouldn't be looping through 100,000 records. Do you know about how many entries there will be per ticket number, i.e., 5000020_N how large will N get? If so let me know and I'll see if I can create some 100, 000 records of test data so we can see if there will be problems.

If are thinking of storing the sequence number this would slow the input process. Since the data could be entered out of time sequence the sequence numbers would have to be updated for each new entry. How slow this would be would depend on the number of sequence numbers per ticket number.

So storing or not storing the sequence number is a trade off between slowness in the input or slowness in the output. I think we should do some testing. Even without testing I can tell you that the output would be sped up by putting indexes on the fields being used by the DCount.

Code:

Why time format has "17::00" instead of "17:00" ?

I guess it's because my finger got shaky when I typed that format.

You can take the extra colon out of the format or just replace the expression with this one.

Code:

Formatted Ticket: [TicketNo] & "_" & [SequenceNo] & "_" & Format([TicketTime],"yyyy") & " from " & Format([TicketTime],"hh:nn d mmmm") & " " & [User]

jaryszek · Aug 28, 2016

Aaa ok. I don't understand exactly how D count function works but if you are saying that this function is looping only through selected records with the same ticket number and count them - this will solve the problem.

I dont know also this Criteria:
[TicketNo] = " & [TicketNo] --> I havent worked with Access before (always Excel with VBA). Maybe there is a site where I can find operators with explanation connected to this?

Yes it can be a lot of records, even more then 100 000 tousends per year.

Unfortunately new inputs will be done by users quite often and Table with Ticket Numbers will have to be counting again and again by D count Function.
I am afraid of this a little.
When you have 10 users and each of them add 40 new entries per day this Table will be counted 400 times per day.

I can try to implement this. But this model has to work excellent - my clients have to be satisfied

Thank you very much for your help,
I will tomorrow ask you about more details with this D count function.

Cheers,
Jacek Antek

sneuberg · Aug 28, 2016

The best way to know if this will "work excellent" is to test it. I'm willing to write some code that will enter 100,000 records so we have something to test on but I need to know about how high the sequence numbers will go (how many times a number like 5000020 repeats) so that the test data is realistic. Please give me a number for this even if it's a guess.

sneuberg · Aug 28, 2016

I went ahead a created a test case which is attached. For this test I assumed the maximum sequence number would be around 100. If you open frmAddTicket you can add a ticket and see it appear in the subform below. The data in this subform is sort in descending order so if you want to see your input without scrolling down enter something on or after the ticket no and ticket time you see.

This required indexes on the TicketNo and TicketTime fields. Without indexes the query runs so slow that it's unusable.

jaryszek · Aug 29, 2016

sneuberg - it can be for example 8000 tousends record for 50000020.

Thank you for database!

I will test this database after I came back home about 11.00 am.

Jacek Antek

sneuberg · Aug 29, 2016

The attached database has 104,000 records with the sequence numbers going to 8000.

jaryszek · Aug 29, 2016

Ok i think that is very quick solution indeed. Hmm there is an error in the bottom of table : error within expression of our query (please see attachments).

I think that i can implement it.

But first off all what I should have - one table with Query for this?

So i will be have strings from VBA from Excel, ticket number, time user.

After that i should input this values to the table ale use our D count function?

jaryszek · Aug 29, 2016

And one more thing to get this last inputed value from Access can i done it from Access level or should i do query table in Excel and from there check the last value with VBA?

jaryszek · Aug 29, 2016

I am also wondering how to this SaveRecord button work,
I see that there is acCmdSaveRecord for Table 1 and after that query wtih D count gunction is running.

How these User, TicketNo is bound with Table1? Is it connected automatically through source format? (please see the attachment).

I have an idea. I will create empty table and do via VBA adding recrods. After that i will be trying to implement D count function.

Hot to get the last implemented record ? Import table to Excel or maybe Access can do it?

Jacek Antek

jaryszek · Aug 29, 2016

I would like to go deeper in D count function:

DCount("*","[qryTable1]","[TicketNo] = " & [TicketNo] & " And ( [TicketTime] < #" & [TicketTime] & "# Or [TicketTime] = #" & [TicketTime] & "# And [User] < '" & [User] & "')")+1

"*","[qryTable1]" - this means that all numbers should be counting in qryTable1.
Would not be better if in this place we can show Access seraching number : 5000020 ?

next there are criteria:
[TicketNo] = " & [TicketNo] - Take current choosen record (last record in our case)
[TicketTime] < #" & [TicketTime] & "# - what does it mean?
or [TicketTime] = #" & [TicketTime] & "# - what does it mean?
And [User] < '" - User should be not empty ?
[User] & "')")+1 - what does it mean?

Why can't i do:

SELECT TicketNo, TicketTime, User FROM Table1 and i ahve to have SELECT Table1.TicketNo, Table1.TicketTime, Table1.User?

Please help,
Jacek Antek

p.s. I have tried do similar Query but with other fields and other Time/Date format and NumKolenosc(SequenceNO) is all the time = 1.
Please find my trials in attachment.

sneuberg · Aug 29, 2016

jaryszek said:
Ok i think that is very quick solution indeed. Hmm there is an error in the bottom of table : error within expression of our query (please see attachments).

I think that i can implement it.

But first off all what I should have - one table with Query for this?

So i will be have strings from VBA from Excel, ticket number, time user.

After that i should input this values to the table ale use our D count function?

The time is essential for the calculation of the sequence number. You will need to add code to ensure they are added.

jaryszek · Aug 29, 2016

Ok i have this.

This will be new table in Excel with TicketNo, TimeEntry and User column.
And this values will be added via VBA to Access table.

my Code in Excel looks like:

Code:

Sub SQL_Baza()

Dim Connectstr As String
Dim HurtowniaADO As New ADODB.Connection
Dim ZdanieSQL As String
Dim Login As String
Dim FileName As String
Dim Moja As New MyForm
Dim Lokalizacja_Pliku As String
Dim Lokalizacja_Folderu As String
Dim TimeEntry As String
Dim TicketNumber As String
Dim Wiersz As String
Dim rs As ADODB.Recordset
Dim NumerSpółki, User, CzasWpisu As String
Dim rsQuery As ADODB.Recordset

// path of the database
Lokalizacja_Pliku = "C:\Users\ljar01\Desktop\Makro\Pełnomocnictwa\Baza_Pełnomocnictwa.mdb"
Lokalizacja_Folderu = "C:\Users\ljar01\Desktop\Makro\Pełnomocnictwa\"

//name of Excel
FileName = "'" & ThisWorkbook.FullName & "'[Excel 8.0;]"

//entries in user Workbook
Login = "ljar01"
TicketNumber = "5000020"
TimeEntry = "29-08-2016 14:33:33"

//connection string to access
Connectstr = "DSN=MS Access Database;DBQ=" & Lokalizacja_Pliku & ";DefaultDir=" & Lokalizacja_Folderu & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

On Error Resume Next

// opening databse

HurtowniaADO.Open Connectstr
Set rs = New ADODB.Recordset
Set rsQuery = New ADODB.Recordset

// opening table with all records and adding new entry

rs.Open "tb_wpisy", Connectstr, adOpenKeyset, adLockOptimistic, adCmdTable

With rs
    .AddNew
    ![NumerSpółki] = TicketNumber
    ![CzasWpisu] = TimeEntry
    ![User] = Login
    .Update
End With

// refreshing query with D count Function

rsQuery.Open "qry_tb_wpisy", Connectstr, adOpenKeyset, adLockOptimistic, adCmdTable
rsQuery.Update

Set rs = Nothing
Set rsQuery = Nothing

Now what i have to do is to read the added Record via VBA.
And understand D count function at all. seuberg could you please explain me our WHERE clause connected to Dcount function. This strange expression - i have to know this how it is working.

sneuberg · Aug 29, 2016

jaryszek said:
And one more thing to get this last inputed value from Access can i done it from Access level or should i do query table in Excel and from there check the last value with VBA?

It's not clear to me how this system is to work. In your first post you seem to indicate that they would be send from Excel. How? One at a time or in batches? Do you have this code to do this already?

I'm really concerned if these are going to be added one at a time by different users and your want the user to get the output immediately. If that the case this DCount probably won't be the solution or at least not the total solution. The problem with this one at a time by different users would be entry arriving out of sequence. For example:

User1 submits 5000020 at 16:00 3 June and gets sequence Number 1
User2 submits 5000020 at 15:59 3 June and gets sequence Number 1

User2 would get sequence number 1 because his time is earlier by one minute. The sequence number User1 one got is now wrong. It would now be 2.

If the users are using different computers I don't think its a good idea to believe their clocks will always be the same. If this is how the data is entered and used then I suggest that Access time stamp the inputs. The users would enter only a ticket number and userid and they would get back the output. In this case it would make more sense to store the sequence number as it wouldn't change.

If you give me the big picture I'll try to help you create the rest of this. Please give me a step by step description of the process.

jaryszek · Aug 29, 2016

Ok - i dont understand what are you afraid of.

First of all user is fullfiling In Excel's Useform his database within his workbook with proper data.
Please see attachment - userform.

Excel added this data after user clicks Send Button to sheet "Reported tickets" (attachment User's database) As you can see there is an empty place here:

"Numer zgłoszenia" - this is our FormattedTicket Number which is empty now.

After send button also in sheet "Counter of Entries" is added to table: (counter of entries attachment):

NumerSpółki - TicketNo
CzasWpisu - TimeEntry with format dd-mm-yyyy h:mm:ss (i think that seconds will do the work here but i can add miliseconds also and entries will be always diffrent).
User - login.

And after that my code above is goiing:
1. added last record for table with TicketNo, TimeEntry and User on basis of
entry by User (only time is counted by formula Now())
2. Connecting to access and add last row to table1 - tb_wpisy in polish
3. refreshing query with Dcount function(qry_tb_wpisy)
4. Added first_row from qry_tb_wpisy to sheet "Reported tickets" and that is all.

We have full database.

Jacek Antek

Macro to find and sort column in proper way on the basis of unique Ticket_ID number (1 Viewer)

Registered User.

AWF VIP

Attachments

AWF VIP

Attachments

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

AWF VIP

Attachments

Registered User.

AWF VIP

Attachments

Registered User.

Attachments

Registered User.

Registered User.

Attachments

Registered User.

Attachments

AWF VIP

Registered User.

AWF VIP

Registered User.

Attachments

Similar threads

Users who are viewing this thread