Re: Data data from a Data Table

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I hadn't come back to check for posts on this after your pseudocode was
posted. Looking at it, I can definetly see how I could apply it.

Over the weekend of the 11th and then on the 14th, I decided that I pretty
much needed to at least have a table that had a complete list of "path #'s".
Yes I could query the entire table on just path #'s and sort for unique
values, etc; however, I needed to be able to do more than I could with just a
query. Especially since I am looking to renumber some of the path #'s to
reduce "user confusion". So by having a table of each path # that is linked
and has enforced referential integrity with cascade update related fields I
can change the path # in the table and have all related data updated.

Also, by storing the sequence for that path in the same table, I can present
similar paths when building a new one. Ultimately by building an existing
path the list of similar paths decreases until either the two paths match or
there is a new path developed (1 or 0 is the result).

In addition, I have kept my table(s) normalized (to what level I forget),
but I have no duplicated information in the table of the assembled path(s),
and I have a crosslink table that is made up of only primary keys to get from
one set of information/data to the anything else (related) in the database.

Currently as all tables only relate to this one "topic", all tables are
setup to where I could get to data of any other table if needed and if it is
used/exists. I believe that I have full normalization. The only data field
I'm not happy with, though I think approrpiately used is a Yes/No field.
More records are No than Yes, but it is used to indicate that of all of the
potential points in the path, those marked as Yes are starting points. Those
marked No, are just points available in the path.

By query, I only have two points in all of the tables that are not used.
One is because there is no expectation to use the point, but it is part of
the map, the other is that I have a point designated as NONE or No More data
points available to indicate the end of a journey.

I may end up using that pseudocode to present a report or something as I get
to more of my data presentation. Any corrections I find that are necessary,
I will be sure to post so that others may have access to your thoughts as
code. :)

At the moment, I am exporting my necessary data to Excel, by looping through
the desired table(s) and presenting my "results" in various columns. I would
like to be able to create a "report" or other Microsoft copy/paste version,
but for now an excel spreadsheet seems sufficient. Beyond that I'm really
good at working with Excel VBA. :) So definetly in a comfort zone there.

"John Spencer" wrote:

Forgot to mention that the function is "air code", not checked for correct
operation or correct syntax.

And now that I look at it again - it should be more like

Public Function fBuildSQL(strIN As String) As String
Dim StrSQL As String
Dim iCount As Long
Dim vItems As Variant

vItems = Split(strIN, ";")
For iCount = LBound(vItems) To UBound(vItems)
StrSQL = StrSQL & " OR (Column2=""" & vItems(iCount) & _
""" And Column3 =" & iCount + 1 & ")"
Next iCount

'strip off the leading " OR "
StrSQL = Mid(StrSQL, 4)
'build the entire sql string
StrSQL = "SELECT * FROM TheTable WHERE Column1 IN" & _
" (SELECT Column1 FROM TheTable WHERE " & StrSQL & _
" GROUP BY Column1 HAVING Count(Column1) = " & UBound(vItems) + 1 & ")"

fBuildSQL = StrSQL
End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

.

Relevant Pages