My first database-design issues

[rorybecerra]

Hello I am new to database design and am having a few problems. I had made a database for a quality control log. It has a main log with many lookups and a form for entry and a few pivot-charts for to output defect paretos and other charts.

I need to update the database because I need to be able to customize the charts more and to be able to do charts by customer when right now it only charts the customer id and not the customer name. I have a multi-select defects field and it works but on pivot charts it does not show the correct data (does not split up the defects, just shows first two characters of value). Also on the pivot charts, just recently it freezes when I bring in a field into and section on the chart like the category section.

Another issue is that there are about 20 concurrent users at the same time and it is beginning to slow down because of the increasing amount of data.
This was the first database I have made and may have designed it incorrectly.

A few questions I have:
I created lookups on the main table and it points to a table with one column that is the primary key. The main log shows the value (or values if it a multiselect). Is this the right way to do this? Should I have made the lookup tables with an autonumber ID and then the value so then the main log would show the ID number and not the value?

The multiselect fields are not showing up correctly on the pivot charts. What is the correct way to set them up so that it works?

I have the database split on a network drive and all of the computers have the entry form on the front end on the desktop. Is that the best setup in this situation?

I will attach the unsplit database with no relationships (I also added primary auto number fields on the lookup tables because I suspect this is the correct way). There are also three other logs for other functions that are on there as well that share some fields. QClog is the main one.

If anyone can help at all I would appreciate it. I am just an intern that got ambitious and taught myself access to create this log that is important to the company, the first version was rough but was much better than the excel log. they were using when I was hired. I now see that I have made some mistakes. Thank you in advance

[rorybecerra]

I think I have figured out the major problems and fixed them. It now stores the id of all lookups in the main table without the values and I am able to query the values back and make charts out of them.

I have another question though. In the query Defects by Area (CONN,MTD) I am putting criteria on area to "CONNECTORS" and for the date in i want MTD but I do not know what to put in the criteria on that field. I have looked online but there are 50 different answers.

There still may be structure issues but here is the most recent version.

Thank you.

[rorybecerra]

Sorry for piling on the questions but is there a way to make a time stamp automatically come up on pivotcharts? So we would know when the charts were printed?

[June7]

You have a Document Type of 'RMA #'. The # is a special character (wildcard) and all wildcard characters should be avoided in data and special characters should not be used in names. Also, spaces in names should be avoided.

You can use the autonumber for PK in tables like DocType but when the data values are short, could just save the data itself in main table.

You have SalesCoordinator names in one field. Parts of names should be in separate fields.

I appreciate that you removed the Lookup from table setup. I NEVER set Lookup in tables.

For MTD filter you want from the 1st of the current month to the current date? This can be tricky. If you run this query on August 1, do you want August data or do you really want July data?

[rorybecerra]

Thank you for your help. I changed all the names to not have spaces and wildcard characters. Also for the MTD I just chose to use query parameters to ask for start/end dates when the chart is ran.

[June7]

That is one solution. I never use query input parameter prompts because can't validate user entry and the entry is not available for further reference. I have a form for user entry and the query refers to the form controls for input parameters.

You changed the data value to not use the wildcard character? By names I meant fields, tables, queries, forms and reports.

[rorybecerra]

There is only one person that is responsible for gathering data and she would be entering the parameters. I completed two out of four sections of the database and I think I have done them correctly. Everything works (charts,reports,forms) and I am happy with them.

I have one concern still though and that is the slowdown I am expecting when multiple users (up to 30) entering/running reports at the same time. From what I heard splitting the database, putting the be on a network location and copying the front end files to each desktop is the fastest way. I don't know if this is true. The way I imagined it working is everyone but two users will have only the entry forms and the look-up forms on their front end while myself and the person making the charts/reports weekly will have all of the other forms, charts etc on the front end. Also about 10 planners will have access to the lookup forms to check status of parts.

Is there anything I should do to minimize slowdown on this database?

EDIT: Also from the log I have implemented now there are around 6500 lines of data in the qc main log in two months so in one year there would be ~39,000 lines in a year. This will be 99% of the bulk of the database. Should I start the log fresh yearly or bi-yearly, never? What would you recommend?

[rorybecerra]

I forgot to attach the file with my last post

[June7]

Number of records is not the issue. It is db size. Access is limited to 2gb size.

Up to 30 users - should split. Here is how I handle multi-user db http://forums.aspfree.com/microsoft-...ue-323364.html

One db for all users, controlling what features are available to what users.