Help applying validation rules to data entry form

[LLJ]

I'm working on a 'hobby' db for tallying votes for 'book of the year'. This db has a short shelf life - about a month or two. Three main tables: Books, Publishers and Votes. Typically there are about 150-200 Book records, 75-100 Publishers, and 1500-2000 Votes. (The Books & Publishers tables are built and indexed properly, with referential integrity applied, etc., but Data integrity, performance, etc., are not significantly important here because of low record count, short shelf-life, etc.)

For ease of data entry, I'd prefer to enter these votes on a datasheet and not on a form/subform. I'm trying to build data entry form that applies these rules and checks votes invalid when appropriate:



Voter must vote for two and only two book titles. If vote count <> 2, all votes are flagged invalid.
Voter may not vote for two books by same publisher. If this condition is not met, all votes are flagged invalid.

I appreciate your help with these validation rules. Here's a screenshot that shows the Votes table as it's currently structured with sample data:

[CJ_London]

since this appears to be dynamic think you need a separate queries to check validity

perhaps something like for Q1

SELECT VoterID
FROM Votes
HAVING Count(VoterID)=2
GROUP BY VoterID

will list all voters who have voted twice

and for Q2
SELECT DISTINCT VoterID
FROM Votes
Having Count(VoterID)=1
GROUP BY VoterID, Publisher

will list all voters who have not voted for more than one publisher

then your combined query

SELECT Votes.* (Q1.voterID is not null and Q2.voterID is not null) as Valid, Switch(Q1.voterID is null,"Invalid - vote count<>2",Q2.voterID is null,"Invalid - publisher not distinct") as Notes
FROM (Votes LEFT JOIN Q1 ON Votes.VoterID=Q1.VoterID) LEFT JOIN Q2 ON Votes.VoterID=Q2.VoterID

[LLJ]

Thanks for the reply. It's clear, though, that the info I posted above is incomplete, and probably confusing!

First of all, I didn't make it clear that the values in the 'Notes" field were entered by me for illustration purposes only, to explain the validation rules I'm trying to apply. Those values are not being entered, manually or automatically, in the data entry process, and would not be available in a query.

Also, your query approach is a) only showing valid responses (I want to be able to to build a query that shows a total tally of both valid and invalid votes at the end of vote collection), and b) required as a separate action after the data entry process, where I'm looking to set the 'Valid" flag dynamically as the data is entered.

I should add that this is not totally 'throw away' code I'm looking for - it's something I've been managing in an excel spreadsheet for a number of years and expect to be doing for some years to come. Also, I've managed to build validation formulas in Excel that set the 'valid' flag correctly for all the rules (including some others I haven't defined here) except the publisher count rule. That's the one I'm most hoping to get some help for.

[CJ_London]

a) only showing valid responses (I want to be able to to build a query that shows a total tally of both valid and invalid votes at the end of vote collection

no - its not

b) required as a separate action after the data entry process, where I'm looking to set the 'Valid" flag dynamically as the data is entered.

you shouldn't be storing these values since they change - otherwise you would get

voter...publisher valid
abc…...123...…….false
abc…….345...…… true
abc…...567...…….false

[LLJ]

Apologies - I wrote my reply last night after a quick (and inaccurate) read of your message, and it was 98 degrees at the time. Hot weather = brain freeze.

I've built out your suggested queries and I'm getting desired results (I had to add a group by clause to the combined query - does that sound right?). Now I'll play around with adding two other validation rules, following the same ideas for Q3 and Q4. May need your subsequent help with that. It's possible for a vote to fail more than one validation rules - do you see any concerns with that?

you shouldn't be storing these values since they change - otherwise you would get

I understand it's not best practice to store the publisher in the votes table, but it's not a concern in this instance. The voter is voting on the book, which has one and only one publisher, which doesn't change,.

[CJ_London]

I understand it's not best practice to store the publisher in the votes table

wasn't talking about the publisher - I was referring to the valid field

and you said the rule was 'Voter may not vote for two books by same publisher'

I had to add a group by clause to the combined query - does that sound right?).

No - add DISTINCT to the first query

[LLJ]

Adding DISTINCT to q1 and removing group by from Combo query gives me duplicates for the valid votes.

[CJ_London]

should not be happening - show the sql you are actually using

[LLJ]

Thanks - fixed it by removing Votes.Publisher from Q2 select..,

Q2:

SELECT DISTINCT Votes.VoterID, Votes.Publisher
FROM Votes
GROUP BY Votes.VoterID, Votes.Publisher
HAVING (((Count(Votes.VoterID))=1));

[LLJ]

I have successfully added two other validation rules to the combined query, and I'm now getting the correct results for data entry. I'll do a little more sample data input, but it looks like I'm good to go.

Thanks for your help, Ajax. I would never have dreamt up those nested queries on my own (and I still don't understand why one of them works, but I'll let it go ).