Data from multiple yrs in one form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to have data from multiple years (which is currently in one table per
year) appear in one form. The type of data is the same from year to year.
This is for tracking supplies and deliveries for multiple water companies.
Each company has a unique number that stays the same from one year to
another. I would like to be able to have a look up function for the
number/name and the year which would then cause the other data to be
displayed.

Thanks for any suggestions.
 
I need to have data from multiple years (which is currently in one table per
year) appear in one form.
Click to expand...

Storing data - years in this case - in a tablename is considered bad
design; this is one of the most prominent reasons.
The type of data is the same from year to year.
This is for tracking supplies and deliveries for multiple water companies.
Each company has a unique number that stays the same from one year to
another. I would like to be able to have a look up function for the
number/name and the year which would then cause the other data to be
displayed.

Thanks for any suggestions.
Click to expand...

Your best bet would be to create one master table with all the fields
in these yearly tables, plus a year field (not named Year, that's a
reserved word), and run Append queries to copy the data from your
separate tables into the master table. You can then create a Query
with

[Enter year:]

as a criterion on the year field, to recover the annual view; or you
can use crieria on any other field or fields to search all years
together.

If, for some reason, you don't want to do this, you can create a UNION
query to string together all the tables. See UNION in the online help.
The SQL (and you must use the SQL window, not the grid) would look
something like

SELECT thisfield, thatfield, anotherfield, 1996 AS BillYear
FROM [tbl1996]
UNION ALL
SELECT thisfield, thatfield, anotherfield, 1997
FROM [tbl1997]
UNION ALL
<etc etc through all the tables>

John W. Vinson[MVP]
 
Back
Top