Table ID to be generated from names...

[arrudac30]

Hi all.

I have been playing around with excel and have done quite well, considering my limited knowledge on VB. Had it not been for the invaluable help from a forum (its members), I would have not been so successful.

Any way, I was advised that I would be better off, in the long run to have my own invoicing small application, to be access based. I do about 1 to 2 invoices, and I am quite happy with my excel sheet. But, as everything, I am keen on learning a bit more about access. It does bring back old memories (20 years ago or so) when I was told that I had replicated data across almost all of my tables. So, in all, I have quite a lot to learn.

Anyway, my first question would be in relation to a table ID field. I can have it in auto format, and this will give each record a number which will increment by 1.

Is it possible to have this field to be, again auto populated but to retrieve info based on customers name? For example, the customer's name is John Smith. The ID would become JOHSMI01. I understand that this could repeat again, so more code would be added (VB I take it) to prevent another ID field to become JOHSMI01 and instead become JOHSMI02.

I have no doubt that I will find here the same help, and perhaps some of the same users that have helped me in the past years, on the excel forum.

I shall go to the introduce me forum and introduce myself properly.

Meanwhile and first and foremost, many thanks to you all.

Kind regards,
Albert

[CJ_London]

Is it possible to have this field to be, again auto populated but to retrieve info based on customers name? For example, the customer's name is John Smith. The ID would become JOHSMI01

Autonumber fields are purely numeric and are only guaranteed to be unique and not consecutive and should be used for record identification purposes only and assigned no other meaning at all - see this link http://www.utteraccess.com/wiki/index.php/Autonumbers.

it is doable (google 'dmax+1') but your suggestion is potentially fraught with problems - what if customer changes name (or you correct it), customer is one name only, two customers have the same first three letters (John Smith, John Smithson) etc. Here is a link to get you started http://www.access-programmers.co.uk/...d.php?t=221597

By all means create a unique code initially based on first three letters, but don't tie yourself to this being the only option - suggest your customer table would be something like

tblCustomers
CustomerPK autonumber
CustomerAccountNo - text, indexed, no duplicates (initially based on 1st 3 chars)
Firstname text
Surname text
etc

[arrudac30]

Autonumber fields are purely numeric and are only guaranteed to be unique and not consecutive and should be used for record identification purposes only and assigned no other meaning at all - see this link http://www.utteraccess.com/wiki/index.php/Autonumbers.

it is doable (google 'dmax+1') but your suggestion is potentially fraught with problems - what if customer changes name (or you correct it), customer is one name only, two customers have the same first three letters (John Smith, John Smithson) etc. Here is a link to get you started http://www.access-programmers.co.uk/...d.php?t=221597

By all means create a unique code initially based on first three letters, but don't tie yourself to this being the only option - suggest your customer table would be something like

tblCustomers
CustomerPK autonumber
CustomerAccountNo - text, indexed, no duplicates (initially based on 1st 3 chars)
Firstname text
Surname text
etc

Hi Ajax.

Many thanks for your input. It makes sense.

I shall read the links you have pointed out.

Kid regards,
Albert

[arrudac30]

Hi Ajax.

I understand this is different than excel, but what I had done in excel was to auto generate a user ID by taking the first 3 letters of first and last name following by a 01.

If, in case of these first 3 letters already existed and thus in the database like say JOHSMI01, then the ID would be JOHSMI02.

But I see what you mean and I will hold on to your advise.

Many thanks.

Kind regards,
Albert

[Micron]

I do about 1 to 2 invoices, and I am quite happy with my excel sheet.

This may seem weird, but given that you already have something that works, why re-create the wheel for the small amount of invoicing you seem to be doing? Your spreadsheet has the ability to do calculations on numbers far easier than in Access, and it's easy to set up rows and columns to create wonderfully formated paperwork that you can keep records of as sheet copies. There is nothing wrong with wanting to learn about Access, and if that's the primary reason for converting your project, I'd say go for it. First, do yourself a favour and read up on database design - there are lots of free tutorials and web info. The worst thing you can do is dive in and end up with something that is worse than your spreadsheet. Sounds like you are fairly adept at Excel code, but go in knowing there isn't a lot of similarity. Even something as simple as opening an Access form versus Excel userform is not the same. In your situation, I'm not sure I see a big gain for the time spent, but that may not be your primary goal. The satisfaction of learning something new and useful can be immeasurable, and your invoicing project should be as good a reason for learning as any other.

[arrudac30]

Hi Micron.

Many thanks for the kind words.

There are quite a few things that are pushing me to work on this invoicing project.

First, as you well mentioned, I do find programming fascinating. The manipulation of data and its results depending of what is programmed to do, is remarkable.

Even though I have been using excel for the past 8 years, I have not created a single line of code on my own. Always on-line googling and on forums for any help I can get.

Fortunately for me and others, there are quite a few ( a lot I should say) kind good people, ready to give their knowledge and time to those who need.

I am fairly afraid of access as I can't even remember the last time I allowed it to be installed on an office installation.

And also, I have never grasped the relationship between tables.

I still remember 20 years ago, when I was told I had a good concept/idea of what I wanted but my tables were cluttered with duplicated data.

As for an example, back in the days I was trying to use access, this was when I was literally afraid of excel, I had a table for customers and a table for post codes.

The post codes where also being entered in the customer table. I was told that I could have those values only in the post codes tables. I always wondered, if this is the case, how do I relate the customer to their own post code if I am not saving this data, although replicated, in the customer table?

So, I admit, there is a long rood ahead of me but I am eager to learn.

Also, even though I only produce a couple of invoices a month, I am looking into growing as a business (self employed fire alarm technician).

Again, I have been mostly fortunate for having good people on the other side, forums especially, giving me their time and knowledge to which I can't thank enough.

From what I have seen on youtube, the VB code is different but I feel that the principle is still there.

Many thanks once again for your input.

Regards,
Albert