Counting Categories in Columns

[silversurfer19]

Hi there, I'm still very new to Access and am struggling to do something I'm sure must be possible. I have been given a table containing three columns, 'homephonetestcode' 'mobilephonetestcode' and 'workphonetestcode'. Each of these columns contains one of a fairly limited number of values. My aim is to produce some kind of automated summary which will show the overall number of records within the table, and the number of records which have a match to each possible value within each column. I've attached an example of what I'm aiming to do.

I've managed to use queries to obtain a count of each value within one column, for example the number of values in the homephonetestcode column which match each possibility, but can find no way of then joining this with the other two columns, or merging them into a useful summary.

Any help would be greatly appreciated,
Thanks in advance.

 

[DCrake]

Would be more useful if we could see the raw data that is being summarised.

 

[silversurfer19]

I've attached a sample of the data I've got to work with for you to have a look at.

 

[jal]

I've attached a sample of the data I've got to work with for you to have a look at.

The summary report shown in the image you attached seems to involve a complex of info not found in the uploaded .MDB table. In other words I don't see how the table even provides all the kinds of data necessary to produce that kind of summary report. Care to clarify?

 

[silversurfer19]

Sorry, I'll try to explain better.

The table which I'm working with and which I provided an example of contains three columns, homepingcode, workpingcode and mobileping code, for each record. Each of these columns contains a code representing the result of trying to ping a customer's three contact details. I want to be able to summarise this information into the number of each type of ping code obtained for each of those three columns. i.e how many 100 ping code results were there in the housepingcode column,how many 200 ping code results etc..
Does that help?

 

[weeblebiker]

I just did something similar
you want to do an if then statement to change the information to numbers then sum the results
first bring the information into an access table
then you make a query on the table use the "create a query in design view"
in the "Field" line of each column you write a code for the suming of EACH response in the table column, if there are 3 responses in the table column and three columns, then three columns in the query per column in the table so nine columns in the query. the code has to be named something different than the table column name followed by a colen

example houseping100ping: not housepingcode:

Sum(IIf([table column name]="info in column field you want to sum',1,0))
where the 1 is the True output and 0 is the False output

the code is Sum(IIf([housepingcode]="100 ping code",1,0))

so

houseping100ping:Sum(IIf[housepingcode]="100 ping code",1,0))

then when you have written all the query columns, you can veiw the results in the query by hitting the "view" icon up top or make a form
by hitting the "new object auto form" icon at the top of the screen to make a form. then open the form in editor to make it look presentable

 

[silversurfer19]

Thanks, that sounds helpful. I don't know when I'll get a chance to try it out, but when I can I will, and will let you know how it goes.

Thanks again.

 

[weeblebiker]

sure, read the above again cause I edited it a bit to make sure you get it the first time

 

[jal]

Here's a solution - that took some time !

But I didn't manage to print the summary in numeric order. I used an Order By clause, which almost produced numeric order, but I had to UNION in a totals row, and the UNION engine messed up the numeric order. Maybe someone else can help with that part. I'm worn out.

 

[jal]

I thought I saved "qryFinal" - guess not. Just paste in the SQL yourself and save it as "qryFinal."