Get the reverse of a query

[Bob Vance]

This shows me what HorseID have Invoicing True how can I get the reverse and
show the other HorseID not shown?
So If I have 10 HorseId's and this query shows me 3 of them I need another
query to show me the other 7 HorseID's

SELECT tblHorseDetails.HorseID
FROM tblHorseDetails INNER JOIN qryHorseNameActive ON
tblHorseDetails.HorseID = qryHorseNameActive.HorseID
GROUP BY tblHorseDetails.HorseID
HAVING (((Count(*))=Sum(Abs([Invoicing]))));

 

[Larry Struckmeyer[SBS-MVP]]

Is there a product that will diagnose an Access .mdb file and report out
the sequence, or the flow, or the procedure or the logic or whatever one
calls what seems to me to be a mish mash of tables, forms, queries, reports
and macros.

Many years ago I dabbled in dbase and clipper and took pride in commenting
and documenting every line of code. One could pick up good code and read
it like a book, following every call, noting the end of any program or routine
and where it jumped to, forward or back.

In most access "programs" that I have seen there is no way to tell what is
going on except if there is a "autoexec" macro, that will/should/might start
when the .mdb is clicked.

In the one at hand, there are close to 100 each of tables, queries, forms
and macros, with 7 modules and 7 reports. I guess one could print out each
one and read it and diagram it, but what a pain.

Is there no easier way?

-
Larry
Please post the resolution to your
issue so others may benefit
-
Get Your SBS Health Check at
www.sbsbpa.com

 

[JohnR]

Try reversing the criteria to not equal.

SELECT tblHorseDetails.HorseID
FROM tblHorseDetails INNER JOIN qryHorseNameActive ON
tblHorseDetails.HorseID = qryHorseNameActive.HorseID
GROUP BY tblHorseDetails.HorseID
HAVING (((Count(*))<>Sum(Abs([Invoicing]))));

 

[Steve]

Hello Bob,

Create an unmatched query and find the HorseIDs in your Horse table that are
not returned by your query here.

Steve
(e-mail address removed)

 

[Banana]

Is there a product that will diagnose an Access .mdb file and report out
the sequence, or the flow, or the procedure or the logic or whatever one
calls what seems to me to be a mish mash of tables, forms, queries,
reports and macros.

For one thing, Access has a utility, Documenter, which you can use to
list out all objects. But it is basically a listing and in some way,
it's too verbose for its own good.

But what I would probably look at is Crystal Long's , an Access MVP,
awesome Data Dictionary:

http://www.accessmvp.com/Strive4Peace/Dictionary/Index.htm

That will take care of your tables & queries & how data relates.

For following the logic of VBA codes, I'm not sure there's a good tool
(unless you want to shell out for FMS Inc.'s Total Analyzer which I've
heard great things but never bought one), but at least with MZ-Tools,
there's a button to see what procedures will call a selected procedure
and you could work backward from there. MZ-Tools for VBA is free to
download & use.

http://www.fmsinc.com/Products/analyzer/index.html
http://www.mztools.com/v3/download.aspx

Best of luck.

 

[Bob Vance]

Thanks John, Just Brilliant
Bob

Try reversing the criteria to not equal.

SELECT tblHorseDetails.HorseID
FROM tblHorseDetails INNER JOIN qryHorseNameActive ON
tblHorseDetails.HorseID = qryHorseNameActive.HorseID
GROUP BY tblHorseDetails.HorseID
HAVING (((Count(*))<>Sum(Abs([Invoicing]))));

This shows me what HorseID have Invoicing True how can I get the reverse
and show the other HorseID not shown?
So If I have 10 HorseId's and this query shows me 3 of them I need
another query to show me the other 7 HorseID's

SELECT tblHorseDetails.HorseID
FROM tblHorseDetails INNER JOIN qryHorseNameActive ON
tblHorseDetails.HorseID = qryHorseNameActive.HorseID
GROUP BY tblHorseDetails.HorseID
HAVING (((Count(*))=Sum(Abs([Invoicing]))));

 

[John W. Vinson]

Is there a product that will diagnose an Access .mdb file and report out
the sequence, or the flow, or the procedure or the logic or whatever one
calls what seems to me to be a mish mash of tables, forms, queries, reports
and macros.

Well... an Access database isn't a procedure in the sense that (say) a Basic
or C++ program might be. It's event driven, so there will be lots and lots of
code that only runs in response to some user action.

Therefore, the "sequence" or "flow" is ipso facto unpredictable; the program
will do what the user asks it to do, in the order in which the user asks.

Obviously, tables in particular are not executable code at all; they are the
data repositories. Queries select and organize data from tables; Forms present
that data onscreen for user interaction; Reports present the data in printable
form. Macros and Modules might not exist at all in some databases (you can do
a heck of a lot just with forms and queries), but they'll be used for more
advanced actions than can be done with these.

 

[Larry Struckmeyer[SBS-MVP]]

Thanks...

It is as I suspected.

If you program for multiple projects how do you keep track of what you have
done in case you have to revisit it later, or turn it over to a (presumably)
junior programmer?

Notes in word, excel, or is there a special format / program for keeping
track.

-
Larry
Please post the resolution to your
issue so others may benefit
-
Get Your SBS Health Check at
www.sbsbpa.com

 

[John W. Vinson]

Thanks...

It is as I suspected.

If you program for multiple projects how do you keep track of what you have
done in case you have to revisit it later, or turn it over to a (presumably)
junior programmer?

Notes in word, excel, or is there a special format / program for keeping
track.

I'm just not that organized, personally. May explain why I'm not rich.

 

[David W. Fenton]

Is there a product that will diagnose an Access .mdb file and
report out the sequence, or the flow, or the procedure or the
logic or whatever one calls what seems to me to be a mish mash of
tables, forms, queries, reports and macros.

This is a very 1989 question. Or, maybe, 1992, before Windows.

Event-driven programming doesn't have a sequence to analyze.

 

[David W. Fenton]

If you program for multiple projects how do you keep track of what
you have done in case you have to revisit it later, or turn it
over to a (presumably) junior programmer?

Keep track of *what*?

The application is, in some sense, its own documentation. That is,
the objects indicate their own relationships (e.g., the code behind
a command button on a form tells you where the user can go from that
particular form).

Some people use Visio to produce flow charts of workflow and
procedures, but mostly, I think that happens in the planning/design
stage, not as part of the documentation for a finished application.

I end up being brought in to fix/revise/update old apps all the
time. I don't have any documentation in these cases, but I don't
have much difficulty figuring out the workflow. The hard part is
figuring out which objects are in use and which aren't, and that can
be helped by:

1. temporarily turning on Name Autocorrect so it builds its
dependency table, which you can then use to see which objects have
which dependencies.

2. using a good search/replace utility.

I am currently in the early stages with one project of "making the
database my own" by converting it from the way the previous
developer designed it to the way I'd design it. This entails:

1. renaming tables, combining duplicate data tables, implementing
proper RI, renaming fields within the tables, removing errors (like
lookup fields in tables).

2. converting all macros to code, weaning menuing systems off of
macros.

3. revamping forms to not use saved queries as recordsources,
revising appearance in all forms, integrating my own utilities for
controlling appearance, etc.

4. revising reports to remove dependencies on saved queries,
revising appearance, etc.

And so forth.

This process leaves me understanding the application a lot better so
I can move forward with whatever needs to be done to make it better.

I hardly ever encounter an app with any significant amount of VBA
code, so I mostly don't have to worry about that. Code navigation is
a lot easier, as the VBE and the object browser make it pretty easy
to figure out how things interrelate.

 

[Larry Struckmeyer[SBS-MVP]]

Interesting the answers I got on this, more than one off line. It appears
this type of programing is not "logical" and does not flow, and follows no
rules, and can't be explained.

I guess that is why I don't understand it. But my hat is off to those of
you who do.

-
Larry
Please post the resolution to your
issue so others may benefit
-
Get Your SBS Health Check at
www.sbsbpa.com

 

[John W. Vinson]

Interesting the answers I got on this, more than one off line. It appears
this type of programing is not "logical" and does not flow, and follows no
rules, and can't be explained.

I guess that is why I don't understand it. But my hat is off to those of
you who do.

Sorry if my reply came across as condescending or sarcastic - that was
certainly not my intent.

I think of the objects and code in a database as being highly modular: there
isn't "the program" so much as there are a lot of little, selfcontained
programs which serve a specific function. Some of them are reusable, some are
specific one-time-only solutions to a particular problem. So I'll have a lot
of "little bits" of code that I can pick up and apply where they're needed.

Another way to think of it is that the "program" has a whole lot of entry
points, under user control; this is a very different mindset than was required
when I started programming in 1968, where a program had one start point and a
defined, predictable logic flow!

 

[David W. Fenton]

It appears
this type of programing is not "logical" and does not flow, and
follows no rules, and can't be explained.

That is not even close to correct.