REWARD-Is this even possible? Auto-Schedule

[andrewghunt]

Hi everyone, just joined, just started making databases for a living (go figure). I'm up for a contract tomorrow, and I know how to build everything they are requesting, except this one thing, is it even possible? If your solution works and I get the job, there's a $20 paypal reward in it for you, that's Canadian dollars, so something like 10 pounds? aanyway here is the problem:

Alot of the database is entering results from audits gathered from different locations/branches. Every 3 years, a branch is up for an audit. Any year they are not up for an audit, they are mini-audited. So the rotation goes, for example:
2009-audit
2010-mini audit
2011-mini audit
2012-audit
2013-mini audit

...and so on. The thing is, they want the database to automatically alert them with one picture on a report/the branch's detail form if they are up for an audit in the current year, and another picture if they are up for a mini audit in the current year.

I know it does'nt seem very accurate, but that's what they want. SO,
2009-red flag appears on report/form
2010-green flag appears on report/form

The tricky part is, (well, tricky for me), each branch has a different rotation/schedule of which years they get which audit.

PLEASE help, I need to sound like I know what I'm talking about tomorrow. If I'm unclear please let me know
Thanks!

 

[CameronM]

Here's one possible solution - create a table linking Branches and the years in which they have a full audit. End users would set their current Branch location during login.

Then you only require two queries, the first "qryAuditYear", returns branches being audited in the current year.

The second "qryBranchAudit" returns all branches with a value of true or false depending on the results of "qryAuditYear". You could add a WHERE clause to this query to filter down to the required BranchID.

You can easily use the results of "qryBranchAudit" with conditional formatting in a form to toggle on or off the image/textbox or whatever you need to show.

Table: AuditBranch
Fields: BranchID, BranchName
 
Table: AuditYears
Fields: AuditID, BranchID, AuditYear (4 character text)
 
Query: qryAuditYear
SELECT AuditYears.AuditID, AuditYears.BranchID, AuditYears.AuditYear
FROM AuditYears
WHERE AuditYears.AuditYear=Year(Date())
 
Query: qryBranchAudit
SELECT AuditBranch.BranchID, AuditBranch.BranchName, IIf(IsNull([AuditYear]),False,True) AS FullAudit
FROM AuditBranch LEFT JOIN qryAuditYear ON AuditBranch.BranchID = qryAuditYear.BranchID

 

[CameronM]

PS. Best of luck for the Job.

 

[Atomic Shrimp]

I did something like this with an auto-stock-replenishment program - different branches had different desired order cycles with different intervals (in weeks) and not in sync with each other.

The way I did it was to create two number (integer) fields for each branch record - one that expressed how many weeks there were between the orders for that branch, the other that expressed where that branch was within its order cycle.

Each week, when orders were processed, all of the Order Position fields were incremented by one - those that matched the Order Cycle value got an order, and the Order Position field was reset back to 0, starting the cycle again for that branch.

So if a branch needed orders weekly, the Order Cycle would be 1 and the order position would start at 0 - each week, Order Position would be incremented, would match Order Cycle, generate an order and get reset back to zero.

If a branch needed orders fortnightly, the Order Cycle would be 2 and the order position would start at 0 - each week, Order Position would be incremented - it would match Order Cycle only on every second week, when it would generate an order and get reset back to zero.

If a branch needed orders every three weeks, the Order Cycle would be 3 and the order position would start at 0 - each week, Order Position would be incremented - it would match Order Cycle only on every third week, when it would generate an order and get reset back to zero.

If two branches each wanted orders fortnightly, but on opposite weeks from each other, they would both have an order cycle value of 2, but one would start with Order Position 0 and the other on Order Position 1.

 

[andrewghunt]

Thanks Cameron, I definitly didn't make a fool of myself in the interview! I have not found out yet if I got the contract, but please forward your paypal address to andrewclb@hotmail.com anyway, as I can definitly use this macro information in the future. thanks!

 

[CameronM]

Hi andrew,

No need for the reward - why not give it to a worthy cause.

Just happy to help. Maybe you can shout me a beer next time I am in Canada - which is a pretty safe deal since I was last there about 15 years ago

 

[andrewghunt]

Thanks I really appreciate the help A beer on me any time (I would post on the American equivalent of this forum but no one ever replys to anything there)

I got the job today so I'm starting to sweat a little about this code hahaha. I got the first query working fine until I realized that the AuditYear field would have to contain more than one date, for the future, like "2009, 2012, 2015..." etc, so the query doesn't return any results any more. How can I make it look and see if a field CONTAINS a certain value, rather than being equal to it?

 

[CameronM]

The query will still work, but what you want to do is add a new record for each year, rather than adding the years into the same record. I actually made the AuditYear field in the table AuditYears only 4 characters long so that it would only hold one year.

For example, I added three new rows the the AuditYears table for BranchID=1.

AuditID, BranchID, AuditYear
1, 1, 2009
2, 1, 2012
3, 1, 2015

The WHERE clause in qryAuditYear checks for only the current year i.e. 2009. AuditYears.AuditYear=Year(Date()).

As 2009 is already in the list, qryBranchAudit returns -1 (true). You can test qryBranchAudit works, by changing the 2009 to 2008 and re-running the query and you should get 0 (false).

 

[andrewghunt]

Alright yes that works nicely. I think I'm still missing something though. Is there supposed to be a relationship between the two branchIDs?

 

[CameronM]

Yes, you can certainly add a cascading delete relationship between the tables, as this will ensure that records get deleted from AuditYears, when the parent record in AuditBranch is deleted.