Hello again, Doug.
Sorry for not posting real data. I don't know how much space on the site here I should devote to my data. Maybe I should have posted a file. Also, this is a very long story, so I don't always feel it is beneficial to post all the gory details - but have decided to type some out here.Click to view attachment
Attached is the file I am working with. It is actually data out of a geotechnical program that is based on Access. There are datapoints missing that should be added to tables within the other program. I have to pitch this to those working with it as they don't "get" things like normalization, etc. The program documents soil (and pavement) strata from the field. As a note - soil is a very non-exact science and I believe those working with it tend to act in that manner. I have been struggling with that for years.
The office has been looking at this data, using excel to determine aggregate totals for the thicknesses of "layers" (again - non-exact - a layer is not always well defined),and outputting to a Word document in memo format (aggregate totals actually end up in a table within Word). There are "layers" defined from the field, then there are different "layers" defined in the office based on what the field says (even what and who the field is gets convoluted as many different people touch the data before it gets to this point).
My plan of the "pitch" is to show benefit using Access (create dog-and-pony to show benefit for change), and then *possibly* change the way they create/manage their data.
So, the pertinent data from this program is as follows
table POINT, GintRecID, PointID, Location
table LITHOLOGY, GintRecID, PointID, Depth, Description
Again, the above tables are output from another program.
Within the POINT.location lies the Route, LatPos, and Bound data (basically, they look at it and type/sort in Excel).
within the LITHOLOGY.Description lies the LayerTyp
within the LITHOLOGY.Depth lies the Thickness
My plan was to create temporary tables to append POINT and LITHOLOGY using values from lookups (one for each of the Route, LatPos, Bound, LayerTyp) and forms to allow them to read from the tables and select appropriate values. The thickness is (I *believe*) is code that I must tackle by once they select the LayerTyp, finding adjacent layers of the same LayerTyp, determining the thickness, and perform the necessary aggregate totals and output them.
Now, *maybe* I have developed my problem statement appropriately... ? I'm sorry if I have made this confusing.
In addition to the above, I was planning on having the system propose values for Route, LatPos, Bound, and LayerTyp to the user for verification. This would require searching for specific values within POINT.location and LITHOLOGY.Description, showing the proposed values for verification/updating within my proposed tables tblLithAddndm and tblPointAddndm.
Getting back to some of your questions...
As I sit here, I am not sure what values of Route, LatPos, Bound, and LayerTyp will exist for the dataset - so my plan was to display aggregate totals of every combination of them (including nulls), and let them cut/paste or type into MS Word.
I read through your article and found the statement
"a crosstab query will generate Null values if there is no value for a particular intersection of the left-side field and the top field, like regular Select queries".
I believe that you took issue with my wanting to generate nulls - but given that my users are reading and transcribing (under my current plan) I don't see an issue. A null simply means that the combination doesn't exist.
With respect to
I don't see what purpose calculating minimum and maximum serves."
I think you can see now that I'm only calculating aggregate totals on the thickness.
So, in summarizing, I think I'm putting the cart before the horse a little. I should figure out how to get my "layer" thicknesses first (I'm thinking code to find adjacent "layers" that are the same), then try to get my aggregate totals. If you have any thoughts at all about what I'm doing - please share them.
I'm sorry for not realizing you were not in the US. I easily forget that I may be talking with those outside the US. I'm glad to hear that you are retired! I hope you enjoy every minute - again - thanks for your help. I enjoy figuring these things out too.