I have two tables in an Access 2000 database with multiple instances of a particular field in each table. I want a single Access 2000 SQL query that will give the total count of occurrences for each distinct appearance of the field from the two tables, have in line SQL queries to give the distinct
totals from the individual tables and want to combine the two into one SQL query to be executed in VB without writing to the database or saving any of the queries to the database. The tables have headings of aor_id and pos_id which are actually the same thing and hence the same data type. No changes
can be made to the structure of the tables. No queries can be saved on the database. Ideal solution would be inline SQL query. second prize would be to create queries temporarily in Access and then delete the queries after using them but this is far from optimal. I can provide the queries already there
and the tables populated with sample data. The two queries I have are basically the same with table name only changed. SELECT DISTINCT b1.AOR_ID,
(SELECT count (b2.AOR_ID) from BS_FREQ_REQ b2 where b2.AOR_ID=b1.AOR_ID) AS
FROM BS_FREQ_REQ AS b1;. How to combine the two to give one query is the issue.
1) Complete and fully-functional query text 2) Access 2000 MDB with short, sample data on tables and query saved to demonstrate successful processing 3) Complete ownership and distribution copyrights to all work purchased.
Access 2000, Windows