Create overview query for report

[Michelangelo]

Hi

I'm struggling to make a overview report. Let me first explain what I'm trying to do.

I'm making a project managment database for my company. Each project consists of 3 types of costs which are put in different tables all with an identifier field which is the projectcode. So I have four tables:

General: Projectcode + general info fields
Hours: Projectcode + hour info
Costs: Projectcode + costs info
Third party: Projectcode + third party info

Not all of the fields in all the tables have to be filled for each project. So there can be projects with hours but no costs

Now I want to create a report which produces info per projectcode like:

Projectcode -- Hours info -- Costs info -- Third party info.

To do this I've created a query of the four tables to list them all together. But if one of the tables doe'n't contain a record for that specific projectcode the whole projectcode doesn't appear, including a projectcode which has info of hours and not costs or third party.

Hope my question is clear and look forward to a solution.

 

[Jon K]

In query Design View, link the tables by dragging the ProjectCode field from the General table to the ProjectCode field of each of the other three tables.

Then double-click on each of the three joining lines. And in each of the dialog box that pups up, choose the option that includes ALL records from the General table.

 

[Michelangelo]

Thank you both

I've got it working with the four seperate tables. But I can also see the benifits using only 1 table. However I've got a question about the 1 table approach.

The table will have all the fields that are in the seperate tables, and an identifier field for the type of info in the record. For each record there will only be information on 1 of the three subjects, so there will be a lot of blank spaces. How does access handle this, will the table's size grow rapidly? Or don't blank spaces add to the table size?