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

[sneuberg]

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?

For the basic information on DCount read this. Let's break this down. Consider the following simple DCount. I've replaced qryTable1 with Table1. It could be either.

Total Count: DCount("*","[Table1]")

If you observe this in the attached database you will see it gives you the count of all of the records for each record. Not very interesting. Let's add some criteria. Consider

Count of Same: DCount("*","[Table1]","[TicketNo] = " & [TicketNo])

This will give the count for records with the same ticket number. The & [TicketNo] in the criteria concatenates the TicketNo of the current record in the criteria. So if you are look at a line in the output that has TicketNo 5000015 the DCount for that record turns into:

Count of Same: DCount("*","[Table1]","[TicketNo] = 5000015)

Now if we want the count of the tickets that are the same and have times earlier we add [TicketTime] < #" & [TicketTime] to the criteria and we have

Count of Earlier: DCount("*","[Table1]","[TicketNo] = " & [TicketNo] & " And  [TicketTime] < #" & [TicketTime] & "#" )

Here the time of each record is concatenated in. If you look at the output you will see that for TicketNo 5000020 at time 6/3/2016 15:45 the count is zero as there are no records with this ticket number that are earlier. In the final expression I added one to this so the counts starts at one.

If the times are always different this expression would be enough. The addition criteria is added for cases where the times are the same. When the times are different this

[TicketTime] < #" & [TicketTime]

is the controlling part and when they are the same this

[TicketTime] = #" & [TicketTime] & "# And [User] < '" & [User] & "'

is the control part.

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

The sequence numbers are the same because the time and users are the same or null. If the TicketNo, Time, and User are the same the sequence number will be the same. How could it be different?

Why can't i do:

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

You should be able to do that. The only time you have to specify the table is when two fields in different tables have the same name, e.g., Table1.TicketNo,Table2.TicketNo,

 

[jaryszek]

Great explanation sneuberg but i have a few doubts:

1. [TicketTime] < #" & [TicketTime] - what # is exactly mean? & this one means that this is the current record?

2. what exactly mean [User] < '? ' means that there is something written <> null?

Others is working lik a charm !

 

[sneuberg]

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

I'm not afraid but unless the clocks in these difference computers are perfectly synchronized you should be. Sooner or later the users are going to end up with the wrong sequence numbers as I explained. Why don't you have Access add the time and avoid this potential problem?

 

[jaryszek]

It is great idea. I suppose that there is a function for this ;-)

What about this my friend ?

1. [TicketTime] < #" & [TicketTime] - what # is exactly mean? & this one means that this is the current record?

2. what exactly mean [User] < '? ' means that there is something written <> null?

 

[sneuberg]

Great explanation sneuberg but i have a few doubts:

1. [TicketTime] < #" & [TicketTime] - what # is exactly mean? & this one means that this is the current record?

The criteria part of a DCount (or DSUM, DMAX, etc) is equivalent to the WHERE clause of an SQL statement. Sometime it is actually helpful to use the query designer to create a select statement and then use the pieces for your DCount.

When you concatenate variables into an SQL statement you needed to add delimiters when the variable is either text or dates. Read this for more information. In this case the pound sign (#) is the delimiter for dates. The ampersand operator (&) concatenates strings together. Please read this for more information on concatenation. So an expression like:

 "#" & [TicketTime] & "#"

ends up looking for example like:

#6/3/2016 3:45:00 PM#

So that Access SQL interpreter knows that it is a date. Internally 6/3/2016 3:45:00 PM is stored as 42524.65625 so Access needs to know that it has to be converted.

2. what exactly mean [User] < '? ' means that there is something written <> null?

The part of the criteria

[User] < '" & [User] & "')"

is true if the User in the current record would be sorted after the other Users. In the case of the whole expression for the same time and ticket number. This was added so that if the times were the same the sequences number would still be difference. Users whose names would sort higher (ascending) would get larger sequence numbers.

The symbol < means less than. The symbols <> means "not equal"

 

[jaryszek]

Thank you sneuberg for your help!

In this criteria:

[User] < '" & [User] & "')"

the most difficult for me is that you are taking current record, but to values with the same name you have to use sign ")" to sort them? i suppose...i suppose


One more thing - this date from Access. How can we use this to our model ?

 

[sneuberg]

Thank you sneuberg for your help!

In this criteria:



the most difficult for me is that you are taking current record, but to values with the same name you have to use sign ")" to sort them? i suppose...i suppose

That was just a closing parenthesis. It has nothing to do with sorting.

One more thing - this date from Access. How can we use this to our model ?

It seems to me that you'd be better off just putting Now() in as a default for the ticket time field in Access than using the time sent from the Excel spreadsheets.

 

[jaryszek]

I almost understand this ;-)
Ok I will use Now Function.

In this expression :

Or [TicketTime] = #" & [TicketTime] & "# And [User] < '" & [User] & "')")+1

So each record with the same time as current record time and each user which is less then current - it is the true?

[User] < '" & [User] & "')")

Each user is less then current user, so if you have 2 records:

15:45 User1
15:45 User2

so this first one is more than user 2 ?

Sorry that I am bothering you - I am person which must know

Jacek Antek

 

[sneuberg]

Assuming the ticket number are also the same when the Dcount evaluates

15:45 User1

There are no Users that are before User1 so this would get a dcount of 0 or 1 when the 1 on the end is added

when the Dcount evaluates

15:45 User2

There is one record before the current one that meet the criteria so the count is 1 or 2 after the 1 on the end is added.

 

[jaryszek]

Thank You my friend !

Your support and engagement are very helpful and nice !
I understand all ;-)

Jacek

 

[jaryszek]

Hmm sorry but my knowledge of Access is very small already...

It is possible to write Now() function in Access for first record? Using First() funtion or something like this?

Jacek

 

[jaryszek]

I solved it - with now function in design view

The last problem is that from VBA my macro doesnt refresh the query... i will create new topic for this

Jacek Antek