Prefix + AutoNumber as Item Code (1 Viewer)

[GinaWhipp]

This is done after you make the Table and the Form. You can have it any way you like and it can be automatic but again, you are jumping ahead of yourself. You need Tables and Forms first.

 

[johannaellamay]

This is done after you make the Table and the Form. You can have it any way you like and it can be automatic but again, you are jumping ahead of yourself. You need Tables and Forms first.

You are right. But I've already made my initial table and a form to go with it. I just used my old database in Excel and imported it in Access. I'll attach it in the next post.

 

[johannaellamay]

Here's a copy of my very simple database. I didn't want to edit the layout/style yet.

View attachment Database1 - Copy.accdb

 

[GinaWhipp]

Looking at your sample now...

 

[GinaWhipp]

Hmm, there is no...

ItemID
ItemCode

And if you're going to be needing Reports by Department, I don't see a field for that either.

 

[johannaellamay]

Hmm, there is no...

ItemID
ItemCode

And if you're going to be needing Reports by Department, I don't see a field for that either.

I guess I'm going to have to make a different table for a list of employees and departments. What I've shown you is just a table of all the items that I have on stock, and a form to input or edit new or existing items. I just really want to have an automatic ItemCode. When you said that I have to have Tables and Forms, I thought what you meant was this. ))

Disregard the departments for now. I'm just focusing on my main table first.

 

[GinaWhipp]

Okay, let me explain clearer...

You can't just throw a Table and Form in and then go from there. This is like building a house...

Foundation = Tables
Walls = Queries
Forms = Decor

You can't just throw up any old foundation and throw up some drapes and think okay, I can add on as I go along. You need to get the foundation correct from the beginning or the house is going to fall down. So, forget about the Forms and everything else and put ALL the tables you are going to need together now. Here are some links that should help you along the way...

Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page...
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials

UtterAccess Newcomer's Reading List
http://www.utteraccess.com/forum/Newcomer-s-Reading-List-t1998783.html
Help with Normalization
http://www.access-programmers.co.uk/forums/showpost.php?p=1146957&postcount=2

Sample data models...
http://www.databasedev.co.uk/table-of-contents.html
http://www.databaseanswers.org/data_models/

Naming Conventions…
http://www.access-diva.com/d1.html

Other helpful tips…

Setting up a Model Database
http://www.access-diva.com/d11.html

My Database Standards...
http://regina-whipp.com/blog/?p=102

Databases do not get built in a day or two, soe of them take months just to get the tables right. So, relax, take deep breathes and work on the tables and then post those.

 

[johannaellamay]

I know what you mean. I've been reading and watching tutorials on the internet and have made a sort of framework on how I want my database look like.

I really just thought that I could generate an automatic ItemCode (prefix+autonumber) as I go along setting up my Items Table.

Thanks a lot for the help and thanks for the links. Will definitely check them out and will let you know about the progress.

 

[GinaWhipp]

We don't want to write the code twice... So, we get all the other parts in place first and then we write code.

 

[gemma-the-husky]

Yeah, I get your point. But doing so would totally defeat the purpose of extracting the first letter of an Item and combining it with a number. It's just gonna add more fields and a lot more confusion.

Plus, I want the numbers to return to 1 after every letter, example:

Item Code Item
Adapter A001
Air freshener A002
Alcohol A003
Ballpen B001
Bandage B002
Cotton bud C001

And so on...

So if I follow your advise, it's gonna look like this:

ItemCode (PK) ItemNo (autonumber) Letter Item
A1 1 A Adapter
A2 2 A Air freshener
A3 3 A Alcohol
B4 4 B Ballpen
B5 5 B Bandage
C6 6 C Cotton bud

Since the ItemNo is autonumbered, then I wouldn't be able to reset the numbers for every letter, would I?

:banghead: HAHA. I'm sorry, I think too much sometimes. I appreciate your reply, really.

No. I said it was a matter of taste whether you had one sequence for each prefix or not.

Either nextnumber = dmax (existingnumbers) + 1

Or

nextnumber = dmax (existingnumbers for required prefix) + 1

Making the field a single text field and then having to slice the string is going to be much less efficient.

 

[johannaellamay]

No. I said it was a matter of taste whether you had one sequence for each prefix or not.

Either nextnumber = dmax (existingnumbers) + 1

Or

nextnumber = dmax (existingnumbers for required prefix) + 1

Making the field a single text field and then having to slice the string is going to be much less efficient.

How's that gonna look like?

 

[vbaInet]

I know what you mean. But where do I put that expression? I mean, in which format property? Thanks by the way for the quick response.

Ignore that post. I misread your OP and I can see you're getting help from GinaWhipp and gemma-the-husky so I'll stay on the sidelines.

 

[johannaellamay]

Gina,

I know I'm being really annoying right now. But I want to just settle with having different fields for my strings.

Yes, I'd have an [ID] Field which is autonumbered.
I'd have an [Item] Field
Then I want to have a different field for [ItemCode] which will add the first letter of the [Item] Field to the [ID] autonumbered field.

How do I do that?

 

[GinaWhipp]

You are not being annoying...

You cannot add the ItemCode till after we get all the Tables designed and we move on to Forms. (You keep focusing on the decor and not the foundation.) That said, if you want to add them manually because you have data in the table you can. However, the only thing that should be in the database is test data until all the tables are worked out.

 

[johannaellamay]

You are not being annoying...

You cannot add the ItemCode till after we get all the Tables designed and we move on to Forms. (You keep focusing on the decor and not the foundation.) That said, if you want to add them manually because you have data in the table you can. However, the only thing that should be in the database is test data until all the tables are worked out.

Okay okay. I'll work on the tables. :3 Will update soon. :banghead:

 

[johannaellamay]

Gina,

Let's just say I'm doing something like this in Access:

http://indzara.blogspot.com/2013/07/InventorySalesManager.html

A sort of point-of-sale system, but applied in a different way. I've already been able to do something like this in Excel, but for confidentiality reasons, I've just showed a link of where I got my format from.

1. I want to be able to keep track of inventory levels especially when they are low.
2. I want to know who or which department gets to use most supplies.
3. I want to update, live, the level of inventory everytime a staff requests for a certain item
4. Every department has a different fund source because our company has more than one income generating programs

This is how the process goes in the office:

1. I, the property custodian, keep all inventory
2. The procurement department for example purchases office supplies under their own fund source
3. I receive them, record them on my database (inventory level/quantity), and add new items if the item is not listed yet
4. A staff requests for an item from me. I let the staff sign an Acknowledgement Form (AF) before releasing the item. The AF looks exactly like this:

Ref. No. | Date | Requesting Staff | Department | Fund Source | Item Code | Qty | Unit | Signature of Requester | Released By | Remarks

5. I release the items. Every time a staff requests for an item, his/her department sort of "borrows" items from the Procurement Department's fund source. Later on, Procurement will "replenish" the items and will be charged to the requester's fund source.

6. After 3 weeks, I will prepare a consolidated list of all items released under different departments and fund sources.

7. I will forward the list to Procurement and they are in charge of purchasing all the items.

8. I receive all items, and the cycle continues.


This is my progress so far:
View attachment X.accdb

I have included an Employee table because I would need those information when I issue employees with items that need accountability for example: laptop, cellular phone, etc. But that's a different problem. I just want you to know that I'm gonna need that table later on, so it's not unnecessary.

For confidentiality purposes, I've also made fictional employees and items. But the thought is totally the same.

PROBLEMS:


1. What should I do next?
2. What kind of object should I use to keep track of inventory? Like when I receive and release items.

:banghead:

 

[GinaWhipp]

You are going to need a lot more tables. This is not Excel, this is Access. Here's a sample Data Model for tracking Purchase Orders and Inventory...
http://www.access-diva.com/dm12.html

And there are plenty more Data Models out there but they all come down to the same thing, you need more tables and you have to stop thinking *flat file* (Excel) and think relational (Access). You also have to build for tomorrow not right now. You don't build the foundation for a 3,000 square foot hose to only accomodate 2,000 square feet because you're going to add the other 1,000 at a later date. You need to do a foundation for 3,000 square feet now. Understand, this is not a two hour or weekend job, so stop rushing...

 

[jdraw]

A great line indeed, Blaster and Gina.
johannaellamay,
I think you have a fixation with the alpha character and a number. Access only needs a unique identifier for its PK use. I would recommend an autonumber as PK -that's it's purpose/function. If you really "need" an alpha and a number for your use, then use separate fields and concatenate them. This field is not necessary for Access. If the alpha character and number mean something to you, then go for it--but not as PK.

Bottom line as the others are trying to tell you, get all your facts identiifed, get your tables designed, establish your relationships, work through the set up with test data to make sure the model matches your business need --then deal with the decor.
Good luck with your project.

 

[johannaellamay]

You are going to need a lot more tables. This is not Excel, this is Access. Here's a sample Data Model for tracking Purchase Orders and Inventory...
http://www.access-diva.com/dm12.html

And there are plenty more Data Models out there but they all come down to the same thing, you need more tables and you have to stop thinking *flat file* (Excel) and think relational (Access). You also have to build for tomorrow not right now. You don't build the foundation for a 3,000 square foot hose to only accomodate 2,000 square feet because you're going to add the other 1,000 at a later date. You need to do a foundation for 3,000 square feet now. Understand, this is not a two hour or weekend job, so stop rushing...

That's overwhelming. Thank you. I'll study your model first and I'll rethink my database. Thank you!

 

[GinaWhipp]

Did not mean to overwhelm just wanted you to understand it's more than a few tables. We'll be here when you ready...