Query shows only records with data

[zbird]

Hi,

I have built up a database based on geographical sites. I would like to create a report of each sites with data from related tables. I have created a query, as the base of the report but unfortunately only one site of the recorded 400 turned up in my query. There are a few tables that don't contain any information about most sites yet, still it needs to be part of the query, otherwise I can't build them in the report. I haven't put any criteria to the fields. The report is needed to collect further data about each sites. Shall add '0' or 'NoInfo' as a default to each field? Or is there any other solution?

Many thanks
Susanna

 

[MSAccessRookie]

Hi,

I have built up a database based on geographical sites. I would like to create a report of each sites with data from related tables. I have created a query, as the base of the report but unfortunately only one site of the recorded 400 turned up in my query. There are a few tables that don't contain any information about most sites yet, still it needs to be part of the query, otherwise I can't build them in the report. I haven't put any criteria to the fields. The report is needed to collect further data about each sites. Shall add '0' or 'NoInfo' as a default to each field? Or is there any other solution?

Many thanks
Susanna

I have an idea as to what might be worth considering, although I am not quite sure from the information that you provided whether it is the right answer or not.

I suspect that your Table Joins are INNER JOINs, and that is why information is being ignored. Remember that INNER JOINs only create records when there is an entry in both Tables. You might try to change the Geographical Site Table to a LEFT JOIN. This will create a resulting set of data that has all of the Geographical sites and will substitute NULLS for any items that are not defined in the other Tables. This method can be refined for multiple Tables as more information becomes available.

-- Rookie

 

[zbird]

Hi Rookie,

Thank you for this. I have changed the join type in the queries. Is it important to also change it in the tables relationship? Or can I leave those as they are and just simply apply it when I build a query?

Many thanks,
Susanna