Lookup within table VBA for each user

[jaryszek]

Hi Guys,

I have form and table connected with it.
I would like to add algorithm which will be counting how many tickets each user did.

So for user username1 and username2 tickets will be looking like:
username1_year_1
username2_year_1
username1_year_2
username1_year_3
username2_year_2

I have button in form and i would like to do this within VBA code.
It is possible witout bulding new query?

I think about something like this maybe:
one column with Ticket_ID. and my VBA code has to search within all column and find if specific ticket exists. If yes take max value from right number from ticket number column (username1_year_1) and add 1 to get:
username1_year_2.

Please help,
Jacek

 

[Minty]

Why not just build a totals query grouped on the year of the data?
I'm not sure form your explanation exactly what you are trying to achieve.

Can you you show some sample data, and your expected results?

 

[jaryszek]

Hi Minty,

thank you for your response!

Sample database please find in attachment.

Login will be read from Login Form.

If user click button (here "Zarejestruj nowy blad") the lookup shoud search in column [Numer błędu] if ticket with specific username ([Login] column) exists and is it within the same year as present take the last number and add to it 1.

So in this sample if i will add new ticket the new ticket number should be :
"ljar01_2017_4" in tbl_Main.

Thank you for your help,
Jacek Antek

 

[jaryszek]

Anyone?

Best Wishes,
Jacek

 

[Minty]

That hasn't really helped. Make a spreadsheet with the input data and your expected output data.

Include enough data to account for all possible outputs and inputs.

 

[jaryszek]

Minty, thank you.

I can do this in Excel or here if you want:

Input:

1. username

Output:

1. username_year_number

where number is the last find username ticket + 1.
year = current year

Is it enough?
I don't really understand what do you expect from me?

Jacek

 

[jaryszek]

Ok maybe this will satysfy you

Jacek

 

[Minty]

Thank you, that's a clearer explanation.

What happens when the year changes, eg from December to January, are you restarting the count, or do you have another field that determines the year we are adding?

You almost certainly need to use a subquery http://allenbrowne.com/subquery-01.html

 

[jaryszek]

Thank you for Minty,

Count should be restarted for new year.
the year should be added something like this: year(now()).

Jacek

 

[Minty]

Okay - so how do we determine which year the login happened, which field records this information. Is there a LoginDateTime?

Or are you planning on storing this calculation? - which is not considered good practice. Take a step back - what purpose does this ticket reference serve? What happens if you delete one?

 

[jaryszek]

User will log into Access form using login form. I will have the username from this.
No, there is no LoginDateTime.
I am planning to do it with function year(now()) and use it in VBA code. But i do not know how to do this.

So after clicking the button access will be checking the username and set variable Year.

Tickets will be for doing reports and this Tiecket number will be unique.
Hmm if the record will be deleted - for example ljar_2017_2 is deleted.
Next record should find the last one not deleted (ljar_2017_1) and add to it 1.

Jacek

 

[Minty]

So what if ljar_2017_2 is deleted but ljar_2017_3 already exists?

You see the problem with convoluted "created" record numbers like you are trying to create. They might look pretty on reports but serve no real purpose.

If you want a count of that users tickets per year / month / week, then store a logindate. You can then calculate the number of tickets over any period you care to report on, and if one is deleted your count will still be accurate.

Your Table already has a unique Autonumber field - simply use that as your ticket number. If one is deleted it doesn't matter it was unique, and all the others still are.

 

[jaryszek]

Ok you have the point.

Ok could you please help me with this logindate?
So in my Main table i should create logindate columns yes?

How it should be fullfilled? Automatically with autonumber yes?
So in autonumber i can create login and date (date in format "YYYY-MM-DD"?)
And autonumber will do the thing?

So after that i can make queries to select all tickets from about first quater of 2017 in order to report it?

Ok if this will be autonumber there can be empty tickets so for example i will have ljar_2017_1 and after that there will be ljar_2017_100 , do you think is a good aproach?

Jacek

 

[Minty]

Post up a picture of all your tables and relationships. I confused by the login / ticket relationship. The database you uploaded only had one table.

My thoughts are you have a Item/Job/Thing (Ticket) that your process is dealing with, you have users. You then assign a ticket to a user?

You ticket is your main item and hopefully has a unique autonumber ID field.

 

[jaryszek]

Minty, thank you.

In my model there is only one main table and table with Employees.
there is no relationship between them (in access).

The table employee is for login purposes using login form.
VBA code is looking for login and store it as variable.

After that i have the current username.

And i have to create this relationship between ticket/username now.

I am assigning tickets for logged user.

I hope that my post is not too confusing,

Jacek

 

[jaryszek]

Ok maybe i am beginning to understand what you are mean:

You want to have in field Format in properties within TicketID autonumber field something like this: "User"00000 where user is Username connected to current user, yes ? So you wnat to build relationship?

wow it is nice i like it but i have lack of knowledge unfortunately... ;/

JAcek

 

[Minty]

No not quite. Leave the Autonumber as a number. You will use this to identify the Ticket. In simplest way you can have a field in the ticket for your current Assigned user ID.

You then have who is dealing with each ticket but it can be changed.

If you want to keep track of changes then you will need another table to record who was assigned to which ticket and when.

Database 101 - You can't report or do things in Access if you don't tell the database about it. Write your process down on paper, if you can't get it down on paper then Access can't do it either.

 

[jaryszek]

aaa okey, i understand this. I will provide the solution soon.

What if year will change and i would like to reset autonumber?
I will have to do it with append query?

Jacek

 

[jaryszek]

Ok there is a problem.

How can i populate field TicketID using autonumber ?

Example:
I will be adding new record. I am adding it and autonumber is created for example number 50.

So waht now i should use UPDATE statement and create a TicketID field (username + autonumber + year).

Please help,
Jacek Antek

 

[Minty]

Nooooo- Stop trying to make your ticket ID so complicated. Your TicketID Autonumber IS the ticket ID. Just the number nothing else.

When you want to order food at a restaurant and they ask for your table number, it's 32 or 12 etc. It's not 32_David _6 because David has already served 6 people that day.

What benefit does giving your ticket number a persons name and another arbitrary number based on previous work actually serve in your process?