Help with ms access query

[claufranck]

Here is the query:

PARAMETERS date_start Text ( 255 ), date_end Text ( 255 );
SELECT DISTINCTROW Count(*) AS [SOE Count], SOE.SOE, Proposal_all.Subm_date, CDate([date_start]) AS start_date, CDate([date_end]) AS end_date
FROM SOE INNER JOIN (Proposal_all INNER JOIN prop_soe ON Proposal_all.Catalog_no = prop_soe.Id_prop) ON SOE.Id_soe = prop_soe.Id_soe
WHERE (((Proposal_all.Subm_date) Between CDate([date_start]) And CDate([date_end])))
GROUP BY SOE.SOE, Proposal_all.Subm_date
ORDER BY Count(*) DESC;

I need to add to the select a way to count the total number of SOE's, right now I get the number of soe per propoposal I would like to get to total as well could sum all of the soe's?

Al so I need the total number of proposals or results.

Maybe in the query or using an expression or some vba code, but I would rather use a query or be able to use 2 query's on the same form without using subforms because it is a continous form.

Thank you

 

[jrbarnett]

In the form, add a group on Subm_Date.
In the report footer, add a calculated control with the control source of:

=Count(*)
to give you the total numbe of proposals that meet your criteria.

John

 

[claufranck]

How do I add a group?
How do I create a calculated control source?

Sorry I am more used to DMBS such as Oracle and MYSQL, but the big boss asked for some updates on this so there is no argument

Thanks

 

[jrbarnett]

To add a group: In form design mode, go to the View menu and choose Form Header/Footer.
This will add two new bands at the top/bottom of the form, either side of the detail row.

To add a calculated control, when in the form design mode, click the textbox control in the toolbox (the one that has the ab| icon) and put it in the form footer.
Set the control source property of the control to =Count(*) (you will find this on the Data tab of the control in form design mode).

John