Data data from a Data Table

[GB]

I think this is a tough problem to solve, and I'm not sure if I have caused
the problem by the way I have captured my data; however, any assistance would
be great.

I have created a data table that has three fields to create the primary key.
There is one additional field at the moment that is used for sorting to
maintain a proper sequence.

The data table is created by generating a unique series of events to occur
that are then stored into the table, something like a map. To get from point
A to point B, the individual must pass through certain decision points. Each
path taken is unique and each path is given a designation. So path 1 may be
to go to points A, C, D, then B. Path 2 may be to go to points A, C, E, then
B. This is stored into the table like so:
Column 1: Path #
Column 2: Point in Path
Column 3: Order/sequence along the path.

1 A 1
1 C 2
1 D 3
1 B 4
2 A 1
2 C 2
2 E 3
2 B 4

Now what I would like to be able to do, is say if I enter the path A, C, D,
B to identify the path number associated with it. I have toyed with a few
ideas to accomplish this, but am wondering what the most expedient way to not
only establish this capability but to maintain it is. I.e., If additional
information/tables are needed from what I have entered, then I need to
"convert" the data, but I also need to add in the ability to create that new
data upon initial entry.

One idea I had was to create another table that has a primary key set to
just the path itself, so I would have one field that identifies the path #
and a second field that concatenates the path in the order traversed. So I
would have a table that looks like:

1; "A, C, D, B"
2; "A, C, E, B"

the ";" separates the two fields.

To circumvent the question of why I created the table I did. I need to
capture each point along the way in a field so that I can query the path
field to locate additional information about that point. Now why I didn't
create something else to pull the information back out the way I am trying to
get it? Well, I was thinking that there ought to be a way to pull the data
back out from the other direction without creating additional tables.

I've been trying to think of queries that could do this and to
programmatically go through and determine if the count of items for a path#
equals the count of records originally testing when testing the user list
against the entered list. This seems like a computer "intensive" operation
and may cause a lag time to the user that must be addressed. Would be better
if I could cut it off at the pass.

Another option I had considered but realized wouldn't work was to add a zero
operation to each path that contained the concatenated path; however, I also
realized that all the items in the path points are pulled from a table and I
have required that the path point must exist in the table to use the path
point. Therefore, a concatenated list of path points would not be in the
referenced table until added to that table, and would provide undesirable
results if not filtered out. I.e., would almost create more work than needed
to address this attribute.

So..... Got any ideas? Is the additional table having the path designation
and the concatenated path points the (or a) right way to go?

 

[GB]

Anyone? Anything?

I think this is a tough problem to solve, and I'm not sure if I have caused
the problem by the way I have captured my data; however, any assistance would
be great.

I have created a data table that has three fields to create the primary key.
There is one additional field at the moment that is used for sorting to
maintain a proper sequence.

The data table is created by generating a unique series of events to occur
that are then stored into the table, something like a map. To get from point
A to point B, the individual must pass through certain decision points. Each
path taken is unique and each path is given a designation. So path 1 may be
to go to points A, C, D, then B. Path 2 may be to go to points A, C, E, then
B. This is stored into the table like so:
Column 1: Path #
Column 2: Point in Path
Column 3: Order/sequence along the path.

1 A 1
1 C 2
1 D 3
1 B 4
2 A 1
2 C 2
2 E 3
2 B 4

Now what I would like to be able to do, is say if I enter the path A, C, D,
B to identify the path number associated with it. I have toyed with a few
ideas to accomplish this, but am wondering what the most expedient way to not
only establish this capability but to maintain it is. I.e., If additional
information/tables are needed from what I have entered, then I need to
"convert" the data, but I also need to add in the ability to create that new
data upon initial entry.

One idea I had was to create another table that has a primary key set to
just the path itself, so I would have one field that identifies the path #
and a second field that concatenates the path in the order traversed. So I
would have a table that looks like:

1; "A, C, D, B"
2; "A, C, E, B"

the ";" separates the two fields.

To circumvent the question of why I created the table I did. I need to
capture each point along the way in a field so that I can query the path
field to locate additional information about that point. Now why I didn't
create something else to pull the information back out the way I am trying to
get it? Well, I was thinking that there ought to be a way to pull the data
back out from the other direction without creating additional tables.

I've been trying to think of queries that could do this and to
programmatically go through and determine if the count of items for a path#
equals the count of records originally testing when testing the user list
against the entered list. This seems like a computer "intensive" operation
and may cause a lag time to the user that must be addressed. Would be better
if I could cut it off at the pass.

Another option I had considered but realized wouldn't work was to add a zero
operation to each path that contained the concatenated path; however, I also
realized that all the items in the path points are pulled from a table and I
have required that the path point must exist in the table to use the path
point. Therefore, a concatenated list of path points would not be in the
referenced table until added to that table, and would provide undesirable
results if not filtered out. I.e., would almost create more work than needed
to address this attribute.

So..... Got any ideas? Is the additional table having the path designation
and the concatenated path points the (or a) right way to go?

 

[John Spencer]

I've been pondering on this for a bit and have not come up with any good fast
solution.

One question. Can a series of events repeat a step? That is would the
following be a legitimate path - A C D B E A ?

You could obviously do as you suggest and have an additional table that stroed
the entire path as a string and then match against that. This might give you
the speed your need. The problem here would be maintaining the non-normalized
data in this table when you made changes to your "master" table. Adding
records, changing the sort order, adding another step, deleting records would
all need to be handled.

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

 

[GB]

I have taken care to not allow that condition of repeating the original
starting point. I did that because I with my original outlying plan I did
not want to have to address eliminating the possibility of an infinite loop
when selecting the path to be taken. I.e., when I start from point A I have
X number of paths that can be taken, when I get to the next point, that point
has Y number of paths, etc... If I were able to make it back to the original
starting point, then I could cycle again and again, and the user may get
trapped in a loop and never be able to "store" the data. Data storage
capability is currently flagged as having a path of "NONE" as the next
available path.

Yes, as for data updating, I am realizing that I would have to always
"recreate" the stored path. Something of a nightmare if I much later decide
to add additional capability.

I am finding that (and have a post related to it "Tab Key on Primary form
with Subform") I can create a form that contains the data needed to create
the string. I figured that if the data table was the way to go that I ought
to start coding it, now I'm thinking that I could use the work of the form to
possible produce the string on the fly if I can somehow use the power of
access to more automatically go through the records.

The form works like this: With a given path #, if the path is already
stored and I am trying to "update" it, then I delete the path, add the new
path, and at the same time create the text string that could be used to back
fill and identify the path #. If the path does not yet exist, then I simply
add the path and create the text string that could be used to back fill and
identify the path #. I am somewhat wondering if I could use a function in a
query perhaps, that would open a form as hidden, take the path number, look
up the path, create the string, and simply return the string into the query,
and then when a path is to be searched, use the query and look up in the
string field for that path number, and return the path #.

The query of course would show the concatenated path next to each path
point, and "look" like it's not normalized; however, it would get the job
done.

Problem I have had though, is that I have had trouble getting a hidden form
to do what I expect. :\ I've tried to use some of the predeveloped features
of Access, like

DoCmd.GoToRecord , , acNewRec

and if I am in debug mode, I think it halts operation and says that I can
not do this while in debug mode. I haven't found a successful way to perform
the same action without using this command. Doesn't mean it doesn't exist,
it's just that I'm not familiar with the proper methodology to get the
desired result. (As posted elsewhere, I can work well with VBA, but Access
VBA is yet another creature.)

If this information helps, then please continue pondering, I've been
thinking about it since yesterday and am trying to figure out a way to get
access to do what it does best and me (or someone else) to be able to
successfully maintain the database.

 

[John Spencer]

The easiest way I can think of would be to use VBA to build the query. I'll
assume your table and field names follow the naming guidelines - only letters,
numbers, and the underscore character.

The query would look like
SELECT *
FROM TheTable
WHERE Column1 IN (
SELECT Column1
FROM TheTable
WHERE (Column2 = "A" and Column3 = 1)
OR (Column2 = "C" and Column3 =2)
OR (Column2 = "D" and Column3 =3)
OR (Column2 = "B" and Column3 =4)
GROUP BY Column1
HAVING Count(Column1) = 4)

The function would look like the following where you would pass in the path as
a delimited string (A;C;B;D). Then you could build the SQL string and return
it to be used in a query, as the source for a combobox or listbox, or as the
source for a recordset.

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 & _
""" And Column3 =" & iCount & ")"
Next iCount
'strip off the leading " OR "
strSQL = Mid(StrSQL, Len(StrSql)-4)
strSQL = "SELECT * FROM TheTable WHERE Column1 IN" & _
" (SELECT Column1 FROM TheTable WHERE " & strSQL & _
" GROUP BY Column1 HAVING Count(Column1) = " & icount & ")"

fBuildSQL = strSQL
End Function

All that said, you may still be better off in terms of speed storing the full
path string in order in a separate table and using that for finding your
matches. That solution breaks the "rules" of normalization when you look at
it from one angle. On the other hand any other solution may be too slow for
use in the real world.

I kept hoping I would think of a better solution than the above, but nothing
better has come to me.

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

 

[John Spencer]

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

 

[GB]

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.