Merge two tables and display in list box

[jayke]

Hi,

I am currently working on an adressbook which contains persons and companies. I have two tables tblCompanies and tblPersons. Both of them contain some simular data like name, phone, E-mail, ...

I would like to merge these two tables in one List Box. So the List Box would contain both the Persons and Companies.

1) How can I do this?
2) Is it also possible to put Icons in a List Box?

[tblCompany]
name
adres
phone
E-mail
Website


[tblPersons]
name
adres
phone
E-mail
Company


List box should look like this

                        name               phone                 E-mail
--------------------------------------------------------------------------
[Person Icon]       Jay                 25455                [email]info@dkdkd.com[/email]
[Company Icon]       HP                  8954                 [email]info@hp.com[/email]

Thanks

Jayke

 

[jayke]

HI,

I tried the union and it worked. I learned something new here today!!!

but what I can't do is an ORDER BY on the two different selects.

What I also want is that I can double click on one of the items in the list en then open the related form.

Ex. When double clicking on a person in the list I want to open frmPersons
Ex. When double clicking on an organisation in the list I want to open frmOrganisation.

But this I can't do with the current solution. Using different Icons in the list box is also not possible now.

Is there an other way to do this?

regards

 

[jayke]

I understand, but I did do normalisation.

In my database a company can have one or more persons (employees). So I have a one-on-many relationship between tblCompanies and tblPersons. So I think I did this like I should do it. I think not splitting them up would be a bad normalisation.

What about using a table create query to create a new table with persons and an append query to append the company data to it?

Regards,

 

[Bat17]

one way to do it is to go with the Union query and add an extra field in it to tell you which form to use.

SELECT name,adres,phone,[E-Mail], "frmPersons" as Loc
FROM tblPersons;

UNION SELECT name,adres,phone,[E-Mail], "frmCompany" as Loc
FROM tblCompany
order by Loc;

Using a list box like this for addresses can get very unwieldly very quickly though.

In you design do "Persons" have to belong to a company or are the two tables independent?

Peter

 

[jayke]

Sorry for the delay!

Persons do not have to belong to a company but it can be. I have a one-to-many relationship between these tables.

Regards

 

[Bat17]

I have a one-to-many relationship between these tables

but you are not enforcing it if you have orphaned records on the many side.

Did the Union queries sort your problem?

Peter

 

[jayke]

Yes, I am using it now. And it works ok!

Thanks you all!