0

I have a question. Is it possible to use a table as a query criteria to count how many items in another table?

I have two tables. Table 1 is the query criteria. Table 2 is a table with many data.

enter image description here

I'd like to have a new table or insert into an existing table like the figure below.

enter image description here

Count number of items from table 2 with criteria from table 1. I can run the query many times to count the data of different years. So the main problem is how to count the items use another table as criteria.

I have no idea how to achieve this. Hope you may help me with this. Thanks a lot for your help >"

Thom-x
  • 841
  • 1
  • 6
  • 20
Webster H.
  • 35
  • 1
  • 11
  • No you cannot reference foreign table in a calculated field. Whats wrong with having a query? – Krish Jul 31 '15 at 09:58
  • I only know how to query it one by one. Like create a query for 000-009. And another for 010-019. But it's impossible for me to create more than 1000 queries @@ – Webster H. Jul 31 '15 at 10:05
  • its time to learn how to design tables and write queries :) start linking the tables with a foreign/linked key. i.e. if table1 has C1, table2 must also represent c1 not a1 – Krish Jul 31 '15 at 10:11
  • Oh my... I know if I add a key to table 2, it will be much easier. But no any other way?... I will have more than 500000 data in table two... – Webster H. Jul 31 '15 at 10:16
  • maybe I should use DCount Function? But still no idea how to use it...@@ https://support.office.com/en-in/article/DCount-Function-f6b5d78b-ad0b-4e42-be7a-11a64acbf3d3 – Webster H. Jul 31 '15 at 10:49
  • either way you need to link both tables otherwise how do you want to tell a query what fields to sum?? – Krish Jul 31 '15 at 10:55
  • really okay... I'll find a way to connect two tables. Thanks for your help! – Webster H. Jul 31 '15 at 13:40

1 Answers1

0

The following query is the closest I can get for you. rows for C5 and C6 would be missing as no value exists for them.

TRANSFORM Nz(Count([number]),0) AS CountValue
SELECT Table1.ID
FROM Table1, Table2
WHERE (((Table2.number) Between [table1].[start] And [table1].[end]))
GROUP BY Table1.ID
PIVOT DatePart("yyyy",[ndate]);

yeilds:

ID  2000    2001
C1  2       0
C2  2       0
C3  1       0
C4  0       2
C7  1       0

most likely with a larger dataset this answer may yeild some incorrect values to the lack of a definite join, but hopefully it points you in the correct direction to try, once you incorporate an appropriate join in your your tables.

Gene
  • 392
  • 6
  • 15
  • WOW!!!!!!!!!!!! This is exactly what I want! Thanks for your direction! May I ask another question. Is there any way to write back the result to table 1 base on the ID? Thanks@@~ – Webster H. Jul 31 '15 at 15:31
  • Post another question for that, we don't want to clutter up this one with multiple questions on the same thread. Be sure to post the actual query you are going to use. – Gene Jul 31 '15 at 15:51
  • OK! Thanks for your help! – Webster H. Aug 01 '15 at 08:10