Best way to store 'date pairs'? (1 Viewer)

[Franky G]

Hi,

I would like to keep track of maps which I control, such as who the map was given to, MapOut date and MapIn dates. (and ultimately to produce report on each map on 1 years worth of OUT/IN data, average time out etc)

I have a table containing Map information (MapNo, MapName, MapOwner etc), but I didn't think it wise to include the MapOut and MapIn dates in this table as I've no idea how many pairs of 'OUT/IN' dates there will be, so I've created a table call 'Dates' with those two fields, plus MapNo field. I'm not really sure of how long I'll want to keep the records, but I have to enter old data for the last year anyway.

Is this an acceptable way of storing a series of 'date pairs' such as MapOut and MapIn? Is there a better way of doing this? One of the things I would like to do is have a form which displays the chosen MapNo, with the past years OUT/IN history...if that makes any odds.

I'm open to any suggestions really, as I've only spent a few hours on this little project so not much to lose if I have to scrap it

 

[Pat Hartman]

Your setup is fine as long as all you need to store is the in and out dates and don't need to know who borrowed the maps. Use MapID plus OutDate as the primary key to the dates table. Don't forget to go to the relationships window and create a relationship between the two tables. Check the enforce RI box and then check Cascade delete.

In case you don't know how to make a compound key, open the table in design view, click on the first key field, then cntl-click on the second. With both fields highlighted, press the key button on the toolbar.

 

[Franky G]

Thanks Pat, although I do need to store who borrowed the map. I have actually included a field in the Dates table to record who takes the map, is that OK?

What does Cascade delete do?

Thanks

FrankyG

 

[Pat Hartman]

If Cascade delete is selected, then when you delete a map from the maps table, all the in and out date records for that map will also be deleted.

In reality, you need a third table to hold people information. That way you can use a combobox when adding new maps to select the owner. You can also use a combo when adding the borrowed records to choose the borrower. This table will help to ensure data integrity and minimize input. Don't forget to create the proper relationships between this table and the map and dates tables.