Simple Inventory Managment - Item Codes

[richkoi]

I am using MS Access to create an inventory database so I can run queries to do product counts/analysis/etc... The best way to make tables work together is to use a key. I would like to create a key code for my inventory that is intuitive so I have come up with the following:

Examples:

ITEM NAME: BAMBUSA OLDHAMII
ITEM SIZE: #15
CODE: BAMOLD15

ITEM NAME: PHYLLOSTACHYS NIGRA
ITEM SIZE: #05
CODE: PHYNIG05

My question is: Is there a way to have Access automatically generate this code based on the ITEM NAME and ITEM SIZE fields? Please let me know if this is possible.

Thanks,

Rich

[RikR]

You should be able to fill the field by taking left six charectors of name and right two charectors of size and putting them together to form the code.

I don't remember the details but I think it is pretty basic Access stuff. I remember reading about it when I read one of those big 900 page Access books once.

[RikR]

I thought the keys were always auto-generated and sequential.

You may have to let Acccess do the keys it wants, and you create your code number, or stock number.

[richkoi]

You should be able to fill the field by taking left six charectors of name and right two charectors of size and putting them together to form the code.

Yes, that's what I want to do, but can't figure out how to do it.

Anyone know where there is a tutorial explaining this?

Thanks,

Rich

[RikR]

Search Access Help for:

"Extract part of existing text values using a calculated field"

Result:

Extract part of existing text values using a calculated field
In query Design view, create a query. Add the tables whose records you want to use.


To find values in part of a field, use the Left, Right, or Mid function in an expression in an empty cell in the Field row in the query design grid.
The syntax for these functions is:

Left(stringexpr,n)
Right(stringexpr,n)
Mid(stringexpr,start,n)

The stringexpr argument can be either a field name (enclosed in brackets) or a text expression; n is the number of characters you want to extract; start is the position of the first character you want to extract.

The following table shows examples of these functions. If the value in
Part ID is This expression Returns
BA-7893-R12 Left([PartID],2) BA
BA-7893-R12 Right([PartID],3) R12
BA-7893-R12 Mid([PartID],4,4) 7893


Add any other fields you want to include in the query to the query design grid.

[richkoi]

Thank you! That is very similar to the way it is done in Excel.

Thanks again,

Rich