UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Limited Serial Numbers, Access 2016    
 
   
lenartc
post Nov 15 2019, 02:18 PM
Post#1



Posts: 2
Joined: 15-November 19



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.



Go to the top of the page
 
NimishParikh
post Nov 15 2019, 02:28 PM
Post#2



Posts: 235
Joined: 30-November 10



welcome2UA.gif

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
Go to the top of the page
 
projecttoday
post Nov 15 2019, 02:40 PM
Post#3


UtterAccess VIP
Posts: 11,245
Joined: 10-February 04
From: South Charleston, WV


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.

--------------------
Robert Crouser
Go to the top of the page
 
lenartc
post Nov 15 2019, 02:51 PM
Post#4



Posts: 2
Joined: 15-November 19



QUOTE
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.

Go to the top of the page
 
GroverParkGeorge
post Nov 15 2019, 05:47 PM
Post#5


UA Admin
Posts: 36,165
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th December 2019 - 07:58 AM