Add table to query

Hi
I have a problem in access 2002. When I add a table to a query it changes
the amount records displayed without me adding any fields or changing
criteria.
Can anyone explain why?

Regards

You need to use an outer join.

Details:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"cjgav" wrote in message
...
Hi
I have a problem in access 2002. When I add a table to a query it changes
the amount records displayed without me adding any fields or changing
criteria.
Can anyone explain why?

It is known as cartesian effect. It multiplies the number of records from
one table by that from the other.
If you join the two tables on a common field it will not happen.
--
KARL DEWEY
Build a little - Test a little


"cjgav" wrote:

Hi
I have a problem in access 2002. When I add a table to a query it changes
the amount records displayed without me adding any fields or changing
criteria.
Can anyone explain why?

Regards

Hi
I've not explained this properly my problem is I am using a query as data
source for a report . My problem is that when I add a table to this query
there are no longer any records in the query.


"KARL DEWEY" wrote:

It is known as cartesian effect. It multiplies the number of records from
one table by that from the other.
If you join the two tables on a common field it will not happen.
--
KARL DEWEY
Build a little - Test a little


"cjgav" wrote:

Hi
I have a problem in access 2002. When I add a table to a query it changes
the amount records displayed without me adding any fields or changing
criteria.
Can anyone explain why?

Regards

Did you try using an outer join in your query?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"cjgav" wrote in message
...
Hi
I've not explained this properly my problem is I am using a query as data
source for a report . My problem is that when I add a table to this query
there are no longer any records in the query.

On Sat, 15 Nov 2008 05:12:02 -0800, cjgav
wrote:

Hi
I've not explained this properly my problem is I am using a query as data
source for a report . My problem is that when I add a table to this query
there are no longer any records in the query.

Then fix the error in the query. If you would like help doing so, please open
the query in SQL view and post it here; it may help to describe the nature of
the tables and give an example of the data you expect to see.
--

John W. Vinson [MVP]

HI this is the query
SELECT Repairs.LastName, Repairs.Address, Repairs.City, Repairs.County,
Repairs.PostalCode, Repairs.Appliance, Repairs.[Model No], Repairs.HomePhone,
CCur(Products.UnitPrice*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice,
[repair details].ProductID, Repairs.RepairsID, [repair details].UnitPrice,
[repair details].Quantity, [repair details].Discount, Products.ProductName,
fee.Fee, Repairs.Manufacturer, Products.PartNo, Repairs.[Techicians Report],
Repairs.[Acion Taken], Repairs.Fault, Repairs.Recieved,
Repairs.DateCompleted, Repairs.RepairType
FROM (Products RIGHT JOIN (fee INNER JOIN ([repair details] RIGHT JOIN
Repairs ON [repair details].[Repairs Id] = Repairs.RepairsID) ON
fee.RepairsID = Repairs.RepairsID) ON Products.ProductID = [repair
details].ProductID) INNER JOIN [Repair Type] ON Repairs.RepairType = [Repair
Type].RepairType;
The tables are customer details for repair of an appliance . This query is
for data for invoices .After I followed the earlier suggestion re outside
join I get all fields displayed in query but a null error in the report . I
have just added the Repairs type table to query and I receive a Type mismatch
in expression error.




"John W. Vinson" wrote:

On Sat, 15 Nov 2008 05:12:02 -0800, cjgav
wrote:

Hi
I've not explained this properly my problem is I am using a query as data
source for a report . My problem is that when I add a table to this query
there are no longer any records in the query.

Then fix the error in the query. If you would like help doing so, please open
the query in SQL view and post it here; it may help to describe the nature of
the tables and give an example of the data you expect to see.
--

John W. Vinson [MVP]

On Sun, 16 Nov 2008 05:44:01 -0800, cjgav
wrote:

HI this is the query
SELECT Repairs.LastName, Repairs.Address, Repairs.City, Repairs.County,
Repairs.PostalCode, Repairs.Appliance, Repairs.[Model No], Repairs.HomePhone,
CCur(Products.UnitPrice*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice,
[repair details].ProductID, Repairs.RepairsID, [repair details].UnitPrice,
[repair details].Quantity, [repair details].Discount, Products.ProductName,
fee.Fee, Repairs.Manufacturer, Products.PartNo, Repairs.[Techicians Report],
Repairs.[Acion Taken], Repairs.Fault, Repairs.Recieved,
Repairs.DateCompleted, Repairs.RepairType
FROM (Products RIGHT JOIN (fee INNER JOIN ([repair details] RIGHT JOIN
Repairs ON [repair details].[Repairs Id] = Repairs.RepairsID) ON
fee.RepairsID = Repairs.RepairsID) ON Products.ProductID = [repair
details].ProductID) INNER JOIN [Repair Type] ON Repairs.RepairType = [Repair
Type].RepairType;
The tables are customer details for repair of an appliance . This query is
for data for invoices .After I followed the earlier suggestion re outside
join I get all fields displayed in query but a null error in the report . I
have just added the Repairs type table to query and I receive a Type mismatch
in expression error.



Are there in fact records in [repair details]? What is the datatype of
[RepairsID] in [Repairs] and in [Repair Details]? How about ProductID? If you
do this two tables at a time - joining Repairs to [Repair Details], and
Products to [Repair Details], and [Repairs] to [Repair Type], do you get
similar errors?
--

John W. Vinson [MVP]

My data base is for my appliance repair company with customer repair details.
I've been able to list all records by using the outer join but when I try
and open the report I get a "INVALID USE OF NULL" I know this is because a
field is empty,
it is the extended price field !
How can I ask it to display £0.00 if empty?

Please find copy of query below.

SELECT Repairs.LastName, Repairs.Address, Repairs.City, Repairs.County,
Repairs.PostalCode, Repairs.Appliance, Repairs.[Model No], Repairs.HomePhone,
CCur(Products.UnitPrice*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice,
[repair details].ProductID, Repairs.RepairsID, [repair details].UnitPrice,
[repair details].Quantity, [repair details].Discount, Products.ProductName,
fee.Fee, Repairs.Manufacturer, Products.PartNo, Repairs.[Techicians Report],
Repairs.[Acion Taken], Repairs.Fault, Repairs.Recieved,
Repairs.DateCompleted, Repairs.RepairType, Customers.CompanyName,
Customers.CompanyName
FROM (Products RIGHT JOIN (fee INNER JOIN ([repair details] RIGHT JOIN
Repairs ON [repair details].[Repairs Id]=Repairs.RepairsID) ON
fee.RepairsID=Repairs.RepairsID) ON Products.ProductID=[repair
details].ProductID) LEFT JOIN (Customers RIGHT JOIN [Repair Type] ON
Customers.CompanyName=[Repair Type].[INVOICE TO]) ON
Repairs.RepairType=[Repair Type].TypeID;





"John W. Vinson" wrote:

On Sun, 16 Nov 2008 05:44:01 -0800, cjgav
wrote:

HI this is the query
SELECT Repairs.LastName, Repairs.Address, Repairs.City, Repairs.County,
Repairs.PostalCode, Repairs.Appliance, Repairs.[Model No], Repairs.HomePhone,
CCur(Products.UnitPrice*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice,
[repair details].ProductID, Repairs.RepairsID, [repair details].UnitPrice,
[repair details].Quantity, [repair details].Discount, Products.ProductName,
fee.Fee, Repairs.Manufacturer, Products.PartNo, Repairs.[Techicians Report],
Repairs.[Acion Taken], Repairs.Fault, Repairs.Recieved,
Repairs.DateCompleted, Repairs.RepairType
FROM (Products RIGHT JOIN (fee INNER JOIN ([repair details] RIGHT JOIN
Repairs ON [repair details].[Repairs Id] = Repairs.RepairsID) ON
fee.RepairsID = Repairs.RepairsID) ON Products.ProductID = [repair
details].ProductID) INNER JOIN [Repair Type] ON Repairs.RepairType = [Repair
Type].RepairType;
The tables are customer details for repair of an appliance . This query is
for data for invoices .After I followed the earlier suggestion re outside
join I get all fields displayed in query but a null error in the report . I
have just added the Repairs type table to query and I receive a Type mismatch
in expression error.



Are there in fact records in [repair details]? What is the datatype of
[RepairsID] in [Repairs] and in [Repair Details]? How about ProductID? If you
do this two tables at a time - joining Repairs to [Repair Details], and
Products to [Repair Details], and [Repairs] to [Repair Type], do you get
similar errors?
--

John W. Vinson [MVP]

On Sun, 16 Nov 2008 15:24:03 -0800, cjgav
wrote:

My data base is for my appliance repair company with customer repair details.
I've been able to list all records by using the outer join but when I try
and open the report I get a "INVALID USE OF NULL" I know this is because a
field is empty,
it is the extended price field !
How can I ask it to display £0.00 if empty?


Use the NZ() function to convert the Null (empty) field to 0. E.g.

SELECT Repairs.LastName, Repairs.Address, Repairs.City, Repairs.County,
Repairs.PostalCode, Repairs.Appliance, Repairs.[Model No], Repairs.HomePhone,
CCur(NZ(Products.UnitPrice,0) * NZ([Quantity], 0) * (1-NZ([Discount],
1))/100)*100 AS ExtendedPrice,
[repair details].ProductID, Repairs.RepairsID, [repair details].UnitPrice,
[repair details].Quantity, [repair details].Discount, Products.ProductName,
fee.Fee, Repairs.Manufacturer, Products.PartNo, Repairs.[Techicians Report],
Repairs.[Acion Taken], Repairs.Fault, Repairs.Recieved,
Repairs.DateCompleted, Repairs.RepairType, Customers.CompanyName,
Customers.CompanyName
FROM (Products RIGHT JOIN (fee INNER JOIN ([repair details] RIGHT JOIN
Repairs ON [repair details].[Repairs Id]=Repairs.RepairsID) ON
fee.RepairsID=Repairs.RepairsID) ON Products.ProductID=[repair
details].ProductID) LEFT JOIN (Customers RIGHT JOIN [Repair Type] ON
Customers.CompanyName=[Repair Type].[INVOICE TO]) ON
Repairs.RepairType=[Repair Type].TypeID;

--

John W. Vinson [MVP]