Need help!

[Chris]

I have built a database for a pilot log book. I have the
table and form and the main query that gives me the
totals of each column all set and working great.
But i can figure out how to make a query that will cross
reference entries of two or more columns and give me the
sum of one of the selected column..
Example: I have NIGHT column and i have a DUAL and a
SOLO column, i want a query that will give me the total
DUAL/NIGHT and another one that will give me to total
SOLO/NIGHT... I having trouble building the proper
expressions.... can anyone help? If i can these last
couple queries figured out, mine and a bunch of my pilot
friend's lives will be just a little easier...
Thanx

 

[Steve Schapel]

Chris,

On the basis of what you have told us so far, it sounds like your table
design should be reviewed. It sounds like you have fallen into the ol'
"fields as data" trap! I suspect all this data that you are trying to
get totals of different columns should in fact all be in one field, with
another field to identify the type of entry. If you would like further
help with this aspect, please post back with some further details, maybe
with examples, of the data we are talking about, especially related to
the Night and Dual and Solo data.

 

[Alfred Nash]

Chris if you are using a query it might look like this.

Select [night] + [dual] as [Dual/Night], [night] + [solo] As [Solo/Night]
Form Tablename

 

[Guest]

Thank you for offering help.
As far as I can see i have set up the table and fields
properly, but this is the first time I've attempted
something as complicated with Access.

This is a pilot flight time log book.
Each new entry starts with a Date, then i have a couple
Text fields for specifics about AirCraft and Description
of the Flight and number of landings
The rest of the fields are filled in with number of hours
in decimal form(ex: 1.2 (= 1hr12min))
There are SEL,MEL and SEAPLANE, then NIGHT,IFR,HOOD and
SIM then DUAL,SOLO and X-COUNTRY, and finally TOTAL
DURATION OF FLIGHT.
Unless it is SIM time, SEL or MEL, and DUAL or SOLO, and
TOTAL D.O.F. must be filled in all with the same value.
If it is SIM time, only the SIM column needs to be filled
in.
NIGHT, IFR, HOOD and X-C will only be filled in if any of
those condition existed and only for the amount of time
it existed. (ex: I flew for 2.0hrs TOTAL SOLO SEL X-C,
and 0.6 of it was at night.) [After typing it that way, I
think I see where you are going...]
Regardless, I wouldlike to have a query that will only
show me show me that i have(based on the above example)
0.6 SOLO NIGHT, but i want it to total up all the SOLO
NIGHT from each applicable entry. I would also like one
to show me DUAL NIGHT, and one for NIGHT X-C, and one for
DUAL X-C and one for SOLO X-C.
I hope that isnt too confusing, it sounds clear enough to
me... I appreciate any advice/help you may have.
Thanx

 

[John Vinson]

Thank you for offering help.
As far as I can see i have set up the table and fields
properly, but this is the first time I've attempted
something as complicated with Access.

PMFJI - just tome thoughts here.

This is a pilot flight time log book.
Each new entry starts with a Date, then i have a couple
Text fields for specifics about AirCraft and Description
of the Flight and number of landings

You should - at the VERY least - have a table for Aircraft (with the
aircraft type, description, etc.); this should have *NO* information
about flights. It would be related one-to-many to a table of Flights.
There'd also be a table of Pilots. Things like total durations, number
of flights, etc. should not be stored in ANY table, but should be
calculated on the fly.

The rest of the fields are filled in with number of hours
in decimal form(ex: 1.2 (= 1hr12min))
There are SEL,MEL and SEAPLANE, then NIGHT,IFR,HOOD and
SIM then DUAL,SOLO and X-COUNTRY, and finally TOTAL
DURATION OF FLIGHT.

Rather than separate fields, I'd suggest having a table of FlightLegs.
This table could have a start time and an end time (entered into a
Date/TIme field, preferably using the actual date and time to handle
flights that go past midnight), with a field for the Condition. A
flight might consist of several legs: e.g.

Flight; Solo?; PilotID
218; True; 335 <<< pointer to the name of a pilot

FlightNo; Leg; StartTime; EndTime; Condition
218; 1; 6/6/2004 18:35; 6/6/2004 18:45; VFR
218; 2; 6/6/2004 18:45; 6/6/2004 19:30; IFR
218; 3; 6/6/2004 19:30; 6/6/2004 22:10; NIGHT

A Totals query using the DateDiff() function could then calculate the
time in minutes under each condition (just divide by 60 to get
fractional hours); similar queries could be used across multiple
flights, e.g. all flights for pilot 335 for a given date range.

 

[Steve Schapel]

Chris,

Thanks for the further information, and you confirmed my suspicions
about the incorrect table design. I agree entirely with John's
suggestions (though I consider his choice of terminology in "should be
calculated on the fly" to be unnecessarily cute).

 

[Gary Walter]

Plus..every time he has a few thoughts,
he's thinking another "tome."

 

[John Vinson]

Plus..every time he has a few thoughts,
he's thinking another "tome."

<BLUSH>

Neither was intentional... note the time of the post (I should have
been in bed!)

 

[Guest]

Thank you John.
I appreciate you suggestions and will redesign my tables
and hopefully that will help with the queries, although
this is a personal log and not a flight or company log
and getting the date and time from each flight would
impossible. Also i need the times for each separate
field to show a total time in the fractional or decimal
column when i print out just a report or the entire log
to submit.
I will make some changes and post my results when i can.
Thanks for your help.

 

[John Vinson]

Thank you John.
I appreciate you suggestions and will redesign my tables
and hopefully that will help with the queries, although
this is a personal log and not a flight or company log
and getting the date and time from each flight would
impossible. Also i need the times for each separate
field to show a total time in the fractional or decimal
column when i print out just a report or the entire log
to submit.
I will make some changes and post my results when i can.
Thanks for your help.

Well, if what you're working from already has the durations, you don't
really need the date/time field; just store the durations in a Single
Float number field.

As for the totals - don't confuse data STORAGE with data PRESENTATION.
Your Report should do the totalling, or should be based on a Totals
Query which does so; it is neither necessary nor wise to store the
totals in a Table to do so.

Just to emphasise - it would be better to store each type of time that
you're trying to sum *in a separate record*, NOT in a separate field
in one record. You can actually do it either way, but breaking the
data down into individual records will give you much more flexibility.