Duplicate tables with selected data ?

[acjeff]

In Access 2003, I have a table storing thousands of test result data. My users feel troublesome when they want to trace back some data in desired categories. For example, if they want to see all data in February 2004, they do not want to search in the table.

I am asked to create some more tables which are duplicated from the test result table but with different month categories. For example, a table only stores data from Jan to March 2004, another table stores data from April to June, etc. However, there are some criteria:

1. These duplicated tables should be read-only and not editable.

2. Any data changed in main test result table will also change the duplicated table automatically.

I know using Query is a good way to do it. However, my users do not like it. They like working with tables only. What is the best way to do it?

 

[dcurtis]

First, don't duplicate the data. It breaks the normalization rules.

If your users insist on using tables, create forms and set them to display datasheet view only. You can add filters to show only the dates (months) desired. And rather than having to create a new form every month you could set your filter criteria dynamically.

You could also create the queries and base the forms on those.

Both ways the user sees 'a table' but are really working in a form.

 

[lespaul]

find some way that your users like using queries. Having duplicated data that you have to update anytime there is a change is massive work. Using queries to just get the data the users need is a much better choice. What kind of user interface did you develop? Why do you have users looking directly at the tables anyway?

Leslie

 

[dcurtis]

I forgot to address the "read only" part. In your form properties, set allow updates to NO.

 

[acjeff]

Thanks guys. Both form and query are good ideas. However, how can I make them read-only that not letting users to open it and change any data?

 

[acjeff]

How about Query table. How can I set it to be read-only?

 

[dcurtis]

When you create your form, set the allow edits, allow deletions and allow additions to No.

 

[PHV]

You may also using a non updatable query, ie SELECT DISTINCT or using outer join.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

a table only stores data from Jan to March 2004, another table stores data from April to June

How are these tables populated - imported from an external source?

they want to trace back some data in desired categories

You can use a Union query to "join" the tables together to present like tables in one select statement.

Instead of "Duplicating", have you thought of joining into one table? How big is your database?

Richcard