Updating Dates with a Query... or not

[SaraMegan]

Okay, here goes: (Is this something I can do?)

My database tracks all of the files in my office. It stores basic info, like name, account number, addresses, case type, case history, etc. It also has a "tickler" system which gives each of the secretaries a list of files they should get to that day. There is a field for the tickler date, a field for the name of the secretary who gets the tickler, and a checkbox which gets check if the file is urgent and *needs* to be seen on that day. Once the file has been worked on, the secretary will change the date on the database for the next time she needs to see it.

Unfortunately, not all the files can be seen on that day, so I want to set it up so that the tickler date updates itself if it becomes out of date. Do I need to set up an update query? Can I get it to automatically run? And here's the kicker - is there some way it can put the updated ones on top of the list, because though they may not be urgent, they should still have some sort of priority. How do I do that? I don't know anything about writing code for Access, so if I have to do any of that, please show it to me like I'm in Kindergarten...

Also... I currently have all my information on one table... it didn't seem necessary to make a second table because everything was related... but should I be adding another table devoted to the ticklers? Would that make things easier in the long run, or would it not do much at all?

Thanks so much!!!

--Sara

 

[ColinEssex]

Hi Sara

Couple of questions first

Is each file dealt with by the same secretary each time?

I'm thinking you have a "One"(secretary) to "Many"(files) in which case you may need to re-examine your database table structure to have TblSec of SecID and SecName then have a TblFiles including a SecID field.

What do you do if a Sec leaves? Her name will still be in the file.

If a file is not seen on the appropriate day when is the next "tickle" date? tomorrow?

Col

 

[SaraMegan]

Hi, Colin

Yes, every file is dealt with by the same secretary each time. I have thought about what would happen if one left... Couldn't we just add the new secretary and switch the names on the ticklers?

The ticklers aren't the primary function of this database... it's for tracking all their files and replacing their current index card system. The Primary Key is the name of the Person/Business.

I'm not a fan of the idea of restructuring my database... but if I need to, then I need to... but I'm not sure what this stuff was about:

"I'm thinking you have a "One"(secretary) to "Many"(files) in which case you may need to re-examine your database table structure to have TblSec of SecID and SecName then have a TblFiles including a SecID field. "

Especially that last part...

I'm sorry... I was a theatre major who took one computer class in college (HTML). I just started with this a few weeks ago with no prior knowledge or experience... Just me, Access, the index cards, and a copy of MS Access for Dummies...

thanks for your patience and help. For real.

--Sara

 

[SaraMegan]

PS

Yeah, the new tickle date becomes the next day.

 

[ColinEssex]

Hi Sara

What I was trying to explain is that to avoid big headaches later it may be an idea to consider changing the structure of your tables. You need 2 tables - one for the Sec details and one for the files they use. The Sec table should have an ID_No, SecName etc etc. The files table should have the files detail (whatever) AND the SecID_No who is dealing with it.

The date of tickle (sorry - but the thought of secretaries and tickle makes me smile ) can be updated by using an update query with the criteria being if the tickle date is still today (ie, it hasn't been updated) then update it to today+1 (tomorrow)

I'm a bit unclear if you have forms and the like, but you could run the update query at the end of the day or something. The updated ones 'on top' can be done with the sort order.

Have I helped or hindered you

Col

 

[SaraMegan]

Hey there,

You're definitely helping me work it out... but I have some more questions... bear with me, here... let me explain what I've got set up right now...

Okay, everything is in one table. I have one main form for everything which is split into three pages using tabs. I have five different tickler queries set up: one for each secretary, and one for all ticklers. When run, these queries show all of the current day's ticklers in a nice tidy list. I also have buttons set up on the form that will run the day's ticklers. It works pretty well so far.

So I copied my relevant database stuff into another database to try to make this relational database you were talking about without murdering brutally what I've already done, and I guess I just don't understand how it works. How do I get the form to send data to the other table? (My "Dummies" book just doesn't cut it for me any more...) And how do I decide what my relationships should be?

I took these things off my old table and put them into the new table:

Tickler (which is a date field)
Urgent (Which is my checkbox)

And overlapped with these fields:
Name (of business or claimant)
SSN/ID (of business or claimant - this is not the primary key in my first table because of duplicates due to x-referencing)
Tickler for (which is the name of the secretary)

Are all of these necessary? Do I need both Name and SSN/ID? Do I need to re-enter all of the Names and SSN/IDs?

Finally, is it possible to, instead of doing today's date+1 and run it at the end of the work day, dmake ">Date()" become "Date()" and run it first thing in the morning?

Does that give you more information? Aren't you sorry you signed on to this little project?

--Sara

 

[ColinEssex]

Hi Sara

Is it possible for you to do a copy of what you have and zip it then email it to me? You can delete confidential stuff and add dummy stuff for demonstration purposes.
If you can then compact it first then zip it.

Failing that - we'll have to do things in bite sized pieces but we are on the right track.

Col

 

[SaraMegan]

I can try to do that... but I don't have a zip program on my pc. it's not huge... compacted and with one table, it's only 836kb... Let me know what you want me to do with that...

--Sara

 

[ColinEssex]

You can download a Zip programme. Hayley did it the other day. Our email won't accept unzipped stuff (viruses and the like)

Col