Here are some tech questions that I don't really understand
May 21, 2010 9:03 AM Subscribe
No one here knows anything about databases or web stores, and our current ones are very bad. Do you know things?
I started a new job about a month ago after Big Company bought Little Company. I work in the department that is now comprised of what was Little Company. LC holds training and compliance seminars internationally. These seminars vary in cost, length, and location. LC was owned by an incredibly nice older couple who didn't know how to use technology. What I am working with currently is Access 2003 and a basic website.
My tech ability is pretty standard for someone my age, I think. I have never done any programming except with Scratch (I know, not really programming). I am relatively good with Excel, and have about 2 years of website maintenance/management experience. I am very, very un-knowledgeable about Access; I don't even know how to say what I want.
So there are two main problems. We have spoken with tech about them but they are busy, and not quite sure (this is very different from what most of the company does).
1. The database
I have never worked in Access. I have used SLX and SalesForce, but not for anything like this. Access seems to be a clusterfuck that our tech dept doesn't know how to use. I suspect there may be a better database for our use.
The things I want it to do, it doesn't do. I am not sure if this is user ineptitude. We track four things - contacts (upwards of 22,000), sessions (1,000 and growing), hotels (~400), and the course catalog. These cross-reference each other (contacts come to the seminar, which takes place at a hotel and is a specific course).
Access also doesn't output what I would like. For example, it would be great if I could export an xls of who went to a seminar and if they have paid. I can't do that without deleting a billion extraneous columns. Or get a list of the email addresses of people attending a seminar. Instead, I have to print a list of attendees, and then go through the contacts form. Also, searching kind of sucks.
Is there a better database for our use? Or can Access be optimized to not be so dumb? If the latter is true, can I (a relatively intelligent person, I think) learn enough in the next month to fix it?
2. The website
The current website allows people to register with a form. Then, I manually input their data into Access and create and send invoices.
Tech pitched us a site based on Shopify. Unfortunately, it looks like the registrants would have to pay with credit cards with USD. This is problematic, as many of our registrants cannot do so.
What would be ideal is if, like an airline site, you could "reserve" a product (a seat in the seminar) and hold it for a few weeks. The admin could remove the hold and mark payment made. Or, if rather than using a credit card, you could select an alternate means of payment.
Are there any out-of-the-box sites that do this? Or do you know any work-arounds for Shopify?
3. The combination
It would make my life (and, in turn, everyone in my department's life) so much easier if the web forms used when registering for a course online could automatically pull to a database. This is my dream. No, I really had a dream about it. It was sad when I woke up.
I started a new job about a month ago after Big Company bought Little Company. I work in the department that is now comprised of what was Little Company. LC holds training and compliance seminars internationally. These seminars vary in cost, length, and location. LC was owned by an incredibly nice older couple who didn't know how to use technology. What I am working with currently is Access 2003 and a basic website.
My tech ability is pretty standard for someone my age, I think. I have never done any programming except with Scratch (I know, not really programming). I am relatively good with Excel, and have about 2 years of website maintenance/management experience. I am very, very un-knowledgeable about Access; I don't even know how to say what I want.
So there are two main problems. We have spoken with tech about them but they are busy, and not quite sure (this is very different from what most of the company does).
1. The database
I have never worked in Access. I have used SLX and SalesForce, but not for anything like this. Access seems to be a clusterfuck that our tech dept doesn't know how to use. I suspect there may be a better database for our use.
The things I want it to do, it doesn't do. I am not sure if this is user ineptitude. We track four things - contacts (upwards of 22,000), sessions (1,000 and growing), hotels (~400), and the course catalog. These cross-reference each other (contacts come to the seminar, which takes place at a hotel and is a specific course).
Access also doesn't output what I would like. For example, it would be great if I could export an xls of who went to a seminar and if they have paid. I can't do that without deleting a billion extraneous columns. Or get a list of the email addresses of people attending a seminar. Instead, I have to print a list of attendees, and then go through the contacts form. Also, searching kind of sucks.
Is there a better database for our use? Or can Access be optimized to not be so dumb? If the latter is true, can I (a relatively intelligent person, I think) learn enough in the next month to fix it?
2. The website
The current website allows people to register with a form. Then, I manually input their data into Access and create and send invoices.
Tech pitched us a site based on Shopify. Unfortunately, it looks like the registrants would have to pay with credit cards with USD. This is problematic, as many of our registrants cannot do so.
What would be ideal is if, like an airline site, you could "reserve" a product (a seat in the seminar) and hold it for a few weeks. The admin could remove the hold and mark payment made. Or, if rather than using a credit card, you could select an alternate means of payment.
Are there any out-of-the-box sites that do this? Or do you know any work-arounds for Shopify?
3. The combination
It would make my life (and, in turn, everyone in my department's life) so much easier if the web forms used when registering for a course online could automatically pull to a database. This is my dream. No, I really had a dream about it. It was sad when I woke up.
re: 1.
Access can do the things you want it to do, including export excel spreadsheets with only the columns you want. If you don't understand how to use a database and how to build queries, forms and reports, then you probably need to find someone who does.
posted by utsutsu at 9:28 AM on May 21, 2010
Access can do the things you want it to do, including export excel spreadsheets with only the columns you want. If you don't understand how to use a database and how to build queries, forms and reports, then you probably need to find someone who does.
posted by utsutsu at 9:28 AM on May 21, 2010
Everything you want to do is possible using the technology you have, but will require someone with expertise in databases and web sites to do number 3.
You can learn enough Access (specifically SQL) to do #1 in a relatively short time. Any random MS Access book should teach you enough.
You can also find a non-overly technical solution to #2. There's lots out there.
posted by blue_beetle at 9:50 AM on May 21, 2010
You can learn enough Access (specifically SQL) to do #1 in a relatively short time. Any random MS Access book should teach you enough.
You can also find a non-overly technical solution to #2. There's lots out there.
posted by blue_beetle at 9:50 AM on May 21, 2010
( I found that link by googling "online event registration" )
posted by blue_beetle at 9:51 AM on May 21, 2010
posted by blue_beetle at 9:51 AM on May 21, 2010
I suppose I'm only going to reiterate what was said but I feel it's important to be aware of your limitations. By "your" I mean "you" and not the tools you have. You seem to be critiquing something that you admit you don't fully understand. The tools are completely adequate for the size database you mention. If it is not outputting what you'd like it really isn't the fault of the tool. There are many ways to approach this problem with a web front end and, in the end, it may be best for your company to purchase a solution if they don't have the skills in-house.
By the way, item #3 is not a dream... it is a large part of how web sites are designed now: form front-end/database backend.
Good luck!
posted by mcarthey at 10:04 AM on May 21, 2010
By the way, item #3 is not a dream... it is a large part of how web sites are designed now: form front-end/database backend.
Good luck!
posted by mcarthey at 10:04 AM on May 21, 2010
Response by poster: I am very, very un-knowledgeable about Access; I don't even know how to say what I want.
Well, that's the problem then, I suppose. (nb: "un-knowledgeable" is so not a word. I think I meant "ignorant".) Was hoping that I could just cobble something together; looks like I'll have to talk to people about getting someone who knows what they're doing in.
Any additional insights appreciated!
posted by quadrilaterals at 12:00 PM on May 21, 2010
Well, that's the problem then, I suppose. (nb: "un-knowledgeable" is so not a word. I think I meant "ignorant".) Was hoping that I could just cobble something together; looks like I'll have to talk to people about getting someone who knows what they're doing in.
Any additional insights appreciated!
posted by quadrilaterals at 12:00 PM on May 21, 2010
Best answer: Of 2,
there are better databases, but that's not your problem. You don't understand tables yet. They are not fancydumb spreadsheets. Changing DBs will not help.
To do what you want you query the database and ask it to produce what you want from it. You don't say how. You say what you want. It's the job of the DB to figure out how.
From within MSFT Access, construct a query. Ask for the columns you care about, perhaps conditional on some value in the row. Order them. You could then take that result table and save it to a spreadsheet, if you were so inclined. A smart person would save the query, so it's always up-to-date and refer to the result table instead of saving a stale spreadsheet somewhere.
There are graphical query builders in Access, and a text box here will not convey how you use them, but here's a sample SQL query that experts would use:
SELECT DISTINCT contact.name, session.name FROM contacts LEFT JOIN session ON contacts.session_id=session.id LEFT JOIN hotel ON session.hotel_id=hotel.id WHERE contact.address_state=hotel.address_state
This emits a table showing the contact's name and the session name where they traveled to a hotel taking place in their home state. (I'm assuming lots about the column-names and structure of the DB, of course. A smart DB design would have contacts more separate from session, with some king of "attendance" table joining them.)
posted by cmiller at 12:25 PM on May 21, 2010
there are better databases, but that's not your problem. You don't understand tables yet. They are not fancydumb spreadsheets. Changing DBs will not help.
To do what you want you query the database and ask it to produce what you want from it. You don't say how. You say what you want. It's the job of the DB to figure out how.
From within MSFT Access, construct a query. Ask for the columns you care about, perhaps conditional on some value in the row. Order them. You could then take that result table and save it to a spreadsheet, if you were so inclined. A smart person would save the query, so it's always up-to-date and refer to the result table instead of saving a stale spreadsheet somewhere.
There are graphical query builders in Access, and a text box here will not convey how you use them, but here's a sample SQL query that experts would use:
SELECT DISTINCT contact.name, session.name FROM contacts LEFT JOIN session ON contacts.session_id=session.id LEFT JOIN hotel ON session.hotel_id=hotel.id WHERE contact.address_state=hotel.address_state
This emits a table showing the contact's name and the session name where they traveled to a hotel taking place in their home state. (I'm assuming lots about the column-names and structure of the DB, of course. A smart DB design would have contacts more separate from session, with some king of "attendance" table joining them.)
posted by cmiller at 12:25 PM on May 21, 2010
Best answer: Since you have experience with SalesForce, it is probably your best bet (even though you haven't used it specifically for this).
However, I agree with ChrisHartley, you really need to hire someone to do this. Any time where you are processing a customer's money (your company's source of income), you absolutely must make sure it is done properly. All the out-of-box solutions will use credit card payments as the primary payment method. Any others (like direct bank transfers) will be even more convoluted with insanely complicated APIs (Paypal, I'm looking at you). Having been on the client side of a similar project (event/workshop registration), I know that the development for that kind of project isn't necessarily complicated, it just needs a professional developer to make sure everything is done properly.
posted by thebestsophist at 9:56 PM on May 21, 2010
However, I agree with ChrisHartley, you really need to hire someone to do this. Any time where you are processing a customer's money (your company's source of income), you absolutely must make sure it is done properly. All the out-of-box solutions will use credit card payments as the primary payment method. Any others (like direct bank transfers) will be even more convoluted with insanely complicated APIs (Paypal, I'm looking at you). Having been on the client side of a similar project (event/workshop registration), I know that the development for that kind of project isn't necessarily complicated, it just needs a professional developer to make sure everything is done properly.
posted by thebestsophist at 9:56 PM on May 21, 2010
« Older Help me put together a cinematic music mix for my... | opera browser 10.53 compatible with mac OS 10.4.11... Newer »
This thread is closed to new comments.
posted by ChrisHartley at 9:24 AM on May 21, 2010 [1 favorite]