SQL

If I understand you correctly, you want to use the COUNT command.

Using your table as it is. it would be:

SELECT COUNT("kind of house") FROM your_table WHERE town="new york" AND kind of house="1"

That should return the number of rows in your table with a specific town and type of house.

Cheers,

Bob

----------------------------------

I think you need to think about your design. The anonymous poster presumed you were storing this all in one table.

You want a table describing the types of houses, and a table describing the towns? You then want to know how many houses of each type are stored in each town.

Table 1 (house types)

Type_id, house_desc

Table 2 (Towns)

Town_id, town_desc

Table 3 (Houses)

house_id, town_id, type_id, address... any other information you want to store.

For each new house you can add a record into table three.

Unique ids from table1 and table2 stored in their relevant positions in table3 with an sql statement as follows;

select b.town_desc, a.hous_desc, count(c.hous_id)
from table1 a, table2 b, table3 c
where c.town_id = b.town_id
and c.hous_id = a.hous_id
group by c.town_id, c.hous_id

would yield something like:

New York City , Town House 1, 2

Where 2 records had been inserted into table 3 for town house 1.

Make sense?
=*=
If things seem bad, think that they can get a whole load worse, and they don't seem so bad anymore

=*=

[edited by - hammerstein_02 on January 27, 2004 7:52:34 AM]

I have the table i described in the first post. and now i want this:

type 1 type 2
New york 3 2
La 1 4
etc etc
So i have everything in one table and want to extract this info with a query. Is that possible?

thanx again

Oh and i use access. It had to be made in this packet so..

[edited by - Pillejunior on January 27, 2004 8:39:06 AM]

Why are you storing everything in one table? You are going to replicate the names over and over again, and if you decided to change one name, then you will have to go right through and change all the descriptions.

Because the table is already there and i have to modify it.

Then something like;

select town_desc, house_desc, count(house_desc)
from the_table
group by town_desc, house_desc

This should work, it will group each distinct Town name, then each house description and give you a total for that group.

Hope this helps!

I have know this table
kind count
new york 1 3
new york 2 4
LA 1 2

but i want the kind as tables itself
k1 k2 k3
new york 3 4 0
LA 2 0 0

how do i do that, or how do i make a report that look likes this

You are going to have to explain yourself a little better I am afraid, I don''t understand.

I think you mean this;

Kind | Count
----------------------
New York 1 | 5
LA | 7

Is that right? In which case, my original response to your posting is what you want.

Sorry i mean this

i have
---------kind------count
ny.........1.........3
ny.........2.........4
la.........1.........2
la.........4.........1

i want

------kind1----kind2----kind3----kind4
ny.....3........4........0.......0
la.....2........0........0.......1

is this better in hope

If you are only ever going to have four types of house then I suppose that would work. My original solution is your best bet if you are going to have varied numbers of house types.

You are wanting to know how many types of each house are in each town. The solution I first offered requires three tables, you store house descriptions, town descriptions and then a table full of houses that link to the relevant descriptions.

If you create a table like;

Town
House Type 1
House Type 2
House Type 3
House Type 4

Where the Town field contains the town description and the other fields keep a count of the types. Its not a very dynamic way of doing it, and will restrict you.