[X]My Assistant

[lenartc]

Hi all,

Using Access 07-16 I am trying to create a Wine Database for our company. I have a beautiful vision of how I want it to work and in spite of browsing through a million posts in the past week, I really don't get, how to do it, the way i see it. Any help would be appreciated.

The biggest problem I have, is defining BIN numbers (or serial numbers) correctly. Due to limitations of our other systems we are limited to 4 digit serials to be constructed as:

Digit no.1: from 1-9 will tell you the type of wine
Digit no.2: from 1-9 will give you subcategory
Digits 3&4: from 00-99 - unique wine ID within category

EXAMPLE 1: If Type: RED WINE is 4 and if Subcat: USA is 3 a wine like: Silver Oak Cabernet, RED WINE, USA - would have its BIN number as 4300
EXAMPLE 2: If Type: RED WINE is 4 and if Subcat: FRANCE is 7 a wine like: Chateau Petrus, RED WINE, FRANCE - would have its BIN number as 4700
EXAMPLE 3: If Type: RED WINE is 4 and if Subcat: USA is 3 a wine like: Ramey Syrah, RED WINE, USA - would have its BIN number as 4301

IDEAL WORLD: When entering new wine via a form, once selecting Type and Subcat, the BIN field auto-populates with a unique BIN number. We will never have more than 100 wines within a subcategory at the same time, so the limitation of 2 digits is not a problem right now, but it will be in the future. To solve that, once checking ARCHIVED a query would send the wine into Archive and amend its BIN number with a suffix, ie. 4300-0001, 4300-0002 or 4700-0001, 4700-0002 etc, with each newly archived wine that once possesed same BIN incrementing by 1. This would release its BIN to allow a new wine to take it over.


WORST CASE: I will populate a table with 9999 BIN numbers and assign as we go along.


Any ideas on how to get the IDEAL scenario to work?





____
Lenart
Rookie Access user, but determined and quick to learn.

[NimishParikh]

I am hobbyist kind of Access user and my suggestion may not be as good as other members in the group, but, instead of having a field with four digits you can have three distinct fields, one for type of wine, one subcategory and one for the unique ID within the subcategory. You can make composite primary key on these three field to prevent duplication.

You can combine these fields to represent full four digit bin in your reports or display forms.

Nimish

[projecttoday]

This reminds me of SAP which has a "business area", like a department, of 4 digits. This was in the 1990's. I don't know if SAP has a 4-digit business area nowadays or not. Anyway, we had to cram 6 digits of information into 4 digits. We did manage to do it. But we should have designed a correct solution and just ditched SAP.

[lenartc]

I am hobbyist kind of Access user and my suggestion may not be as good as other members in the group, but, instead of having a field with four digits you can have three distinct fields, one for type of wine, one subcategory and one for the unique ID within the subcategory. You can make composite primary key on these three field to prevent duplication.

You can combine these fields to represent full four digit bin in your reports or display forms.

Nimish

Thank you for the welcome. This was one of the ideas yes. The other idea would also be to only have 2 columns and the categories would be a 2 digit number, combining both type and sub, so instead of 1,1 it would be 11. Another idea I played around with was to do the categories as thousands ie 1100 and just incrementing that by 1. So plenty of solutions there, but all a bit "dirty". The problem also comes if I used Dmax to automatically add highest 1 to highest ID, those 99 digits would run out quickly. I don't know how to define a lookup to look for "empty" BINs. So if in Category 13 i have 77 wines (1377) and I decide to archive wine number 1356, that releases that BIN, but Dmax will not see it. It will see 1377 as highest number and create new bin as 1378.

Hope that makes sense.

[GroverParkGeorge]

The best place to start is to invest time in learning the principles of good Relational Database Table Design.

Here's where I would recommend you start.