How best to store history in MS Access

[LizaHawkins]

Hi

I have a table with 10 fields for 100 units
Any of the 10 fields for any of the 100 units can be updated by the user at any time.
1) I want to store the data such that i can choose a unit and show what the latest entry at a given date was.
2) I want to show on a report the changed bits highlighted.

e.g. 01/01/09 unit 1 entered as A,B,C on 03/01/09 unit 1 was update to A, B, D no further changes made to date.

1) If I choose Unit 1 on 01/01/09 I get output A,B,C
If i choose Unit 2 on 04/01/09 I get output A,B,D

2) The report will look like this

03/01/09 A, B, D
01/01/09 A, B, C

How should I store the data. I have looked and found a lot of information on audit tables but then I can't figure out how to make the querys work to pull the data back out?

[mshmyob]

Simple normalization of the tables would take care of that.

3 tables needed.

1st Table - Holds the Unit descriptions

UnitID - PK
UnitDesc - ie: Unit 1, Unit 2, etc.

2nd Table - holds the fields A,B,C - whatever those are

ComponentID - PK
ComponentDesc - A, B, C, etc.

3rd table - Bridge table to track historic changes

UnitID - PK
ComponentID - PK
Date - PK

As long as the configuration of the unit can only be changed once on each date and each component can only appear once for each unit on a certain date then this works.

Example of data in the brisge table would be

1 = UnitID
1 = ComponentID
03/04/2009 - Date
1 = UnitID
2 = ComponentID
03/04/2009 - Date
1 = UnitID
1 = ComponentID
03/20/2009 - Date
1 = UnitID
3 = ComponentID
03/20/2009 - Date

Query would result in
Unit 1 configuration on the following dates
03/04/2009 A,B
03/20/2009 A,C


This would give unlimited configuration changes for unlimited units and unlimited components.

cheers,

[LizaHawkins]

Thank you
I am still a bit confused though - What would the query look like that would give you this result?

[NeoPa]

Producing a list of items in the results of a query can only really be done using VBA code. Some help for that can be found at Combining Rows-Opposite of Union.
The SQL for the query (just the atomic data) would be something like :

SELECT   tblUnitComp.UnitID,
         tblUnit.UnitDesc,
         tblUnitComp.ComponentID,
         tblComponent.ComponentDesc,
         tblUnitComp.Date

FROM    ([tblUnitComp]
         INNER JOIN [tblUnit]
  ON     tblUnitComp.UnitID=tblUnit.UnitID)
         INNER JOIN [tblComponent]
  ON     tblUnitComp.ComponentID=tblComponent.ComponentID

ORDER BY tblUnitComp.UnitID,
         tblUnitComp.ComponentID,
         tblUnitComp.Date

[LizaHawkins]

Thanks very much thats great.
Any idea with the second question?

2) I want to show on a report the changed bits highlighted.

[NeoPa]

Not really without a clearer definition of exactly what determines a change.

I can say that, as query output is text-based, you won't manage to get a bold format in a query. Alternatively though, you could print parentheses or other brackets around your value to illustrate the status. This all depends on identifying the status in the first place of course. I think your first step is to get a query that does the basic for you, because it will need to be built on that, and not on some example SQL provided simply to illustrate the basic concept.