Best practice...

[and]

Hi everyone.
I have dabbled with Access at a fairly basic level in the past, but would like to ask a question on the best practice for building a particular dbase so that I don't go down the wrong path. If possible just some advice on what features of Access I should use and how I should relate my tables/forms would be great.

I want to build a database that records clients that sign up to a jobs placement programme. I have set up one table fed by a form that records all basic data about the client. There could be up to 3000 clients. Each interaction with each client then needs to be recorded in the dbase. I would like it so that the person entering the data can just bring up a small form into which they input the data from an interaction with the client (date, time, what happened, duration etc...probably 6-7 fields), but I am unsure about what table(s) I should set-up to record this data, and how it should be related to the main table. There could be an unknown number of interactions with each client (50+). How should I organise this data?

I would like it so that the user can look at the original client details in the main form, and here also on a tabbed page see maybe an embedded table showing the last few interactions and a running total of the time that client has received. Is this possible?

If anyone can just give me a few pointers....ideas about which features of Access I can utilise.....tips of best practice for building this kind of database that would be great and much appreciated. I have not (yet) learnt anything about VB/SQL so I would also like to know if I can acheive this kind of dbase with being able to script, just with the usual features of Access.

Sorry for the long post!
Thanks for any help.
Andrew.

 

[MaxEd]

Here is one way to do it. I would have a Clients table and an Interactions table. Each interaction would just hold a ClientUID. This would be a one to many relationship. You can do a running total, but that might require a bit of VB.

-Laughter works miracles.

 

[and]

Thanks MaxEd.....Can you help me further?

I have set up a client table and an Interactions table. I related them so that they both contain the field ClientID and also Client_Name. I have a client_data_form that feeds the client table, and a small Interactions_Form that feeds the Interactions table.

I would like to have it so that on the main Client_data_form there is a button that opens the Interactions Form - no problem there - but how do I set it up so that when the Interactions Form is opened it displays the Client ID for that particular Client, and also the Client_Name ?

Also, do you know how I set-up a form so that when it opens it does so as a Pop-up window that cannot be resized?

Thanks if you can help Max!

Andrew.

 

[glalsop]

Have you looked into subforms? They are designed to service exactly this type of situation, and are very easy to set up. Just create your subform and drag it onto the main form. Then in the properties, set up the Child ID and Parent ID to be the ClientID field. Now all of the Interaction info can be displayed along with the Client info for each Client.

Good luck

-Gary

 

[and]

Thanks Gary. Subforms are the future! (for me anyway)...

 

[MaxEd]

and,

Displaying the clientID is simple as just pulling the related field from the fields list in your internactions form. Now to display the client name, you can just use the "dlookup" function to find yor client name. The other way to do this is use subforms. I think taht subforms would be much easier to do in this case.