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. |
|
|
Thread Tools | Display Modes |
#31
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 04:40 PM |
Problem Updating New Messages from NTTP News Server OE | Chad Harris | Outlook Express | 19 | February 7th, 2005 07:21 PM |