0

I need some help figuring out how I should code this. We have this really awful POS program at work (a salon/spa) All the data is stored in a MS Access database.

What I am trying to do is create a table like the one here.

http://breze.ca/example.PNG

There will be a seperate table for each of our employees which shows their list of clients and if they have had an appointment that month.

My data is being taken from the Appointments table in ms access. There are only 3 columns I am using data from. ApptDate, Employee, Client

I know how to use php to fetch data from the ms access database. My current idea involves a HUGE amount of queries and loops and I am sure someone can point me towards a better method.

Thanks & let me know if you need any more info!

Improve this question
3
  • 1
    We would be able to help you much better if you post your table structures. Commented Nov 29, 2009 at 1:13
  • 1
    I do not think that having a separate table for each employee is a good idea. Not good for queries, reports, tracking and you have to propagate changes to each employee table every time. Commented Nov 29, 2009 at 1:14
  • 1
    You should edit your question to add detail rather than answer it with details. I am moving details from your answer to your question. Commented Nov 29, 2009 at 1:28

3 Answers 3

Reset to default
1

I'd suggest you put all the employees in one table:

| employee_id  |  employee_name | employee_other_fields  |
+--------------+----------------+------------------------+
| 001          | employee_one   | whatever else you need |

Clients in one table:

| client_id    |  client_name   | client_other_fields    | 
+--------------+----------------+------------------------+
| 001          | client_one     | whatever else you need |

And possibly a lookup-table to link the clients to employees, though you might consider just using a foreign key in the clients table, if you're sure there will never be, or could never be, a many-to-many relationship between clients and employees.

I imagine that the huge number of queries is a result of the table-per-employee design, whereas the three-table scheme should allow for simple -small and fast- queries (certainly relative to the existing structure). Obviously this might not work for your use, especially if it requires a lot of conversion and re-entry of data, so I'd suggest converting a sample quantity to the three-table format -or any other scheme suggested in the answers- and test whether or not the cost in time of changing the system is recouped by the efficiency/improvements of any change.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Note: Additional details provided here by poster have been moved to the question

Improve this answer

Comments

0

You should look into CROSSTAB (Pivot) queries in MS Access for this. Once you understand the concept behind the CROSSTAB, MS Access has a query wizard that can build the query for you.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.