Can I use Access for this?

[Lightwave]

Yes form design is the part that takes all the time.

For Access 2010 some tips
http://www.youtube.com/watch?v=nxCUj0X0K5k

This not bad short part on buttons at end
http://www.youtube.com/watch?v=huxHwvO6FHo&feature=related

this one is a bit more advanced shows more about properties.
http://www.youtube.com/watch?v=PVVFl4RGYFc&feature=related

Afraid I don't know any specific sources

 

[Pat Hartman]

I don't like the Access split form. I find it hard to control so I prefer to build my own. It is simple enough. Use an unbound main form with two subforms. SubformA is list view and subformB is single view. Use the click event of the listview to requery the single view. Use the AfterInsert event of the single view to requery the list view so it shows the newly added record.

 

[shariq1989]

That looks great Pat. I'll definitely apply that to my forms. Starting to lose heart though.. Nothing really has been going smoothly at all. I'm gonna go back to the drawing board and figure out how to make the look up tables work and then restart form design.

 

[Beetle]

Starting to lose heart though.. Nothing really has been going smoothly at all. I'm gonna go back to the drawing board and figure out how to make the look up tables work and then restart form design.

Not sure what you mean by "make the lookup tables work". Are you talking about working out your junction tables? Something else? Hopefully you're not talking about lookup fields in your tables. See here for more on that.

It could be that at least part of the reason you are struggling with your forms is that your table structure is not correct yet. I'm not saying it's wrong, I'm more just asking the question - are you sure it's right? As has been stated by others in this thread, table structure is the absolute most important part of this whole process. If you don't get it right, then the rest of your development will be nothing but headaches and wasted time.

When I look at your ERD, a couple of questions immediately come to mind. Things for you to ponder anyway.

• The VesselInspections table essentially is the history of Inspections for each Vessel, so what's the purpose of the InspectionHistory table? Is this supposed to record details of what was done during each Inspection?
  
• What about tblEngines? A Vessel like this can have more than one Engine, but wouldn't a particular Model of Engine made by a given Manufacturer possibly be used in more than one Vessel? I'm not talking about the individual serial number of a specific engine here, just a make/model, like the RoadRunner 500 Turbo Diesel made by the Acme Engine Co.

If you can give us some more details about the nature of your inspections, we should be able to offer some more specific advice on table design.

 

[Lightwave]

You get substantially quicker as you get used to the interfaces and principles.

But this is why we thought it would be a push to produce what your wanting in the time available.

Trust me its a lot quicker in access than it would be in any other IDE

 

[shariq1989]

The VesselInspections Table is meant which Vessel needs what Inspection. So I have all the vessels in there with the inspections they need next to them. Sort of like:

Vessel1 Inspection1
Vessel1 Inspection2
Vessel2 Inspection2


And then the VesselHistory was meant to be a database of completed inspections.

Inspections are conducted by regulatory organizations like ABS and USCG. They are going to be independent of the overhauls and components. Overhauls need to be performed on components that are not just a part of the engine (pistons, cylinders, etc) like pumps or the propeller. Most overhauls are tracked by engine running hours. Inspections go off of time intervals. Most vessels has four engines. I actually did broaden my engine table a bit with attributes like "Manufacturer", "Model Number", "Location" (Port/STBD/FWD/AFT) and "Type" (Main/Aux).

 

[shariq1989]

And I understood that lookup fields are evil but I was wondering how I would incorporate the lookup feature in forms using combo boxes. Where users would select a VesselName but it would fill a VesselID into the table.

 

[Lightwave]

Shariq

When in design use the tools menu to select combo box.
Place the combo box on the form and relate it to a query which has two columns...

One column is the ID of the VesselName and the other is the Vessel Name
Make sure that the query has a sort on the Vessel Name field. (you will see why later)

Now refer the combo box to this query and ensure that when you create the column widths VesselID is set to 0 (Zero). It will ask what field you want to store this field value in when the combo is selected . You say you want to store the VesselID field in the VesselID field of the form source.

You are effectively creating the combo box on the form and referring it to the table rather than creating the combo in the table. Same result for the user but it avoids the problems associated with combos within tables.

Now when you select the combo the user will see a list of the Vessels (they won't see the ID because the width of the column is set to zero.) The query having a sort on it allows a user to have an alphabetical list rather than a random list (which is easier for operability)

Once you get good at this method you can increase the visible fields available when the drop down is selected (which can be very handy for usability) additionally the combo box object has a property which is list records default is 8 but I like to set this way up usually no less than 30 or so. This reduces the requirement on the user to scroll down through a tiny box.

 

[Beetle]

The VesselInspections Table is meant which Vessel needs what Inspection. So I have all the vessels in there with the inspections they need next to them. Sort of like:

Vessel1 Inspection1
Vessel1 Inspection2
Vessel2 Inspection2


And then the VesselHistory was meant to be a database of completed inspections.

OK, that makes more sense.

Inspections are conducted by regulatory organizations like ABS and USCG.

Then you probably want a table for Regulatory Agencies as well. If each type of Inspection is always performed by only one of the Agencies, then the Agency table would likely be related to tblInspections. If a given type of Inspection can be performed by different Agencies, then it would be related to tblInspectionHistory.

Overhauls need to be performed on components that are not just a part of the engine (pistons, cylinders, etc) like pumps or the propeller.

Yet you have all Components tied to an Engine. Is this what you want? As it is, you cannot track an overhaul of any Component without first selecting an Engine.

 

[shariq1989]

Two questions are pestering me right now.
1) Do I have to create a new table for every option I provide the users with? For example, the Main/Aux option and the PORT/STBD/FWD/AFT options. Do I need to create new tables called "Engine Type" and "Engine Location" even if its just for a couple of options.
2) The reason why I did not include Manufacturers, Agencies, etc was because I was still a bit stuck in my spreadsheet phase, where all I really needed was an engine name and hours. The engines were uniquely identified by using a different workbook for each vessel and within each vessel having "Port Main Engine", "STBD Aux Engine".. and so on. It's probably best that everything is documented but how necessary is it? I was just thinking I could create rough tables right now and add attributes once its all shaped up. But from what I collect about your advice is that I should not build my house on sand? And that each attribute should be broken down to its bare essentials? Like instead of "Port Main Engine" being the engine name, there should be a different columns where "Port" and "Main" are individually selected?

I did think I could get away with having the inspections be called "ABS Load Line Inspection", "Coast Guard Certificate", etc and not have to deal with who performs it because really, the title is all that was needed. Not trying to be lazy, just not grasping the importance right now..

And yea, for starting off I was just going to do main engine components and see how it would turn out and then add other machinery as I went. But if it is better to do it all in one go, I can start heading that way..

 

[shariq1989]

What i'll do is change "Components" to "EngineComponents" and then create a new table for auxiliary machinery where everything else would be. But then I'll also have to create yet another table which lists which vessel has what aux machinery (sort of like VesselInspections).

 

[shariq1989]

Thanks for showing me how to relate queries with combo boxes, Lightwave! Seems like it makes a lot more sense to me now. Queries and forms make it easy for humans to fill in and edit the database whereas tables should make sense only to the computer... and thus it would be best for the end user to not be able to see or access the table directly.

 

[Beetle]

1) Do I have to create a new table for every option I provide the users with? For example, the Main/Aux option and the PORT/STBD/FWD/AFT options. Do I need to create new tables called "Engine Type" and "Engine Location" even if its just for a couple of options.

That's somewhat of a judgement call on your part, but it's not uncommon to have several tables that are really nothing more than look up tables that hold a few values that have to be repeatedly entered for most or all records. This makes data entry easier for the users and, more importantly, more consistent. Users will often get "tired" of typing, for example, PORT in every record and will start short cutting it with P or PT or who knows what.

I was just thinking I could create rough tables right now and add attributes once its all shaped up.

Depends on what you mean by "all shaped up". If you're talking about making some final table design tweaks before you spend a lot of time designing queries, forms and reports, then that's a normal part of the process. Once you've created created those other objects (queries, etc.), if you then go back and do some table re-design then you have to (possibly) re-design the other objects as well. That will introduce you to a whole new level of frustration. You may want to throw together a few quick and dirty forms just for testing basic functionality, but I wouldn't worry about appearance at this point.

I did think I could get away with having the inspections be called "ABS Load Line Inspection", "Coast Guard Certificate", etc and not have to deal with who performs it because really, the title is all that was needed.

Again, that's going to be a judgement call on your part. I don't know the intricacies of your business, so I can only offer suggestions. Mostly, I'm just saying don't press too far into query, form and report design until you're confident that your table structure is going to work the way you envision it.

 

[shariq1989]

Yup. I think I pretty much have all I need. I'll finish up and confirm table design by the end of this week and throw it up here. Thank you so much for following along, Sean.