Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2010
    Location
    New Orleans
    Posts
    4

    Unanswered: Help w/ Queries to create Pilot LogBook in Access 2003

    Ok, I know just enough about Access to be dangerous - that's my skill level. I took a 2-day SQL class to try to help, but it hasn't yet.

    I'm trying to create a logbook for my military and civil flight time in Access 2003. I know there are commercial logbooks available, but I already have the data in the tables in my dbase and think it would be just as big a pain to try to field map for an export/import for a commercial logbook.....plus I want to customize the reports tha way I want them, etc.

    Among other reports, I would like for my logbook to summarize my flight time to complete page 3 of the FAA form 8710:
    http://forms.faa.gov/forms/faa8710-1a.pdf
    which provides those specific totals.
    I'd also like to create totals for the previous 90 days, 6 mos, and year (with a few differences from the 8710).

    Anyway, I've tried to normalize the data as best I can.
    Here's a breakdown of tables:
    AircraftType: lists aircraft types with check boxes to detail the aircraft as much as possible (with the information in column 1 of the 8710 and a few extra like 'high performance', 'complex', etc. )
    AircraftBureauNumber: lists all the individual serial numbers of aircraft and their types
    MilitaryFlightData and CivilFlightData: 2 tables with flight time information (essentially what you put in your log books)

    The FlightData tables only have Serial Number blocks to link the lines of flight data to the AircraftType table via the AircraftBureauNumber table.

    I've tried using the Query Wizard but can't seem to get the right combination of data included in the table to give me anything close, so I'm essentially starting from scratch on the queries.

    The reports will come later obviously.

    Any advice?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'm not sure I exactly understand but here's where I'd start:

    1. Table structure and relationships. Keep in mind that you can OVER-Normalize a structure which will complicate all your queries. When developing your table structure and relationships, you need to keep in mind your form design, queries and reports. For example, on a customer type database, separating the city, state, and zip into separate tables means complicating all your queries by having to link in 3-4 different tables to return a simple address (which is something often requested from the db).

    2. For your situation, it sounds like you're on the right track but you may need to work a bit more on the table structure and relationships. If done correctly, queries should come fairly easy and should not cause you complexity issues. Try to structure your tables so that you don't have to link in multiple tables for your typical queries.

    3. If you have an 'aircraft' type table, all properties associated with that aircraft (ie. serial number, color, type, etc..) would most likely be in 1 table (1 field could possibly distinguish between military/civilian aircraft). You then might setup a 'log' type table for flights where one of the fields is the 'AircraftID' (or unique Aircraft serial #) and apply other attributes (ie. fields) needed for the log table (ie. flight data). MilitaryFlightData or CivilFlightData could be a simple designator type field in this table distinguishing the type (if the other data in this table is the same.) Remember that you can utilize fields in a table to distinguish the flight type (or other properties) if other fields in the table record the same type of data (ie. you wouldn't want to make a separate table if only 1 or 2 fields are different) Ex: It 'may' not make sense to have a separate MilitaryFlightData AND a CivilFlightData table if all the other information in the tables are essentially the same. It may make more sense to simply have a 'FlightType' field in the table and use criteria in the query to distinguish between the 2. If I recall, when I recorded flight information into log books (both military and civilian), there wasn't a lot of information which distinguished the 2 types of logging (except for the military flights, I had a few extra fields in the log table specific to the military flight.)

    I hope that helps. It's difficult for me to say though without actually seeing how you've got it structured since I'm a little confused with your description.

    Otherwise, you'll need to supply the SQL statement of your query for us to help you with the query. Sometimes it's easier to just use the query designer without the wizard and manually add your tables/joins.
    Last edited by pkstormy; 04-05-10 at 23:57.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Apr 2010
    Location
    New Orleans
    Posts
    4
    Thanks!
    I'll attach the database to this thread so you can poke through it if you want.
    I have actually thought about merging the Military and Civil flight data tables like you suggested, but just haven't yet. You're right, it is essentially the same, but it is categorized a little differently (in fact, I've added a few of the civil fields to my military table).

  4. #4
    Join Date
    Apr 2010
    Location
    New Orleans
    Posts
    4
    Here's the zip file.

    The more I think about it, I will merge the 2 flight data tables..
    Attached Files Attached Files

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Nice job!

    A couple of things to think about in your tables:

    1. For the naming of your field names, please, please avoid using spaces and characters such as / or () in the fieldname itself. In the long run, it will save you a lot of hassles with coding and queries.
    2. Never name a field just: Date. Avoid using any 'reserved' words. Instead use something like: DateEntered or DateOfFlight, etc..
    3. I personally like to have an autonumber field type in all my tables. Only because in older versions of MSAcces, not having an autonumber field in the table could cause problems on the data form. I (personally) like to use autonumber fields in my main data tables and then use that field to join my 'main' table with my relational tables (where the field in the relational table is an integer type field.) It tends to work 'nicer' with your forms/subforms and data entry.
    4. I (again, personally) detest the switchboard. Only because the switchboard is then based upon a table for your main menus. I like to avoid any Main Menu type forms that are bound to a table since this is the form users typically always keep open. When a form is left constantly open (that's bound to a table), with multiple users in the mdb, that can cause problems for other users who then try to open the mdb. Instead, I'll just manually create buttons and code in the OnClick event of the button to do the appropriate thing and again, I leave main menu type forms unbound (ie. no recordsource). The switchboard in MSAccess is not a popular feature among developers.

    Also, your 2 flightdata tables (ie. Civilian/Military) are a lot more different than I originally thought. You may want to keep these as is. The advantages of merging them into 1 table is that it makes your form design easier (ie. you don't need to design 2 separate forms) and you wouldn't need 2 separate queries to return "all" flight data. But with all the different fields between the 2 tables, I might recommend keeping them as is since there wouldn't be a lot of advantages to having 1 form to meet both needs (you'd have a lot of fields on the form which would be ingored when entering Civilian data which could be problematic if certain Military only fields were required.) But you'll need to decide if this is important enough for you to change.

    Again - nice job.
    Last edited by pkstormy; 04-11-10 at 09:07.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Apr 2010
    Location
    New Orleans
    Posts
    4
    pkstormy -

    Thanks again for the help! I've been playing with merging the 2 tables, but work has been crazy, family in town, etc.

    I'll re-post the edited file soon.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •