Issue with absolute walues in a yes/no table

[Liam Murphy]

I have created a database to track issues with paperwork, and use a yes/no block to identify whether an issue exists for each page. I have finally been successful in running a query that counts each instance of each particular error, by date, but since I am using a yes/no, a yes is, by default, a -1, and when it sums them, I get a negative number. I would like to display positive numbers, so I can export to excel and create a spreadsheet with just the numerical data, and perform various math functions from there. I will post the SQL so that it can be more easily pointed out what I need to fix. Each error type is it's own field, so there are a lot of fields, so it is a bit long. Thanks for any help.

SELECT DISTINCTROW Format$([Data_Entry_Tbl].[SurveyDate],'mmmm yyyy') AS [SurveyDate By Month], Data_Entry_Tbl.Boat, Sum(Data_Entry_Tbl.[SSDS Missing/Wrong Step No]) AS [Sum Of SSDS Missing/Wrong Step No], Sum(Data_Entry_Tbl.[SSDS No Initial And/Or Badge]) AS [Sum Of SSDS No Initial And/Or Badge], Sum(Data_Entry_Tbl.[SSDS Missing Page Number]) AS [Sum Of SSDS Missing Page Number], Sum(Data_Entry_Tbl.[SSDS Wrong Survey Results]) AS [Sum Of SSDS Wrong Survey Results], Sum(Data_Entry_Tbl.[SSDS Survey Results/Type Blank]) AS [Sum Of SSDS Survey Results/Type Blank], Sum(Data_Entry_Tbl.[SSDS Line Item Number Missing]) AS [Sum Of SSDS Line Item Number Missing], Sum(Data_Entry_Tbl.[Block 1 Blank]) AS [Sum Of Block 1 Blank], Sum(Data_Entry_Tbl.[Block 5 Inadequate Description]) AS [Sum Of Block 5 Inadequate Description], Sum(Data_Entry_Tbl.[Block 5 Incorrectly N/A'd]) AS [Sum Of Block 5 Incorrectly N/A'd], Sum(Data_Entry_Tbl.[Block 5 Blank or Not N/A]) AS [Sum Of Block 5 Blank or Not N/A], Sum(Data_Entry_Tbl.[Block 6 Not Checked/Wrong Checked]) AS [Sum Of Block 6 Not Checked/Wrong Checked], Sum(Data_Entry_Tbl.[Block 6 No/Wrong Procedure Name]) AS [Sum Of Block 6 No/Wrong Procedure Name], Sum(Data_Entry_Tbl.[Block 6 No/Wrong Step Number]) AS [Sum Of Block 6 No/Wrong Step Number], Sum(Data_Entry_Tbl.[Block 6 Inadequate Reason]) AS [Sum Of Block 6 Inadequate Reason], Sum(Data_Entry_Tbl.[Block 6 No Reason]) AS [Sum Of Block 6 No Reason], Sum(Data_Entry_Tbl.[Block 7 Incorrect Survey Instrument]) AS [Sum Of Block 7 Incorrect Survey Instrument], Sum(Data_Entry_Tbl.[Block 7 Missing Information]) AS [Sum Of Block 7 Missing Information], Sum(Data_Entry_Tbl.[Block 8 Not Checked]) AS [Sum Of Block 8 Not Checked], Sum(Data_Entry_Tbl.[Block 8 Not N/A]) AS [Sum Of Block 8 Not N/A], Sum(Data_Entry_Tbl.[Block 8 Missing/Incorrect Data]) AS [Sum Of Block 8 Missing/Incorrect Data], Count(*) AS [Count Of Data_Entry_Tbl]
FROM Data_Entry_Tbl
GROUP BY Format$([Data_Entry_Tbl].[SurveyDate],'mmmm yyyy'), Data_Entry_Tbl.Boat, Year([Data_Entry_Tbl].[SurveyDate])*12+DatePart('m',[Data_Entry_Tbl].[SurveyDate])-1;

 

[John W. Vinson]

I have created a database to track issues with paperwork, and use a yes/no block to identify whether an issue exists for each page. I have finally been successful in running a query that counts each instance of each particular error, by date, but since I am using a yes/no, a yes is, by default, a -1, and when it sums them, I get a negative number. I would like to display positive numbers, so I can export to excel and create a spreadsheet with just the numerical data, and perform various math functions from there. I will post the SQL so that it can be more easily pointed out what I need to fix. Each error type is it's own field, so there are a lot of fields, so it is a bit long. Thanks for any help.

SELECT DISTINCTROW Format$([Data_Entry_Tbl].[SurveyDate],'mmmm yyyy') AS [SurveyDate By Month], Data_Entry_Tbl.Boat, Sum(Data_Entry_Tbl.[SSDS Missing/Wrong Step No]) AS [Sum Of SSDS Missing/Wrong Step No], Sum(Data_Entry_Tbl.[SSDS No Initial And/Or Badge]) AS [Sum Of SSDS No Initial And/Or Badge], Sum(Data_Entry_Tbl.[SSDS Missing Page Number]) AS [Sum Of SSDS Missing Page Number], Sum(Data_Entry_Tbl.[SSDS Wrong Survey Results]) AS [Sum Of SSDS Wrong Survey Results], Sum(Data_Entry_Tbl.[SSDS Survey Results/Type Blank]) AS [Sum Of SSDS Survey Results/Type Blank], Sum(Data_Entry_Tbl.[SSDS Line Item Number Missing]) AS [Sum Of SSDS Line Item Number Missing], Sum(Data_Entry_Tbl.[Block 1 Blank]) AS [Sum Of Block 1 Blank], Sum(Data_Entry_Tbl.[Block 5 Inadequate Description]) AS [Sum Of Block 5 Inadequate Description], Sum(Data_Entry_Tbl.[Block 5 Incorrectly N/A'd]) AS [Sum Of Block 5 Incorrectly N/A'd], Sum(Data_Entry_Tbl.[Block 5 Blank or Not N/A]) AS [Sum Of Block 5 Blank or Not N/A],
Sum(Data_Entry_Tbl.[Block 6 Not Checked/Wrong Checked]) AS [Sum Of Block 6 Not Checked/Wrong Checked], Sum(Data_Entry_Tbl.[Block 6 No/Wrong Procedure Name]) AS [Sum Of Block 6 No/Wrong Procedure Name], Sum(Data_Entry_Tbl.[Block 6 No/Wrong Step Number]) AS [Sum Of Block 6 No/Wrong Step Number], Sum(Data_Entry_Tbl.[Block 6 Inadequate Reason]) AS [Sum Of Block 6 Inadequate Reason], Sum(Data_Entry_Tbl.[Block 6 No Reason]) AS [Sum Of Block 6 No Reason], Sum(Data_Entry_Tbl.[Block 7 Incorrect Survey Instrument]) AS [Sum Of Block 7 Incorrect Survey Instrument], Sum(Data_Entry_Tbl.[Block 7 Missing Information]) AS [Sum Of Block 7 Missing Information], Sum(Data_Entry_Tbl.[Block 8 Not Checked]) AS [Sum Of Block 8 Not Checked], Sum(Data_Entry_Tbl.[Block 8 Not N/A]) AS [Sum Of Block 8 Not N/A], Sum(Data_Entry_Tbl.[Block 8 Missing/Incorrect Data]) AS [Sum Of Block 8 Missing/Incorrect Data], Count(*) AS [Count Of Data_Entry_Tbl]
FROM Data_Entry_Tbl
GROUP BY Format$([Data_Entry_Tbl].[SurveyDate],'mmmm yyyy'), Data_Entry_Tbl.Boat, Year([Data_Entry_Tbl].[SurveyDate])*12+DatePart('m',[Data_Entry_Tbl].[SurveyDate])-1;

First off, you can use the Abs() function to convert each number to its
absolute (positive) value: e.g. Abs(Sum Of SSDS Missing/Wrong Step No].

BUT... much more importantly... you should really reconsider your table
structure. You've "committing spreadsheet", storing data in fieldnames. A MUCH
better normalized design would have three tables, e.g.

Data_Entry_Tbl
PagelID <primary key>
SurveyDate
Boat
<other fields pertinent to the page as an entity>

Issues
IssueID <primary key>
Issue <text, e.g. "SSDS Missing/Wrong Step No" or "Block 6 Not Checked">
<any other information about this issue, i.e. how critical is it to the
process>

PageIssues
PageIssueID <autonumber primary key>
PageID <link to Data_Entry_Tbl>
IssueID <link to Issues>
IssueDate
<any information about THIS issue for THIS page>

This will make your counts much easier - just count the number of records in
PageIssues! You could even weight them for importance (in the Issues table) if
that's appropriate. With your current design, if you identify a new issue you
must change the table design, revise all your queries, change the structure of
your data entry form, revise all your reports... with the normalized design,
you add a new row to Issues and you're DONE.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Liam Murphy]

Thank you very much. I am relatively new to Access, and know very little about SQL. This is my third itteration of this db, and have been learning through trial and error what works and doesn't. I'll give yours a try, it seems like it will save a lot of space, as I am going to have tens of thousands of records.

 

[Liam Murphy]

In the third table, since it references previous fields,
would those be lookups, or stay as text? I am going to
play around with it to see how it functions, and to see
if I can sort and add like I need to.

 

[Liam Murphy]

Also, with this setup, how would I enable the ability to assign multiple issues to a particular date and boat? My
first design of this DB had a similar setup for the issues, but I found on the input form, I had to keep
adding input blocks as I found surveys with multiple errors on a page, which is why I went to a yes/no field
for each error type, so I could more easily assign multiple errors to a single survey. I have a minimum of
1 per date (no errors), and an unlimited maximum.

 

[John W. Vinson]

Also, with this setup, how would I enable the ability to assign multiple issues to a particular date and boat? My
first design of this DB had a similar setup for the issues, but I found on the input form, I had to keep
adding input blocks as I found surveys with multiple errors on a page, which is why I went to a yes/no field
for each error type, so I could more easily assign multiple errors to a single survey. I have a minimum of
1 per date (no errors), and an unlimited maximum.

Liam, on these Usenet newsgroups, it's very helpful to quote the previous
text. Not everyone has the same threading of messages as you're seeing; to
find out what you're asking would require a Google groups search.

Please repost with enough information to help someone answer your question.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[John W. Vinson]

Also, with this setup, how would I enable the ability to assign multiple issues to a particular date and boat? My
first design of this DB had a similar setup for the issues, but I found on the input form, I had to keep
adding input blocks as I found surveys with multiple errors on a page, which is why I went to a yes/no field
for each error type, so I could more easily assign multiple errors to a single survey. I have a minimum of
1 per date (no errors), and an unlimited maximum.

ok... found your previous discussion.

You're missing the point. With the normalized structure you don't HAVE any
checkboxes, and you don't NEED the Abs() function. You just count records!

The PageIssues table I'm suggesting would have multiple records for each page.
It's MUCH BETTER than the wide-flat checkbox interface in this regard; you can
add any needed number of records to the table, each with its own PageID (a
Long Integer number foreign key link to the Pages table), its own IssueID (a
Long Integer number foreign key link to the Issues table), a date/time, and
perhaps optional other fields for details of this issue for this page.

You can create a unique multifield index if you wish to *prevent* entering the
same error twice for the same page on the same date, if you wish; but the
default is that there are no limits.

I would NOT use Lookup Fields, period; they don't give you *anything* that you
can't get just as simply by using a form. All the Lookup Field does is conceal
the actual contents of your table behind a combo box.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com