VBA coding help

[admessing]

I'm going to start charging you by the line

LOL...yeah...problem is no funding....otherwise this would have been shipped off to a contractor to do.

There is the query SQL that your recordset is based on, there is no field Photo_Year2 in it.

If photo_year2 isn't a field in that table/Query, how should that value be arrived at?

I know that the Photo_Year2 field (or any of the IMG_*2 fields) do not exist in the Photo_Link table....I am attempting to insert those values into the new table based upon matching coordinate values.

The Photo_Link table contains rows with duplicate coordinate sets, but differing Photo_Year and images. My goal is to "mash" all the matching coordinate data into the same row so that there is only one row with that coordinate, but contains all of the images and the years. Hence the Photo_Year2 and IMG_*2 fields.

I'm not trying to be snotty, but while Access has more than its fair share of obscure error messages it does occasionally manage to pretty accurately describe the problem.

Your code is a little overcomplicated for what it's actually doing, but we'll worry about that later, get it working first and then figure out how to make it work efficiently.

I know, and you are being very patient with me. VBA is not exactly my style of programming....I specialize in HTML/XML and intermediate SQL. As a GIS professional I have little need to know VB coding since anything that we normally use it for is either generated by our software or is customized by a professional programmer out of shop. But, I don't get that luxury on this project.

 

[admessing]

Attached is a snapshot of what I have to combine...notice the Photo_Year difference....for the same coordinates....

 

[tehNellie]

The Photo_Link table contains rows with duplicate coordinate sets, but differing Photo_Year and images. My goal is to "mash" all the matching coordinate data into the same row so that there is only one row with that coordinate, but contains all of the images and the years. Hence the Photo_Year2 and IMG_*2 fields.

Right then, so what are we doing with the Photo_Year Value (and all the other _2 values that aren't actually in the recordset)?

i.e

        If intNewPhoto_Year > intPrevPhoto_Year Then

            intNewPhoto_Year = rs![Photo_Year2]

        End If

Here what you are saying is that if the intnewPhoto_Year value is greater than the previous year value overwrite it with rs![Photo_year2] which, as we've established, doesn't exist. So where or how do we get the value of Photo_year2?

 

[admessing]

Right then, so what are we doing with the Photo_Year Value (and all the other _2 values that aren't actually in the recordset)?

i.e

        If intNewPhoto_Year > intPrevPhoto_Year Then
            intNewPhoto_Year = rs![Photo_Year2]
        End If

Here what you are saying is that if the intnewPhoto_Year value is greater than the previous year value overwrite it with rs![Photo_year2] which, as we've established, doesn't exist. So where or how do we get the value of Photo_year2?

The values come from the second matching row with the same coordinates.

i.e.
Row #2> Photo_Year = Photo_Year2, IMG_North = IMG_North2, etc.

The first four fields (Easting, Northing, Location, Stand) are what is common to both rows (green), but the Easting and Northing are the key to getting the records right. The next five fields (Year, North, East, South, West) are from the first instance of the coordinate set (orange). The final 5 fields (Year2, North2, East2, South2, West2) are derived from the second instance of the coordinate set (blue). These final 5 fields need to be created from the original data, and be on the proper record in the new fields. I have shortened the actual field names to save on typing right now.

Example of final row:

[Easting][Northing][Location][Stand] [Year][North][East][South][West] [Year2][North2][East2][South2][West2]

I want the years to be listed oldest first, then the most recent. It causes less confusion for my users on the mapping side of things.

 

[tehNellie]

Now I think I'm starting to get it you could do it in with a couple of queries and a couple of tables:

(granted the sort order isn't quite right)

based on two tables and three queries (plus another two to populate the Tables to begin with):

qrySELECTYearandDirection makes a combined field of the year and direction + the file path:

SELECT tblPhotos.CoordID, [PhotoYear] & " " & [PhotoDirection] AS
 [Year&Direction], tblPhotos.PhotoPath
FROM tblPhotos;

The use a cross tab query to Pivot those results:

TRANSFORM First(qrySELECT_yearandDirection.PhotoPath) AS FirstOfPhotoPath
SELECT qrySELECT_yearandDirection.CoordID
FROM qrySELECT_yearandDirection
GROUP BY qrySELECT_yearandDirection.CoordID
PIVOT qrySELECT_yearandDirection.[Year&Direction];

Finally qrySELECTFinalOutput adds the co-ordinate details:

SELECT tblCoords.coordID, tblCoords.Easting, tblCoords.Northing, tblCoords.Location, 
tblCoords.Stand, Query1_Crosstab.*
FROM tblCoords 
INNER JOIN Query1_Crosstab ON 
  tblCoords.coordID = Query1_Crosstab.CoordID;

Couple of append queries to populate the data and no messing around with VBA required.

Set a unique INDEX on the tblCoords easting, northing, location and stand fields then just APPEND from as many source tables as you like, duplicate values will raise errors but won't be imported.

Inserting records into the Photos Table would look something like:

You could be more precise about setting things up, but as an example of doing it without needing a line of code and having output that will dynamically grow as more records are added I'd consider perhaps giving that a go.

 

[admessing]

Okay...I think I can see where you are trying to going with this. But, how exactly am I supposed to do a coordID as a primary (or foreign) key, when there isn't one to begin with....and the coordinates are entered every time with the new photos? The real DB as it stands right this minute, already has over 5000 records in it. How am I going to get a coordID into that beast?

Also, the coordinate fields need to be displayed exactly "as is" (in their own separate fields) in the final table. How is this accomplished with this option?

 

[tehNellie]

the CoordID is an Access Autonumber, that's generated as your existing coord records are imported into the table. A compound UNIQUE INDEX across the 4 co-ordinate fields ensures that duplicate co-ord data isn't imported so each combination only appears once in the table and hence has a unique Co-ord ID.

in the final output query they are displayed as listed (or as stored in any case), it's a formatting issue that's stripping off the leading 0 of the field, it was late, I wanted to go to bed so I didn't spend much time sorting out the finer details, I just wanted to illustrate that a normalised structure and a couple of queries would seem to give you want you want with much less effort and more dynamiscm than writing a whole bunch of code to try and force your data into an inconvenient (and inefficient format).

Think about it from an XML perspective for a minute (and no more). How would you structure your data in XML to cater for your requirements? A relational data model isn't analogous to an XML schema but your tables are your data structure, the queries are your data presentation (HTML in this analogy if you like).

I based the whole thing on your Word Document sample, this thread and knocked that up in about 20 minutes so it wasn't intended to be an opimal implementation, simply to show you that there might be an easier (and arguably ) better way to structure your data to give you the output.

 

[admessing]

Okay....I have a SELECT DISTINCT * query that can populate a new table with just the unique coordinate sets (plus location and stand). How do I get it to add the PK within the SQL?

As for the tblPhotos....I'm not entirely sure how to get to that structure from what I have.

I apologize if this is getting frustrating for you....maybe I need more coffee.

 

[tehNellie]

Create a table tblCoords (or whatever you want to call it) and add your 4 fields in the relevant data formats. Create an index covering those 4 fields and set Duplicates to NO.

To that, before you add any records, add coordID, with a type of Autonumber and set it to be the Primary Key.

Add your data, the number is created as each new row is inserted.

For table photos you'd need to run the append query for each photo column (north, south, east,west) as per the last query photo. The 4 joins between the two tables allows you to insert the relevant coordID. Currently the direction (sorry Flickr, where I stuck the screenshots, is blocked from this office so I'm going from memory) is a text field that is manually updated depending on the column being inserted. i.e. when you run the append for the north Photo, put "north" in the Updateto: field for the direction column, "South" when you do the south column and so on.

(you could make a case that these should go in a separate table and you have a directionID column instead, but I digress)

 

[tehNellie]

I apologize if this is getting frustrating for you....maybe I need more coffee.

It's not frustrating because I think my quick and dirty example made you stop and think about your data, what you're trying to do with it and what your code is currently trying to achieve versus what Access can do with a couple of queries.

I can't do it until I get home, but I'm quite happy to send you the database I knocked up last night that has the tables and queries in it and you can play with it. The co-ord Data I entered manually, but I'll include a sample INSERT query for that too.

 

[admessing]

Okay....I am making a bit of headway...but the issue I am having is with the Photos table. The PhotoPath as you have it is what my IMG_North, IMG_East...etc are. I do not have a PhotoDirection separate from that, and to have that in my table, I think, is very redundant. Maybe if I can look at your mock DB it will help. How long till you are at home?

One more thing...the coordinates cannot be set to unique values all by themselves, only as a pair. There is a strong likelyhood that there may be multiple points that have the same easting (or northing) coordinate, but the other coordinate is where the difference is. Also, the FSVeg_Location and Stand_No are not unique to any coordinate set. These markers can apply to hundreds of records.

 

[tehNellie]

Couple of hours. (16:45 now, I tend to get home at 19:00-19:30)

The direction field I added simply because your photo(path) columns were labelled North South, East and West for each entry so it was simply to differentiate a single PhotoPath Field into the respective "directions" in the output.

i.e. in your final output you got

2008 North,  2008 East,  2008 south, 2008 west
----------------------------------------------
Path,        path,       path,       path

Based on the direction column in the (new) photo table.

 

[admessing]

I seem to be having a major issue with the first query that you gave me. I can't get the query to combine the year and direction together. I'm not entirely sure what I am doing wrong.

Code Snippet for Directions:

SELECT
CoordID,
North AS PhotoYear&" "&North,
East AS PhotoYear&" "&East,
...
FROM Photos;

 

[tehNellie]

as per the original example:

SELECT tblPhotos.CoordID, [b][PhotoYear] & " " & [PhotoDirection] AS
 [Year&Direction][/b], tblPhotos.PhotoPath
FROM tblPhotos;

you don't need to specify the direction, that's coming from the photoDirection Column.

the field name comes after the actual field(s) itself

so you get:

coordID  year&direction  photoPath
-----------------------------------
1         2008 North     c:\temp\etc
1         2008 south     c:\temp\etc

as your ouput

 

[admessing]

I don't have a column with "Direction", all I have is the image paths, coordID, and photoID.

What is it that I am not seeing here? How do I get the "Direction"?

 

[tehNellie]

I replaced four columns for the photos (north south etc) with 1 column for the path and a Direction column to indicate which one it is.

Hopefully trying to attach the sample file for you to have a look at

 

[admessing]

Okay...looked over the DB you sent. Still having issues with getting the tblPhotos to populate with anything other than the North photos. How do I need to enter the parameters for East, South and West? So far all i get is errors.

And I noticed that your source table is completely empty...why?

 

[tehNellie]

The source data table is empty because, well I don't have the source table, it's an example.

as for inserting the photos, the design view:

Illustrates it.

The photodirection column is currently set to text so as the North_photo column from the source is imported to the PhotoPath, "North" is entered as text in the PhotoDirection Column. So when you import the south Photos column, put "South" where north is and so on.

 

[admessing]

Okay...can get it to work for the most part. But it won't bring in the year....I just get a NULL field.

INSERT INTO Photos ( CoordID, PhotoYear, PhotoDirection, Path )
SELECT 
Coords.CoordID, 
Source.PhotoYear, 
"North" AS Expr1, 
Source.North
FROM Source INNER JOIN Coords ON (Source.Stand = Coords.FSVeg_Stand_No) AND (Source.Watershed = Coords.FSVeg_Location) AND (Source.Northing_UTM = Coords.Northing_UTM) AND (Source.Easting_UTM = Coords.Easting_UTM) ORDER BY CoordID;

 

[admessing]

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.

Also, once a relationship is established from one table to another, I cannot update, delete, or append that table in any way.

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