Subform to filter tables 1

[Qino]

I have seven tables I need to query at once and display the results requested. Each of the seven tables lists the unique ID and 25yrs of data (ie. certification or state of project)

I need a subform within the main form (which lists basics like address and contact info) to query the seven tables and pull in the data one year at a time, or better yet only list the fields that have data entered. Some have 1 or 2 years of info, a few others have 20+, most have 10-15.

I've found several quasi-solutions but nothing quite works and I'm desperately trying NOT to create 25 tables (one for each year) and create 25 buttons (one for each of 25 queries) which will populate the subform one at a time.

Any elegant solutions would be welcome...

 

[Remou]

Why seven tables? Can you use a union query to create one table?

It is possible to link a subform on more that one field and / or control, so it would be possible to provide a year combo or textbox that could be the link master for a similar year link child field in the (combined?) table.

 

[Qino]

When I got this it had most of the 175 fields (7x25) bunched into two tables, I simply broke up the info into the more logical grouping (by category) rather than year.

When I search a given ID I need to be able to pull up (even if one at a time) all 25 years worth of the 7 categories of tables. Some are very short and simple and originate from a combo in another table, others are very specific and detailed.

I'm fairly sure I could Union them together, but would that allow me to query one of the tables and it would pull in the same year from the others?

I guess I'm looking for the sql-macro-vba equivalent of:
(I apologize for my code pidgin but it ought to get the idea across)

"If combo box is #

in Table 1 select Unique ID Year# write to field 1
in Table 2 select Unique ID Year# write to field 2
...
in Table 7 select Unique ID Year# write to field 7"

There might be an easier way to do it and I'm all for new ideas, but I'm pretty sure I've seen this performed in other DB's but this function is beyond me.

Any nudges in the right direction would be welcome.

 

[Remou]

It is not necessary to break a table into categories, a new category field should be added, you may wish to read

http://www.r937.com/relational.html.

If you do not wish to edit the data, a crosstab query is probably what you want. First, you will need a Union:

SELECT ID, Year, "A" As Category FROM T1
UNION ALL
SELECT ID, Year, "B" As Category FROM T2
<...>
UNION ALL
SELECT ID, Year, "G" As Category FROM T7

I am not exactly sure what you wish to return, but here is an example:

TRANSFORM First(CatUnion.ID) AS FirstOfID
SELECT CatUnion.Year, CatUnion.ID, First(CatUnion.ID) AS [Total Of ID]
FROM CatUnion
GROUP BY CatUnion.Year, CatUnion.ID
PIVOT CatUnion.Category;