Using MS Access/Verify Data set

Advertisement


Question
QUESTION: Hi Scott,

How vba code (e.g. if statement) should look like to perform the following task.
There is a table that looks like this:
ColumnA    ColumnB
-------------------
  1          
  1          1
  1
  2
  2          
  2          1
  3
  3          1
  3
 ...        ...

ColumnA has many identical values (1,2,3,etc.). For each identical value in ColumnA there should be the only value in ColumnB (and it is always = 1). I need to catch when there is more than one 1 in ColumnB for an identical value in ColumnA. For example:

ColumnA    ColumnB
-------------------
  1          1        
  1          1
  1     
  2
  2          
  2          1
  3
  3          1
  3

ANSWER: I would do a Group BY query, grouping by Column A and summing Column B. Then filtering for any records where the sum > 1. That should give you output you need.



---------- FOLLOW-UP ----------

QUESTION: Thank you, Scott. This is a good solution but not very convenient for me. Essentially, this is a mistake that happens while entering data. I need to catch it when a user clicks Save command button on the form. Due to the complexity of the form (faking crosstab followed by normalization) it is difficult to implement through data validation right on the form. So my goal is when user clicks command button the VBA code should verify if the mistake took place and issue a warning message for the user to correct the entry.

ANSWER: OK, I remember now. But this goes back to what I have said before. You are trying to create an interface that does not match relational design. As I recall I suggested using a temp table, but I believe you rejected that in terms of an unbound form. So you are constantly having to jury rig things.

You have to refresh my memory of how the form is setup so that I can suggest a way to deal with this issue.

---------- FOLLOW-UP ----------

QUESTION: This is how the form is setup:

First, the following tables are created: tblCare, tblHours and tblCareHours for interaction (all Care Type - Hours of Work combinations).

Then a crosstab query qryCrosstab is created:
---------------------------------------------------------------
RANSFORM First(tblCareHour.Available) AS FirstOfAvailable
SELECT tblCare.Care
FROM (tblCareHour INNER JOIN tblCare ON tblCareHour.[Care] = tblCare.[CareID]) INNER JOIN tblHour ON tblCareHour.[Hour] = tblHour.[HourID]
GROUP BY tblCare.CareID, tblCare.Care
ORDER BY tblCare.CareID
PIVOT tblHour.HourOrder;
----------------------------------------------------------------
Obviously, the query is not editable. "Available" here only has one value - 1. This means that for a specific Care Type a user can only allocate 1 time interval

Then a table that stores the results from crosstab is created:
-----------------------------------------------------------------
Public Sub CrosstabAvailability()
'This procedure writes the results of qryCrosstab to a temp table tblCrosstab
On Error GoTo EH
'delete previous temp table if it exists
If Nz(DCount("[Name]", "MSysObjects", "[Name]='tblCrosstab'"), 0) <> 0 Then
   DoCmd.DeleteObject acTable, "tblCrosstab"
End If

'create tblCrosstab
CurrentDb.Execute "SELECT qryCrosstab.* INTO tblCrosstab FROM qryCrosstab;", dbFailOnError + dbSeeChanges

Exit Sub
EH:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly, "Error"
End Sub

As a result we have an editable form that looks like this:

         Hour1(0-15min)  Hour1(16-30mins)  Hour1(31-45 mins)  .....
------------------------------------------------------------------------------
Care Type 1          1          1
Care Type 2          1          
Care Type 3
...

When the formed is filled out and a user clicks command button "Submit" the normalization occurs.

The goal is to capture and inform the user when Submit is clicked that, by mistake, 1 was entered more than once for a specific time interval (goes by 15 mins) under the assumption that a user can only deliver one specific care type within this 15 mins.

Answer
OK, First, I would not use a DLookup against the MSysObjects table like you are doing. Instead use a function like the one found here: http://www.projectperfect.com.au/microsoft-access-sample-10.htm.

Second, you can use the original solution I suggested. You run your Group By against the temp table (tblCrosstab). So when they press the Submit button you do what I suggested to check the input. If it passes muster, then you go on to Normalize the data. If not, you return to the form with a message to correct the data.

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.