Doubts about partial sum

[esse09]

Hello,

i've been reading the forums to find an answer but due to english not being my first language it is taking long so i thought i'd ask in the meantime.

First, i've been using Access for a couple day only and for a single job, i will probably never use it again, so i didnt buy any book and im not expert at all.

I have created a very simple database made of 4 tables:

"Clienti" keeps all data of our clients such as address, name etc.
"Lavori" does the same but for single jobs
"Ore" is the table where every day we are supposed to input how many hours of work and the different cost for each job.
"Operatori" has the list of people working here.

There is a query that basically gathers almost all records, with a Between[Date1] And [Date2] criteria, and a Like[Job_Name] criteria.

There are two thing that i cant do no matter what i try.

First: in my report i need to print all given hours entry from Date x to date y, and i did that using a Between [Date1] And [Date2] in my query criteria.
Now i have all the records from different people and i need to print (in the same report) the partial sum of hours for each worker.
How can i do that?

Second: is there a way to print in my report the [Date1] and [Date2] that the user enters in the Between And criteria?

Thanks a lot,
Andrea.

 

[Galaxiom]

First:
Create a new query based on the Hours and OperatiID fields
Right click on the query design grid and turn on Totals.
Change the Totals cell on the Hours field to Sum and leave OperatiID as Group By.

Second:
In the query for the recordsource of your report, enter somethng like
BETWEEN [Enter Start Date] AND [Enter End Date]
in the criteria box for the Date field.

Access will prompt for these values when the query is run.

 

[esse09]

thanks for your reply.

i will try about solution 1.

about the second, i didnt explain myself well.
i have the user enter two dates and a job of interest through the query prompt but after that i would like to print on the report what he entered. is there a way to achieve that?

EDIT: further searching solved doubt #2.
I tried the first solution but cant get it working...

 

[esse09]

First:
Create a new query based on the Hours and OperatiID fields
Right click on the query design grid and turn on Totals.
Change the Totals cell on the Hours field to Sum and leave OperatiID as Group By

doesnt work.
i need to tell access something like
add [hours] only if [operator_id] linked to that [hours] entry is 1 (and print result), is 2 (and print result), is 3 (and print result)

 

[Brianwarnock]

I read your first post to mean that you want to print all of the data between the dates for a given job, and to sum the hours per operator for that job.

You need to use report grouping

In the design view of the report you can select grouping from an icon on the toolbar, then select the field to group on, and turn the group footer on. in the group footer place a text box and enter = Sum([hours]) or whatever the field is.

Brian

 

[esse09]

You need to use report grouping

In the design view of the report you can select grouping from an icon on the toolbar, then select the field to group on, and turn the group footer on. in the group footer place a text box and enter = Sum([hours]) or whatever the field is.
Brian

If you go to studiobecchio.it/site/misc/ReportLavoro.pdf

Here you can see what the report looks like when printed.
I grouped by day, and then by operator. But the total is always the sum of all hours.
I cant figure it out... how to get 3 rows at the end of the report with partial total
Nome operatore colums is [operator_id]
Ore column is [hours] entry

 

[Brianwarnock]

I misunderstood your requirements.

In the report Footer you need 3 textboxes containg something like

=Sum(IIF([operator_id]="anna",[ore],0))

The problem here is that you need to know your operator_id

If you group by operator_id Then Date, then in the Group Footer for Operator_id you could get the Total for the operator, but the layout would be different.

The other solution would be a sub report in the report footer based on just the Operator_id and hours for the date period.

Brian

 

[esse09]

If you group by operator_id Then Date, then in the Group Footer for Operator_id you could get the Total for the operator, but the layout would be different.

I have already tried this way by making three reports, one for each operator, but my boss requires the entries to be grouped by date first...

The other solution would be a sub report in the report footer based on just the Operator_id and hours for the date period.

As long as i can "hide" this trick in the overall layout it would work perfectly, i will try now.

Thanks a lot.

 

[esse09]

Just one last question.
I created the new report and it works perfectly, but now when i launch it it prompts twice for the Date and Job request (one for the main and one for the sub report i suppose)
Any way to get rid of that?

 

[Brianwarnock]

I never use prompts but always drive my Reports from a form

The form has textboxes or dropdown lists, then as long as the form is open the data is available to all queries and reports.
You access the data
Forms!formname!controlname

this can be in a query or in a Report for say the header.

You could use a dropdown list or combo created from the table so that the user need only select the appropriate project, no typing errors.

If he wants to run multiple reports on the same date range there are less input changes.
The report is run via a command button, the wizard leads you through it.

Hope this helps
Brian

By the way , you seem very competent for a beginner, and your English is excellent.

 

[esse09]

I never use prompts but always drive my Reports from a form
You could use a dropdown list or combo created from the table so that the user need only select the appropriate project, no typing errors.

By the way , you seem very competent for a beginner, and your English is excellent.

Thank you, i learn fast if i can find a good source of training
You are being very helpful, i made great progress.

The idea of a forms is good, i also put a few button to launch my macro to add client, add job to create some kind of a "control panel".

Now i have this problem:
1)Created form with these textboxes [Startdate] [Enddate] [Job]
2) Changed in my query criteria
Like [job_id] to Like [Forms]![GestioneOre]![Job]
Between [StartDate (old one)] And [EndDate (old one)] -> to -> Between [Forms]![GestioneOre]![Startdate] And [Forms]![Gestioneore]![Enddate]
3) Created button to launch report.

Now if i launch the report it comes up blank. If i launch the query also blank.
Can you see if i am doing something wrong?

Thanks again.

Edit: i also read the office online help office.microsoft.com/en-ca/access-help/create-a-form-to-enter-report-criteria-HP005187411.aspx
about this topic. It suggest to create several macro and some code.
In my opinion that is useless, but i can be wrong, still i havent created them.

 

[Brianwarnock]

Sorry for the delay in responding I was out walking all day with other retired folk.

The problem probably is your Like criteria, it is normally of the form

Criteria: Like "*" & [Forms]![FormName]![ControlName] & "*"

This has leading and trailing wild cards but you can omit one

If you have no wild cards you have to have an exact match.

Do you want to do a Like, would the use of a dropdown combobox based on the table to give an exact match be better?


I haven't read it but I would ignore that link.

Brian

 

[Brianwarnock]

I have added a very simple DB showing the use of a combobox
Note that the query on which the Combo is based is best viewed in SQL view.

Note the extra "or" condition in the criteria in the query on which the report is based , this allows selection of "all" if the combo is left empty.

Hope this is of help

Brian

 

[esse09]

Thank you the database helped me figured out some thing i didnt understand.

Can i call the dropbox directly from the table and not from a query like you did? Is that a problem?


I think everything is setup right, but it doesnt work.
Now it says "Cant find the Form "Gestione" specified in a macro or in VB" (sorry this isnt the right message just a translation)

My query "QueryLavoro" says (code from wizard)

SELECT Clienti.id_cliente, Clienti.cognome_cliente, Lavori.id_lavori, Operatori.nome_operatore, Ore.id_operatore, Ore.data_ore, Ore.ore_ore, Ore.copie_ore, Ore.spese_ore, Ore.note_ore, Ore.note_spese, Ore.km_ore, Ore.note_km
FROM Operatori INNER JOIN ((Clienti INNER JOIN Lavori ON Clienti.id_cliente_auto = Lavori.id_cliente_auto) INNER JOIN Ore ON Lavori.id_lavori_auto = Ore.id_lavori_auto) ON Operatori.id_operatore = Ore.id_operatore
WHERE (((Lavori.id_lavori)=[Maschere]![Gestione]![CriterioLavoro]) AND ((Ore.data_ore) Between [Maschere]![Gestione]![CriterioInizio] And [Maschere]![Gestione]![CriterioFine]));

But the WHERE is exactly the same as the example you posted, just with different names (Maschere is the italian for Forms - access changes it automatically)
I double checked all names, will check again.
I dont want if possible to put "Is Null" parameters because all the data must be entered for correct print.

When i push the button it still asks for [Maschere]![Gestione]![CriterioLavoro] (job dropdown), [CriterioInizio] (startdate) and [CriterioFine] (end date) twice...
I'm sure im making the most basic mistake but i cant figure it out!

 

[Brianwarnock]

If you base the combo on the table all records will be represented in the order that they are in the table, in other words you will get an unsorted list of jobs probably with duplicates. The Distinct keyword in the query avoids this.

Can you attach your DB,or a cutdown version of it, and I will take a look, I can only ask for a coffee or beer in Italian but can try to understand the coding.

The error does suggest something basic such as spelling, or even closing the Form I've seen that done, it must be open.

Obviously you can ignore the Is Null bit.

Brian

 

[esse09]

[...]

studiobecchio.it/site/misc/gestione_ore_en.mdb

Thanks for you time, i translated something to english in the little time i had so you could understand something.

If we can get it working i will change the report layout a little bit and i could then share it here, i know it's a very simple database but maybe someone could use something like this. I wouldnt know what else to do to thank.

 

[Brianwarnock]

The first thing I spotted was that the Form name is GestioneOre not Gestione,

but I intend to change the Print to Printpreview as it is simpler for testing, will then comeback later

Brian

 

[esse09]

The first thing I spotted was that the Form name is GestioneOre not Gestione,

but I intend to change the Print to Printpreview as it is simpler for testing, will then comeback later

Brian

Feel free. I will email the DB to myself so that i can work on it from home later since it's friday.
Thanks again!

 

[Brianwarnock]

Ok I also changed the record source of the form to Lavori, from the Select etc, and once I realised that I had to change maschere to Forms it ran without the messages you were getting but asked for cognome_cliente I think it was.

Anyway if you correct the form name and record source you will make progress.

I will continue to examine it but I suspect that I will struggle.

Brian

 

[esse09]

I will change those and get back to you