Looking for some Simple Advice

[ConcordMan733]

Hi there, I've been researching the feasibility of using a database to complete some tasks at our company. We want to implement an Engineering Change Notice system, and our idea was to use Access to insure that numbers aren't doubled up by accident. My question is given the following description, in reality is it very difficult to program or are there any sample databases you can think of that are along the same lines? Note: I have little experience in access but am an expert in excel if that overlaps at all?

Basically i'd like to be that you open the file, the home tab has a small window with Pending ECN's with their description. Theres a button on the home Tab to create a new ECN with a form that pops up to fill out the info including description. Theres a button to show an excel sheet of all the ECN numbers with descriptions. Also as i mentioned before I need to make it so that it assigns a new number for each complete ECN form.

This is the basics maybe down the road try to make different logins for each person since some people wont be doing ECN's etc..

But for now can you give me some advice on my basic described tasks? Thanks alot

 

[Galaxiom]

Search the forum for DMax. The most common use of it is to find the largest existing number in a field when generating a new sequence number.

 

[The_Doc_Man]

What you describe is also similar to a trouble-report system. Try searching the forum for topics such as

trouble reports
service calls
maintenance calls

 

[Lightwave]

The following site has a number of pre-built options and you get all the code so you can go behind the scenes and view the code. You are unlikely to find something that does exactly what you want but it will be a good start and once you understand your problem better you can come on somewher like here and ask a specific question.

http://www.accesstogo.org.uk/index.html

If you are continually generating new codes the very simplest bullet proof system available in a database (any database) is to have the PKID as the ECN number. PKID stands for primary key identification (a long word for a completly unique number) As you are aware unique codes are central to nearly all systems. With databases the standard format is just to have a incrementing number (no frills no slashes dots or spaces). If you actually distill your ECN down that's what it is.

In databases you create a PKID very very easily (I can probably do it in under 10seconds)
Creating a field set the generic character eg (Long Integer) and allocate it the autonumber property and annoint it as the Primary Key.

This means it counts
1
2
3
adds one every time

If a record is deleted it will remain vacant and numbers will continue from the maximum of the table list
1
2
4
5
7
8

8 records initially input with 3 and 6 subsequently deleted.

On your repair notes you would have the 1,2,3 or whatever number. If this field is given the primary key attribute all databases will absolutely NEVER repeat the number......

Repeating primary key numbers is an end of the world scenario for a relational database.

Spreadsheets and Databases are similar in many many ways. I often think of a spreadsheet as an unstructured database. You will have to slightly alter your thinking from rows, cells and worksheets to the idea of records, columns and tables

Forms are the equivalent of highly tied down worksheets
Tables are the equivalent of worksheets with rows of columns but no calculations
Macros are in both
And programming can be in both

By designing forms you are creating the ability to manipulate individual values the way excel spreadsheets manipulate individual cells. The only difference is that with databases you have to formely isolate the record prior to making the calculation. With a spreadsheet you may not realise this but by scrolling down to the appropriate row and selecting the cell you are intuitively doing the exact same thing (isolating a record) that you do in a database by scrolling to the particular record.

Calculated amounts can be stored or instantly forgotten. This is useful for instance calculating a persons age. Why store that value just call up the person and calculate age from the system date and the true important date the birth day... While in other circumstances it may be important to store calculated amounts - eg tax as rates change.

If you can use a spreadsheet with a bit of reprogramming databases should be easy.

 

[Galaxiom]

If you are continually generating new codes the very simplest bullet proof system available in a database (any database) is to have the PKID as the ECN number.

Creating a field set the generic character eg (Long Integer) and allocate it the autonumber property and annoint it as the Primary Key. If this field is given the primary key attribute all databases will absolutely NEVER repeat the number......

Some developers eschew the use of an autonumber field for any purpose where the value has meaning to the user because they are not controllable. Although they generally do increment steadily they have been known to go off to a new starting point, even using negative numbers.

In a perfect world they "absolutely NEVER repeat the number" but in reality they occasionally can and do go wrong. While Access won't duplicate the number, the problem is it won't insert any records either. I have only had it happen once.

When it happens, intervention is required to reset the seed. Until Access 2000 this simply required Compact and Repair. In later versions it requires more complex steps.

Incrementing a number is not particularly difficult. DMax + 1 is popular and reasonably reliable with a limited number of users but I prefer to store the next number in a table. Done properly it is absolutely reliable under all conditions.

Tables are the equivalent of worksheets with rows of columns but no calculations.

The superficial similarities between a worksheet and a table can lead to misunderstandings. The most important thing to realise is that records in tables have no intrinsic order like the rows in a spreadsheet. The concept of the next record has no meaning until you order the records in a query or recordset.

Moreover there is no simple, standard way to refer to the previous record even after thay are ordered.

 

[Lightwave]

I should qualify my above statement - in my experience they don't repeat...

Shiould remember the only certainty in life is that there aren't any.