Frustrated said:
Ok - The table "Yearly Grains Bookings" has multiple fields but the ones in
question are:
Field - Property
Week - number - long int
Week starting - medium date
Day - text
Time - time
Cont ID - number
Seq - Number
Day Lookup Table:
Day - Text
DayID - Autonumber
The database is set up with a week number and the week starting is a
Thursday. The data is placed in the table for the week. At a later stage,
someone will retrive the records for the week and then allocate that contract
to a day and a time for that week. The contract is then loaded etc.
The report is set up with a query and a parameter for the user to enter the
week number :
SELECT [Weekly Productivity Report].DayID, [Weekly Productivity
Report].Week, [Weekly Productivity Report].[Cont Id], [Weekly Productivity
Report].[Booking ID], [Weekly Productivity Report].[Week Starting], [Weekly
Productivity Report].Day, [Weekly Productivity Report].[St 1 Cancelled],
[Weekly Productivity Report].[Cancelled Other], [Weekly Productivity
Report].[No of Containers], [Weekly Productivity Report].[Completed
Containers], [Weekly Productivity Report].Tonnes, [no of
containers]-[completed containers] AS Variance, [Weekly Productivity
Report].[CBH Lost Time], Sum([T_Crew Downtime].Minutes) AS SumOfMinutes,
Sum([T_Crew Downtime].Numbers) AS SumOfNumbers, Sum([minutes]*[numbers]) AS
[Total Time], Sum([minutes]*[numbers]*0.65) AS Cost
FROM [T_Crew Downtime] RIGHT JOIN [Weekly Productivity Report] ON [T_Crew
Downtime].[Booking ID] = [Weekly Productivity Report].[Booking ID]
GROUP BY [Weekly Productivity Report].DayID, [Weekly Productivity
Report].Week, [Weekly Productivity Report].[Cont Id], [Weekly Productivity
Report].[Booking ID], [Weekly Productivity Report].[Week Starting], [Weekly
Productivity Report].Day, [Weekly Productivity Report].[St 1 Cancelled],
[Weekly Productivity Report].[Cancelled Other], [Weekly Productivity
Report].[No of Containers], [Weekly Productivity Report].[Completed
Containers], [Weekly Productivity Report].Tonnes, [no of
containers]-[completed containers], [Weekly Productivity Report].[CBH Lost
Time];
I have the grouping and sorting as -
St 1 Cancelled first with Group Properties - Yes to Group header and footer,
Each Value, 1 and whole group
DAYID - with the same group properties as above.
The DAYID is from a lookup table that has an autonumber as the primary key
and the days of the week (with the first day being Thurs which is the start
of the working week). I sort on the DayID so that the records are listed for
each day starting Thursday.
The DayID Header has the "Day" field
The DayID Footer has the following fields: "Day" is the control source.
The field next is the "Week Starting" field + "DAYID"-1. This gives me a
date for each day in the report based on the Week Starting listed in the
Report Header.
The DayID footer has all the containers etc as sum values.
I Have tried the "Day" field in the DAYID footer with the count equation. I
have tried having "Day" header and footer and "Week Starting" header and
footer but they always show 31 which are the number of contracts that are
processed in that week.
I have also tried using the "DayID" field to count but in every case it
also comes back to the 31.
My problem is that most weeks the crews will only be working 5 days but in
some instances they can be working 6 or 7. The report shows the correct days
worked but I need to be able to use the number of days worked in the week
against the calculations. I have even tried putting in an unbound control
and putting this in the footer to count - same conclusion.
I'm sorry but it's not possible to copy the report into this area.
Thanks for the information. I won't be able to look at it until Monday.
In the meantime anyone who would like to step in and tackle this one
is welcome. Note that Week, Day and Time aren't very good field names
to use because they are potentially confusing to Access. Also, even
showing a single record from "Yearly Grains Bookings" and "Day Lookup"
would help quite a bit so that no one wastes time guessing at what you
have. A sample record from the query could also help you receive an
answer more quickly. I often create a similar table and create a
similar query so that I can experiment and see what is going on. The
sample record/row from the query helps me make sure that we are looking
at the same problem. You're very familiar with your data. We in this
NG are not. An example of what you want to get helps us verify that our
solution fits your problem. The field [Week] can be derived from [Week
starting] so it might be possible to delete one or more fields. Also,
by seeing an example it may be possible to reorganize your table
structure in a way that makes getting the information you want into a
query more easily. It's likely that the way you have chosen to manage
your data's lifetime is not the best. Is the data in the table deleted
after it is retrieved so that it can be used the following year? In
fact, much of your data's organization seems nonstandard and looks like
it needs some TLC in order to do what you want efficiently. I would
guess that you don't need the "Day Lookup" table at all. It's likely
that an entry date (including the year) in the "Yearly Grains Bookings"
table is all you need to do everything you want to do in your report.
James A. Fortune
(e-mail address removed)
