Removing Nulls and displaying data together

[Bradley]

Hi



I have 4 separate tables of which all have fields "Surname", "Forename" and
"DOB", plus a data column.



Tbl_0 = S | F | DOB (this is all 5 users)

Tbl_1 = S | F | DOB | Data1

Tbl_2 = S | F | DOB | Data2

Tbl_3 = S | F | DOB | Data3



Index above = S (Surname)| F (Forename) | DOB | Data



I can build individual queries like;

Tbl_0 & 1 which lists all the Users that have a Data1 enter - Totaling 1

Tbl_0 & 2 which lists all the Users that have a Data2 enter - Totaling 2

Tbl_0 & 3 which lists all the Users that have a Data3 enter - Totaling 2



My problem is that I want to see them all in one table.

For Example;

Users
Data1
Data2
Data3

Joe
text



Bill

text
text

John


text

Bob

text


Peter







Peter should not even show in the list above, because he doesn't have any
data to display.



If this is possible how should I build the Query



Regards



Bradley

 

[Bradley]

Hi

SORRY THE TABLE DIDN'T COME OUT AS EXPECTED

I have 4 separate tables of which all have fields "Surname", "Forename" and
"DOB", plus the data column.

Tbl_0 = S | F | DOB (this is all 5 users)
Tbl_1 = S | F | DOB | Data1
Tbl_2 = S | F | DOB | Data2
Tbl_3 = S | F | DOB | Data3

Index above = S (Surname)| F (Forename) | DOB | Data

I can build individual queries like;
Tbl_0 & 1 which lists all the Users that have a Data1 enter - Totaling 1
Tbl_0 & 2 which lists all the Users that have a Data2 enter - Totaling 2
Tbl_0 & 3 which lists all the Users that have a Data3 enter - Totaling 2

My problem is that I want to see them all in one table.
For Example;
Users Data1 Data2 Data3
Joe text
Bill text text
John text
Bob text
Peter

Peter should not even show in the list above, because he doesn't have any
data to display.

If this is possible how should I build the Query

Regards

Bradley

 

[John Vinson]

Hi

SORRY THE TABLE DIDN'T COME OUT AS EXPECTED

I have 4 separate tables of which all have fields "Surname", "Forename" and
"DOB", plus the data column.

Tbl_0 = S | F | DOB (this is all 5 users)
Tbl_1 = S | F | DOB | Data1
Tbl_2 = S | F | DOB | Data2
Tbl_3 = S | F | DOB | Data3

Index above = S (Surname)| F (Forename) | DOB | Data

So you're storing the SAME data in four different tables? What do you
mean by "this is all 5 users"?

Any time you have two or more tables with identical structure, there's
something seriously wrong.

I can build individual queries like;
Tbl_0 & 1 which lists all the Users that have a Data1 enter - Totaling 1

What do you mean by "totaling 1"??? Do you mean you want to list those
records for which there exists data for that user in Tbl_1; or for
which the value of the field Data1 is not null; or something else?

Tbl_0 & 2 which lists all the Users that have a Data2 enter - Totaling 2
Tbl_0 & 3 which lists all the Users that have a Data3 enter - Totaling 2

My problem is that I want to see them all in one table.
For Example;
Users Data1 Data2 Data3
Joe text
Bill text text
John text
Bob text
Peter

Peter should not even show in the list above, because he doesn't have any
data to display.

If this is possible how should I build the Query

I'm GUESSING here but let's give this a try:

Crate a new Query in the query grid. Add Tbl_0, Tbl_1, Tbl_2 and
Tbl_3.

Join Tbl_0 to each other table, joining S to S, F to F, and DOB to
DOB. Do note that even these three fields do NOT guarantee uniqueness;
you may want to consider having an autonumber "surrogate" primary key
and joining on it instead.

Select all 9 join lines and choose Option 2 - "Show all data in Tbl_0
and matching data in Tbl_n".

Select the user name from Tbl_0 and the data fields from the other
three tables; use a criterion on each data field of

IS NOT NULL

with the three criteria on three separate lines; this will show you
only those users for whom there is data in at least one table.


John W. Vinson[MVP]