Queries Reports and RecordCounts

[dcjones]

QueriesReports and record counts

Hi All,

I am currently trying to produce a database for a Drugs and Alcohol
Rehab centre I am working for.

I have tables that hold data as follows:

Table "Referrals" holds personal data, Name, Age, DOB, Gender and so on.
Table "Stage" holds data which shows what stage in the programme the client on on.
Example: Stage 1,2,3,4,5 or 6.

I have queries that produce the required results, example:

Query 1 "currentFemaleClients" Field "Gender" Criteria "Female"
This produces all the current clients that have a gender of "Female".

I have a Form "AllCurrentFemaleClients" based on the Query "currentFemaleClients". On the
form I have a unbound textbox which has the Controlsource set to "=Count(*). This shows
the number of client with a gender of "Female".

Query 2 "stageone" Field "stage" Criteria "1"
This produces all the client who are on stage "1" of the programme.

I have a form "stageoneclients" which has a unbound textbox with the Controlsource
set to "=count(*). This show the number of client who are on stage "1".

There a number of simular queries and forms all based on the same layout with
different criteria.

I have produced a form containing all the sub forms to show the overall current
position of the Rehab programme.

My question is, how do I produce the same data in a report, using the queries
as outlined above.



If anyone can advise please.


Kind regards to you all.
Keep safe and well.


Dereck

 

[Ally]

Reports are very similar to forms in many ways. You can create a report via the wizard, using the same query as that which feeds your form. If you need what would be a subform in a form, you can create a subreport too. Again, you can have unbound textboxes the same as forms, with your =count* and other calculations. It's just learning exactly where to put them.

In your case, in a report you wouldn't necessarily need to display fields like gender for your female only report. So long as your query fed it as female. Alternatively, you could have a joint report for male and female and "group" it so that it displayed the males separately, then the females.

You could try using the wizard and then post back with any other questions.

HTH

 

[dcjones]

Reports are very similar to forms in many ways. You can create a report via the wizard, using the same query as that which feeds your form. If you need what would be a subform in a form, you can create a subreport too. Again, you can have unbound textboxes the same as forms, with your =count* and other calculations. It's just learning exactly where to put them.

In your case, in a report you wouldn't necessarily need to display fields like gender for your female only report. So long as your query fed it as female. Alternatively, you could have a joint report for male and female and "group" it so that it displayed the males separately, then the females.

You could try using the wizard and then post back with any other questions.

HTH

Hi and thanks for such a prompt reply.

I will have a play and let you know.

Many thanks

Dereck

 

[dcjones]

Hi Ally,

If I create a report with an unbound textbox and set the contrilsource to "=Count(*) based on my "CurrentFemaleClients" query it displays a line in the report for all the current female clients.


If my query show 8 female clients the report show 8 lines of records.

How do I restrict the results just to show the record count.


Can you advise.

Kind regards


Dereck

 

[Ally]

If I create a report with an unbound textbox and set the contrilsource to "=Count(*) based on my "CurrentFemaleClients" query it displays a line in the report for all the current female clients.

How do I restrict the results just to show the record count.

If you put it in the "Detail" section, it will do a count for each record. If you move it to the group footer (or if you've not got one of those, if you're only grouping it on females, in the report footer), that will give you the total count for the group / report.

 

[dcjones]

Hi Ally.

With your help and input it has put my brain into gear. I can now see where you were pointing me and I have sorted my problem.


Thanks again, and kind regards.


Dereck

 

[Ally]

Glad you got it sorted. Any more queries, just post back!

 

[dcjones]

Hi Ally,

Sorry to be a pain.

If I run a query on a table and the results of a =Count(*) is 0 (zero)
how can I display a 0 on my report.

At the moment if the result is 0, the report does not displey the result.


Any ideas.


Kind regards



Dereck

 

[Ally]

Not sure off-hand and have tried re-creating it to try it but can't. You probably need the Nz function, and I'm sure someone else can correct / confirm this and help.

In the meantime, can you post a sample of the db so I can have a go?

Am going shortly, so won't be able to look again till tomorrow.

Ally