Assistance needed - modifying an existing report

[optimus_1]

Morning,

I have two seperate parts to this query, Im sure its possible to do both but im not sure as my access skills are limited.

I have attached a file with a report previously written (i have deleted unnecessary reports etc).

PART1
On the excisting report i need to show the breakdown of 'hours/points' by 'tool'.
ie... it currently looks like
(for example)
Monday hours 5 Monday points 50
Tuesday hours 2 Tuesday points 88

And what i need is to show the 'actual tools' that make up these points.
(for example) - (note - some tools may have blank points/not nil)

Strimmer#1 2 Points 20
Strimmer#2 6 Points 30
Monday hours 8 Monday points 50
Chainsaw#1 1 Points 83
Strimmer#2 1 Points 5
Tuesday hours 2 Tuesday points 88
(Or something similar)

PART2
Although i want to see the report as above, It would be of great use if there was some form of 'button' that could be pressed that only showed the people who have exceeded 100 POINTS on any given day (so these people could be located easier than going through the whole report).

I hope that i have explain myself adequately.

Many thanks in advance
Nathan

 

[jzwp22]

Taking a look at your database, in your main table you have the days of the week. These would be considered as repeating groups which suggests that your database is not normalized. In a sense you are using Access as if it were a spreadsheet which it is not.

From what I am able to get out of your DB, it appears that you are trying to capture tool usage by your operators.



In order to come up with the correct structure, could you explain your process in more detail? Here are some questions that I came up with in order to try and hash out the table structure

Can an operator use more than one tool in a time period?
Can multiple operators use the same tool in the same time period?
Can an operator belong to more than one depot?

 

[optimus_1]

Hi

Thanks for taking a look.

The answers to your questions are:
Yes an operator can use more than one tool per time period.
Yes multiple operators can use the same tool over a time period, and is the case quite often.
and
No an operator can only belong to one Depot.

Thanks again

 

[jzwp22]

With the answers to the questions in mind, I would recommend a structure along these lines.

First a table to hold the different Depots so you don't have to type the name over and over for each employee.

tblDepots
-pkDepotID primary key, autonumber
-txtDepotName

Now your employee table
tblPersonalDetails
-pkOperatorID primary key, autonumber
-txtOperatorName (the word "Name" is a reserved word in Access, so it is best to use something else)
-ClockNumber (it is not recommended to have spaces or special characters in table or field names
-fkDepotID foreign key to tblDepots
-Comments
I'm not sure what the recommended usage field is for, but if it is not something for the operator then it should not be in this table


A table to hold the tool information

tblOperatingTool
-pkToolID primary key, autonumber
-Description

The max usage hours per day is a calculated value and should not be stored. I'm not sure what the points p/hour, but it also sounds like a calculated value and thus should not be stored.

Now a table to bring together the operator and the tools they use

tblOperatorTools
-pkOperToolsID primary key, autonumber
-fkOperatorID foreign key to tblPersonalDetails
-fkToolID foreign key to tblOperatingTool
-dteUsage (usage date)
-TimeUsed

 

[optimus_1]

Hi

The 'points per hours' column in the Tools table is not a calculated field (in the table) it is the 'points' per hour that the item of equipment gives out (in terms of vibaration levels-of which no person is allowed to exceed 100 per day-hence the need for the original report stated in the first message).

I am still strugling to create this report from what you have recommended i do.

Sorry for my ignorance but i do not use access that much.

Thanks anyway

 

[jzwp22]

You will need to create some queries to bring the information together for the report. If you have restructured the database as I recommended and have added some data, I would be willing to take a look at it.

 

[optimus_1]

Hi Thanks

I think i have done all the changes although with my very limited knowledge i am unsure if it is correct.

Thanks

 

[jzwp22]

In order to join the foreign key fields to their respective primary key fields, they must be long integer number datatypes since that is what the autonumber (primary key) fields are. Also, it is not a good idea to have spaces and special characters such as *,/() etc. in your table or fieldnames. I also established the relationships between the tables in the relationship window. This will aid in creating forms, queries and reports.

In order to be able to enter data for the tool usage, a subform on your operator form (frmPersonalDetails) will be necessary.

I migrated the data you had in the old main table to the tblOperatorTools table.

Now with respect to the queries, you will need to calculate the total points for each tool used by each operator on each day. (see qryPointsPerDayByToolByOperator)

You can then use that query in order to create a query that does the totalling for you. (see qryTotalPointsByDayForEachOperator)

I have incorporated all of the above changes in the attached DB.

 

[optimus_1]

Thanks for your assistance, i think iv got my head around it!!. Does the way it is now set up mean my 'inputting' form is obselete as i have been inputting to weekend date and then each day?. Will i need to create a new input interface?.

Thanks again

 

[jzwp22]

Yes, the form you had for inputting of data was based on the old table structure, so it will not work with the new table structure. I included a form that you can use for that purpose; feel free to change it to suit your needs. With the new structure, you only need to enter the dates where tools were used; this will prevent having a bunch of records with zeros in them. If you want to group data (using a query) by week you can do that without changing the structure; you will need to look into some of date manipulation functions like weekday(), datepart() etc. to accomplish that.