Conditional Query

[LFC]

I've been racking my brain trying to think of the best way to do this, but have no idea how. I have line items that get audited with different frequencies (i.e. Monthly, Mondays, Twice a week, Every 2 weeks). I'm trying to get a query that will show which ones have not yet been entered for a specific date that need to be entered. I have a query showing the last audit date that works fine, but I'm struggling with where I should go from here.

Thanks

 

[vbaInet]

Two criteria, the date range and Is Null. Or use the Unmatched Query Wizard if that helps.

 

[jzwp22]

It sounds like you need to determine the difference in days between the last audit date and today's date to see if it exceeds the frequency. Relative to your frequencies, I get monthly (every 30 days), twice a week (every 3 days or so), every 2 weeks (every 14 days), but Monday's is a little odd in that it implies 2 criteria--weekly and a specific day of the week. How do you have your frequency value set up in the record, as text or as a number (of days)?

I would recommend having a table of the frequencies

tblFrequencies
-pkFrequencyID primary key, autonumber
-txtFrequency (your descriptive name)
-longFreqDays (a numerical field representing the # of days in the frequency)


In your item table, just reference the pkFrequencyID as a foreign key.


Since an item can have many audit dates, I assume that you have those detail records in a separate but related table and that you are pulling the most recent with an aggregate query that utilizes the max(auditdatefield) and grouped by the itemID

First I would construct a new query that joins the item table to the frequency table and select the relevant fields. I'll call this qry1


Then I would create another query that includes the query just created and your aggregate query. You can then do some evaluations using some calculated fields




SELECT item, lastauditdate, longFreqDays, dateadd("d",longFreqDays,lastauditdate) as NextAuditDue, IIF(NextAuditDate>Date(),"Overdue","ONTime") as AuditStatus
FROM qry1 inner join youraggregatequery ON qry1.ItemID=youraggregatequery.ItemID

 

[LFC]

I have the field set up as text. The one thing that I question is what to do for the amount of days if it's supposed to be Tuesday/Thursday or Monday/Wed/Friday because the length would be messed up by the weekends(sorry i forgot to include these when i posted earlier. The other thing would be, say someone does the audit on a Tuesday because they forgot to do it on Monday, then the frequency would be off by a day, right?

vbaInet, wouldn't that method just be showing which ones haven't been done over a period of time? I'm trying to have it so on the day of you can see which ones are supposed to be done/by the end of the day you can see which ones weren't done. With that way it seems like a Monthly torque would end up showing up on every day.

 

[vbaInet]

Then you need Not Is Null plus the date criteria. And your join must include all records from the main table.

 

[jzwp22]

Tuesday/Thursday or Monday/Wed/Friday because the length would be messed up by the weekends(sorry i forgot to include these when i posted earlier. The other thing would be, say someone does the audit on a Tuesday because they forgot to do it on Monday, then the frequency would be off by a day, right?

Yes on both counts, so we need to develop a way to capture the day interval or the days of the week depending on the "type" of audit frequency.

Perhaps something like this, but I am just thinking out load here...

tblFrequencyType (by day or by day of week)
-pkFreqTypeID primary key, autonumber
-txtFreqType

tblFrequency
-pkFreqID primary key, autonumber
-txtFrequencyName
-fkFreqTypeID foreign key to tblFrequencyType

For those audits that occur by at regular day intervals: monthly, weekly, twice monthly, you will have 1 value related to each frequency. However, for those audits that occur on days of the week, you will have multiple values associate with the frequency. This describes a one-to-many relationship which requires a separate table.

tblFreqTimes
-pkFreqTimeID primary key, autonumber
-fkFreqID foreign key to tblFrequency
-longInterval

The longinterval field will hold either the number of days between audits or the day of the week (depending on the type). Assuming that Sunday is the first day of the week, if an audit needs to be carried out on Monday, Wednesday and Friday, you would have 3 records in tblFreqTimes where longinterval=2, 4, 6 respectively (you will have to set how you want to define the day of the week number).

Your table that holds the audit dates will also have to reference the fkFreqTimeID so that you can correlate the frequency or day of the week with the audit date. Of course, your query will become more complicated.


Any other ideas?

 

[LFC]

Not really anything too useful. I was hoping for a quick way, so i'd been trying a huge IIF statement, but I'm thinking that is probably not the best way to go about it.

IIF(([Day_Freq] ="Weekly" Or "Monday" Or "Tuesday" Or "Wednesday" Or "Thursday" Or "Friday"), DateAdd("d",7,([Audit_Date]))<Date(), IIF(([Day_Freq] = "Monthly"), DateAdd("d",30,([Audit_Date])) < Date(), IIF(([Day_Freq] = "1/10"), DateAdd("d", 14, ([Audit_Date])) < Date(), ""))

This is what i had so far. It's not showing a lot of potential though.

 

[jzwp22]

I think a table based approach to capture the interval information will probably be best in the long run especially if you add new interval/frequencies in the future. It would give you the most flexibility. From there, it might be more advantageous to develop a custom function to determine the audit status of the item rather than the IIF() function approach.

Perhaps others on the forum might have an easier way to handle this?