Prev: I'm Also having trouble with the 2499 message
Next: I have two Yes/No fields in a table (ContractorInstall, andInHouseInstall)
From: justme43 on 27 Jan 2010 00:44
Please help
Please note I am not use to using Access
I would like to be able to count items in fields. I have a table which I
produced by doing a query of 2 tables and making it a query table.
The fields are Client no, Date, nationality, gender, postcode, marital status,
and a few others.
I can do a cross tab query and count the items in each in field.
However what I am having trouble doing is bring this together in the one
report or query.
I am also open to suggestion as to better ways of obtaining my data.

Thanking you for any help that you are willing to supply.
please remember I am not an access user so simple is my best option

From: John W. Vinson on 27 Jan 2010 01:10
On Wed, 27 Jan 2010 05:44:22 GMT, "justme43" <u57805(a)uwe> wrote:

>Please help
>Please note I am not use to using Access
>I would like to be able to count items in fields. I have a table which I
>produced by doing a query of 2 tables and making it a query table.
>The fields are Client no, Date, nationality, gender, postcode, marital status,
>and a few others.
>I can do a cross tab query and count the items in each in field.
>However what I am having trouble doing is bring this together in the one
>report or query.
>I am also open to suggestion as to better ways of obtaining my data.
>
>Thanking you for any help that you are willing to supply.
>please remember I am not an access user so simple is my best option

To get a simple answer you'll need to ask a simpler question...

What is it that you're counting?

What do you want as the final result?

It will probably require several queries if you want to count the number of
records for each nationality, for each gender, etc. etc.; the Count operator
in Access counts *records* in the table, and you want each record to be
counted several times by several different criteria.
--

John W. Vinson [MVP]
From: justme43 on 27 Jan 2010 02:12
I want to have a report that will give me

[Client no] [agegroup], [gender], [nationality], [postcode],[employment],
[benifits], [volunteer] by service utilised and date of service.

I realise that Postcode and Nationality will probably need to be done
seperately.

At the moment I do this in excel and then convert it to a pivot table which
works fine but I was hoping that I could do it in access where the client
data is held.

I can get the counts by doing a crosstab query but I dont know how to get one
report from these. (they are done by each field at the moment)

I would also like to be able to select dates for my report eg from 1st jan
2010 to 7th jan 2010

I do so hope this makes sense.

John W. Vinson wrote:
>>Please help
>>Please note I am not use to using Access
>[quoted text clipped - 9 lines]
>>Thanking you for any help that you are willing to supply.
>>please remember I am not an access user so simple is my best option
>
>To get a simple answer you'll need to ask a simpler question...
>
>What is it that you're counting?
>
>What do you want as the final result?
>
>It will probably require several queries if you want to count the number of
>records for each nationality, for each gender, etc. etc.; the Count operator
>in Access counts *records* in the table, and you want each record to be
>counted several times by several different criteria.

From: Daryl S on 1 Feb 2010 11:42
Justme -

If you have set up a crosstab query for each of your variables, and the
variables are the column headers, and the rows (first column) of each of
these queries is the same, then you can create a single query that combines
them all. Open a new query in design mode and add each of the crosstab
queries as you would add tables. Join them all on the first field. If there
are any of these crosstab queries that might not have all the values in the
first column, then join them with an outer join (double-click on the join and
select ALL Records from the crosstab that will have all the values in the
first field).

Then add the qryname.* field from all the crosstabs to the field list in the
report grid. You need to use the qryname.* for each of these in case there
is a time period where there are no records for a particular value (for
example all clients were females in the week). This does cause that first
column to repeat for each crosstab, but that is a small price to pay for the
simplicity and flexibility you get with this. If you add another postcode
to your data, it will automatically show up.

Hope that helps!
--
Daryl S


"justme43" wrote:

> Please help
> Please note I am not use to using Access
> I would like to be able to count items in fields. I have a table which I
> produced by doing a query of 2 tables and making it a query table.
> The fields are Client no, Date, nationality, gender, postcode, marital status,
> and a few others.
> I can do a cross tab query and count the items in each in field.
> However what I am having trouble doing is bring this together in the one
> report or query.
> I am also open to suggestion as to better ways of obtaining my data.
>
> Thanking you for any help that you are willing to supply.
> please remember I am not an access user so simple is my best option
>
> .
>
 | 
Pages: 1
Prev: I'm Also having trouble with the 2499 message
Next: I have two Yes/No fields in a table (ContractorInstall, andInHouseInstall)