count number of data in one database in a range from another file?

[pemt]

Dear all,

I have a 5 millions records from a database imported from a text file
including Name, Values two columns. And I have another Excel file with 4,000
records including Name, RangeStart, RangeEnd, 3 columns. I need to count how
many records from the 5 millions data in each range.
For example,
DataBase Records: RangeFile Records:
Name Values Name RangeStart RangeEnd
Name1 20 Name1 13 22
Name1 30 Name2 20 30
Name2 15

So, the count should be
RangeFile Records:
Name RangeStart RangeEnd Count
Name1 13 22 1
Name2 20 30 0

I imported both database and range file into Access as Tables, so how to
count it with either Macros or Modules?

Really appreciate for your help.

pemt

 

[KARL DEWEY]

Try this --
SELECT FirstTable.Name, Count(FirstTable.Values) AS CountValues
FROM FirstTable INNER JOIN RangeFile ON FirstTable.Name = RangeFile.Name
WHERE Values Between RangeStart And RangeEnd
GROUP BY FirstTable.Name;

 

[pemt]

Thanks Karl,

could you please write down all the lines of coding?
thanks a lot!

pemt

 

[KARL DEWEY]

could you please write down all the lines of coding?
I did. What I posted was the SQL for a query.

Create a query in design view, select no table, click on VIEW -SQL View,
paste post in the window.

 

[pemt]

Karl,

Thanks a lot! It works.
One more question, if in the Range file, Name1 has many different ranges,
how to change the code? May I change the code "GROUP BY FirstTable.Name" to
"GROUP BY SecondTable.Name.RangeStart"?
for example,
RangeFile Records:
Name Values Name RangeStart RangeEnd
Name1 20 Name1 13 22
Name1 12 Name1 25 40
Name1 30 Name2 20 30
Name2 25

Best,

pemt

 

[KARL DEWEY]

May I change the code "GROUP BY FirstTable.Name" to "GROUP BY
SecondTable.Name.RangeStart"?
Yes you may as you do not need my permission. Why did you not just try it
instead of asking? By the way it will not work as there is no such object as
'SecondTable.Name.RangeStart' so you will get an error.

Try this --
SELECT FirstTable.Name, [RangeStart] & " - " & [RangeEnd] AS Range,
Count(FirstTable.Values) AS CountValues
FROM FirstTable INNER JOIN RangeFile ON FirstTable.Name = RangeFile.Name
WHERE Values Between RangeStart And RangeEnd
GROUP BY FirstTable.Name, [RangeStart] & " - " & [RangeEnd];

 

[pemt]

Hi Karl,

It's great! Huge Thanks!

pemt

SecondTable.Name.RangeStart"?
Yes you may as you do not need my permission. Why did you not just try it
instead of asking? By the way it will not work as there is no such object as
'SecondTable.Name.RangeStart' so you will get an error.

Try this --
SELECT FirstTable.Name, [RangeStart] & " - " & [RangeEnd] AS Range,
Count(FirstTable.Values) AS CountValues
FROM FirstTable INNER JOIN RangeFile ON FirstTable.Name = RangeFile.Name
WHERE Values Between RangeStart And RangeEnd
GROUP BY FirstTable.Name, [RangeStart] & " - " & [RangeEnd];

Karl,

Thanks a lot! It works.
One more question, if in the Range file, Name1 has many different ranges,
how to change the code? May I change the code "GROUP BY FirstTable.Name" to
"GROUP BY SecondTable.Name.RangeStart"?
for example,
RangeFile Records:
Name Values Name RangeStart RangeEnd
Name1 20 Name1 13 22
Name1 12 Name1 25 40
Name1 30 Name2 20 30
Name2 25

Best,

pemt