monthly totals

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

benny - 03 Nov 2007 08:23 GMT

I want to be able to type in the month and get total hours for that month and
also get (as a seperate number) the total hours for the year including that
month (i.e if i type in june, i want to get a display that gives the hrs for
june, but also the hrs for jan,feb,mar,apr,may,jun as a total.)

any help much appreciated, thanks heaps

Reply to this Message

Marshall Barton - 03 Nov 2007 16:11 GMT

>I want to be able to type in the month and get total hours for that month and
>also get (as a seperate number) the total hours for the year including that
>month (i.e if i type in june, i want to get a display that gives the hrs for
>june, but also the hrs for jan,feb,mar,apr,may,jun as a total.)

I think you are only giving us a small part of the objective
here. If you only want a report to show two numbers, why
not just calculate the values on a form using DSum?

If you relly need a report, why not go ahead and calculate
the value for all the months. It would be easier to use a
Totals type query with a criteria for the year and show up
to 12 values instead of just one value.

Signature

Marsh
MVP [MS Access]

Reply to this Message

benny - 03 Nov 2007 21:13 GMT

Ok sorry I'll try give you a bit more information. It's for a logbook, and we
have to give monthly summaries of hrs done, but also the total of hrs for
that year. so for example at the end of august we have to give the total hrs
for august, and also the total hours for 2007. im fairly new to access, so
any ideas how this can be done would be appreciated.

thanks heaps
benny

> >I want to be able to type in the month and get total hours for that month and
> >also get (as a seperate number) the total hours for the year including that
[quoted text clipped - 9 lines]
> Totals type query with a criteria for the year and show up
> to 12 values instead of just one value.

Reply to this Message

Marshall Barton - 04 Nov 2007 18:00 GMT

Sorry, but that didn't help me to understand what you are
trying to accomplish. Logbook with monthly amount and
running total for the year is too vague for me to help you
design a report.

Do you need the calculated values for the entire
organization, for a singly individual/project or for each
person/project in the organization?

Does the report have to be confined to a single month or can
it be for the entire year?

Do you need to have the values in the report grouped in some
way? If so, how do the calculations fit in with the
grouping?

What fields fo you have in the report's record source
table/query?

How are you specifying the month? Why do you think that you
do not need to specify the year? If the report can be for
the entire year, seems like you only need to specify the
year and let the report output the values for all 12 months
or just up to the current month.

OTOH, maybe you don't want help designing the report and are
only looking for a way to calculate a value. In this case,
I still need to know the relevant fields in the record
source and an explanation of the context of the calculations
in the report.

Signature

Marsh
MVP [MS Access]

>Ok sorry I'll try give you a bit more information. It's for a logbook, and we
>have to give monthly summaries of hrs done, but also the total of hrs for
[quoted text clipped - 15 lines]
>> Totals type query with a criteria for the year and show up
>> to 12 values instead of just one value.

Reply to this Message

benny - 05 Nov 2007 03:22 GMT

sorry bout that, im still new to access and still learning a lot, i'll try
give you all the info this time...

we're making a log book for pilots to log their hours in. the data is stored
in a table with information on each flight (fields like DTG (date), what,
why, who, what a/c, and how long). at this stage theres only one table in the
file, so selecting a particular pilot is another problem for another day. but
we want to be able to run a query that first of all asks us for a month and
year, then displays data like the following:
captain (hrs) co-pilot (hrs)
total (hrs)
month 45.2 13.2
58.4

year 258.6 87.9
346.5

so the month row shows data for the month that the query asked us for, and
the year row shows data for all the months in the year so far incl the one
typed in (so if halfway through september you typed in aug, it would NOT
include the hours that you did after august in the yearly total...it would
just be the total of jan,feb,mar....aug).
another example: if today you typed in mar, the month row would show that
data for march, and the year row would show the data for jan, feb, mar added
together.

The date data is stored in a field called DTG in the format yymmdd, the
problem i'm having is making the query to get only this data, and also then
in transferring it to a report....the problem is the syntax in the query
design.

hope this is more helpful, thanks for the time so far

benny

> Sorry, but that didn't help me to understand what you are
> trying to accomplish. Logbook with monthly amount and
[quoted text clipped - 45 lines]
> >> Totals type query with a criteria for the year and show up
> >> to 12 values instead of just one value.

Reply to this Message

Marshall Barton - 05 Nov 2007 18:18 GMT

>sorry bout that, im still new to access and still learning a lot, i'll try
>give you all the info this time...
[quoted text clipped - 26 lines]
>in transferring it to a report....the problem is the syntax in the query
>design.

Oh boy! Sorry to break the news, but your problem sounds
like it is a lot bigger than query syntax. First, you
really need to break that spreadsheet like table into
several relational normalized tables where each table
contains only data for a specific entity (pilots, planes,
flights, etc). Try to make a list of the entities you need
to deal with and the data about that entity without regard
to other entities. Then I suggest that you post your
tentative table designs to the tabledesign newsgroup for
review.

You also need to think long and hard about what the query is
supposed to do in **all** possible scenarios (e.g. a person
is a pilot on one flight and a copilot on another flight, a
pilot has different copilots on different flights in the
same month, etc).

From what you've explained I don't see any way to produce a
useful query/report, because all you are doing is totaling
the total flight hours without regard for who the pilot and
copilot were or which plane was used.

If all you want is the total flight time for each captain
(without regard for who the copilot was), then you could use
something like:

SELECT captain,
    Sum(IIf(flightdate>=[Enter momth/year] And
        flightdate<DateAdd("m",1,[Enter momth/year]),hours,0))
    Sum(IIf(Year(flightdate=Year[Enter momth/year],hours,0))
FROM yourtable
GROUP BY captain

Signature

Marsh
MVP [MS Access]

Reply to this Message

benny - 05 Nov 2007 03:23 GMT

Reply to this Message