I Have a problem anyone help?

[AWilderbeast]

Hi Right i have Consultancy Database, and im trying to get a report query together that will give me what my client wants:

I gather the following information from my tables

Business:
cu_id
cu_name

Consultancy:
cu_id
co_id
co_subject
co_description
co_hrs
co_date

These tables are linked via relationship and what i need to display through my Query report are:

ammount of hrs(co_hrs) total for each business
after each company has totals for the ammount of consultancy hrs i need the user to be able to type a figure in co_hrs textbox and then be able to view all total ammount of hrs above that figure e.g type in 25 in the box and it will give all companies with more than 25 hrs total consultancy

sounds longwinded ive explained best i can, anyone help?

 

[seth_belgium]

Don't quite get it, you want to total the hours per business and then let the user overwrite that value? Please give us more ;-)

Seth

 

[AWilderbeast]

Sort of

Yeah sort of Total the values for all the businesses then give the user the option to only print businesses off with a total say over 25hrs or 15hrs what ever they type in

does that help?

 

[Murli]

Hi,

You can create a query to get the total hours group by company and with a criteria >[forms]![formname]![textboxname] and call this query using a command button on your form after entering the data in the text box.

The query will display the records based on the number entered in the text box.

Hope this helps.

 

[AWilderbeast]

Wierd Error

Thanks for your help Murli i have done what you said but when i open the form in form view the screen goes blank below is what i have in my query

[Forms]![DateSelector]![hrslimit]

 

[Murli]

Attached is the sample database for your reference.

You need to open the form and enter the condition (just the number without any operator) and click the command button.

Let me know if that helps.

Murli

 

[AWilderbeast]

can you have a look at this

Hi Murli Thanks that works great, i was wondering if you could have a look at the sample attached and tell me why it isnt grouping on one i tried to recreate.

Also you can see i have added a date function to it but i was wondering if i choose between 2 dates then a consultancy hours sum it will only group the figures between those dates wont it?

Another thing could you make the results of the query go to the test report on click of the button

Thanks for all your help so far

 

[Murli]

I had a look at it and below are my findings:

1. You were trying to group by too many fields which is unnecessary - I have created another query(Myquery) for your reference.
2. Make sure that when you enter data into hrslimit, you do a tab so that the focus is not on the textbox. If the cursor is still in the textbox the query wouldnt work
3. Giving start date and end date would give the results between the two days.
4. I have given a command button on the form (with caption as Preview Report) - This opens up the report (MyReport).

Attached is the DB.

Does that help?

Murli

 

[AWilderbeast]

almost perfect

Exactly what i wanted apart from the sum function doesnt work unless the dates are the same, is there away of making the hrs sum without the dates having to be the same?

Thanks for all your help

 

[Murli]

Can you please elaborate and explain? Its not clear to me.

 

[AWilderbeast]

When running the query we have the hrs grouped by sum so it adds them up but the records dont add up if the dates are not the same.

e.g a company has two records for consultancy with 12hrs in each record to they should add up to 24 but they dont add up unless the date entered is the same

e.g

Bobs Builders | 12hrs | 01/05/2005
Bobs Builders | 10hrs | 01/07/2005

Those records above should be grouped to say 22hrs but they wont unless the dates are the same

 

[AWilderbeast]

Not Possible is it!

Hi Since looking at what i just asked,trying to get the results from a standard query and thinking about it what im asking is not actually possible it would be contracdicting the date in the field.

I have attached a image of what i am looking for.
The Query that we used for finding hrs will need to search for the totals but I dont know how to get the total can you help?

 

[Murli]

Very much possible

Hi,

For your given output you need to group by the Business name, consultancy type and sum the hours spent with a condition on the dates.

Sum for each business name may be generated directly at the report level using a sum operation at the businessname footer level. (see report: MyReport2 in the attached DB)

Waiting for the results:
Best Regards,
Murli

 

[AWilderbeast]

Thanks

Cheers Murli you an absolute genius exactly what i wanted

Really appreciate you time

Thanks