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 |
#11
|
|||
|
|||
Add table to query
HI
Thank you for your help. When i try to open query as you posted I receive error Syntax error in join i cannot find an error in query can you help "John W. Vinson" wrote: 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] |
#12
|
|||
|
|||
Add table to query
On Mon, 17 Nov 2008 13:50:06 -0800, cjgav
wrote: HI Thank you for your help. When i try to open query as you posted I receive error Syntax error in join i cannot find an error in query can you help Odd. I didn't change the JOIN. Does the original 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, 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; work (at least does it only give the Invalid Use of Null error?) -- John W. Vinson [MVP] |
#13
|
|||
|
|||
Add table to query
Sorry I do not understand I posted the original query earlier
"John W. Vinson" wrote: On Mon, 17 Nov 2008 13:50:06 -0800, cjgav wrote: HI Thank you for your help. When i try to open query as you posted I receive error Syntax error in join i cannot find an error in query can you help Odd. I didn't change the JOIN. Does the original 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, 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; work (at least does it only give the Invalid Use of Null error?) -- John W. Vinson [MVP] |
#14
|
|||
|
|||
Add table to query
On Tue, 18 Nov 2008 01:51:01 -0800, cjgav
wrote: Sorry I do not understand I posted the original query earlier Does the original query give the same error? That is - did the change I suggested cause an (apparently unrelated) error message to appear? -- John W. Vinson [MVP] |
#15
|
|||
|
|||
Add table to query
No the original query did not give this error.
"John W. Vinson" wrote: On Tue, 18 Nov 2008 01:51:01 -0800, cjgav wrote: Sorry I do not understand I posted the original query earlier Does the original query give the same error? That is - did the change I suggested cause an (apparently unrelated) error message to appear? -- John W. Vinson [MVP] |
#16
|
|||
|
|||
Add table to query
Hi
not sure I posted last reply correctly as a did not get a reply The original query did not have this error. Thanks "cjgav" wrote: No the original query did not give this error. "John W. Vinson" wrote: On Tue, 18 Nov 2008 01:51:01 -0800, cjgav wrote: Sorry I do not understand I posted the original query earlier Does the original query give the same error? That is - did the change I suggested cause an (apparently unrelated) error message to appear? -- John W. Vinson [MVP] |
#17
|
|||
|
|||
Add table to query
Hi
In the new query theres a record for all entries inc.records with no entries in repair details these display #Error in the field extended price does this help. "John W. Vinson" wrote: On Tue, 18 Nov 2008 01:51:01 -0800, cjgav wrote: Sorry I do not understand I posted the original query earlier Does the original query give the same error? That is - did the change I suggested cause an (apparently unrelated) error message to appear? -- John W. Vinson [MVP] |
#18
|
|||
|
|||
Add table to query
Your original query returned all the records that met the criteria you set
and where the joined fields at each join have the same value in both tables. When you added another table, you imposed another constraint where the joined field in both the original query and in the new table has to have the same value. The amount of records changed because there are less records in the new table that match records in the original query where the joined field have the same value. Here's a simple example: QryCustomer is based on TblCustomer with CustomerID as the PK and where CustomerTypeID = 1 is residential and CustomerTypeID = 2 is commercial. For QryCustomer, CustomerTypeID criteria is set as 2 so QryCustomer returns only commercial customers. QryCustomer does not return all the records in TblCustomer. Now say you have TblSale that looks like: TblSale SaleID CustomerID etc and you join TblSale to TblCustomer in QryCustomer on CustomerID. Adding TblSale will constrain QryCustomer to only return commercial customers whom you have made a sale to. Steve "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? Regards |
#19
|
|||
|
|||
Add table to query
My problem is my invoices sometimes only have a labour fee and no parts
(products) are used this expression in the query creates a null error in my report, ExtendedPrice: CCur([Products].[UnitPrice] *[Quantity]*(1-[Discount])/100)*100 Any idea how I could prevent this "Steve" wrote: Your original query returned all the records that met the criteria you set and where the joined fields at each join have the same value in both tables. When you added another table, you imposed another constraint where the joined field in both the original query and in the new table has to have the same value. The amount of records changed because there are less records in the new table that match records in the original query where the joined field have the same value. Here's a simple example: QryCustomer is based on TblCustomer with CustomerID as the PK and where CustomerTypeID = 1 is residential and CustomerTypeID = 2 is commercial. For QryCustomer, CustomerTypeID criteria is set as 2 so QryCustomer returns only commercial customers. QryCustomer does not return all the records in TblCustomer. Now say you have TblSale that looks like: TblSale SaleID CustomerID etc and you join TblSale to TblCustomer in QryCustomer on CustomerID. Adding TblSale will constrain QryCustomer to only return commercial customers whom you have made a sale to. Steve "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? Regards |
#20
|
|||
|
|||
Add table to query
Just an idea .............
Make labour fee a "part" in your part table. If you have more than one labour fee depending on the the type, duration or whatever of the labour, make several labour records in your part table. Then treat labour as one or more line items on your invoice the same as you treat one or more parts. TblPart PartID Partname PartPrice 1 PartA $3.50 2 PartB $.75 3 PartC $8.90 4 Labour Type A $25 (per hour) 5 Labour Type B $55 (per hour) Steve "cjgav" wrote in message ... My problem is my invoices sometimes only have a labour fee and no parts (products) are used this expression in the query creates a null error in my report, ExtendedPrice: CCur([Products].[UnitPrice] *[Quantity]*(1-[Discount])/100)*100 Any idea how I could prevent this "Steve" wrote: Your original query returned all the records that met the criteria you set and where the joined fields at each join have the same value in both tables. When you added another table, you imposed another constraint where the joined field in both the original query and in the new table has to have the same value. The amount of records changed because there are less records in the new table that match records in the original query where the joined field have the same value. Here's a simple example: QryCustomer is based on TblCustomer with CustomerID as the PK and where CustomerTypeID = 1 is residential and CustomerTypeID = 2 is commercial. For QryCustomer, CustomerTypeID criteria is set as 2 so QryCustomer returns only commercial customers. QryCustomer does not return all the records in TblCustomer. Now say you have TblSale that looks like: TblSale SaleID CustomerID etc and you join TblSale to TblCustomer in QryCustomer on CustomerID. Adding TblSale will constrain QryCustomer to only return commercial customers whom you have made a sale to. Steve "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? Regards |
Thread Tools | |
Display Modes | |
|
|