Some database planning questions...

[Steve@trop]

I'm in the early stages of creating a database. I don't have alot of Access/Database experience so consider myself a newbie. I've been doing alot of research on this site and others and can see that planning is critical to the success of a database project. That being said, I have already done a significant amount of work on this already but I don't believe I'm too far along yet to make significant changes if necessary.

The project I'm working on is a database that machine operators will use to enter equipment readings twice per shift, three shifts per day. Some of these pieces of equipment have over 20 readings each and there are alot of different types of equipment with different sets of readings they need to record (14 compressors, 30 or so condensers, 6 vessels, etc). They will be using Windows Pen tablets (ruggedized) to enter the data. They are currently doing all of this on paper log sheets and there is no way to track the history of anything with the paper method (they have boxes and boxes of these paper sheets lying around). I was hired to do this in Excel but it became obvious pretty quickly that Excel won't work and now I'm getting my Access trial by fire. This is probably THE classic database project!

To start with, I have some fundamental questions. I know I will at least need to split the database. One question I'm struggling with though is wether to stick with Access or go to SQL for a BE. There probably won't ever be more than a handful of people entering or accessing the data at any one time. They will want to be able to track which operator entered each record based on the logged in user. Is this something that can only be done in SQL?

Also, I believe I've got most of the tables I need already created. Should I wait until I've got all my tables completed before splitting the database?

Quite a few of my tables use lookups, from what I've seen on this forum that isn't the best way to automatically populate a field. What is the preferred method? For each set of readings I have lookups for OperatorName, Shift, MachineName, etc).

One other thing I've been struggling with is that I'll need a way for the operators (and management) to easily see if readings have been missed. On a paper form, it's easy to tell if they haven't filled out a section for a particular machine, in Access the only way to accomplish this that I can think of is a complicated query (a task which I may not be up to). Is there an easier way? Each day, each shift is expected to do at least one set of readings (rounds) for each piece of equipment. Some equipment requires two sets of readings per shift.

I know I've packed alot of questions into one thread. Perhaps I should split it up?

Thanks in advance for any help you can give.

Steve

 

[Steve@trop]

Thanks Pat,

I think the convention center you played at is right around the corner from my kids school in Palmetto. It looked like a big event. Do you live in the area or did you hop a plane to play bridge??

Waiting until it is mostly finished (to split it) makes sense. I experimented a little (split the Northwind database) and found that when you create a new table it doesn't automatically get linked to the front end. If there are alot of tables to add, that would probably get a bit tedious.

Using SQL server sounds more complicated than I thought. But so far everything I've done in this project has been that way. If I can get the system to insert the windows username for each record then I'll stick to Access because that seems to be the only reason I would need to go with SQL. The number of people using it simultaneously will be minimal so SQL may be overkill. I was once able to get Excel to pull the username but so far haven't had any luck doing it in Access.

One term I've seen alot but haven't got my mind around yet is "binding" or "bound". Does that just indicate a relationship or an object is tied to a table or is it something else?

I think I've got a pretty good handle on the difference between Access tables and Excel Spreadsheets. I've been mindful of the number of fields and have kept the number of fields in each table to a minimum. From what I've read, the idea is to keep the subject of the table as narrow as possible. For example the operator field only has the operators employee number, first and last name, shift, etc. I have a seperate table for each type of equipment (compressors, vessels, condensers, etc). .

The table with the most fields (28) is the CompressorRounds table. This is the table that will contain all the readings that the operators will be recording for each compressor. That is alot of fields. I could split it up but I would only be doing it for the sake of splitting it up. Each record will contain one set of readings (Rounds) for one compressor (pressures of various components, fluid levels, etc) so it made sense for me to keep them all in one table. Am I wrong about this? I've done the rounds tables for the other equipment types the same way but they don't have even half the number of fields that the compressor table does. These tables are going to be the ones that will get the largest data-wise. It's where the data we're most interested in will reside.

I set all tables with an autonumber primary key and in some cases also made another field a second primary key (where I knew each record in that field would be absolutely unique). Is that OK or will it cause issues going forward?

I just realized I've bombarded this thread with a bazillion questions. I'll try to slow down!

Thanks again Pat!

 

[Steve C]

Bound is, for example, when a TextBox on a Form Shows or writes something to a Field in a Table

An Unbound TextBox doesn't add or show data from a Field.

You might use an Unbound TextBox to calculate and display a time that an inspection is due to be carried out based on when the last inspection was carried out.

Usually you would keep calculations in Unbound TextBoxes where the result is, say, the sum of two Bound TextBoxes.

Others on here will have a better answer than that

 

[Steve@trop]

Thanks again Pat!

I feel I'm really on the right track now. That one big table worried me a little but it passes your test regarding repeating groups.

I was able to get the username from a user-defined function:

Public Function LoginName()

LoginName = CreateObject("wscript.network").UserName

End Function

I had tried it before but couldn't get Access to recognize the function after it was saved in VB (or so I thought). Turns out that it only works when you use it in a form. Access doesn't even give you the option of using user-defined functions in a table. This really helped me internalize that you shouldn't use any functions/calculations/lookups in tables. You know when you can sometimes almost hear the "CLICK" going off in your head when you really get something? I'm pretty sure that this means I can keep the database in Access but I will split it when it comes time to distribute it to the operators.

I have removed all lookups from the tables. They are only in the forms now. I also removed the timestamp (=now()) fields from the rounds tables and put them into the forms. This has already cleared up a wierd side-effect I was getting in some of my queries where an extra blank record would appear. It was picking up the "next" record because it was pre-filling the date. I do have a few yes/no fields in the tables. I wonder, should I change those to text and put the yes/no in the form instead? I think this might not matter much but just in case...

Thanks for the guidance on "Bound", I'll probably print it out and keep it next to my desk until I really get it.

You've been a great help on this.

Steve

 

[Steve@trop]

Thanks again for the great advice.

I'll change the default on all yes/no fields to "False". Which I think will help prevent any problems if they decide to upsize down the road. I only have test data in there at the moment so I have free reign to make any changes I need.

Thanks again,

Steve

 

[Steve@trop]

After checking my tables I've come to the conclusion that when you create a yes/no field Access automatically sets the default to "0" which I'm pretty sure means "false". I know I didn't set it myself. It's probably a good idea to check just in case though.

Steve