General solution for missing sequence numbers

[Peter Danes]

Hi David,

the gaps are not occurring now, they are leftovers of a sadly mismanaged
database with an uneducated user. And the original database was not even
written for that particular purpose. It was written by a self-taught
scientist in another department for some other use and this woman decided it
might work for her as well. And it did, somewhat - the original author
didn't do that bad a job for someone with no technical schooling. It just
wasn't really suited for her particular needs.

 

[David C. Holley]

Oh my! Please put the gun down and don't go postal. Scientists should be
held responsible for their own stupid actions.

 

[Peter Danes]

You're right, they don't know. Nor do they care. They're scientists, not
computer techs. They expect a database to fill a need, not dictate to them
in matters that do not interest them.

The numbering convention is something used all over the world in this
particular field (parasitic lichenology, to be specific) and calling them
stupid for using a numbering convention seems pointless to me. Records are
generally not deleted, this entire issue came about because gaps in the
numbering appeared due to misuse of a tool not designed for the job.
Duplicates of a sample are sometimes sent to other institutions and the
database now has tools for handling that, but the only time an actual record
might be deleted is if a sample is lost or accidentally destroyed, and in
that case the number is NOT re-used. In fact, I doubt that even then would
the record be deleted, it would probably simply have a remark put in a
comment field to the effect that the sample is no longer in existence, but
the information is still valuable.

And sequential numbering is not 'just wrong'. Thousands of things in daily
life are numbered so, streets, houses, rooms, movie tickets, store receipts.
Can you imagine a library where book catalog numbers were not in sequence?
Or zip codes assigned randomly, instead of at least being geographically
grouped? Certainly, users occasionally have unrealistic expectations and
some education is often necessary. But expecting an entire scientific
community to alter their standards to suit my notions of order is
ridiculous.

--
Pete

In that situation the issue isn't about how to 'fill in the gaps', it's how
to explain to a particular group of scientists that they don't have a clue
about how relational databases work. Are they really stupid enough to not
understand that, although my highest ID number is 10000, that I only have
1000 samples? Even if I point out that 9000 of those records are marked as
'not really a sample' (i.e. the idea of disallowing deletions and marking
the record as inactive instead)?

Expecting sequential numbering is just wrong. It's up to those of us who
actually know about the issues raised by John Vinson to not allow those who
don't to tell us how to put databases together.

Such situations are common, for a variety of reasons. Depends on the
database and the user and what the data is for. The particular example that
inspired this outburst is a mycological database, where the numbers are used
to sequentially number the scientist's samples. She told me that numbering
is important for others in the field to know roughly how many samples a
particular researcher has, and for internal inventory purposes, that they
don't expect to have holes in the numbering sequence.

If someone who has 1,000 samples in their collection publishes something
about their sample number 10,000 and it is known that the person does not
have anywhere near 10,000 samples, it would be viewed as odd at the very
least, possibly unethical and such a person would find himself not taken
seriously by other researchers. One or two numbers amiss in this situation
is obviously not a major concern.

And for the internal inventory controls, if someone sees sample 152 next to
150, they are going to wonder where is number 151. The inventory methods
used expect sequential numbering and a missing number is an indication of
something wrong. For inventory numbers in the original database, she used
the record number that appears in the text box of Access's navigation
control in conjunction with an autonumber field. You may guess what sort of
hash resulted from that. I started out trying to fix a few things for her
and wound up doing almost a complete re-write of the entire thing and this
numbering issue is one of the things that surfaced. She wants to be able to
fill in all the gaps as well as add new numbers to the end as she collects
new samples.

Pete

What is the specific *NEED* to find the missing numbers?

Peter Danes wrote:
I occasionally need to determine a number that I don't have in a
sequence, either the first missing one in a gap in a set of sequential
numbers or the next one in line at the end of a numbered series. Always
it meant some fumbling around, with either VBA at first or later with SQL
when I got good enough at it, establishing the proper join parameters and
such. For SQL experts, this is probably routine and trivial, but for me
it was always a bit of a chore. The last straw came with a database which
I recently wrote, where the converted data had such a numbered series,
and the owner wanted to be able to do both, fill in missing numbers in
the gaps AND add new numbers at the end.

Walking home from a bar last night, I got to thinking about it and
realized that both problems are actually fairly similar and that a simple
and general solution is possible.
I put together a simple table containing one field with the following
entries:

1,2,3,4, 8,9,10, 15,16,17,18, 20, 22,23,24,25, 28,29,30

Missing are:


5,6,7, 11,12,13,14, 19, 21, 26,27 and 31 on up.

This is the dataset used for all of the following examples.


Finding the next new number at the end of a series with SQL is trivial;
here is a simplified version of a statement that I found somewhere in the
discussion groups a few years ago:

SELECT Max(MyTable.MySeqFld)+1 FROM MyTable;

This will return a one-record, one-field recordset containing exactly one
value: 31, which is one greater than the largest value so far used in
that field. This is what you would want to use instead of Access's
autonumber, if the field is to contain meaningful sequence numbering,
rather than just a unique identifier.

Locating gaps is a little more complicated: it involves a self-join from
N to N+1 and finding where N+1 doesn't exist, indicating a gap at that
point.

SELECT MT1.MySeqFld+1
FROM MyTable AS MT1 LEFT JOIN MyTable AS MT2 ON
MT1.MySeqFld+1=MT2.MySeqFld
WHERE MT2.MySeqFld IS NULL;

This generates a recordset of 5, 11, 19, 21, 26, 31, where each value is
the first missing value in a gap, including the "open gap" at the end,
and that's where the trick to a general solution begins. Since these
situations normally call for either the first (lowest number) gap or last
(end of recordset) gap, you need either the first or last record returned
by this query. Sorting and using the TOP predicate gives you exactly
that.

SELECT TOP 1 MT1.MySeqFld+1
FROM MyTable AS MT1 LEFT JOIN MyTable AS MT2 ON
MT1.MySeqFld+1=MT2.MySeqFld
WHERE MT2.MySeqFld IS NULL
ORDER BY MT1.MySeqFld;

This will again return a one-record, one-field recordset containing
exactly one value: 5, the first missing number in the first gap in the
sequence. Ascending sort order is the default, so the smallest number is
the first returned.

SELECT TOP 1 MT1.MySeqFld+1
FROM MyTable AS MT1 LEFT JOIN MyTable AS MT2 ON
MT1.MySeqFld+1=MT2.MySeqFld
WHERE MT2.MySeqFld IS NULL
ORDER BY MT1.MySeqFld DESC;

This will return a one-record, one-field recordset containing exactly one
value: 31, the same "one greater than the highest value so far used in
that field" that is returned by the first simple example. Specifying the
descending order here is necessary, since we want the last (greatest)
record from the set and Access SQL does not have a BOTTOM predicate.

Finally, an even more general statement can be used:

SELECT TOP 1 MT1.MySeqFld+1
FROM MyTable AS MT1 LEFT JOIN MyTable AS MT2 ON
MT1.MySeqFld+1=MT2.MySeqFld
WHERE MT2.MySeqFld IS NULL
ORDER BY ((INSTR("LF",[First or Last (F or L)]) *2)-3)*MT1.MySeqFld;

This expects one parameter, F or L and will return either the first
missing number or the next number at the end of the line. The INSTR
expression evaluates to either -1 or 1 (or -3 if the parameter supplied
is neither F nor L, but that has the same effect as -1 in this instance),
that is then used as a multiplier for the sort field, so the sort is
either by the field or by the negative of the field (or 3 times the
negative of the field), giving either ascending or descending order and
with the TOP 1 predicate again returns exactly the one value of interest.

 

[Peter Danes]

Because doing things by hand leads to errors. If I can give the user a tool
to that automatically informs them which number is the next one available
and sets it as the default in the ID number field, why should they have to
determine it manually for every entry and risk an error? That sort of
situation is exactly how such gaps in numbering could easily arise.

--
Pete

So why not simply create the sample numbers by hand and then enter them?
I would hope that any scientist capable of research would also be
capable of counting from 1 to 1000.

In that situation the issue isn't about how to 'fill in the gaps', it's how
to explain to a particular group of scientists that they don't have a clue
about how relational databases work. Are they really stupid enough to not
understand that, although my highest ID number is 10000, that I only have
1000 samples? Even if I point out that 9000 of those records are marked as
'not really a sample' (i.e. the idea of disallowing deletions and marking
the record as inactive instead)?

Expecting sequential numbering is just wrong. It's up to those of us who
actually know about the issues raised by John Vinson to not allow those who
don't to tell us how to put databases together.

Such situations are common, for a variety of reasons. Depends on the
database and the user and what the data is for. The particular example
that

inspired this outburst is a mycological database, where the numbers are
used

to sequentially number the scientist's samples. She told me that numbering
is important for others in the field to know roughly how many samples a
particular researcher has, and for internal inventory purposes, that they
don't expect to have holes in the numbering sequence.

If someone who has 1,000 samples in their collection publishes something
about their sample number 10,000 and it is known that the person does not
have anywhere near 10,000 samples, it would be viewed as odd at the very
least, possibly unethical and such a person would find himself not taken
seriously by other researchers. One or two numbers amiss in this situation
is obviously not a major concern.

And for the internal inventory controls, if someone sees sample 152 next
to

150, they are going to wonder where is number 151. The inventory methods
used expect sequential numbering and a missing number is an indication of
something wrong. For inventory numbers in the original database, she used
the record number that appears in the text box of Access's navigation
control in conjunction with an autonumber field. You may guess what sort
of

hash resulted from that. I started out trying to fix a few things for her
and wound up doing almost a complete re-write of the entire thing and this
numbering issue is one of the things that surfaced. She wants to be able
to

fill in all the gaps as well as add new numbers to the end as she collects
new samples.

Pete


"David C. Holley" <[email protected]> píse v diskusním príspevku

What is the specific *NEED* to find the missing numbers?

Peter Danes wrote:

I occasionally need to determine a number that I don't have in a
sequence, either the first missing one in a gap in a set of sequential
numbers or the next one in line at the end of a numbered series. Always
it meant some fumbling around, with either VBA at first or later with
SQL

when I got good enough at it, establishing the proper join parameters
and

such. For SQL experts, this is probably routine and trivial, but for me
it was always a bit of a chore. The last straw came with a database
which

I recently wrote, where the converted data had such a numbered series,
and the owner wanted to be able to do both, fill in missing numbers in
the gaps AND add new numbers at the end.

Walking home from a bar last night, I got to thinking about it and
realized that both problems are actually fairly similar and that a
simple

and general solution is possible.
I put together a simple table containing one field with the following
entries:

1,2,3,4, 8,9,10, 15,16,17,18, 20, 22,23,24,25, 28,29,30

Missing are:


5,6,7, 11,12,13,14, 19, 21, 26,27 and 31 on up.

This is the dataset used for all of the following examples.


Finding the next new number at the end of a series with SQL is trivial;
here is a simplified version of a statement that I found somewhere in
the

discussion groups a few years ago:

SELECT Max(MyTable.MySeqFld)+1 FROM MyTable;

This will return a one-record, one-field recordset containing exactly
one

value: 31, which is one greater than the largest value so far used in
that field. This is what you would want to use instead of Access's
autonumber, if the field is to contain meaningful sequence numbering,
rather than just a unique identifier.

Locating gaps is a little more complicated: it involves a self-join
from

N to N+1 and finding where N+1 doesn't exist, indicating a gap at that
point.

SELECT MT1.MySeqFld+1
FROM MyTable AS MT1 LEFT JOIN MyTable AS MT2 ON
MT1.MySeqFld+1=MT2.MySeqFld
WHERE MT2.MySeqFld IS NULL;

This generates a recordset of 5, 11, 19, 21, 26, 31, where each value
is

the first missing value in a gap, including the "open gap" at the end,
and that's where the trick to a general solution begins. Since these
situations normally call for either the first (lowest number) gap or
last

(end of recordset) gap, you need either the first or last record
returned

by this query. Sorting and using the TOP predicate gives you exactly
that.

SELECT TOP 1 MT1.MySeqFld+1
FROM MyTable AS MT1 LEFT JOIN MyTable AS MT2 ON
MT1.MySeqFld+1=MT2.MySeqFld
WHERE MT2.MySeqFld IS NULL
ORDER BY MT1.MySeqFld;

This will again return a one-record, one-field recordset containing
exactly one value: 5, the first missing number in the first gap in the
sequence. Ascending sort order is the default, so the smallest number
is

the first returned.

SELECT TOP 1 MT1.MySeqFld+1
FROM MyTable AS MT1 LEFT JOIN MyTable AS MT2 ON
MT1.MySeqFld+1=MT2.MySeqFld
WHERE MT2.MySeqFld IS NULL
ORDER BY MT1.MySeqFld DESC;

This will return a one-record, one-field recordset containing exactly
one

value: 31, the same "one greater than the highest value so far used in
that field" that is returned by the first simple example. Specifying
the

descending order here is necessary, since we want the last (greatest)
record from the set and Access SQL does not have a BOTTOM predicate.

Finally, an even more general statement can be used:

SELECT TOP 1 MT1.MySeqFld+1
FROM MyTable AS MT1 LEFT JOIN MyTable AS MT2 ON
MT1.MySeqFld+1=MT2.MySeqFld
WHERE MT2.MySeqFld IS NULL
ORDER BY ((INSTR("LF",[First or Last (F or L)]) *2)-3)*MT1.MySeqFld;

This expects one parameter, F or L and will return either the first
missing number or the next number at the end of the line. The INSTR
expression evaluates to either -1 or 1 (or -3 if the parameter supplied
is neither F nor L, but that has the same effect as -1 in this
instance),

that is then used as a multiplier for the sort field, so the sort is
either by the field or by the negative of the field (or 3 times the
negative of the field), giving either ascending or descending order and
with the TOP 1 predicate again returns exactly the one value of

interest.

 

[Peter Danes]

Certainly that is one possible way to do it, but in this case I'm not
interested in ALL the missing numbers. I only want to know which one is the
next one available for use, either the first one in the first gap, or the
next one greater than all the ones used so far. This allows me to fetch that
one number with this single SQL statement, rather than looping through a
recordset with VBA. And a side benefit is that SQL is orders of magnitude
faster than such a VBA loop.

 

[Peter Danes]

Yes, some cannot. In particular, a self-join cannot (as far as I know),
which is exactly what I used here. I'm not certain that I agree with the
notion that SQL is more readable with aliases than without, it seems to me
to be just one more re-direction that must be kept in mind when tracking or
debugging a statement. But that's just my opinion, worth exactly what you
paid for it. And I'm far from being a SQL expert, you may be right. I'd be
interested in your thoughts on how it improves readability.

And as for the question, well, I was just answering it - I didn't mean to
sound snippy. The way you worded it made me think that you really didn't
know what the difference between the statements was, but if you've been
writing SQL queries for years, you probably know more about it than I do.

 

[John Vinson]

The gaps got there in the first place because the owner of the database is a
scientist, not a computer tech.

Well, so am I, if it comes to that (Ph.D. Chemistry, Berkeley 1972).
No academic training in computers beyond a Fortran course back in
1968.

It doesn't really address the issue of why NEW records should backfill
the holes left in this earlier process, but by all means, if that's
what they want, you clearly have several sets of tools to do so. Just
another one for the toolkit: I routinely include a table Num with a
single field N, values 0 to 65536 or so. An "unmatched" query joining
Num to the table will very promptly provide all unused numbers, which
can then be assigned to new records.

John W. Vinson[MVP]

 

[Peter Danes]

Thank you, James. Always neat to learn another way of doing something.

--
Pete

I occasionally need to determine a number that I don't have in a sequence,
either the first missing one in a gap in a set of sequential numbers or the
next one in line at the end of a numbered series. Always it meant some
fumbling around, with either VBA at first or later with SQL when I got good
enough at it, establishing the proper join parameters and such. For SQL
experts, this is probably routine and trivial, but for me it was always a
bit of a chore. The last straw came with a database which I recently wrote,
where the converted data had such a numbered series, and the owner wanted to
be able to do both, fill in missing numbers in the gaps AND add new numbers
at the end.

Walking home from a bar last night, I got to thinking about it and realized
that both problems are actually fairly similar and that a simple and general
solution is possible.
I put together a simple table containing one field with the following
entries:

1,2,3,4, 8,9,10, 15,16,17,18, 20, 22,23,24,25, 28,29,30

Missing are:


5,6,7, 11,12,13,14, 19, 21, 26,27 and 31 on up.

This is the dataset used for all of the following examples.


Finding the next new number at the end of a series with SQL is trivial; here
is a simplified version of a statement that I found somewhere in the
discussion groups a few years ago:

SELECT Max(MyTable.MySeqFld)+1 FROM MyTable;

This will return a one-record, one-field recordset containing exactly one
value: 31, which is one greater than the largest value so far used in that
field. This is what you would want to use instead of Access's autonumber, if
the field is to contain meaningful sequence numbering, rather than just a
unique identifier.

Locating gaps is a little more complicated: it involves a self-join from N
to N+1 and finding where N+1 doesn't exist, indicating a gap at that point.

SELECT MT1.MySeqFld+1
FROM MyTable AS MT1 LEFT JOIN MyTable AS MT2 ON MT1.MySeqFld+1=MT2.MySeqFld
WHERE MT2.MySeqFld IS NULL;

This generates a recordset of 5, 11, 19, 21, 26, 31, where each value is the
first missing value in a gap, including the "open gap" at the end, and
that's where the trick to a general solution begins. Since these situations
normally call for either the first (lowest number) gap or last (end of
recordset) gap, you need either the first or last record returned by this
query. Sorting and using the TOP predicate gives you exactly that.

SELECT TOP 1 MT1.MySeqFld+1
FROM MyTable AS MT1 LEFT JOIN MyTable AS MT2 ON MT1.MySeqFld+1=MT2.MySeqFld
WHERE MT2.MySeqFld IS NULL
ORDER BY MT1.MySeqFld;

This will again return a one-record, one-field recordset containing exactly
one value: 5, the first missing number in the first gap in the sequence.
Ascending sort order is the default, so the smallest number is the first
returned.

SELECT TOP 1 MT1.MySeqFld+1
FROM MyTable AS MT1 LEFT JOIN MyTable AS MT2 ON MT1.MySeqFld+1=MT2.MySeqFld
WHERE MT2.MySeqFld IS NULL
ORDER BY MT1.MySeqFld DESC;

This will return a one-record, one-field recordset containing exactly one
value: 31, the same "one greater than the highest value so far used in that
field" that is returned by the first simple example. Specifying the
descending order here is necessary, since we want the last (greatest) record
from the set and Access SQL does not have a BOTTOM predicate.

Finally, an even more general statement can be used:

SELECT TOP 1 MT1.MySeqFld+1
FROM MyTable AS MT1 LEFT JOIN MyTable AS MT2 ON MT1.MySeqFld+1=MT2.MySeqFld
WHERE MT2.MySeqFld IS NULL
ORDER BY ((INSTR("LF",[First or Last (F or L)]) *2)-3)*MT1.MySeqFld;

This expects one parameter, F or L and will return either the first missing
number or the next number at the end of the line. The INSTR expression
evaluates to either -1 or 1 (or -3 if the parameter supplied is neither F
nor L, but that has the same effect as -1 in this instance), that is then
used as a multiplier for the sort field, so the sort is either by the field
or by the negative of the field (or 3 times the negative of the field),
giving either ascending or descending order and with the TOP 1 predicate
again returns exactly the one value of interest.

An alternative SQL method using subqueries can be found here:

http://groups.google.com/group/microsoft.public.access/msg/094a5fee9ff13437

James A. Fortune

I was staying at a hotel and decided to go down to the pool. A 13 year
old bratty kid splashed water on me. When I told him to stop he said,
"You can't make me. I'm a minor. If you touch me you'll get in a lot
of trouble." I found a 17 year old at the pool and paid him $5 to hold
the kid underwater for 10 seconds. "How do you like me now?" -- Rob

Smith

 

[Peter Danes]

That's a good trick, too. Thanks.

--
Pete

Well, so am I, if it comes to that (Ph.D. Chemistry, Berkeley 1972).
No academic training in computers beyond a Fortran course back in
1968.

It doesn't really address the issue of why NEW records should backfill
the holes left in this earlier process, but by all means, if that's
what they want, you clearly have several sets of tools to do so. Just
another one for the toolkit: I routinely include a table Num with a
single field N, values 0 to 65536 or so. An "unmatched" query joining
Num to the table will very promptly provide all unused numbers, which
can then be assigned to new records.

John W. Vinson[MVP]

 

[Chris2]

I'd be interested in your thoughts on how it improves readability.

Less text equals less to read equals greater readability. (See
below and consider.)

If the aliases are named correctly, then you automatically know what
tables they refer to.

Access, with it's penchant for re-arranging the SQL of queries,
especially for cutting out line-breaks, doesn't help much in the way
of readability, so it needs all the help it can get.

I'll admit most Access users don't care, as they use Design View
instead of SQL View. I use SQL View almost all the time.


Example: From a query in a thread (Group By Last, by Barrattolo_67).

Note: This is also a good example of why not to use spaces, as it
introduces masses of readability reducing brackets (not to mention
the other reasons).

Vanila MS Access Unmodified Query w/Spaces in Object Names:

INSERT INTO [Audit Follow-up Report] ( [No], [Thrust Area], [Title
of
Issue], [Risk Severity Code], Recommendation, [Responsible
Department],
[Management Action Plan], [Target Completion Date], [Revised Target
Date],
[Actual Completion Date], [Follow-up Status], [Change History],
[Management
Status Description], [Auditor's Comments] )
SELECT [tbl Comments].[Order of appearance], [tbl Comments].[Cycle
Name],
[tbl Comments].[Comment Title], [tbl Comments].[Risk Severity Code],
First([tbl Comments].Recommendation) AS FirstOfRecommendation, [tbl
Management Responses].[Responsible Department], First([tbl
Management
Responses].[Management Action Plan]) AS [FirstOfManagement Action
Plan], [tbl
Management Responses].[Target Completion Date], [tbl Management
Responses].RevisedTargetDate, [tbl Management Responses].[Actual
Completion
Date], [tbl Follow-up status codes].[Follow-up status code], [tbl
Management
Responses].[Completion Date Change History and Other Comments],
First([tbl
Follow-up Entries for Findings].[Management's Status Description])
AS
[FirstOfManagement's Status Description], First([tbl Follow-up
Entries for
Findings].[Auditor Comments]) AS [FirstOfAuditor Comments]
FROM ([tbl Comments] LEFT JOIN ([tbl Follow-up status codes] RIGHT
JOIN [tbl
Management Responses] ON [tbl Follow-up status codes].[Follow-up
status
order] = [tbl Management Responses].[Follow-up status code]) ON [tbl
Comments].[Comment Table counter] = [tbl Management
Responses].[Comment Table
counter]) LEFT JOIN [tbl Follow-up Entries for Findings] ON [tbl
Management
Responses].[ID for tbl Management Responses] = [tbl Follow-up
Entries for
Findings].[ID in tbl Management Responses]
GROUP BY [tbl Comments].[Order of appearance], [tbl Comments].[Cycle
Name],
[tbl Comments].[Comment Title], [tbl Comments].[Risk Severity Code],
[tbl
Management Responses].[Responsible Department], [tbl Management
Responses].[Target Completion Date], [tbl Management
Responses].RevisedTargetDate, [tbl Management Responses].[Actual
Completion
Date], [tbl Follow-up status codes].[Follow-up status code], [tbl
Management
Responses].[Completion Date Change History and Other Comments], [tbl
Comments].[Audit Report #]
HAVING ((([tbl Comments].[Audit Report #])="FA-BDI-04-34"))
ORDER BY [tbl Comments].[Order of appearance];


Query Re-Aligned (note the line-breaks caused by the enormous length
of some of the lines.

INSERT INTO [Audit Follow-up Report]
([No]
,[Thrust Area]
,[Title of Issue]
,[Risk Severity Code]
,Recommendation
,[Responsible Department]
,[Management Action Plan]
,[Target Completion Date]
,[Revised Target Date]
,[Actual Completion Date]
,[Follow-up Status]
,[Change History]
,[Management Status Description]
,[Auditor's Comments])
SELECT [tbl Comments].[Order of appearance]
,[tbl Comments].[Cycle Name]
,[tbl Comments].[Comment Title]
,[tbl Comments].[Risk Severity Code]
,First([tbl Comments].Recommendation) AS
FirstOfRecommendation
,[tbl Management Responses].[Responsible Department]
,First([tbl Management Responses].[Management Action Plan])
AS [FirstOfManagement Action Plan]
,[tbl Management Responses].[Target Completion Date]
,[tbl Management Responses].RevisedTargetDate
,[tbl Management Responses].[Actual Completion Date]
,[tbl Follow-up status codes].[Follow-up status code]
,[tbl Management Responses].[Completion Date Change History
and Other Comments]
,First([tbl Follow-up Entries for Findings].[Management's
Status Description]) AS [FirstOfManagement's Status Description]
,First([tbl Follow-up Entries for Findings].[Auditor
Comments]) AS [FirstOfAuditor Comments]
FROM ([tbl Comments]
LEFT JOIN
([tbl Follow-up status codes]
RIGHT JOIN
[tbl Management Responses]
ON [tbl Follow-up status codes].[Follow-up status order] =
[tbl Management Responses].[Follow-up status code])
ON [tbl Comments].[Comment Table counter] =
[tbl Management Responses].[Comment Table counter])
LEFT JOIN
[tbl Follow-up Entries for Findings]
ON [tbl Management Responses].[ID for tbl Management
Responses] =
[tbl Follow-up Entries for Findings].[ID in tbl Management
Responses]
GROUP BY [tbl Comments].[Order of appearance]
,[tbl Comments].[Cycle Name]
,[tbl Comments].[Comment Title]
,[tbl Comments].[Risk Severity Code]
,[tbl Management Responses].[Responsible Department]
,[tbl Management Responses].[Target Completion Date]
,[tbl Management Responses].RevisedTargetDate
,[tbl Management Responses].[Actual Completion Date]
,[tbl Follow-up status codes].[Follow-up status code]
,[tbl Management Responses].[Completion Date Change History
and Other Comments]
,[tbl Comments].[Audit Report #]
HAVING ((([tbl Comments].[Audit Report #])="FA-BDI-04-34"))
ORDER BY [tbl Comments].[Order of appearance];


Query w/Table Aliases:

INSERT INTO [Audit Follow-up Report]
([No]
,[Thrust Area]
,[Title of Issue]
,[Risk Severity Code]
,Recommendation
,[Responsible Department]
,[Management Action Plan]
,[Target Completion Date]
,[Revised Target Date]
,[Actual Completion Date]
,[Follow-up Status]
,[Change History]
,[Management Status Description]
,[Auditor's Comments])
SELECT CO1.[Order of appearance]
,CO1.[Cycle Name]
,CO1.[Comment Title
,CO1.[Risk Severity Code]
,First(CO1.Recommendation) AS FirstOfRecommendation
,MR1.[Responsible Department]
,First(MR1.[Management Action Plan]) AS [FirstOfManagement
Action Plan]
,MR1.[Target Completion Date]
,MR1.RevisedTargetDate
,MR1.[Actual Completion Date]
,FU1.[Follow-up status code]
,MR1.[Completion Date Change History and Other Comments]
,First(FE1.[Management's Status Description]) AS
[FirstOfManagement's Status Description]
,First(FE1.[Auditor Comments]) AS [FirstOfAuditor Comments]
FROM ([tbl Comments] AS CO1
LEFT JOIN
([tbl Follow-up status codes] AS FU1
RIGHT JOIN
[tbl Management Responses] AS MR1
ON FU1.[Follow-up status order] =
MR1.[Follow-up status code])
ON CO1.[Comment Table counter] =
MR1.[Comment Table counter])
LEFT JOIN
[tbl Follow-up Entries for Findings] FE1
ON MR1.[ID for tbl Management Responses] =
FE1.[ID in tbl Management Responses]
GROUP BY CO1.[Order of appearance]
,CO1.[Cycle Name]
,CO1.[Comment Title]
,CO1.[Risk Severity Code]
,MR1.[Responsible Department]
,MR1.[Target Completion Date]
,MR1.RevisedTargetDate
,MR1.[Actual Completion Date]
,FU1.[Follow-up status code]
,MR1.[Completion Date Change History and Other Comments]
,CO1.[Audit Report #]
HAVING (((CO1.[Audit Report #])="FA-BDI-04-34"))
ORDER BY CO1.[Order of appearance];


Query w/out spaces in object names and w/out accompanying brackets
and w/out table object prefixes. I left the column aliases (for
output) alone.

INSERT INTO AuditFollowUpReport
(Nbr
,ThrustArea
,TitleOfIssue
,RiskSeverityCode
,Recommendation
,ResponsibleDepartment
,ManagementActionPlan
,TargetCompletionDate
,RevisedTargetDate
,ActualCompletionDate
,FollowUpStatus
,ChangeHistory
,ManagementStatusDescription
,AuditorsComments)
SELECT CO1.OrderOfAppearance
,CO1.CycleName
,CO1.CommentTitle
,CO1.RiskSeverityCode
,First(CO1.Recommendation)
AS [FirstOfRecommendation]
,MR1.ResponsibleDepartment
,First(MR1.ManagementActionPlan)
AS [FirstOfManagement Action Plan]
,MR1.TargetCompletionDate
,MR1.RevisedTargetDate
,MR1.ActualCompletionDate
,FU1.FollowUpStatusCode
,MR1.CompletionDateChangeHistoryAndOtherComments
,First(FE1.ManagementsStatusDescription)
AS [FirstOfManagement's Status Description]
,First(FE1.AuditorComments)
AS [FirstOfAuditor Comments]
FROM (Comments AS CO1
LEFT JOIN
(FollowUpStatusCodes AS FU1
RIGHT JOIN
ManagementResponses AS MR1
ON FU1.FollowUpStatusOrder =
MR1.FollowUpStatusCode)
ON CO1.CommentTableCounter =
MR1.CommentTableCounter)
LEFT JOIN
FollowUpEntriesForFindings FE1
ON MR1.IDForTblManagementResponses =
FE1.IDInTblManagementResponses
GROUP BY CO1.OrderOfAppearance
,CO1.CycleName
,CO1.CommentTitle
,CO1.RiskSeverityCode
,MR1.ResponsibleDepartment
,MR1.TargetCompletionDate
,MR1.RevisedTargetDate
,MR1.ActualCompletionDate
,FU1.FollowUpStatusCode
,MR1.CompletionDateChangeHistoryAndOtherComments
,CO1.AuditReportNbr
HAVING (((CO1.AuditReportNbr) = "FA-BDI-04-34"))
ORDER BY CO1.OrderOfAppearance;

It simply looks far more readable to me.

Access does mangle it right away after saving and closing the window
.. . . <sigh />.

INSERT INTO AuditFollowUpReport ( Nbr, ThrustArea, TitleOfIssue,
RiskSeverityCode, Recommendation, ResponsibleDepartment,
ManagementActionPlan, TargetCompletionDate, RevisedTargetDate,
ActualCompletionDate, FollowUpStatus, ChangeHistory,
ManagementStatusDescription, AuditorsComments )
SELECT CO1.OrderOfAppearance, CO1.CycleName, CO1.CommentTitle,
CO1.RiskSeverityCode, First(CO1.Recommendation) AS
FirstOfRecommendation, MR1.ResponsibleDepartment,
First(MR1.ManagementActionPlan) AS [FirstOfManagement Action Plan],
MR1.TargetCompletionDate, MR1.RevisedTargetDate,
MR1.ActualCompletionDate, FU1.FollowUpStatusCode,
MR1.CompletionDateChangeHistoryAndOtherComments,
First(FE1.ManagementsStatusDescription) AS [FirstOfManagement's
Status Description], First(FE1.AuditorComments) AS [FirstOfAuditor
Comments]
FROM (Comments AS CO1 LEFT JOIN (FollowUpStatusCodes AS FU1 RIGHT
JOIN ManagementResponses AS MR1 ON
FU1.FollowUpStatusOrder=MR1.FollowUpStatusCode) ON
CO1.CommentTableCounter=MR1.CommentTableCounter) LEFT JOIN
FollowUpEntriesForFindings AS FE1 ON
MR1.IDForTblManagementResponses=FE1.IDInTblManagementResponses
GROUP BY CO1.OrderOfAppearance, CO1.CycleName, CO1.CommentTitle,
CO1.RiskSeverityCode, MR1.ResponsibleDepartment,
MR1.TargetCompletionDate, MR1.RevisedTargetDate,
MR1.ActualCompletionDate, FU1.FollowUpStatusCode,
MR1.CompletionDateChangeHistoryAndOtherComments, CO1.AuditReportNbr
HAVING (((CO1.AuditReportNbr) = "FA-BDI-04-34"))
ORDER BY CO1.OrderOfAppearance;

But the left over results are still more readable than the original.


Sincerely,

Chris O.

 

[Douglas J Steele]

Access does mangle it right away after saving and closing the window
. . . <sigh />.

I didn't think Access changed the formatting unless you went into Design
View again.

 

[Chris2]

I didn't think Access changed the formatting unless you went into Design
View again.

Douglas J Steele,

At least in Access 2000 SP-3, when I go into SQL View, write or
paste a query, save and close it (as I did above), and finally right
click it and select Design View, SQL View actually appears instead
of Design View (as if it's capable of remembering the last View it
closed in). When SQL View appears again, typically MS Access has
had it's way with the formatting. I have seen an occasional case
where it doesn't, but it seems to be random and rare (and could be
memories from earlier service packs or maybe even Access 97).


Sincerely,

Chris O.

 

[Douglas J Steele]

At least in Access 2000 SP-3, when I go into SQL View, write or
paste a query, save and close it (as I did above), and finally right
click it and select Design View, SQL View actually appears instead
of Design View (as if it's capable of remembering the last View it
closed in). When SQL View appears again, typically MS Access has
had it's way with the formatting. I have seen an occasional case
where it doesn't, but it seems to be random and rare (and could be
memories from earlier service packs or maybe even Access 97).

Yes, it does remember the last view in which you saved the query. You're
right that it does sometimes make small "adjustments" (the one that annoys
me is that it'll often remove the CR/LF between my last UNION keyword and
the following SELECT keyword), but for the most part, it usually seems to
respect my formatting.

 

[Peter Danes]

You're right, the example with aliases is more readable in such a
complicated example. My queries are rarely that involved and as you point
out, most Access users (myself included) use the Design View. It's simpler
to use and for non-experts in SQL, less prone to errors. I'm slowly
migrating towards more SQL use, but I have to admit that I like graphic
interfaces.

And a side not on the issue of spaces in table names (this may not apply to
you), besides spaces, I have found that it's a bad idea to use foreign
characters. I regularly work in Prague and the Czech language includes
letters with accent (diacritical) marks. Using those in object names can
lead to even more havoc than spaces, since the cutting and pasting such a
name does not always transfer correctly into the VBA editor. Not only do you
need to use brackets, but you need to check the actual names to see if they
got mangled during the paste operation into VBA.

Thanks for taking the time to show me your alias example.

--
Pete

I'd be interested in your thoughts on how it improves readability.

Less text equals less to read equals greater readability. (See
below and consider.)

If the aliases are named correctly, then you automatically know what
tables they refer to.

Access, with it's penchant for re-arranging the SQL of queries,
especially for cutting out line-breaks, doesn't help much in the way
of readability, so it needs all the help it can get.

I'll admit most Access users don't care, as they use Design View
instead of SQL View. I use SQL View almost all the time.


Example: From a query in a thread (Group By Last, by Barrattolo_67).

Note: This is also a good example of why not to use spaces, as it
introduces masses of readability reducing brackets (not to mention
the other reasons).

Vanila MS Access Unmodified Query w/Spaces in Object Names:

INSERT INTO [Audit Follow-up Report] ( [No], [Thrust Area], [Title
of
Issue], [Risk Severity Code], Recommendation, [Responsible
Department],
[Management Action Plan], [Target Completion Date], [Revised Target
Date],
[Actual Completion Date], [Follow-up Status], [Change History],
[Management
Status Description], [Auditor's Comments] )
SELECT [tbl Comments].[Order of appearance], [tbl Comments].[Cycle
Name],
[tbl Comments].[Comment Title], [tbl Comments].[Risk Severity Code],
First([tbl Comments].Recommendation) AS FirstOfRecommendation, [tbl
Management Responses].[Responsible Department], First([tbl
Management
Responses].[Management Action Plan]) AS [FirstOfManagement Action
Plan], [tbl
Management Responses].[Target Completion Date], [tbl Management
Responses].RevisedTargetDate, [tbl Management Responses].[Actual
Completion
Date], [tbl Follow-up status codes].[Follow-up status code], [tbl
Management
Responses].[Completion Date Change History and Other Comments],
First([tbl
Follow-up Entries for Findings].[Management's Status Description])
AS
[FirstOfManagement's Status Description], First([tbl Follow-up
Entries for
Findings].[Auditor Comments]) AS [FirstOfAuditor Comments]
FROM ([tbl Comments] LEFT JOIN ([tbl Follow-up status codes] RIGHT
JOIN [tbl
Management Responses] ON [tbl Follow-up status codes].[Follow-up
status
order] = [tbl Management Responses].[Follow-up status code]) ON [tbl
Comments].[Comment Table counter] = [tbl Management
Responses].[Comment Table
counter]) LEFT JOIN [tbl Follow-up Entries for Findings] ON [tbl
Management
Responses].[ID for tbl Management Responses] = [tbl Follow-up
Entries for
Findings].[ID in tbl Management Responses]
GROUP BY [tbl Comments].[Order of appearance], [tbl Comments].[Cycle
Name],
[tbl Comments].[Comment Title], [tbl Comments].[Risk Severity Code],
[tbl
Management Responses].[Responsible Department], [tbl Management
Responses].[Target Completion Date], [tbl Management
Responses].RevisedTargetDate, [tbl Management Responses].[Actual
Completion
Date], [tbl Follow-up status codes].[Follow-up status code], [tbl
Management
Responses].[Completion Date Change History and Other Comments], [tbl
Comments].[Audit Report #]
HAVING ((([tbl Comments].[Audit Report #])="FA-BDI-04-34"))
ORDER BY [tbl Comments].[Order of appearance];


Query Re-Aligned (note the line-breaks caused by the enormous length
of some of the lines.

INSERT INTO [Audit Follow-up Report]
([No]
,[Thrust Area]
,[Title of Issue]
,[Risk Severity Code]
,Recommendation
,[Responsible Department]
,[Management Action Plan]
,[Target Completion Date]
,[Revised Target Date]
,[Actual Completion Date]
,[Follow-up Status]
,[Change History]
,[Management Status Description]
,[Auditor's Comments])
SELECT [tbl Comments].[Order of appearance]
,[tbl Comments].[Cycle Name]
,[tbl Comments].[Comment Title]
,[tbl Comments].[Risk Severity Code]
,First([tbl Comments].Recommendation) AS
FirstOfRecommendation
,[tbl Management Responses].[Responsible Department]
,First([tbl Management Responses].[Management Action Plan])
AS [FirstOfManagement Action Plan]
,[tbl Management Responses].[Target Completion Date]
,[tbl Management Responses].RevisedTargetDate
,[tbl Management Responses].[Actual Completion Date]
,[tbl Follow-up status codes].[Follow-up status code]
,[tbl Management Responses].[Completion Date Change History
and Other Comments]
,First([tbl Follow-up Entries for Findings].[Management's
Status Description]) AS [FirstOfManagement's Status Description]
,First([tbl Follow-up Entries for Findings].[Auditor
Comments]) AS [FirstOfAuditor Comments]
FROM ([tbl Comments]
LEFT JOIN
([tbl Follow-up status codes]
RIGHT JOIN
[tbl Management Responses]
ON [tbl Follow-up status codes].[Follow-up status order] =
[tbl Management Responses].[Follow-up status code])
ON [tbl Comments].[Comment Table counter] =
[tbl Management Responses].[Comment Table counter])
LEFT JOIN
[tbl Follow-up Entries for Findings]
ON [tbl Management Responses].[ID for tbl Management
Responses] =
[tbl Follow-up Entries for Findings].[ID in tbl Management
Responses]
GROUP BY [tbl Comments].[Order of appearance]
,[tbl Comments].[Cycle Name]
,[tbl Comments].[Comment Title]
,[tbl Comments].[Risk Severity Code]
,[tbl Management Responses].[Responsible Department]
,[tbl Management Responses].[Target Completion Date]
,[tbl Management Responses].RevisedTargetDate
,[tbl Management Responses].[Actual Completion Date]
,[tbl Follow-up status codes].[Follow-up status code]
,[tbl Management Responses].[Completion Date Change History
and Other Comments]
,[tbl Comments].[Audit Report #]
HAVING ((([tbl Comments].[Audit Report #])="FA-BDI-04-34"))
ORDER BY [tbl Comments].[Order of appearance];


Query w/Table Aliases:

INSERT INTO [Audit Follow-up Report]
([No]
,[Thrust Area]
,[Title of Issue]
,[Risk Severity Code]
,Recommendation
,[Responsible Department]
,[Management Action Plan]
,[Target Completion Date]
,[Revised Target Date]
,[Actual Completion Date]
,[Follow-up Status]
,[Change History]
,[Management Status Description]
,[Auditor's Comments])
SELECT CO1.[Order of appearance]
,CO1.[Cycle Name]
,CO1.[Comment Title
,CO1.[Risk Severity Code]
,First(CO1.Recommendation) AS FirstOfRecommendation
,MR1.[Responsible Department]
,First(MR1.[Management Action Plan]) AS [FirstOfManagement
Action Plan]
,MR1.[Target Completion Date]
,MR1.RevisedTargetDate
,MR1.[Actual Completion Date]
,FU1.[Follow-up status code]
,MR1.[Completion Date Change History and Other Comments]
,First(FE1.[Management's Status Description]) AS
[FirstOfManagement's Status Description]
,First(FE1.[Auditor Comments]) AS [FirstOfAuditor Comments]
FROM ([tbl Comments] AS CO1
LEFT JOIN
([tbl Follow-up status codes] AS FU1
RIGHT JOIN
[tbl Management Responses] AS MR1
ON FU1.[Follow-up status order] =
MR1.[Follow-up status code])
ON CO1.[Comment Table counter] =
MR1.[Comment Table counter])
LEFT JOIN
[tbl Follow-up Entries for Findings] FE1
ON MR1.[ID for tbl Management Responses] =
FE1.[ID in tbl Management Responses]
GROUP BY CO1.[Order of appearance]
,CO1.[Cycle Name]
,CO1.[Comment Title]
,CO1.[Risk Severity Code]
,MR1.[Responsible Department]
,MR1.[Target Completion Date]
,MR1.RevisedTargetDate
,MR1.[Actual Completion Date]
,FU1.[Follow-up status code]
,MR1.[Completion Date Change History and Other Comments]
,CO1.[Audit Report #]
HAVING (((CO1.[Audit Report #])="FA-BDI-04-34"))
ORDER BY CO1.[Order of appearance];


Query w/out spaces in object names and w/out accompanying brackets
and w/out table object prefixes. I left the column aliases (for
output) alone.

INSERT INTO AuditFollowUpReport
(Nbr
,ThrustArea
,TitleOfIssue
,RiskSeverityCode
,Recommendation
,ResponsibleDepartment
,ManagementActionPlan
,TargetCompletionDate
,RevisedTargetDate
,ActualCompletionDate
,FollowUpStatus
,ChangeHistory
,ManagementStatusDescription
,AuditorsComments)
SELECT CO1.OrderOfAppearance
,CO1.CycleName
,CO1.CommentTitle
,CO1.RiskSeverityCode
,First(CO1.Recommendation)
AS [FirstOfRecommendation]
,MR1.ResponsibleDepartment
,First(MR1.ManagementActionPlan)
AS [FirstOfManagement Action Plan]
,MR1.TargetCompletionDate
,MR1.RevisedTargetDate
,MR1.ActualCompletionDate
,FU1.FollowUpStatusCode
,MR1.CompletionDateChangeHistoryAndOtherComments
,First(FE1.ManagementsStatusDescription)
AS [FirstOfManagement's Status Description]
,First(FE1.AuditorComments)
AS [FirstOfAuditor Comments]
FROM (Comments AS CO1
LEFT JOIN
(FollowUpStatusCodes AS FU1
RIGHT JOIN
ManagementResponses AS MR1
ON FU1.FollowUpStatusOrder =
MR1.FollowUpStatusCode)
ON CO1.CommentTableCounter =
MR1.CommentTableCounter)
LEFT JOIN
FollowUpEntriesForFindings FE1
ON MR1.IDForTblManagementResponses =
FE1.IDInTblManagementResponses
GROUP BY CO1.OrderOfAppearance
,CO1.CycleName
,CO1.CommentTitle
,CO1.RiskSeverityCode
,MR1.ResponsibleDepartment
,MR1.TargetCompletionDate
,MR1.RevisedTargetDate
,MR1.ActualCompletionDate
,FU1.FollowUpStatusCode
,MR1.CompletionDateChangeHistoryAndOtherComments
,CO1.AuditReportNbr
HAVING (((CO1.AuditReportNbr) = "FA-BDI-04-34"))
ORDER BY CO1.OrderOfAppearance;

It simply looks far more readable to me.

Access does mangle it right away after saving and closing the window
. . . <sigh />.

INSERT INTO AuditFollowUpReport ( Nbr, ThrustArea, TitleOfIssue,
RiskSeverityCode, Recommendation, ResponsibleDepartment,
ManagementActionPlan, TargetCompletionDate, RevisedTargetDate,
ActualCompletionDate, FollowUpStatus, ChangeHistory,
ManagementStatusDescription, AuditorsComments )
SELECT CO1.OrderOfAppearance, CO1.CycleName, CO1.CommentTitle,
CO1.RiskSeverityCode, First(CO1.Recommendation) AS
FirstOfRecommendation, MR1.ResponsibleDepartment,
First(MR1.ManagementActionPlan) AS [FirstOfManagement Action Plan],
MR1.TargetCompletionDate, MR1.RevisedTargetDate,
MR1.ActualCompletionDate, FU1.FollowUpStatusCode,
MR1.CompletionDateChangeHistoryAndOtherComments,
First(FE1.ManagementsStatusDescription) AS [FirstOfManagement's
Status Description], First(FE1.AuditorComments) AS [FirstOfAuditor
Comments]
FROM (Comments AS CO1 LEFT JOIN (FollowUpStatusCodes AS FU1 RIGHT
JOIN ManagementResponses AS MR1 ON
FU1.FollowUpStatusOrder=MR1.FollowUpStatusCode) ON
CO1.CommentTableCounter=MR1.CommentTableCounter) LEFT JOIN
FollowUpEntriesForFindings AS FE1 ON
MR1.IDForTblManagementResponses=FE1.IDInTblManagementResponses
GROUP BY CO1.OrderOfAppearance, CO1.CycleName, CO1.CommentTitle,
CO1.RiskSeverityCode, MR1.ResponsibleDepartment,
MR1.TargetCompletionDate, MR1.RevisedTargetDate,
MR1.ActualCompletionDate, FU1.FollowUpStatusCode,
MR1.CompletionDateChangeHistoryAndOtherComments, CO1.AuditReportNbr
HAVING (((CO1.AuditReportNbr) = "FA-BDI-04-34"))
ORDER BY CO1.OrderOfAppearance;

But the left over results are still more readable than the original.


Sincerely,

Chris O.