Help on FE, BE, Linked tables and Bloating Concepts and some advice on how i should construct my database

[Arun]

Hi..I am arun and Thanks to all of you in advance for solving my
problem...

I am working as a quality co-op for Maytag and I have developed a
defect tracking database using access and VBA. It looks good and its
performance is good.

My problem is in data accessing and the size of the tables.
1. More than 15 people are going to access the database.
2. There are 5 lines and there are 3 stations in each line entering
data.
3. There are 3 Quality engineers, 5 manufacturing Engg, 3 lean sigma
guys and 3 directors (not mentioning the team leaders, supervisors and
co-ops) accessing the queries.
4. I have one main table for data entry and 2 more small tables for
other stuff. From these 3 tables, I take data for my queries.
5. 15 people access the main table for data entry simultaneously (5
lines, 3 stations each line).
6. So, I decided to have 15 differant database with same underlying
structure. Then each and every one of the engineers,team leaders,
supervisors and Directors having their own FE and accessing the tables
by the LINKED TABLES concept. IAM YET TO DO THIS STEP.

7. My question is whether this is the right method to do it?
8. Every production line has 150 enteries a day...so we are talking
about 540k records for an year....

9. Please advice me on my data base design...Mainly on my table
design. whether i should have 15 different tables of the same dsign
and then link them to the FE or create temp MDB etc

Please start with some general comments and advices and I will explore
those comments and will go deeper...

[Tony Toews]

ar******@rediffmail.com (Arun) wrote:

My problem is in data accessing and the size of the tables.
1. More than 15 people are going to access the database.
2. There are 5 lines and there are 3 stations in each line entering
data.
3. There are 3 Quality engineers, 5 manufacturing Engg, 3 lean sigma
guys and 3 directors (not mentioning the team leaders, supervisors and
co-ops) accessing the queries.
4. I have one main table for data entry and 2 more small tables for
other stuff. From these 3 tables, I take data for my queries.
I'd put these in one table.
7. My question is whether this is the right method to do it?
8. Every production line has 150 enteries a day...so we are talking
about 540k records for an year....
Not too much of a problem.
9. Please advice me on my data base design...Mainly on my table
design. whether i should have 15 different tables of the same dsign
and then link them to the FE or create temp MDB etc

You should really, really have one MDB with a FE for each workstation.

If you find you are getting a lot of locking issues with everyone entering new
records then you might want to consider using a temp tables for the duration of the
data entry and then append those records to the main table at periodic intervals.
However you really won't know this until the folks start using the system in volume.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm