Need Help adding value to Query output

[RickK]

I have a field in a query that shows amount of hours a site is covered. This field name is Coverage hours and it changes depending on the number of work days in a month. This part is working correctly but some times addtional hours may be requested by the customer. I want to add a another field to this query that will prompt me if their were any addtional covered hours. Then I would have the ability to enter a value and the shown output for this field would be the vaule entered plus the standard coverage hours in the other field.

I'm not sure how to set up the new field.


Thanks
Rick

 

[JHB]

How does the query determine that there are extra hours?
You can only put in extra hours one time per query run.

 

[RickK]

I have a table that has the amount of hours covered Sunday thru Saturday. The table has 16 hours per weekday for each day and then 8 hours per day each for Saturday and Sunday. Then I have code in a module that determines how many of each of these days were in a particular month since each month will have a different amount of days.

My query has a field that prompts for the month to be reported. My query uses the table and the code to supply me with a total amount of hours covered by that month. Lets say the output for the month of Novemeber was 485 hours covered, and we covered an additional 5 hours on a Saturday, I want a field that will prompt if there were any additional hours and the amount entered would be totaled with the amount in the first field (485 hours) and the new field would show the result as 490 hours.

Thanks
Rick

 

[RickK]

So either , no one understands my question or they dont know if it can be done?

 

[jdraw]

It can be done. What isn't clear on first reading is how you get the extra coverage hours.
Who enters/approves the extra hours? Do you need to report (at some time) What extra hours were requested? By whom? and on what dates were these extra hours requested?

Seems to me if you are invoicing someone for hours of coverage, you should have some detail on who requested what on what date. But it's your business process so you'll have to supply some details if we are going to get more specific. Some accounting for the hours invoiced is needed in my view.

Good luck with your project.

 

[RickK]

Jdraw,

My query already shows the amount of contracted hours covered per month, which changes from month to month depending on how many weekdays and weekend days there are for that month. This part of the query works perfect. The same query looks through my workorders table for the same time period and shows all downtime in hours. In another field in the same query I take these two values above and show the availabilty of the equipment. This represents uptime in a percentage . All of this works as expected. Each month the customer will request extra coverage depending on their workload.

When I run my query at the end of the month I will know how much extra coverage there was and I would like my query to prompt if there was any additional hours to be added. At the prompt I could select "enter" for zero or enter the value in hours. Then have this added to the already known value of contracted hours.

The field in my query that shows the contracted hours is named "ContractedHours"
The field in my query that shows the downtime hours is namd "DownTime"
The field in my query that shows the percentage of availabilty is named "SystemAvailibility"

I was thinking that I could add another field that would that the value in "ContractedHours" and the value that I enter when prompted and add those together and show it as a value. Then I could redirect my"SystemAvailibility" to this new field instead of the contracted hours field.

I'm the person running the query, and Yes, I have a report that is built from this query. The extra hours have already been accounted and approved by the customer. My report will show both, the contracted hours and the extra hours.

I just need help with a field that would prompt for input and add add to the field Contracted hours.

If it cant be done , then I might have to make a new table that allows me to enter in the extra hours and name the table "Extra" . The have the query include this value when its ran.

Thanks
Rick

 

[JHB]

On way can do it is by using an UDF, but you need to put the result from the query into a temp table, because the query will rerun each time you more around with and in it.
I've made an example for you so you can get some ideas.
Run "Query1" and then open report "ExtraHours".

 

[RickK]

JHB,

I tried the example that you sent and the concept is there and it does give me something I can work with. My goal now , is to edit it to work with my query names and field names. Also as you mentioned , it writes the output to a table, I need it to be in the query output, but I think I can work with a macro to make it do what I need it to do.

Thanks for the input
Rick

 

[RickK]

On way can do it is by using an UDF, but you need to put the result from the query into a temp table, because the query will rerun each time you more around with and in it.
I've made an example for you so you can get some ideas.
Run "Query1" and then open report "ExtraHours".

JHB,

I did get it working in my DB. But I have a question. When you run your example , you will notice that it prompts you for each date line, so the second and third date line prompts for an entry. In my db , I use machine serial number instead of a date line. So it prompts me 23 times before it updates the table because I have 23 serial numbers. The available time and the extra time will be the same for each machine. Example :This past month we had 524 available hours plus an extra 20 hours for a total of 544 hours.

Do you know of a way to enter the answer once and have it duplicate for the rest of the lines in the query?

Thanks
Rick

 

[JHB]

The easy way is shown below:
If you in the query replace
"TotalHours: AskExtraHours([HoursSpend];Month([TheDate]) & "-" & Year([TheDate]))"
with
"TotalHours: [How many extra hours]+[HoursSpend]"

 

[RickK]

Bingo, we have a winner! This was exactly what I was trying to do. It works perfect.

Thanks
Rick