Just to go through in order.
Just had a thought...I notice that this is an append query...the issue that I see is that this will cause a lot of duplicate data since all of the queries will have to be run anytime that more data is added to the DB. I am trying to avoid duplication at all costs. Especially since one the scripting is done and working, I will no longer have anything to do with the DB (other than a quick fix now and again)...it will be fully in the hands of the department that needs it, and they are not DB knowlegable.
You need the append query initially to populate the tables, once they're populated run qryFinalOutput. The initial population is a one-off exercise now you just need to cater for adding new coordinates and photos.
That structure significantly reduces the duplication (though you could legitimately argue that it's not normalised
enough) and removes the need for your complicated code to try and turn Access into Excel.
Currently you need one query to add new co-ordinate data and one query to add new photos. A form or two to pretty those up wouldn't hurt but you could get away without them if you really wanted to
Also, once a relationship is established from one table to another, I cannot update, delete, or append that table in any way.
Yes you can. What you can't do is delete co-ordinates that have related photos associated with them, you'd need to delete or re-assign the photo records first. You can add co-ordinates until the cows come home. You can edit them and every photo automatically reflects the changed information when you run the output query. Photos have to be added with a valid Co-ordinate ID, other than that, you can do what you like with them including delete them.
I need to lump as many of the queries together as possible so that my users only have a button to push or a single query to click on
One form, 6 text boxes (or a couple of drop down lists), two queries to append and One query to display the data based on two subqueries and about 100 lines of VBA would probably get it up and running, maybe not prettily.
The output is one query to click on: qrySELECT_FinalOuput or you could stick a form on it to pretty it up a bit.
Okay...finally got everything to display correctly. But my queries for each direction are in different tables now....how do I write the code so that they are all in the same one?
They don't need to be,
populate the direction field and you've got one table with all your Photo data rather than 4.
Whew!!.....managed to get everything working....all the way to the end. Now I just have to see how the final table displays on my map and then run it through a few times to make sure it doesn't break. Then I need to "mash" all the queries into one simple update Module/Script...whatever.
The output is simple at the moment it just displays everything by running one query.
Now, before you go rushing headlong into designing forms and writing VBA, take stock first:
I know those tables aren't fully normalised. For just outputting the data that you have now that isn't necessarily a problem the idea was to get you out of recreating Excel in VBA and thinking about how your data is organised in a relational data model. For ongoing management it might be worth breaking down that data further.
I can see that the coordinate data isn't fully normalised. There really should be at least separate tables for Location and Stand. Easting and Northing perhaps less so. is there additional information that gives context to Location and Stand Data that would further add weight to putting that data into separate tables?
How many unique values of Location are there? How many of Stand? (and easting and northing for that matter?)
You had 5,000+ rows of Co-ordinate data before you started, how many rows are there now?
Arguably the Direction field in tblPhoto should related to a table
tblDirection that only contains "North" "South" "East" and "West" rather than it being a text field as it is now. If your users can only select one of those 4 values from a list you don't get typos or abbreviations creeping into your data.
Based on your Word Document, I'd perhaps consider something along the lines of:
yes it complicates the initial set up, but the ongoing management is made much simpler with far less margin for (user) error.
Who likes free time anyway
