If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Add table to query
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? |
#3
|
|||
|
|||
Add table to query
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 |
#4
|
|||
|
|||
Add table to query
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 |
#5
|
|||
|
|||
Add table to query
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. |
#6
|
|||
|
|||
Add table to 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] |
#7
|
|||
|
|||
Add table to query
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] |
#8
|
|||
|
|||
Add table to query
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] |
#9
|
|||
|
|||
Add table to query
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] |
#10
|
|||
|
|||
Add table to query
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] |
Thread Tools | |
Display Modes | |
|
|