basic design question - when to (not) update a table

[Jesse]

I am setting up an Access 2000 db for dealing off-line with data from
a webstore. The Access db will get its data by importing a basic set
of csv transaction files into two tables: tbl_trans and tbl_ details,
related via a trans_id field. tbl_trans includes a column with the
transaction date (trans_date), while the tbl_details does not include
the date. At some point, it will be important to start looking at
sales totals and trends over certain time intervals.

To get the basic data in place for this, do you experts who have left
the dark side of flat spreadsheets for the world of proper database
structure think it is better/proper to:

1. Create a trans_date column in tbl_details and do an update query
with values from the trans_date column in tbl_trans?

or

2. Create a query on tbl_trans and tbl_details for a results table?

(or, 3. Something else?)

On the one hand, as I understand (?) db design, non-index data should
not be duplicated across tables. OTOH, the date associated with each
record in tbl_details will never change, so I wonder why the db should
have to dynamically generate a results table each time a combination
of tbl_details records and their dates is needed.

If it is relevant to the answer to this, the two tables will likely
have 10s of thousands of records, not 100s of K or millions.

Thanks. -- Jesse

 

[Guest]

Number 2 is the correct method.

 

[Jesse]

Will us flat-file types ever learn??

Thanks. -- Jesse

 

[George Nicholson]

Asking the question is often the first step to enlightenment.