user2270796 user2270796 - 17 days ago 4
SQL Question

Access return a different value

I am working with a database from SharePoint built by someone else.
I am generating a simple pull into a report through Microsoft Access.
I have everything working properly but I have a value that in SharePoint is a list that is for a particular buyer attached to a purchase order. In SharePoint it returns a name value, however in Access it is returning a # Value.
I was wondering if a there was a simple fix that could be made for when the data returned from the SharePoint table returned is = to (specified #) it would return (specified name).

Right now it will return say 137. I would want it to return a buyers name.
It is a shame conditional formatting doesn't expand into something more than formatting and could actually change If [Field] returns [#value] than return [specified return value] instead.

This is my query from the tables. I am just pulling directly.

SELECT
[115_12 RFQ's].RFQ, [115 Procurement].PO,
[115 Procurement].[Issued Date],
[115 Procurement].[C/O],
[115 Procurement].Status,
[115 Procurement].Vendor,
[115_12 RFQ's].[RFQ Title],
[115 Procurement].[PO Type],
[115 Procurement].VDR,
[115 Procurement].[Approval Drawings Required].Value,
[115 Procurement].[Inspection Required],
[115 Procurement].[Actual Inspection Date],
[115 Procurement].[Shipped Date (Vendor)],
[115 Procurement].[Actual Delivery Date],
[115 Procurement].[Delivering To?],
[115 Procurement].[Delivery Required Date],
[115 Procurement].[Promised Delivery Date],
[115 Procurement].[Last Vendor Contact Date],
[115 Procurement].Comments,
[115_12 RFQ's].Commodity,
[115_12 RFQ's].Buyers
FROM [115_12 RFQ's]
LEFT JOIN [115 Procurement]
ON [115_12 RFQ's].ID = [115 Procurement].RFQ;


[115_12 RFQ's].Buyers is returning numbers those numbers I want to return a specific value.

Answer

There is likely a Buyers table in the database that stores the ID (which is what is returned by the query now) and the name of the person associated with the ID. For example:

[Buyers table]
BuyerID
FullName

137   Tom Jones
138   Fred Smith

What you will need to do is find out the name of the table or view that exposes the names of the buyers and modify your query to perform a join to that table/view. From there, you include the name of the buyer from the table instead of or in addition to the Buyer ID.

Something along the lines of this:

SELECT
   [115_12 RFQ's].RFQ, [115 Procurement].PO,
   [115 Procurement].[Issued Date],
   [115 Procurement].[C/O],
   [115 Procurement].Status,
   [115 Procurement].Vendor,
   [115_12 RFQ's].[RFQ Title],
   [115 Procurement].[PO Type],
   [115 Procurement].VDR,
   [115 Procurement].[Approval Drawings Required].Value,
   [115 Procurement].[Inspection Required],
   [115 Procurement].[Actual Inspection Date],
   [115 Procurement].[Shipped Date (Vendor)],
   [115 Procurement].[Actual Delivery Date],
   [115 Procurement].[Delivering To?],
   [115 Procurement].[Delivery Required Date],
   [115 Procurement].[Promised Delivery Date],
   [115 Procurement].[Last Vendor Contact Date],
   [115 Procurement].Comments,
   [115_12 RFQ's].Commodity,
   [115_12 RFQ's].Buyers,
   [Buyers].FullName
FROM [115_12 RFQ's]
LEFT JOIN [115 Procurement]
   ON [115_12 RFQ's].ID = [115 Procurement].RFQ
LEFT JOIN [Buyer] 
ON [115_12 RFQ's].Buyers = [Buyer].BuyerID;