Query from two tables

[Galsaba]

I want to make a query from two tables.

One table, TechActivity, has the name of the technician, the service call
number, and the date.

The other table, TechServiceCallLog, has the name of the tech, the service call
number, the customers, name, and the customer address.

The two tables are links by ServiceCallNumber.

I want to create a table that has a list of service call number, techs name,
and customer name. However, sometimes there is an activity that showa on the
TechActivity but not on the othertable. let's say that ServiceCallNumber "1000"
appears in both, but ServiceCallNumber "1001" does not, and ServiceCallNumber
"1002" does appear in both. Normally, I know that if I have a query that will
take th e info from both tables, it will have only two lines, but I want to
show all three lines, including "1001". In this case I want to show that for
"1001" there is not customers name and no address associated.

Thanks,

Joe

 

[PC Datasheet]

The design of your tables is not correct and what you are trying to do is only
making it worse. You can remove the problem you are having by redesigning your
tables:

TblTechnician
TechnicianID
FirstName
LastName

TblCustomer
CustomerID
CustName
Address
City
State
ZipCode

TblServiceCall
ServiceCallID
ServiceCallNum
CustomerID
TechnicianID
ServiceCallDate

You then need to create a one-to-many relationship between CustomerID in
TblCustomer and CustomerID in TblServiceCall and a one-to-many relationship
between TechnicianID in TblTechnician and TechnicianID in TblServiceCall.

In your example, ServiceCall numbers 1000 and 1002 TblServiceCall will have an
entry for CustomerID while ServiceCall# 1001 wil not have an entry for
CustomerID. You can easily create a query to list your service calls by
including all three tables in the query and including:
ServiceCallNum from TblServiceCall
ServiceCallDate from TblServiceCall
Custname From TblCustomer
Technician:[Firstname] & " " & [Lastname] From TblTechnician