RC FlightLog Database Setup

[ZJGMoparman]

I am working on converting my RC Flight log from Excel to Access. Excel does ok, but it is not the most user friendly for finding certain information. I have been playing around with Access for a while now and trying to learn as much as possible. Last week someone posted a very helpful article on Table design and setup. After reading this article over and over, I have attempted to setup my tables for my flight log. Would any of you experienced Access users mind taking a look at my at my relationships page I am posting below. This page shows all of my tables and all of the relationships I have assigned so far.



I will try to explain everything I am trying to accomplish with this database.
1. I want to keep track of how many flights I have on each airplane and battery.
2. I want to keep track of how much capacity I am using for each flight from the battery.
3. I want to keep a detailed log of weather for each flight session.
4. I want to know how many flights I am getting during each flight session and how many times I am flying at each field.

There is much more I would like to keep track of, but hopefully these 4 points will help anyone looking at the database get an idea of what I am trying to do.

[June7]

I think you have made an excellent beginning. This structure should allow you to output the data as you describe.

Only thing I will throw at you is that lookups can be an aggravation. Instead of using autonumber ID as PK for Class, Material, Power, Cell, Connector consider using the descriptive field, especially if those descriptors are short.

[ZJGMoparman]

Thank you June7 for replying. I am now starting to work on my querys and forms. Currently, the flighttime field is setup as a text field and the input format is 00:00 for minutes and second. I have a query that then splits this up into a minutes and seconds field. I am hoping that I will then be able to sum my flight time for each aircraft from the query.

I posted this question in another topic a few days ago and no one responded. Is there a way that I can have a combobox or list box in my flightentry form that will only show the batteries that are charged (it would either be batteries where the most recent charge date is more recent than the most recent flight date or maybe some other method). Also, I would like to do the same thing for my charge entry form. I only want to show the batteries that have not been charged. I have little experience with queries and form design, so any help would really be appreciated.

[June7]

I am sure that combobox setup could be done. Your idea for comparing the charge date with flight date sounds reasonable but also complicated. One possible approach is like:

Query1:
SELECT Battery, SessionDate FROM tblFlight INNER JOIN tblSession ON tblFlight.Session=tblSession.SessionID;

FlightEntry combobox RowSource query:
SELECT BatteryID FROM tblBattery WHERE Nz(DMax("ChargeDate","tblCharge","Battery=" & [BatteryID]),Date()) >= Nz(DMax("SessionDate","Query1","Battery=" & [BatteryID]),Date());

ChargeEntry combobox RowSource query:
SELECT BatteryID FROM tblBattery WHERE Nz(DMax("ChargeDate","tblCharge","Battery=" & [BatteryID]),Date()) < Nz(DMax("SessionDate","Query1","Battery=" & [BatteryID]),Date());

[ZJGMoparman]

Flight Log.zip

I have attached my database with some dummy data. I also went and fixed the autonumber primary keys for the tables as you recommended.

[June7]

I edited my previous post, apparently after you initially read it. I provided a possible solution I am now testing with the db. I see complication if flight and charge take place on same date. Should it be assumed that the charge happens after the flight? Will new batteries have a charge record before first use or assume if there is no charge record new batteries are fully charged?

[ZJGMoparman]

Well right now all the batteries are in storage mode, so they will need to be charged before first use. I am fine if that charge is not in the database though. If it is easier to assume all new batteries are fully charged, that is perfectly fine with me. Actually I would probably rather have it that way.

And yes, the charge will always happen after the flight, but it 75% of the same they will both occur on the same day. If i added a field in the charge for time of day, would that make it easier? The flight time of day should be linked to the session time of day, correct?

[June7]

Try the queries in the earlier post. Time of charge not needed.

[ZJGMoparman]

Ok, so far I am able to setup the first query. I am still new to queries and forms, so how do I go about creating the second and third queries. And how exactly is a row source query different than a select query?

[ZJGMoparman]

Nevermind, I was overlooking something obvious. I now have all three queries setup, but the charge entry query is not displaying batteries that have been used.

[June7]

RowSource is combobox/listbox property. It can reference table or query object or it can be an SQL statement. Same applies to form/report RecordSource property.

No batteries show because with the data you provided, no batteries need charging. The only batteries used were already charged on the same day as flight. All other batteries are 'new' and considered fully charged.

[ZJGMoparman]

I have now create a form to enter a flight session. I would like to have a button in this form that when clicked will open up a form to enter a flight for that session. When it opens up the flight form, it would need to automatically reference the sessionID of the session that is currently open in that form. Is this possible? Once I have entered the flight(s) I would like to have a subform( I am guessing this is the best way to have what i am looking for but not sure) that would list all of the flights, battery used, flight time, and sum the total time of the session. Would this be a form in datasheet view? or how would I go about this? I feel like I finally understand tables , but I just dont know all the ins and outs of forms and queries to get what I want.

Thanks for your help/

[June7]

Make the flight form a subform on the session form. Review http://office.microsoft.com/en-us/ac...010098674.aspx

[ZJGMoparman]

Ok, I got the subform working like I wanted. I am still having issues with the combobox source query. I have added a field in the flight table called DateAdded and a field in the charge table called DateModified. Both of these fields are set to default to Now() as the default value. This should give me the date and time that this field was updated. I have then created a query that shows the max value for [DateAdded] and [DateModified] for each battery. Now I am trying to add a criteria that will only show the records where [DateAdded]>[DateModified] but it keeps asking me for parameters. Here is my SQL code without any criteria:

SELECT tblFlight.Battery, Max(tblFlight.DateAdded) AS FltDate, Max(tblCharge.DateModified) AS ChrgDate
FROM tblFlight, tblCharge
GROUP BY tblFlight.Battery;


I want a criteria that will only show where FltDate>ChrgDate

[June7]

See post #4.