Query for change in Responsibility

  • Thread starter Thread starter Greg
  • Start date Start date
G

Greg

Hello,

I am trying to create a query and can not get it correct!
I am trying to do a select query on one table using access 2000.
I am trying to look back one record in order to make a decision on the
record I am processing.
I am not highly skilled at SQL, so this may be trivial to some.

EXAMPLE:

Table:

1112223333,01/01/2006,James
1112223333,01/02/2006,James
1112223333,01/03/2006,James
1112223333,01/04/2006,James
1112223333,01/05/2006,James
4445556666,01/01/2006,Bob
4445556666,01/02/2006,Bob
4445556666,01/03/2006,Bob
4445556666,01/04/2006,Ralph
4445556666,01/05/2006,Ralph
7778889999,01/01/2006,Peter
7778889999,01/02/2006,Peter
7778889999,01/03/2006,Peter
7778889999,01/04/2006,Peter
7778889999,01/05/2006,Peter

In the above table I would like to extract the Ralph records and move all
the fields to the Dynaset(Output from Query). The logic for the selection is
that the Responsible person for account 4445556666 has changed.

Suggestions on how to do this are appreciated... But please remember I am
not an expert at SQL or VBA.

Greg
 
Nesting is something SQL doesn't handle well IMHO.

If you want some (mind-bending) algorithms for nesting in SQL, here's some
links to Joe Celko's suggestions:
http://www.intelligententerprise.com/001020/celko.shtml
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
http://www.dbmsmag.com/9606d06.html

If you want to trace back the generations a limited number of steps, you can
do it like this:
http://allenbrowne.com/ser-06.html

The main problem with self-joins and nesting is the possibility of a record
being entered as its own ancestor in some previous generation. I normally
cheat, insisting that the entire tree resolves in a specified number of
generations (usually between 4 and 8), and programmatically denormalize the
structure into a temporary table that contains all ancestors for an entity,
only proceeding when that works.

I'm not sure if your question applies to VBA as well as SQL, but that's
another area where thinking event-driven is radically different than
thinking procedurally. Coming from a background in dBase/Fox, that was a
major paradigm shift for me. This old article summarizes my learning curve
at the time:
http://allenbrowne.com/xbase-02.html

These days, my Access software is totally event-driven (unless it's written
for users who have no idea or use it rarely.) The documentation explains the
go-anywhere-do-anything-anytime mindset, and I actually find it fun to go
back and discover how it is being used. It is not unusual for them to have
developed approaches I never imagined. It's worth the effort.
 
Allen,

Thank You very much you have a very informative web site. Even with your
help I am struggling with the nested SQL stuff. I used to program in
assembly, Fortran, Cobol, C, and think procedurally. At my age this non
procedural stuff is tough. I know it has got to translate to a procedural
language somewhere(probably Assembly level).
Have you ever heard a comment like this and have you heard how people have
overcome this procedural mind set?
Probably the answer is just learn it!!!

Once again
Thank you,
Greg
 
Allen,

Thank you Again.
I agree that nesting isn't handled well.
It is also somewhat comforting to know you may have experienced the same
struggles that I am experiencing.
I am still working at it.
Thank You for your patience!

I am reviewing your links- I do not know if I want to get into Mr. Celko's
stuff. I am probably confused enough!!!!

Thank You
Greg

PS. I am not a VB or VBA expert but from what I have read and some
experiments I conducted VBA (code) is more straight forward (intuitive) in
my opinion than SQL .
 
Back
Top