Lookup within table VBA for each user

[jaryszek]

Okey Minty, thank you.

So Ticket ID should be only number?

About your second question --> i think that this is more clear if you can filter ticket by username or you can see the username immediately when you are looking at ticket.
If you want to check what the ticket was in 2017 you can simple filter it.

Jacek

 

[Minty]

Yes of course - but filter by a ticket start date field and/or assigned user field, do not try and extract that from a compound string that doesn't serve any other purpose.

Just take 5 minutes to think what you want, and what you might want the system to do. To report on anything with dates/times you have simple powerful functions in Access.

If you store two relevant bits of data with each ticket - CreatedDate, ClosedDate - You can easily report on how many were opened in a period, how many are still open, how many were closed in a period and how long they took to close.

Now try to do the same by reverse engineering ljar_2017_1.

The fact you only have one table for all your data, and only one other for employees makes me think you have taken a current spread sheet and simply copied it into Access. This is not how to make a database.
Have a read here https://sites.google.com/site/msacc...wf-vip/excel-in-access/excel-in-access-part-1 about the steps you need to look into to move from Excel to Access

 

[jaryszek]

Ok thank you Minty.

I will read this article certainly.

There will be more tables in my databse like translation tables. But it has to be one big table (we had a similar topic where you explained that data model should be done in one big table for all users inputing data into it instead a few small tables ).

I know what you mean and you have point.
Still i have to have unique number when user is correcting the ticket.
So If user choose ticket number like ljar01_2017_1 from combobox - it will be clearer then choosing 1 or 34 number from this combobox. Or maybe i am too sensitive about this...

Jacek

 

[Minty]

I don't remember that posting - there was one here https://www.access-programmers.co.uk/forums/showpost.php?p=1526132&postcount=13 where it was suggested multiple small tables for translations were better than one big table.

 

[jaryszek]

I was referred to this post:

https://www.access-programmers.co.uk/forums/showthread.php?t=291132

Thank You Minty for your help, support and involvement.
I am closing the topic.

Best Wishes,
Jacek Antek

 

[Minty]

Ah - I had forgotten that one.
And forgotten that we had already discussed not creating this messy compound primary key.
The single table point was that you should only have your main data stored in one table, not one table per consultant. It certainly meant wasn't to imply that you only had one table for all your data in your database.

Data normally gets split out as Entity's -you really need to read up and understand how this works.

You were given a lot of this advice before, but for some reason I forgot, and have covered a lot of the same ground again. Once again we aren't saying this stuff for fun, it's because we have experience and know what works and more importantly what will not work or be impossible to maintain.

 

[jaryszek]

Yes we have discussed it already Minty and now i have only one data output table. So whole model is based on split database, access runtime and only one database to insert data.

So user is filling form and all data is in one big table - simple, without problems and a lot unnecessary little tables for each user.
Now login form and user access will specify which part of table user could see.

------------------>

Quess what... this strange ticket ID connected with the model from our earlier topic had to be implemented so i had to do it. It was done using Dlookup function and seperate table. Now we have of course problem with this but you know - my business decides about it.

Of course you are experienced and i know that you have knowledge and want to share it.

Thank you for that,

Best Regards
Jacek Antek