Database from a few Excel Woorkbooks - problem with numbering tickets_ID

[jaryszek]

Hi People,

I'm newbie here - please understand this.

I would like to describe my problem here.
First of all I have 2 Excel woorkbooks with the same layout tables connected to Access.

In these woorkbooks there are tables with Ticket number like this: Login & _000001, Login & _000002.





When User is creating new Ticket_ID , automatically User's Ticket is getting number from last row in this table + 1 so for example lastrow is 20 the number of Ticket will be Login_000021. Next copy of this table of each user is moved to Access Table.
From the second user it is the same - the difference is only with Login.

And the last thing - I have Query in Acccess which is joining these 2 tables for one big and thanks to this I have One big Excel workbook which people are using for reporting and implementing changes.

My clients want to have in other way - they want to have number like this (combination of year and number from second column):
5000020_1_2016,
5000020_2_2016,
5000020_3_2016,
5000150_1_2016,
5000150_2_2016,
5000020_4_2016

And this should be done in order in real time. So User 1 Is trying to create new Ticket in Excel, than whole Database with all numbers from Access is downloaded to table to this Excel woorkbook and on this basis the number is automatically added.
I wonder how to do this - whether add time column and upload it to Access for each Ticket, next download it and sort in proper way? Or maybe other ideas

Please help me with solving this solution,
Best Regards,
Jacek Antek

[jaryszek]

I think that problem can be solved in other way - maybe first user will be filling whole data in userform and click button send in Excel -
after that in Access Database there will be a query or macro which should take this number, add to table, and check if it is a last Ticket_Id or first and number it in proper way...

I am just thinking...
Jacek Antek

[RuralGuy]

Is it necessary to continue to use Excel?

[jaryszek]

Unfortunately no...

[RuralGuy]

The reason I asked is because I'm pretty sure Access can do all that you need here. It sounds like you are reluctant to do it all in Access. Is that correct? I would understand if it was.

[jaryszek]

No, Im not reluctant but my CCompany is...Here we have only Excels and Access is only my stored database.

From excel I have only one variable - number 5000020.
The Access should work futher properly...

Jacek Antek

[RuralGuy]

How many people are pounding away on Excel at any one time? Is the Ticket Number cell in Excel text or a number? How much time elapses between Ticket Number creation events in real time?

[jaryszek]

It will be about 10 People pounding away on Excel but on one time it can be different - it maybe one, two, 3 people.
Ticket Number is a Text now but I can change it.
It will be about 1 minute to create new Ticket number max. You have to choose from Dictionary: Person, His unique number and Company which is connected to. And a kind of documents for this person which should be signed later. About 1 minute.

I have similar database model, People are fullfiling Excels but Ticket Number for them is looking like : Login & number (for example ljar01_000001, ljar01_000002). One big database is created by Union all small tables from users. And In the result table you have Login1_number, Login 1 number...Login 2 number etc. It is simple and it is working well.

Here I have a problem with real time adding Ticket numbers...

Jacek Antek
Thanky You RuralGuy For your interested in my topic.

[RuralGuy]

It sounds like you have something that currently works which is great! I keep re-reading your first post to figure out where your issue might be. I guess I don't really understand yet. Maybe if you took another stab at describing just where you are having a problem. Is it with getting the number from the second column?

[ItsMe]

... Is it with getting the number from the second column?

That is what I am hearing. So maybe a separate table with a DMax() or even an Autonumber.

I know people hate using Autonmber in instances like this because of the reliability of sequential numbering. But, I find it better performing (time-wise) than the DMax. Certainly with Excel querying over a network, you will want the true Max before and after any updates to the table.

I have implemented this technique, using Autonumber, and if the table is isolated and the only access to it is code or a custom function, I have not imagined a way for the number to not be sequential.

[jaryszek]

Yes it is working great when you are adding unique User logins to your databases in Excel. There can be a login here. this is different process.
But in my new database this Ticket numbers should be sended for our clients. If you have document ljar01_2016_00001 it will be not official and it cannot be done in that way.

It should have 5000020_2016_1 for our client1, 5000015_2016_1 for client 2. Problem is that one user can have a few clients in his database.

if you imagine situation like this:

User1 is typing Ticket Number - 5000020 at 16:00.
Now this should connect with Access and check if exists already the same number in our main Table (which unions all small tables from all inputing users).
The same number 5000020 was inputed by User2 in his database in Excel at 15:45. In result table it will be 5000020_2016_1 from User2.
Our qryTable should check the last record with Ticket number 5000020 (in this case 5000020_2016_1) and add to this table 5000020_2016_2.
Next from Access will be downloaded 5000020_2016_2 to USer1 database and his Ticket will getted unique number.

Jacek Antek

[RuralGuy]

How did you plan to have Excel "check" with Access to see if the number is the same, or is that what you would like us to resolve?

[jaryszek]

I think that autonumber will not be the best solution. Because I have to search the max value with specific number and add after last record new value. I cant imagine how this should work with autonumber.
Maybe with Dmax function - could you please help me with this?

RuralGuy - this is what i would like to resolve here.

First of all create one big table from a few small ones to check numbers and time when these numbers were inputed by users.
So to get :

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


from:

5000020 15:50 3 June user2
5000020 16:10 3 June user2
5000015 16:25 3 June user2
5000020 16:40 3 June user2
5000020 15:45 3 June user1
5000020 16:00 3 June user1
5000015 16:20 3 June user1
5000020 16:30 3 June user1


And second of all to check access to see if the number is the same in database and add new record after highest value.

Jacek Antek

[RuralGuy]

If this is what you are getting from the Excel users:

5000020 15:50 3 June user2
5000020 16:10 3 June user2
5000015 16:25 3 June user2
5000020 16:40 3 June user2
5000020 15:45 3 June user1
5000020 16:00 3 June user1
5000015 16:20 3 June user1
5000020 16:30 3 June user1

It should not be too difficult to change that into:

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

Again, I need to ask how you plan to have each Excel "check" with Access to see if the number is the same?

[jaryszek]

Ok it will be a macro connected to Button in Excel. When user click "Send" Excel will open Access database, and add new record to our first table:

5000020 15:50 3 June user2

later there will be query or macro which will be changing this table for format:

5000020_1_2016 from 15:45 3 June user1

And after that I will be take number of the last user entry maybe from Excel (qyery in Excel) or maybe immidietely from Access.

Jacek Antek