How to count record

[Guest]

I have 2 table and fields as below

Table A has 2 fileds Table B has 1 field
val1 val2 valRef

All 3 fields is integer data

How can I create query to count all record of table B where valRef between
val1 an d val2.

 

[Arvi Laanemets]

Hi

SELECT CNT(b.valRef ) FROM a a, b b WHERE b.valRef >= a.val1 AND b.valRef <=
a.val2

 

[Guest]

Thank you for answer but yoyr query shows sum of count.
This is my data in table A and B


Table A Table B

val1 val2 valRef
1 8 3
2 8 4
3 8 5
4 8 6
5 8 7
5 11 10

I want to see 3 fields query result as below

val1 val2 CountResult
1 8 5 between 1-8
in Table B is 3,4,5,6,7
2 8 5 between 2-8
in Table B is 3,4,5,6,7
3 8 5 between 3-8
in Table B is 3,4,5,6,7
4 8 4 between 4-8
in Table B is 4,5,6,7
5 8 3 between 5-8
in Table B is 5,6,7
5 11 4 between 5-11
in Table B is 5,6,7,10

Help me again please.

 

[Tom Ellison]

Dear Nova:

I built a database with your 2 tables, entered the data, and tested. I got
the results you suggest you wanted.

The query is:

SELECT A.val1, A.val2, COUNT(*) AS Counted
FROM TableA A, TableB B
WHERE B.valRef BETWEEN A.val1 AND A.val2
GROUP BY A.val1, A.val2

The result of running this query are:

Query1 val1 val2 Counted
1 8 5
2 8 5
3 8 5
4 8 4
5 8 3
5 11 4


I believe this may be what you wanted.

Note I used Aliasing for the names of the two tables. That means I have
used A for "TableA" and B for "TableB".

I also cheated and took the space out of your table names. It just makes
for extra typing and I can't read my own queries if I do this this way. If
you use spaces in table names you must bracket [] them all the time. All
the more reason to use aliases then.

Please let me know if this helped, and if I can be of any further
assistance. Perhaps you might want to know how this works.

Tom Ellison
Microsoft Access MVP (watch out! I'm back!)

 

[Guest]

Thanks for your assistance
I know nothing about "Aliasing" but your query is worked.
In my file has to use query like this in many place, Is it possible to
create function
instead of query

 

[Guest]

Great to see you back in the news groups. Does this suggest you are an April
awardee?
--
Duane Hookom
Microsoft Access MVP

Dear Nova:

I built a database with your 2 tables, entered the data, and tested. I got
the results you suggest you wanted.

The query is:

SELECT A.val1, A.val2, COUNT(*) AS Counted
FROM TableA A, TableB B
WHERE B.valRef BETWEEN A.val1 AND A.val2
GROUP BY A.val1, A.val2

The result of running this query are:

Query1 val1 val2 Counted
1 8 5
2 8 5
3 8 5
4 8 4
5 8 3
5 11 4


I believe this may be what you wanted.

Note I used Aliasing for the names of the two tables. That means I have
used A for "TableA" and B for "TableB".

I also cheated and took the space out of your table names. It just makes
for extra typing and I can't read my own queries if I do this this way. If
you use spaces in table names you must bracket [] them all the time. All
the more reason to use aliases then.

Please let me know if this helped, and if I can be of any further
assistance. Perhaps you might want to know how this works.

Tom Ellison
Microsoft Access MVP (watch out! I'm back!)

Thank you for answer but yoyr query shows sum of count.
This is my data in table A and B


Table A Table B

val1 val2 valRef
1 8 3
2 8 4
3 8 5
4 8 6
5 8 7
5 11 10

I want to see 3 fields query result as below

val1 val2 CountResult
1 8 5 between 1-8
in Table B is 3,4,5,6,7
2 8 5 between 2-8
in Table B is 3,4,5,6,7
3 8 5 between 3-8
in Table B is 3,4,5,6,7
4 8 4 between 4-8
in Table B is 4,5,6,7
5 8 3 between 5-8
in Table B is 5,6,7
5 11 4 between 5-11
in Table B is 5,6,7,10

Help me again please.

 

[Jamie Collins]

Great to see you back in the news groups. Does this suggest you are anApril
awardee?

Oh, is _that_ how it works <g>? Right, I'm not going to post in these
groups again until they make me an MVP and I'm holding my breath too...

 

[Tom Ellison]

Dear Duane:

No, January. Just found out about it. The new Lead had enough ambition to
look up my former participation and get my phone number. He went the extra
mile.

My email address has been swamped with spam and I just got tired of messing
with it. About 200 messages per day. After a week or two, it just doesn't
seem worth it. But I'll have to start checking it all again now.

Good to be "back." Say hi to everyone for me. I'll be in touch later this
week.

Tom

Great to see you back in the news groups. Does this suggest you are an
April
awardee?
--
Duane Hookom
Microsoft Access MVP

Dear Nova:

I built a database with your 2 tables, entered the data, and tested. I
got
the results you suggest you wanted.

The query is:

SELECT A.val1, A.val2, COUNT(*) AS Counted
FROM TableA A, TableB B
WHERE B.valRef BETWEEN A.val1 AND A.val2
GROUP BY A.val1, A.val2

The result of running this query are:

Query1 val1 val2 Counted
1 8 5
2 8 5
3 8 5
4 8 4
5 8 3
5 11 4


I believe this may be what you wanted.

Note I used Aliasing for the names of the two tables. That means I have
used A for "TableA" and B for "TableB".

I also cheated and took the space out of your table names. It just makes
for extra typing and I can't read my own queries if I do this this way.
If
you use spaces in table names you must bracket [] them all the time. All
the more reason to use aliases then.

Please let me know if this helped, and if I can be of any further
assistance. Perhaps you might want to know how this works.

Tom Ellison
Microsoft Access MVP (watch out! I'm back!)

Thank you for answer but yoyr query shows sum of count.
This is my data in table A and B


Table A Table B

val1 val2 valRef
1 8 3
2 8 4
3 8 5
4 8 6
5 8 7
5 11 10

I want to see 3 fields query result as below

val1 val2 CountResult
1 8 5 between
1-8
in Table B is 3,4,5,6,7
2 8 5 between
2-8
in Table B is 3,4,5,6,7
3 8 5 between
3-8
in Table B is 3,4,5,6,7
4 8 4 between
4-8
in Table B is 4,5,6,7
5 8 3 between
5-8
in Table B is 5,6,7
5 11 4 between
5-11
in Table B is 5,6,7,10

Help me again please.

 

[Tom Ellison]

Dear Nova:

I let you know I was "aliasing" so you would know the key word to look up in
"Help". From there you can learn about it yourself.

Basically, instead of this:


SELECT A.val1, A.val2, COUNT(*) AS Counted
FROM TableA A, TableB B
WHERE B.valRef BETWEEN A.val1 AND A.val2
GROUP BY A.val1, A.val2


Without aliasing, it would read:

SELECT TableA.val1, TableA.val2, COUNT(*) AS Counted
FROM TableA, TableB
WHERE TableB.valRef BETWEEN TableA.val1 AND TableA.val2
GROUP BY TableA.val1, TableA.val2

To make things worse, if you have a space in the table names (as your posts
indicate):

SELECT [Table A].val1, [Table A].val2, COUNT(*) AS Counted
FROM [Table A], [Table B]
WHERE [Table B].valRef BETWEEN [Table A].val1 AND [Table A].val2
GROUP BY [Table A].val1, [Table A].val2

From this, perhaps you can see that an alias is a way of setting up a
temporary reference to a table by another name.

To me this seems very natural and makes for more readable queries and saves
significant typing. Sort of a "don't leave home without it" in my
estimation.

You may also want to read up on "cross product" for information on another
technique used in this query.

As for making a function of it, I don't really know what breadth of
variation you may want it to perform. Hard to say what's possible.

Tom Ellison
Microsoft Access MVP

 

[Guest]

Dear Tom Ellison:

My file is Access 2003 WindowsXP, Thanks for let me know abot "alasing".
In my file, I want to count or sum or others arithmatic function by look data
from some reference tables such as Table B in my question.

For example In form AA contains of
control text1, text2 and button "count"
for event button "count" click() I want function to get value from text1 then
compare with data (valRef field) in Table B accordings to condition such as
count number of record in Table B where text1<valref then show results in
text2.

Assume ValRef filed has 5 records

11
12
13
14
15

if value in text1 in form "AA" is 5, when click buuton "count" then text2 = 5
if value in text1 in form "AA" is 16, when click buuton "count" then text2 = 0
if value in text1 in form "AA" is 13, when click buuton "count" then text2 = 2

I it poosible to create function for this?

Thanks

 

[Tom Ellison]

Dear Nova:

Your explanation of the problem is not getting through to me. Perhaps a
more complete example would help.

Would you try that, please.

Tom Ellison
Microsoft Access MVP