linking tables removes data

[Jymoz]

I have a number of tables (i think 3 in total) with vaious data in them, mainly to do with attendance. I have them all linked up joined on an ID field 9which is unique to each person) so that i can gather data from all 3 tables.

The problem is when i try to use a formula with values from one of the tables. Some of the IDs are not in this table (as this one is the persons attendance at a different site and so some have not gone there) and so the moment i put this formula into the report the IDs of the people that are not in the table disappear and i cant see why.

I've tried changing the report links between the tables and so on but nothing i've done works. Can anyone suggest something i can try?

Oh and i've found that i cant put all the data into a single table as when i use the formula it brings back the wrong value.

Any help is appreciated

 

[Endre]

Are you using a left join between your main table that has all the data, and your secondary table that has a sub-set of data? To check, open the query in design view and double click on the line that joins them (The line should should be an "arrow" pointing from the main table to the subset table) and you should have the choice "Include all records from "MAIN_TABLE" and only those records from "SUBSET_TABLE" where the joined fields are equal". This means you can "join" the tables, even if the data is not available in the subset table.