Search results

Hmm, just did a quick and dirty test. Changed Resolution to text(255) instead of memo. The event fired as expected. So what's up with the memo field that it will allow you to bypass this type of validation?

I have a field on which I like to perform some data validation before the record is saved (regardless of either it's an new or existing record). The logic is this: If Closed is not null, then resolution and notified must not be null (have zero length protection at table level). My code is as...

Yes, please. :)

Thanks dallr! Look forward to it.

Ok, this is the database. Above is slightly different than how the database is set out, but the process I describe above is the same and relates to tblPBTC and tblNoAccess. All the personally and property identifiable information have been dropped because of the DPA, which is why you won't...

I have a table that tracks a series of (mostly) date-based events and I'm trying to decide whether to further normalise the table. The process works like this: 1. We are notfied of a problem (PBTCID PK, PropertyID FK, DatePBTC) 2. We send a recorded letter (DateRecLet) 3. We send a 1st class...

Ok, here it is: business logic, current design and possible design. Hopefully this makes sense as I've been putting it together in the presence of a grumpy, chicken pox-covered five-year old. :eek: I would upload the database, but I'm bound by the DPA. If this is better placed in another...

I do agree. You needn't have a service_year field to find overdue services. Been using a similar query to the above, only calculating off days rather than months OR looking for null service_dates which are classed as overdue too. Now this is an interesting bit of kit and warrants some...

Spot on, Brian. It's the year the service is due and it should be in the same financial year as service_date, although that's not always the case and that's where the null service_dates come from. By law, we have to service each year (within 365 days) and I am assume it was easier for the...

Two reasons: 1) I inherited it that way 2) We work off financial years rather than calendar, which makes selection a tad easier. This table contains a lot of information that can be normalised further, but I'm still thinking and testing my way through scenarios. Once I have a bit more time...

Yes, you are right, Brian, that situation above could indeed happen. Not sure when I can do more work on this database, but I will keep you updated as I progress. Thank you again for all your help!

Hmm, got what I expected: all 3027 properties ids with a max service_date or null, as appropriate, and its service_id. Was also able to UNION in the 3rd query rather than having 4th. Now just have to do some serious testing with union query and all the table joins I'll have to do to pull the...

I have been working this problem, hoping to find the answer without someone doing the work for me. Here's what I've found bearing in mind that each service year will automatically have 3027 records generated against it (which = the total number of properties), plus any duplicate services done...

This is close, but not quite. Each financial year, I generate a service_id and service_year for that financial year on each prop_id. In some cases, the service_date might actually be null because we haven't done at least one service in that financial year and that's something I need to know...

I've looked at so many options for writing this query that I can't see the forest for the trees and hoping you kind folks will point me in the right direction. I have a table called services that includes: service_id prop_id service_year (ie: 2007, 2008) service_date (ie: 3/3/08) service_id...