Search code examples
databasems-accessscriptingautomationopenoffice-base

Cloning a PHP/MySQL database app (w/ some automation) in MS Access or OpenOffice.org Base

I wasn't sure whether to ask this here or on SuperUser, so I apologize if it doesn't belong here.

I created a small PHP/MySQL database app to manage the customer loyalty data for my mom's shop, intending to set it up locally on her cash register computer with XAMPP. However, I've been asked to reimplement the system in a GUI relational database such as MS Access or OpenOffice Base, primarily so that she can do things like mail merge and graphical reports with a GUI (that I don't have to write).

I can easily replicate my MySQL table structure and relationships, and create a few of the more basic forms and reports, but I've never done any scripting, macros etc in Access or Base. My PHP handled a lot more than just form input, there was some scripting involved that I don't know how to implement in Access / Base. Worth noting: if I end up using Access, it'll be Access 2007.

Here's a quick overview of what I'm trying to make, in case it helps. Sorry for the length.

The business is a take & bake food market, and the database is replacing a physical stamp-card loyalty system. Each customer gets a stamp on their card for every $25 they spend. They earn free meals as follows:
- On the 8th stamp, they earn a free side dish.
- On the 16th stamp, they earn a free regular size meal.
- On the 24th stamp, they earn a free family size meal, and their card resets to zero stamps.
The date of each stamp must be recorded (otherwise I'd just increment one field instead of having a stamps table).

I have 3 tables: customers, stamps, and freebies. customers has a 1-to-many relationship with both stamps and freebies.

  • customers is a simple contact list.
    columns: ID, firstname, lastname, email, phone
  • stamps keeps records of each stamp earned.
    columns: ID, customerID, date, index (1-24; the Nth stamp on that customer's card)
  • freebies keeps records of each free meal they have earned.
    columns: ID, customerID, date, size, is_redeemed

Here's the magic from my PHP that I don't know how to implement in Access/Base:

  1. When a user selects a customer and clicks an "add a stamp" button:

    • stamps is queried to grab the index from the last stamp for that customer => local variable N
    • if N == 24, set N = 0. Increment N by 1.
    • a record is inserted to stamps with the current date, customer id and an index of N
    • if N == 8, 16 or 24 a record is inserted into freebies with the appropriate size and an alert appears to notify the user that the customer earned some free shit.

  2. Some kind of "view customer" page (form? report?) that shows all the stamps and freebies they've earned, with "redeem" buttons next to the freebies that have not been redeemed.

In general I need to make it fairly idiot-proof and "big-button" -- automation wherever possible -- cashiers at the shop should be able to use it with no prior knowledge of databases.

Is this practical in a program like Access or Base, or should I just convince her to use my PHP version? If I need to write code, what language(s) do I need to teach myself? Should I be structuring my data differently? I'm not sure where to start here.

Solution

  • My suggestion: don't do it. Run a mysql server on the PC in question, have your PHP app as the front end for the cashiers, and then if you want MS Access's reports feature, just have Access connect to the mysql database with ODBC.

    The best implementation is quite frequently the one you already have.