how many queries do I need

[chriswrcg]

Here is one of the eventual reports I will be creating based on the data that I am entering. This report is essentially the backbone of all the other reports that I will need to generate, as it includes all the queries I will need to create the reports.

You can break the queries down into two categories. 1. individual tasks and 2. overall daily. In total I will need at least 20 different calculations or queries. Some of these are rather straight forward as 1+1=2 and some are going to be more complex and requiring the results of other queries.

My question is, do I create a query for each calculation that I need or do I combine some to save space since most are going to be coming off the same tables?

For time I will not list all the queries I will need but some of the simple ones will be.

1. Clocked Time = ([clockouttime]-[clockintime])*1440 This is the time a person works in the day in total minutes.

2. Paid time = Clocked time -lunch

3. Earned time = number of trips times the task standard. The standard is how long it takes to make one trip (dock-bin-dock). So if you take 10 trips and the standard is 6 (10*6=60) the job should take an hour. I will need a query for each task and then one to add all the task earned time together for the daily whole.



and so on. I could combine 1 and 2 into the same query string or create two separate ones. what should I do?

[orange]

In my view queries are a long way down the road. First is to describe the requirement in simple terms, then identify the subject matter and how those subjects are related to one another and build a draft model(pencil and paper is fine), then using your sample reports (required output) create some test data and vet your model adjusting as necessary. When you are confident you can access the necessary data, set up a prototype based on your model. Develop queries to select the needed info.

Good luck with your project.