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

 

[Pat Hartman]

Hi Steve,
Welcome Aboard I was down in your town a couple of months ago. I played at the bridge tournament being held at the convention center.

I don't split the database until I'm almost finished. It is easier to send one file to the users for testing than two.

If you are considering SQL server, do the planning at the beginning so if you decide to go that route, you won't have any problems. Search for "optimizing Access for client server" articles. The basic theme is to always use queries with criteria that limit the number of rows and columns returned. NEVER bind forms directly to tables or queries without criteria. There are a couple of things you need to do when writing DAO code and you have to understand that SQL Server doesn't assign the identity value (autonumber) until the query is sent to the server (saved) unlike Access (ACE) where the autonumber is assigned as soon as you type the first character in a form. Most of the time linked tables and "Access" querydefs will work just fine. Occassionally, you may find you need to create SQL Server views or stored procedures or pass-through queries but don't start with those. Only create them to solve problems. Once you go the unbound form route, you really lose all the benefits of developing in Access to begin with and would be better off with vb.net which is simpler to distribute.

I can't offer any suggestion regarding validation without knowing more about your application. You probably will need to write queries but lets design the tables first.

Do some reading on normalization. The first instinct of someone comfortable with Excel is to create a spreadsheet and call it a table. Relational databases are very different from spreadsheets even though the look similar when you look at a table in datasheet view. With Excel, the data and presentation layers are combined. With relational databases, they are separate. Data is stored in tables and displayed in reports and updated via forms. Neither the reports nor the forms have to look anything like the underlying tables.

 

[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

 

[Pat Hartman]

Yep, the event was in Palmetto. I live in Connecticut. I was visiting Florida to look for investment properties (apartment buildings) and scheduled the trip so I could play a couple of days and visit with a friend in Sarasota.

A simple way to get the network ID is to use the Environ() function.

Environ("UserName") gives you the user name.

second primary key

There can be only one primary key and if you use an autonumber, that should be the PK. You can have unique indexes to implement your business rules. For example, a student should only enroll in a class once so the PK would be RosterID (an autonumber) but you would have a unique index to make sure the combination of StudentID and ClassID are unique.

Forms and Reports can be "bound" and that means that a table or query is defined as the RecordSource. If a form or report is bound, it can contain bound controls so the ControlSources would contain the names of columns contained in the RecordSource. When you "bind" a form/report, Access takes care of populating all the bound controls and saving any updates to them. It also provides navigation controls that let you scroll back and forth through the bound recordset. If you've ever programmed in another language, you would know how much work this saves you.

28 columns isn't too many for a table as long as you don't have any repeating groups. You can usually spot repeating groups because they are given the same name followed by an index such as Contact1, Contact2, Contact3 or some other part of the name repeats such as JanBal, FebBal, MarBal. It's a little harder to recognize, Electric, Gas, Insurance, Water, Trash as a repeating group but it is because they are all expenses.

 

[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

 

[Pat Hartman]

You need to be careful with Y/N fields if you will upsize. Jet/ACE support three states for this data type - True, False, and Null but SQL Server only supports two states - True and False so make sure you have defined the default to be False (or True if that works better). If you actually need the Null state, change the data type to integer and use an option group or combo to choose rather than a checkbox. Using the checkbox, you have no way to return to the "null" state. Once you check the box, unchecking it sets the value to False. It never reverts to null.

I am not familiar with the function you are using so I can't comment.

Regarding functions in a table - Access (the development environment) is tightly integrated with its host database (Jet or ACE depending on the version) and it is sometimes hard to separate the two but in fact, they are separate. Jet/ACE can in fact be used without Access and many applications written with other tools do use Jet or ACE as their data store. The tax program I use to prepare my business taxes uses Jet. Outlook used to use Jet. Even some websites use Jet or ACE to store data. None of these front ends are written in Access VBA. Once you understand that, you will understand that Jet/ACE doesn't support VBA and that is why you can't write functions at the table level. The database engine would not be able to interpret them. The functions used in queries, are all processed locally by VBA rather than by Jet or ACE.

 

[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

 

[Pat Hartman]

Not all versions of Access set the default to zero so checking will prevent future problems.