Display last Record based on person

[bartmanekul]

Hi all,

Hoping someone can help with this one. Having to jump into access as Excel is not useful for multiple users.

I've got a dataset, in it is quote numbers. These vary by type of person, i.e. LB145, ZD67 etc.

My problem is thus - when adding a new quote, they need to know the last number they used. I'd like this to be on the entry form rather than having to look at the last one in the table etc etc.

So is there a way I can show the last number used? Issue is it's got to be by person, so just the last record won't do.

There's all sorts of ways, like getting the windows username and tying that in with bring up the last one (by using a 'where' I hope). I can probably work out how to do some of these individually, but not tie it all together.

 

[Isaac]

Can you try using a dmax with a where clause?

 

[bartmanekul]

Can you try using a dmax with a where clause?

Thanks, that's a function I can use. But not sure about the criteria - I could be smart and tie it to the windows user logon, or simply create a drop down for the user to choose.

I can probably work out how to do the drop down, but not sure how to reference it in the criteria.

In case it's not been noticed, I am very new to access though reasonably experienced with excel.

 

[plog]

2 points based on my experience:

1. Numbers aren't mystical. This method is a lot of work for no real reward. At the table level you should us an autonumber for your quote numbers. They are guaranteed to be unique and that's really the only purpose of quote numbers. If you need to know how many quotes a person has worked on you can run a simple totals query. Making them sequential by person doesn't really add anything.

However, since in my experience that logical argument has never worked:

2. You probably haven't set up your table correctly to do it your way. To accomplish what you want in the manner you want you need 2 fields--a text field to hold the initials of the person and a numeric field to hold the number portion of the quote number. Let's call those fields in the table [QuotePerson] and [QuoteNumber] respectively and you have a field on your form named [FormPerson] where the initials are input/selected. The Dmax then looks like this:

=Me.[FormPerson] & DMax("[QuoteNumber]", "YourTableNameHere", "[QuotePerson]='" & Me.[FormPerson] & "'")

Then, when you want to make the numbers reset every year it gets even harder.

 

[bartmanekul]

Thanks. I know it would be better otherwise (and it would make my life a lot easier), but that's already the format of the quote numbers (and many other things). So a lot of existing data will be added to the database with these formats already used.

I've already thought of splitting the fields, but this is problematic in itself. I'd need to find out how to add initials to the number, or have them put the number in separately, etc.

 

[Isaac]

Thanks, that's a function I can use. But not sure about the criteria - I could be smart and tie it to the windows user logon, or simply create a drop down for the user to choose.

I don't fully understand. Are you already capturing user's logon names and storing them in a table? If not you should be anyway.

 

[plog]

Wait. You are going to display what the number is suppose to be and hope the user is able to retype it in a new field? You have obviously never meet users before. They are incapable of that. Oh they will get it right 99.5% of the time, but that .5% of the time will cost you 4 hours of pulling your hair out wondering why the data is wrong.

You need to have them enter/select initials and then have a field they can't touch populate with the quote number. Behind the scenes the initials would go into their field ([QuotePerson]) and the number into its field ([QuoteNumber]). Data storage is not data display.

 

[Lightwave]

I've done this before using the following - its in a situation where there is a master table and a child table I'm not sure that might not reflect your table structure..

MS Access – SQL to Select Distinct list of Child Records based on a Maximum or Minimum Child Field Value – Cloudy DATA Blog

rounduptheusualsuspects.org

 

[bartmanekul]

Yes, I'm very experienced with users thanks.

This is precisely the reason I'm doing it. I'm going to display the last number they used, as I thought it was the easiest way. It's the current way they do it on the spreadsheet, simply by looking at the last one they used.

My other thought was separating the fields as you said before, and doing an increment by one on the number then adding the initial based on person (a field in the DB for the one doing the quote). But that looked far more complex, for me that is.

@ Isaac,
No sorry, should have explained.
The user will add their name from a list when entering a new quote. I simply need them to see the last quote number (which annoyingly has their initials in) so they know what to use without going into another form/table.

@Lightwave,
I'll have a look at that, thanks.

 

[Isaac]

Ok thanks, I'm guessing now less than I was before, so this may be wrong but here is something you might do:

Me.ControlName.Value=dmax("[IDColumn]","[TableName]","[InitialsColumn]='" & left([ComboboxName].value,1) & _
                            left(right([ComboboxName].value,len([ComboboxName].value)-instrrev([ComboboxName].value," ")),1 & "'") + 1

It finds the max ID # on a record associated w/their initials, then adds one.

Here are all the assumptions made, which you'll have to either make sure are true, or adjust it accordingly:

1. your idcolumn is a real number, on which arithmetic operations can be performed
2. the initials you want is the first letter of the first name, concatenated with the last letter of the last name-last name defined as the last chunk of text after a space, so if name is john paul, this is jp, if name is juan sozo garcia de martinez de espinosa, this is je
3. your combobox BOUND column is the one that holds this name

 

[jdraw]

Do each of the users have to login to the system? If so, you can capture their name/initials.
You could also select the max numeric of the quoteId where initials = loginUserInitials and increment and append/prepend the initials.
Seems if can all be done without the user (quote maker) having to type in their name??

Is the purpose of sequential numbering to identify how many quotes the user has submitted/created?

 

[Cronk]

And all the work around solutions are all propping up "It's the current way they do it on the spreadsheet". Combining two or more bits of information (eg initials of a person and a sequential number) to get a unique number is moving away from normalization.

 

[bartmanekul]

Apologies for the guesswork, I didn't want to write war and peace in the intro, but I see I was lacking in a lot of details.

There are about 6-7 users.

The quote quantity isn't huge, worst case 15 a day so the database won't be massive.

Quotes are done on Sage, and the company insists on relying on hard copies. But Sage doesn't have anywhere near the capability for reporting they want, so they have to also fill in a spreadsheet.

If an order is won, people always go back to the sage printout, but they delete them off the system after a period of time. Don't get me started on the insanity of that, but it means that the reference with initials and number is important.

And yes, I know that with just a number it could be looked up in the database I'm building, but they don't want to rely on that. This is why the number/initials reference is still needed. As bad as this seems, I've made a lot of headway in moving things into this century.

With reference to what's going to be recorded:

The person (via lookup list)
The Sage quote reference
The personal reference (the LB145 type reference)
An autonumber (obviously)
Various other info on the quote.

While spreadsheets allow me to make all the reports needed, it's simply not suitable for multiple users to enter data, as anyone with excel knows.

 

[Isaac]

Does post 10 work? It was specifically in response to your description, that they select their "name" from a drop down, and then you need to look up the max quote ID + 1 from a table, based on a record that also contains their initials.

 

[jdraw]

How do you sync Sage and Access? Or replenish Access? Did you look at the suggestion in post 11?

 

[Pat Hartman]

Here's a sample database that shows how to build a custom, user-friendly, ID. ALWAYS use the autonumber as your primary key and as a foreign key. The user-friendly ID is ONLY data and used for searching for a particular record so don't forget to index it.

 

[bartmanekul]

Does post 10 work? It was specifically in response to your description, that they select their "name" from a drop down, and then you need to look up the max quote ID + 1 from a table, based on a record that also contains their initials.

I've had other things pop up higher on the priority list, but will try that when I can. I'm not sure how to actually implement it, so that will take reading up on. I'm coming back to access after over 2 decades.

How do you sync Sage and Access? Or replenish Access? Did you look at the suggestion in post 11?

There is no sync, this is why people have to enter it in manually. I won't go into the whole method, but it's an incredibly stupid and time consuming state of triple entering much of the same info in order for it to be on various different systems. I am able to pull info from Sage to excel, and I assume it's reasonably easy to do from Access to excel for easier reporting.

I'm mainly trying to stop the need from them having to enter it into something called Vecta, which is one of the most user unfriendly and overpriced bits of software I've come across.
When I next get a chunk of free time, I will be going through the various posts on here trying them, and seeing which I can implement.

 

[bartmanekul]

Do each of the users have to login to the system? If so, you can capture their name/initials.
You could also select the max numeric of the quoteId where initials = loginUserInitials and increment and append/prepend the initials.
Seems if can all be done without the user (quote maker) having to type in their name??

Is the purpose of sequential numbering to identify how many quotes the user has submitted/created?

Realise I missed the questions, apologies:
Only login is the Windows login.

The sequential numbering is just to differentiate the numbers, for each individual. While it does indicate how many they have done, it's not used in that way as it can obviously be different over time. I.e. someone on higher number might have done a smaller amount over time.

Numbers will be something pulled from the database into a report.

 

[bartmanekul]

Ok thanks, I'm guessing now less than I was before, so this may be wrong but here is something you might do:

Me.ControlName.Value=dmax("[IDColumn]","[TableName]","[InitialsColumn]='" & left([ComboboxName].value,1) & _
                            left(right([ComboboxName].value,len([ComboboxName].value)-instrrev([ComboboxName].value," ")),1 & "'") + 1

It finds the max ID # on a record associated w/their initials, then adds one.

Here are all the assumptions made, which you'll have to either make sure are true, or adjust it accordingly:

1. your idcolumn is a real number, on which arithmetic operations can be performed
2. the initials you want is the first letter of the first name, concatenated with the last letter of the last name-last name defined as the last chunk of text after a space, so if name is john paul, this is jp, if name is juan sozo garcia de martinez de espinosa, this is je
3. your combobox BOUND column is the one that holds this name

Thanks for the code, sadly it appears access is a horrific program compared to excel, and is well beyond me. I don't even know what to do with the code you posted. Thought I could learn but it's not proving very friendly. It's frustrating as I've been able to work out pulling data from sage and adjusting SQL queries (when I don't know SQL).

Access just seems so damn difficult and missing so many basic features.

My next hope is to bring up the last [Quotenumber] based on the [Person], who will add their name from a drop down.

Sadly I have no idea how to do this. It needs to look at the [Person] they have chosen from the drop down in the current record they are entering.

Another thing which shows access is unfriendly - there's no easy way to stop it updating records unless you press the add record button. I've found code but that isn't easy to impliment. Grrr.

 

[Gasman]

Yeh, I thought the same when I thought of taking up brain surgery. How hard can it be right?, just a few sharp scalpels and a few clamps should do the trick. I've cut a few things to shape in the garage in the past and superglued them back together.

Then I found out that if you cut in the wrong place, you could do a lot of damage, and as you could never have a backup, I decided to forget that idea.

FWIW I know of people who think anything but a simple formula in Excel is for the technical people.?

Horses for courses.