same length query

[makatak88]

Hi Guys,
Im a bit of a noob at this and im trying to work out how to show the hoses that have the same length as another one. This is as close as I got which all it shows is which hose lengths appear more than once. I used the Having count >1 because if it doesnt appear at least once it cant have another matching length. I think im thinking to hard about this. Any help would be appreciated.


cheers


SELECT hose.length
FROM hose
WHERE hose.length=(length)
GROUP BY length
HAVING Count(hose.length)>1;

[orange]

Try removing the Where line.

Select hose.length, count(hose.length)
GROUP BY length
HAVING Count(hose.length)>1;

[makatak88]

thnx orange but

Select hose.length, COUNT(hose.length)
GROUP BY hose.length
HAVING Count(hose.length)>1;

just gives me a syntax error. I see count used on the select line alot and it never works for me. like i said Im a noob and just trying to teach myself this. making slow headway.

[orange]

Here are a few jpgs. It shows the table with values, the query SQL and the result.
Where's the syntax error?

[makatak88]

orange thanks, its amazing some people actually take the time to help people trying to learn. I simply dont really understand subqueries yet (im assuming thats what it is) and didnt have all the brackets around the last line. Your example works great. I just have one more question. if i wanted to retrieve the name of the hose in that query I add hose.name to the select and group by lines but get 0 results.

Select hose.length, hose.name
FROM hose
GROUP BY hose.length, hose.name
HAVING (((Count(hose.length))>1));

[orange]

First, this is not a subquery. This is an aggregate function

More info here http://www.techonthenet.com/sql/index.php

To your query: You first asked about the number of hoses organized by length.

How do the names of hoses relate to length? You know your business and terminology. The rest of us do not, and can only guess until you give us specifics.

If each hose, even those with the same name, can be of different lengths, then you have to be very specific in what question(query) you want to ask.

"How many 10 ft hoses do we have" is a very different question than
"How many Deluxe_Super Hoses do we have and what are their lengths"

Do you have a few sample records? And a few sample questions you want answered?

Some links for learning:

http://allenbrowne.com/subquery-01.html Subqueries
http://allenbrowne.com/tips.html Tremendous site - all kinds of goodies

http://www.sfubusiness.ca/areas/mis/...essons/map.pdf ( lessons)

http://www.rogersaccesslibrary.com/forum/topic238.html Database design/entity relationship diagrams/hernandez approach

http://forums.aspfree.com/attachment...achmentid=4712 Great short, easy to read re: RDBMS


Re: your question - You would get results if you Group By name also and if there were more than 1 hose for that HoseName.

[makatak88]

hey again orange. Those links are awesome. ive just started to have a look at them.
I should have included a shot of the table to start with. and I got a little confused with what I was actually trying to achieve. Basically I was trying to get all the names of the hoses that had the same length.
Using

Select hose.hose_length
FROM hose
GROUP BY hose.hose_length
HAVING (((Count(hose.hose_length.length))>1));

returns 7 and 9. but i need to get all the names of the ones that are exactly the same length as the other ones. so I should have 3 of the 7's and their names and 2 of the 9's
I think ive gone about it the wrong way.
cheers for youre patience

[orange]

and I got a little confused with what I was actually trying to achieve

.

You are not the first to do so. If you read many of the posts on forums, especially for posters with less than(say 40) posts, the question is usually about a from or subform or a button. Rarely have they looked at Database/Table design, normalization, E_R diagramming etc.

A simple technique that will help in most cases is to write down in a paragraph or so exactly what the problem/issue/opportunity is. Then, on paper,
look for the nouns, look for the verbs....
as per http://www.rogersaccesslibrary.com/T...lationship.zip

[makatak88]

those allenbrowne sites are great. i got it sussed thnx for your help

[orange]

those allenbrowne sites are great. i got it sussed thnx for your help

Absolutely! An excellent reference for many Access issues.
The other sites have valuable info also.