Do I need multiple tables?

[PeterWieland]

I take care of the portable appliance testing for several charitable organisations, as well as my own equipment. I want to move from paper Word document records, and thought it would fairly simple! I've designed some fairly complicated databases in the past (over 10 years ago!), but I seem to be stuck at the first hurdle.

The ultimate aim is to produce a monthly list of all equipment across all organisations (there are 7 of them) that is due for re-testing, and to enter test results straight into the database.

The first step is to produce an equipment inventory. This would be the basic information of make, model serial no. etc, how often it should be tested, last test date and which organisation owns it. So far so good. I have a table with all the fields I need, and an autonumber as a unique identifier.

Now the tricky bit. I also need to generate a second unique identifier (other than the key field) which is the number that goes onto the testing sticker. This will typically be a two letter identifier for the organisation e.g. PW for my equipment, TC for Theatre Club, followed by a sequential number.

ID PAT_ID
000001 PW001
000002 PW002
000003 TC001
000004 TC002
000005 PW003

Obviously each field has to have unique values, the values should be auto-generated, and a PAT_ID of a deleted record should never be used again.

Would it be better to have a separate table for each organisation's inventory? It seemed to me that all of the inventory should be in one table, but I can't figure out how to auto-generate the PAT_ID field.

 

[Dreamweaver]

I'm at work at the moment and cant give a full answer at present but should you not get help I'll see what I can do when I get home, I'm an electrician so understand pat testing

Mick

 

[Deleted member 147267]

My advice would be to split the parts into 2 fields, text in one, number in the other as that will make this so much easier. Generating unique id's is a very common question and I'm sure there are many answered posts about it in this very forum but I don't have any links to any specific threads. As for tables, not one for every org. If an item went from one org to another, you'd have to move data. While this might never happen, the very notion that it could indicates a data structure problem. You'd reassign the location id to such a record, not move it from one table to another.

You probably should have an equipment table, an organizations table, and a joining table that melds owners with equipment. This is typically called a junction table.

 

[The_Doc_Man]

The correct way to approach this problem is to remember and apply a rule about table construction.

The basic idea is that every record in a table should have some sort of key. Then every field in a record should depend that key and nothing else. Your "title" question can be answered by application of this rule.

If you have a location table for which the text portion of the PAD_ID is a location code, good structural design suggests you would want to have this location code isolated so that you can use it in a one-to-many relationship. The numeric part is easy enough to generate using a combination of DCount and DMax functions based on the particular location ID.

In your "testing history" table you would have the item ID (from your item inventory table), the location code (from a separate location table), the individual PAT_ID sequence number, the date of testing, ID of who tested it, etc... anything about the test BUT NOTHING ELSE. Splitting that PAT_ID into two parts retains the ability to establish relationships to a location for subsequent reporting.

If you ever need to build a report you can build a QUERY that contains the location code and the sequence number reformatted and concatenated so as to create your PW001, TC001, PW002, as appropriate. BUT you can build reports based on location, or on dates, or on item "Make" or serial number based on appropriate JOINs between this testing history table and the other tables where the details reside.

 

[Dreamweaver]

I've put a few tables together that should get you started.
I have imported some from other projects so feel free to delete any fields you don't need.
This is a basic system I might start with I have only used a pass/fail for tests as thats what it was when I was a working electrician.


mick

 

[jdraw]

Peter,

I suggest you work through 1 or 2 of the tutorials from RogersAccessLibrary in this link.
Each has a problem statement, a procedure and a solution. Each tutorial will take about 45 minutes, but you will learn about tables, relationships and design. The tutorials lead you to a solution that you can test with some test data based on the description of the situation/tutorial.
Once you have completed the tutorial(s), you can use the procedure to design your own database.
There are several articles at that link that can help you with Database Planning and Design (and more).
Good luck.

 

[PeterWieland]

Thanks for the replies folks.

In the past when starting a database from scratch, I've always started with a table for the primary information and use that as the 'master' table. In this instance the primary purpose of the database is to store PAT results, so that's where I started! I think I am going to have to go back to first principles.

I've put a few tables together that should get you started.

I've looked at your tables Mick, and some interesting ideas there which will probably help further down the road, but I can't see a way to generate several separate sequences for testing label numbers. This is one of the reasons for not using some of the commercial solutions, they just generate sequential numbers with no way to vary them for different locations.

I suggest you work through 1 or 2 of the tutorials from RogersAccessLibrary in this link.

I am working through the entity-relationship diagramming method. It is an interesting approach, and a very different way to normalise a database than the way I was taught many years ago!

My advice would be to split the parts into 2 fields, text in one, number in the other as that will make this so much easier.

I think this will be the approach I take, but still need to work out how to generate the number part.

If you have a location table for which the text portion of the PAD_ID is a location code, good structural design suggests you would want to have this location code isolated so that you can use it in a one-to-many relationship. The numeric part is easy enough to generate using a combination of DCount and DMax functions based on the particular location ID.

After splitting the PAT_ID field, I think this is the best approach.

 

[Minty]

Do you have stickers already created?
I had this issue and I wish I had simply used one set of numbered stickers for all pat test records, rather than getting clever and having a set per department. (in you case per organisation.) It would have really simplified things...

 

[Dreamweaver]

Hi Peter when I designed the tables I created the key of the test results table so it was a incremental value this could be formatted the way you like you could also combine the items formatted I'd and the test result formatted value like you want in post 1 the only thing I missed was a pw, ta but this could be included in the items category table.

You should note in the items table I store the next test date this could be gained by a dateadd but I decided to store it because in my view it's a primary value that would be used to show when an item requires retesting you can do it which way you prefer.

Mick