Queries with Linked Tables

[sshivji]

Hello, here is my issue. I have a FE with linked tables to several BE. Each BE has 2 tables (lets say A and B) and every year I add a BE and link the table with FE.


The 2 unrelated tables in each BE would be named e.g., A2015, B2015, A2016, B2016, etc.

In the FE I have report menu whereby the user would query the Case # and the query would give the info. Is there a way to create a query.

Also, for the current year I would add A2017 and B2017. How would I create a "dynamic" query such that this query would search ALL BE.

Thanks in advance.

[NTC]

If I understand your post; you data structure is not suitable for a relational database. One parameter cannot be queried across multiple tables in the way you describe, and as you add back end tables....

Instead of adding tables - you need to append the data into a common table, while adding a value into an ID field that tells you the source of the data...so that as the records intermingle you will always know the source of each record.

[CJ_London]

An alternative would be to use a union query for each year, but if the tables are so large they need to be in separate back ends it is likely to be very slow. Something like


SELECT *
FROM A2015
WHERE CaseNo=123
UNION ALL
SELECT *
FROM A2016
WHERE CaseNo=123
etc

[sshivji]

Thank NTC. I will try the "common table" way.

[sshivji]

Thanks Ajax.