Display Text Value not ID

Screen Shot

I have attached a screen shot of Rich's SQL when run (Hid some fields to fit on screen). I changed only the Customer Name from tblOrders back to Company Name from tblCustomers. It made no differenece to how the query behaved, Ijust did it as that is how the other query was set for that field.
Thanks,
Phil.

PS: It's getting late!!
 

Attachments

  • Rich Query.JPG
    Rich Query.JPG
    57.7 KB · Views: 100
Should be


SELECT tblOrders.CustomerName, tblCustomers.AddressLine1, tblCustomers.AddressLine2, tblCustomers.AddressLine3, tblCustomers.City, tblCustomers.County, tblCustomers.PostalCode, tblOrders.ServiceAddress1, IIf(IsNull([FaxNumber]),"- - - - - - - - - - - ",[FaxNumber]) AS Fax, tblOrders.CustomerID, [FirstName] & " " & [LastName] AS Employees, tblOrders.OrderID, IIf(IsNull([PurchaseOrderNumber]),"NOT ADVISED ",[PurchaseOrderNumber]) AS PO, tblOrders.OrderDate, tblOrders.ConfirmedServiceDate, tblOrderDetails.ServiceID, tblOrderDetails.ServicePrice, tblServices.ServiceDescription, tblOrderDetails.VehicleReg, tblOrderDetails.Quantity, tblOrderDetails.LineTotal, tblOrderDetails.Discount, tblOrders.Vat, tblOrders.TotalGross, tblOrders.Total, tblMakes.Make, tblModels.Model
FROM tblMakes RIGHT JOIN (((tblCustomers RIGHT JOIN (tblEmployees RIGHT JOIN tblOrders ON tblEmployees.EmployeeID = tblOrders.EmployeeID) ON tblCustomers.CustomerID = tblOrders.CustomerID) RIGHT JOIN (tblServices RIGHT JOIN tblOrderDetails ON tblServices.ServiceID = tblOrderDetails.ServiceID) ON tblOrders.OrderID = tblOrderDetails.OrderID) LEFT JOIN tblModels ON tblOrderDetails.ModelID = tblModels.ModelID) ON tblMakes.MakeID = tblOrderDetails.MakeID;
 
Wow

It never ceases to amaze me how great you guys & girls are.
Rich that works a treat. I have posted a screen shot of your query. I have to admit that I would have NEVER come up with all those joins as my knowledge is very basic. I add the tables then just link the PK + FK. Nine times out of ten I get a 1:M as my second screen dump proves. I NEVER look at the SQL View as I always let Access do its stuff......

Can you offer any tips, advice regards improving my knowledge? Perhaps a GOOD book. There are so many books out there. Which one to go for? I need one that teaches pratical VBA that can be put to good use in a real world situation. Plus of course undestanding Queries as you obviously do!!

Many, many thanks to both yourself and Pat.
Phil.
 

Attachments

  • Rich Query.JPG
    Rich Query.JPG
    35.6 KB · Views: 96
  • Invoice History Query.JPG
    Invoice History Query.JPG
    24.4 KB · Views: 103

Users who are viewing this thread

Back
Top Bottom