Database design query

[tonyA1DL]

We have an events database on Approach currently 85MB with circa 22,000 records and growing rapidly every month.

Our admin function is soon to become a twin site operation, using a VPN based WAN, therefore our customer communications logging (currently a manual process outside of Approach) needs to be incorporated into Approach and "linked" on an event specific basis.

The only way I can think of doing this is to create a load of additional fields in the main events DB (say ten sets of date/time/who/text fields for logging customer communications) however this CANNOT be the most efficient way to achieve our goal. Taking this route would make the file size ridiculously large, limit communications logging to the amount of fields created and have loads of wasted space on old records.

Any guidance would be appreciated, if I am to create a new DB for communications logging, it could be linked be the unique reference number field on the events database, but how do I set it to create records when required (i.e. to log a customer call)?

 

[paulbent]

The customer log should be in a separate joined table. If the events table doesn't have a unique primary key then add an id # field and use the auto serial number default. You'll have to assign an id# to existing records with alooping macro.

Then you can put the event id field in the new customer log table to join on.

On a form with main table events you can add a repeating panel based on the customer log and log any number of customer communications per event.

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[tonyA1DL]

Many thanks Paul, as always excellent advice, have developed, tested and implemented this afternoon!

Just one small point, the repeating panel is sorted by date (descending) I.E. most recent communication at the top, HOWEVER the blank row to key a NEW communication is under the bottom (oldest) entry. Ideally I would like the blank box at the very top of the repeating panel, i.e. above the NEWEST communication.

Probably something simple, but I can't figure it, any ideas?

Thanks
Tony

 

[paulbent]

That's the only way it works I'm afraid and new entries don't get sorted automatically until you switch out of the form and switch back to it.

What I do is put a button on the form with a macro to sort the rp field. After making new entries the user can click it to sort them to the top.

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[tonyA1DL]

Many thanks Paul