Query with field containing multiple values

[bc_newbie]

Hi all,

I am very new to Access but have a basic understanding. Can someone help me to modify the code below to Show the LocationName in the Schema Column instead of the MPID? I attached a pic showing the relationship between the two tables which contain the data I'm trying to query. Thank you in advance for your help.

bc_newbie


SELECT [Locations Query].LocID, Qry_MPLoc.MPID AS Qry_MPLoc_MPID, [Locations Query].Location, [Locations Query].Schema
FROM [Locations Query] INNER JOIN Qry_MPLoc ON [Locations Query].[LocID] = Qry_MPLoc.[LocID];

 

[GinaWhipp]

How do the values get in the Schema column now? And where do the plus signs come from?

And, your posted query appears different then the one shown in your image. A little confusing.

 

[bc_newbie]

Hi Gina,

The values for the Schema column are stored in the MonitorPoints table. Those values are somehow pulled in from a third party software into the MonitorPoints table in the access database.

I apologize, I think I posted the wrong code earlier...I've been trying a few different lines of code trying to get the desired results. Below is the code that produces the query results shown in my attachment. Any help you can provide would be greatly appreciated.

bc_newbie
-----------------------------------------

SELECT Locations.LocID, Locations.Location, MonitorPoints.MPID, MonitorPoints.Schema
FROM Locations INNER JOIN MonitorPoints ON Locations.[LocID] = MonitorPoints.[LocID];

 

[GinaWhipp]

Hmm, well I can't see a way to pull those values without some sort of way to identify which ones to associate to what. And from what you've posted it sounds like that *link* might be from an outside software.

Maybe if you could provide the code used that pulls it from the third party software. But that will only help if the values you want are also in that software.

 

[spikepl]

Write a function that accepts a variant - the content of column Schema, splits the string into the ID's, for each ID finds the corresponding Location using Dlookup, puts it all together into a string and delivers a variant (string) containing your desired result to be stuffed into the column PreferredSchemaResults.

 

[bc_newbie]

Hi spikepl,

Can you PLEASE help me write that? I'm not familiar with how to do it.

bc_newbie

 

[bc_newbie]

Anyone know how to do what spikepl suggested?

 

[JHB]

...
I am very new to Access but have a basic understanding. ...

Okay then I suppose you also understand to make functions and procedure/sub or is that not correct?
spikepl suggested you to write your own UDF (User Defined Functions) here is some link for as a starting point.
http://www.sqlexamples.info/SQL/inlineudf.htm

http://datapigtechnologies.com/blog/index.php/creating-and-using-user-defined-functions-in-access/
Write back if you've any question, we all want to help you, but not doing the work for you!

 

[bc_newbie]

JHB,

This is still new to me but I am trying to learn how to do this with the help you all are providing. I tried creating a UDF to split the contents of the Schema field as spikepl suggested. I'm having trouble with the DLookup function to retrieve the corresponding location name.

Can you please provide some guidance with the DLookup Function? I keep getting errors with what I have below:

Expr1: DLookUp("Location","Locations","LocQry.UpstreamID=" & [Locations].[LocID])


Here's what I have for the query:
SELECT Locations.LocID, Locations.Location, MonitorPoints.MpRainGauge, splitSchema((Replace([MonitorPoints].[Schema],";","+")),1) AS UpstreamID
FROM Locations INNER JOIN MonitorPoints ON Locations.[LocID] = MonitorPoints.[LocID]
WHERE (((MonitorPoints.Schema) Is Not Null));
-------------------------------------------------------------------------------------

Here's what I have for the function to Split the contents of the Schema field:
Public Function SplitSchema(TextIn As String, X) As Variant
On Error Resume Next
Dim var As Variant
var = Split(TextIn, "+", -1)
SplitSchema = var(X)
End Function
-------------------------------------------------------------------------------------
Any help you can provide would be greatly appreciated.

 

[JHB]

You've now change the fields so I don't know what value you expected to get returned, but I've made a small sample of the original data, open/run the query "ConvertLocation"

 

[bc_newbie]

JHB,

Thank you! This does exactly what I was trying to get it to do. Thank you so much for your help.

Do you have any suggestions for websites I can visit that have good Access Tutorials I could view?

bc_newbie

 

[JHB]

You're welcome, luck with your project.

Do you have any suggestions for websites I can visit that have good Access Tutorials I could view?

bc_newbie

Depending what you want to learn - if you Google "Access Tutorials" you got billions of hit, both text and video.