Alternate method to dynamic tables

[hazel4832]

I have a table that has opening and closing dates of stores. Each time
a new store is opened it needs to be added to the table with the
opening date. Each time a store is closed, the closing date needs to
be added to the table. And if a store is already in the table opens or
closes, the original aopening and closing dates need to be kept, and
the new opening/closing date needs to be added to the table. The
problem I have is that each store is going to have a different number
of opening and closing dates. I therefor need to have a dynamic table
that grows with the data being entered. I know that dynamic table
cannot be created in Microsoft Access. Does anyone know of an
alternative method to handle this?
Thanks.

 

[Keith Wilby]

I have a table that has opening and closing dates of stores. Each time
a new store is opened it needs to be added to the table with the
opening date. Each time a store is closed, the closing date needs to
be added to the table. And if a store is already in the table opens or
closes, the original aopening and closing dates need to be kept, and
the new opening/closing date needs to be added to the table. The
problem I have is that each store is going to have a different number
of opening and closing dates. I therefor need to have a dynamic table
that grows with the data being entered. I know that dynamic table
cannot be created in Microsoft Access. Does anyone know of an
alternative method to handle this?
Thanks.

This is a classic one-to-many relationship. Each store can have many opening
and closing times, so you need to store those data in their own table.

tblStore:
StoreID
StoreName
StoreDetails
etc

tblTimes:
ID
StoreID
StoreDate
TimeOpen
TimeClosed

Join the tables on the StoreID field and enforce referential integrity.

Keith.
www.keithwilby.co.uk

 

[hazel4832]

You need a "stores" table for store information, such as address, phone,
whatever with a store ID number as the Primary Key,  and a separate table for
"store dates" with a field for the opening and closing dates, and a fieldfor
a Foreign Key which holds the store ID.

You'd then have the StoresTable with one record for each store and a
StoreDatesTable with multiple records for each store, as they open and close.

For display you'd have a main record based on StoresTable and a subform based
on StoreDatesTable.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted viahttp://www.accessmonster.com

Got it! Thank you!