A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

General solution for missing sequence numbers



 
 
Thread Tools Display Modes
  #31  
Old December 7th, 2005, 01:40 PM posted to microsoft.public.access,microsoft.public.access.queries,microsoft.public.access.formscoding
external usenet poster
 
Posts: n/a
Default General solution for missing sequence numbers

"Chris2" wrote in message
...

"Peter Danes" wrote in message
...
I'd be interested in your thoughts on how it improves readability.



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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



  #32  
Old December 8th, 2005, 05:27 AM posted to microsoft.public.access,microsoft.public.access.queries,microsoft.public.access.formscoding
external usenet poster
 
Posts: n/a
Default General solution for missing sequence numbers


"Douglas J Steele" wrote in
message ...
"Chris2" wrote in

message
...

"Peter Danes" wrote in message
...
I'd be interested in your thoughts on how it improves

readability.


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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)




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.


  #33  
Old December 8th, 2005, 12:55 PM posted to microsoft.public.access,microsoft.public.access.queries,microsoft.public.access.formscoding
external usenet poster
 
Posts: n/a
Default General solution for missing sequence numbers

"Chris2" wrote in message
...

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


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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



  #34  
Old December 8th, 2005, 04:14 PM posted to microsoft.public.access,microsoft.public.access.queries,microsoft.public.access.formscoding
external usenet poster
 
Posts: n/a
Default General solution for missing sequence numbers

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



"Chris2" píše v diskusním
příspěvku ...

"Peter Danes" wrote in message
...
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.IDInTblManagem entResponses
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.




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to look for a sequence of numbers in a string? brett General Discussion 2 October 27th, 2005 01:44 AM
Checking Winning Numbers in the Lottery. Ann General Discussion 4 May 18th, 2005 10:55 AM
sequence numbers su su General Discussion 4 May 12th, 2005 02:51 AM
Generating excel combinations mark4006 General Discussion 2 March 6th, 2005 05:40 PM
Problem Updating New Messages from NTTP News Server OE Chad Harris Outlook Express 19 February 7th, 2005 08:21 PM


All times are GMT +1. The time now is 12:43 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright Š2004-2024 OfficeFrustration.
The comments are property of their posters.