Database Help

[richiemc]

Hi Everyone,

I am an in home carer that manages medications for a number of old people's medication and I want to keep track of when I should be going to pick up their medication from the pharmacy I have an excel sheet to keep track of them for me but I would like to be able to give a list to a new co-worker so she can go and pick up the medicines from the local pharmacy.

So the table looks like this

[Name] [Last Picked Up] [Pharmacy Name] [Number of days Medication] [Next Pick Up date]


Ideally I would like to have a system whereby I could print a list of today's pick ups (a sort of checklist) and when they are picked up I would like to have a button which would calculate the next repeat date based on the number of days medication received.

I know this is possible to setup with auto-filters in excel but I just think that if I could design a nice tidy Access database errors would be minimized. Can anyone help me on how i should do this I'm lost when it comes to queries, reports and relationship design.

Please ask me to clarify if i'm not being clear anywhere
Thanks
richiemc

 

[office politics]

build database with columns (leave off next pickup, we really dont need it but if u want theres the link below)

**Creating a Calculated Field with Access 2007 - For Dummies

enter some data

create a new report

tell it to only show records where =dateadd("d", [last picked up], date()) <= Date()

 

[richiemc]

hi office politics,

thanks for the reply, the reason why i want the next pick up date is because the pick ups tend to be at regular intervals, thus if i have a last pick up date and an interval length (no of days meds) i can have the database calculate the next pick up. then i would also like to have a function where todays pick ups can be marked as done (i.e. last pick up now set to todays date) this way the cycle can continue providing the intervals are constant (which most but not all of the time they are...usually 7/28/30 days) am i being clear?

Sorry i'm a real novice with access!!

and i suppose the critical date is the next pick up, not the last

 

[strollin]

I don't think you need to bother with an Access db for this, Excel can handle it effortlessly. I'm not sure what you are talking about when you say "...this is possible to setup with auto-filters in excel..." Excel has a very powerful macro language built-in and also has many DB functions built-in.

To calculate the "Next Pickup Date" field in Excel the formula (in the "Next Pickup Date" field) would be simply be =B2+D2, based on your table example above where column B would have the "Last Picked Up" date and column D would contain the "Number of Days Medication". Whenever the "Last Pickup Date" was updated, the "Next Pickup Date" would be automatically calculated based on the "Number of Days Medication" value. You could easily sort the Excel table based on the "Next Pickup Date" column and then select and print the records for the desired date to create the list of medicines to be picked up.

 

[richiemc]

hey strollin,

ya i agree excel can handle all this quite simply, but most of the people i work with are non-excel savvy so spreadsheets strike fear into their hearts... leading to mistakes and wasted time when i'm not around to supervise. the attraction i have to access is the possibility to have a nice simple GUI fronting the whole process leaving the manipulations to be done using command buttons. if i can get it to work it would be awesome!

as for the autofilters i would use them to show/hide dates to be printed, but ya i guess printing from a selection is pretty simple too.

thanks for the reply

 

[strollin]

How many people's medications do you need to track? If it's not in the thousands then I still think Access would be overkill. You don't need the relational or reporting capabilities of Access since you have basically a simple, flat file DB.

As I mentioned earlier, Excel has a very powerful macro language (VBA). It can be used to create a GUI with input forms and everything else you would need. You can create command buttons and even date-pickers to be able to select the dates from a pop-up calendar.

Your answer may be that you don't know how to implement that in Excel but it's obvious you don't know how to do that in Access either. Both will require a learning curve on your part. It's my opinion that Excel would be the simpler option. Over the last 25 years, I've developed apps using Excel and Access as well as large DB systems such as Oracle, MS SQL Server and DB2. The heavier duty DBs don't start making themselves really worth the effort until you are dealing with very large data sets containing thousands or millions of records.

Here's a link to a little course that may help you decide which would work best for you: http://office.microsoft.com/en-us/training/choose-between-access-and-excel-RZ010253998.aspx

Good luck in finding a solution that works for you.

 

[richiemc]

awesome, I didnt realise there was that capability with excel and yes your right i need to learn either way.... thanks for the help hopefully it will start me on the road