find all fields with the same name

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

Guest

I have a set of tables that each has a field named 'dued', data type = date.

I want to find a way to search all records, in all tables that have a field
named 'dued' and compare it to system date.

I guess that is done using a for ... next loop but how do I retrieve the
values??? maybe defining a temp table and copying field values????

Please help!!!

PS I am not very familiar with all that ADO, DAO, etc..
 
Dimitri,

You could use a Union query to qery the fiel in all tables in one go.
Assuming there is a dued field in tables Tbale1, Table2 and Table3, the
query would look something like:

SELECT "Table1" As InTable, * FROM Table1 WHERE dued = Date()
UNION ALL SELECT "Table2" As InTable, * FROM Table2 WHERE dued = Date()
UNION ALL SELECT "Table3" As InTable, * FROM Table3 WHERE dued = Date()

This will return all matching records in the tables, with the table name
in the first field (so you know which record comes from which table).

By the way, is there a compelling reason to have several identically
structured tables? Or are they different?

HTH,
Nikos
 
By the way, is there a compelling reason to have several identically
structured tables? Or are they different?
Click to expand...

Niko,
All tables are different but in some there is a due date field, and what I
want is on database open, to check for each record that there is a due date,
if it is past current date to setoff a reminder, and calculate overdue dates.
aka all overdue records should appear in an 'overdue' table which will be
displayed as a TO DO list.

hope this covers up the issue.

PS. thanx for the help. I'll try it, if something goes wrong (hopefully
nothing) I'll get in touch, again.
 
Dimitri,

If the tables have different structure then you might get a problem in
the Union query trying to select all fields form each; in that case,
select specific fields from each table, amking sure the number of fields
selected and their types match. E.g.:

SELECT "Table1" As InTable, Field1, Field2 FROM Table1 WHERE dued = Date()
UNION ALL SELECT "Table2" As InTable, Field3, Field9 FROM Table2 WHERE
dued = Date()
UNION ALL SELECT "Table3" As InTable, Field11, Field4 FROM Table3 WHERE
dued = Date()

HTH,
Nikos
 
Back
Top