Huge Questions/Favor

[LouisZook]

Good Evening All. I am currently working on a project and trying to figure out something. I have asked my contacts with in my agency, however they are unsure either.

So let me explain my layer and it's fields and then what I'm looking at

So my main layer is a point feature layer. Marking locations that have been visited.

Fields: Location Name, Point of Contact, Address, Total Visits, How Friendly

and then I have a table that is called Visits.

Fields: Location, Date of Visit, Friendliness

I want to calculate the fields "Total Visits" and "How Friendly" based on the table.

For "Total Visits," i want it to count how many time the location shows up in the table "Visits."

For "How Friendly," I want to sum up the total amount for that location on the table "Visits" and then divide it by "Total Visits"

The idea is the employee would add a new visit via a form. and then it would update the table.. then when the map is displayed, the map would show the most recent information using the query/calculate from the table in the feature layer.

Please help.

Thanks

Zook

ArcGIS‌ add query layer‌ #calculate locations‌

[deleted-user-qpvAI3Fo0MKR]

This sounds like a great project for SQL - you're doing a 1:M join and then some calculations. I'm not the right guy to provide you the syntax, but I'd look to my Oracle DBA to help solve this one...

[LouisZook]

You be exactly correct. I am can right some SQL when it comes to access. But am not familiar with how it works with ESRI. and how to set up the Oracle DBA...

[deleted-user-qpvAI3Fo0MKR]

Oracle is an enterprise database (and Database Management System), and a DBA is the DataBase Administrator. Do you have your data in an enterprise database, or is it in a file geodatabase (.gdb)?

[LouisZook]

Currently I can see our current Enterprise Database systems. However i am not a DBA. And do not have access to use the current enterprise databases. would need to get access or have the higher ups create one specifically for me. and currently the data is housed in a geodatabase file (.gdb).

[deleted-user-qpvAI3Fo0MKR]

I don't believe you can do what you're trying to accomplish with data in a .gdb, but of course I may be wrong. When I need to do something like this I will migrate the data to the enterprise database and have the administrator help me with the SQL and then schedule the join / update to run on some regular schedule.

[JohnBrockwell]

Are your fields numerical or text? If text, calculating will be hindered until this is resolved.

[LouisZook]

That is a very minute mistake if I made it. just kidding, haha. They are numerical already, because I want to use it in a dashboard to display averages and more.. So go that part down..

[TedKowal]

You can do this with a SQL query as already indicated.  If your DBA cannot help you then you can export your visits table to MS ACCESS yourself and perform queries like below...  You can easily combine the two into one query (little more advanced) but for the sake of clarity I have split the MS Access SQL into smaller chunks.

#Friendliness Calculation
SELECT Location, Avg(Friendliness) AS HowFriendly
FROM Visits
GROUP BY Visits.Location;

#Total Visits
SELECT Location, Count(Location) AS TotalVisits
FROM Visits
GROUP BY Location;


#As One SQL Query
SELECT Location, Count(Location) AS TotalVisits, Avg(Friend) AS HowFriendly
FROM Visits
GROUP BY Location;
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍