Complex query with non-standard return value

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

Guest

Any help will be appreciated!!!
1) What I have:
- 14 tables in Access 2003 with randomly missing records in every table;
- all 14 tables have the same primary key (Column 1) and have a one-to-one
relationship.

2) What I need:
To create a query which will analyse all 14 tables and return all missing
records from 14 tables, but the missing records should be displayed by the
names of the columns they are in.
Thanks in advance.
Svetlana
 
a "missing record" is a record of one table but is in all other 13 tables?

Is this is a one time effort ....are there alot of records?

with the 1-1 relationship you could make a query involving all tables with
just the single unifying ID column from each table....i.e. 14 column query
and then visually see where there are blanks...
 
but the missing records should be displayed by the names of the columns
they are in.

You seem to be mixing terms. Records are not in column.

A database has tables. Tables have fields. Fields have data. Are you
missing data from a field?

If you are thinking in Excel terms then here is the translation --
Workbook Worksheet Rows Columns
Database Tables Records Fields
 
As Karl Dewey wrote in his reply I was mixing up concepts. I meant missing
fields, not records.
Basically the idea is the following:
- There is a master table which lists certain items let's say projects
(every project has a unique name - primary key);
- every project has an amount of attibutes, like: start date; end date;
duration, budget costs etc. Every attribute has a column. Because there are a
lot of attributes and it is impossible to host them in one table (due to
technical reasons), 14 separate tables were created;
- before the project is activated in the system, all fields in all 14 tables
should be populated; Missing data should be provided by a different
department.
- the task I have described before. The task is repetative and manually
scanning all 14 tables will be very inefficient.
 
Dear Karl,
Basically the idea is the following:
- There is a master table which lists certain items let's say projects
(every project has a unique name - primary key);
- every project has an amount of attributes, like: start date; end date;
duration, budget costs etc. Every attribute has a column. Because there are a
lot of attributes and it is impossible to host them in one table (due to
technical reasons), 14 separate tables were created;
- before the project is activated in the system, all fields in all 14 tables
should be populated; Missing data should be provided by a different
department.
- the task I have described before. The task is repetative and manually
scanning all 14 tables will be very inefficient.
 
One way would be to start with 14 queries along the lines of:-

SELECT ID, IIF(ISNULL(Field1, "Field1 missing","") as A, IIF(ISNULL(Field2,
"Field3 missing","") as B, ....
FROM Table1
WHERE ISNULL(Field1) OR ISNULL(Field2) OR ISNULL(............
 
You need to normalize your data. Create a table by coping the structure of
the 14 and add a field that identifies your need for 14 tables.
 
Thanks a lot...

Regards,
Svetlana

David F Cox said:
One way would be to start with 14 queries along the lines of:-

SELECT ID, IIF(ISNULL(Field1, "Field1 missing","") as A, IIF(ISNULL(Field2,
"Field3 missing","") as B, ....
FROM Table1
WHERE ISNULL(Field1) OR ISNULL(Field2) OR ISNULL(............
Click to expand...
 
Back
Top