A form with buttons to store actions and dates?

[hiccup]

Hi, still an absolute beginner,

Suppose I need a database with customers, able to store: what different events, were performed on what date.
So let's say possible actions are: a customer called, emailed or visited.
(note, it might be possible that a customer both called, emailed and visited on the same day)
Should I start with four different tables for: customers, called, emailed, visited? Or would the three actions just go into one table?

Another aspect that I don't understand at all yet is how to proceed with the dates.
Should I create another table with a long list of possible dates for the coming x-years?

To give you some idea about I would like it to work:
I am thinking about creating a form with buttons behind the terms 'called', 'emailed' and 'visited'. When one of those buttons is pressed, I want an entry for that action, day and customer entered in the database.
So that later on I could e.g. look up on what day how many customers called, etc. etc.

Some hints please in what direction I should go to try to accomplish this?

PS,
I am posting this in the 'Forms' discussion since I want to have the interface as a form. Is that o.k. or should I post such questions in another discussion topic?

 

[pbaldy]

This forum is fine, though I think we're going to start with a tables discussion.

I would have 3 tables: customers, actions (your options), and a transactions table that is basically a many-to-many junction table. That table would have records for each "event", with fields for the customer ID, the action ID, and the date (I'd actually store both date and time together, since multiple actions can happen on the same day).

 

[hiccup]

Thnx again, this is very helpful to put me in the right direction.

 

[pbaldy]

No problem.

 

[hiccup]

My mind seems not in the right state to comprehend how to make this work.
(and maybe there's also a bit too much going on in my life right now)

So, I would appreciate it a lot if somebody could show me the way in detail.

I attached an extremely simplified database, only containing a very small aspect of the database I am working on. The real database is coming along quite well, but I can't get a grip on this matter.
The form ('Main') is a simplified example of how this aspect might look in the final version of my database.
So I would like to be able to enter dates for different possible actions. (mayby through the use of combo-boxes and buttons, but I left that out here to keep it as simple as possible)

When in this form you enter a date behind an action, an entry is created in the Contacts table.
In principle this is a good start, but when on a later moment in time I enter another date for that action, of course the previous date is overwritten.
That is not what I want, I want to be able to have a table with all occurrences registered, and so make queries for actions and dates possible.

As pbaldy kindly advised, I should create a 'transactions' table for this.
I did, but after many trials and errors I have no clue how to get the fields and relations working for this purpose.

In the attached example I have indeed added a transaction table to the database, but I removed all relations so to have a clean start for anybody who is willing and able to help me with this.

Tia!

 

[pbaldy]

The form should be bound to the transactions table, not the contacts table. You could have a combo to choose the contact that got its selections from the contact table, and another combo to choose the action. I personally would not use lookup fields as you have:

http://access.mvps.org/access/lookupfields.htm

 

[hiccup]

Ok, thnx, I changed that.
Can you also help me with how the relations should be setup?

 

[pbaldy]

The key fields from actions and contacts are one-to-many with their respective fields in the transactions table. I'd probably name the field ContactID rather than ContactFirstName, which isn't really what it holds.

 

[hiccup]

I think I managed that. But now nothing happens when I select a name from the combobox in the form.

Dadgummit, must I really get through all 795 pages of the Access 'bible' that I have, before getting this to work?
Calling it a night. (Again :banghead: )

 

[bob fitz]

Perhaps something like this:

 

[hiccup]

Thnx Bob, this is helping me a lot to understand how this should work. It is working differently than I had in my mind, but that is probably a good thing ;-)
I'll see if I can progress from your example and try to get it working the way I would like it to.
I noticed you have not setup any relationships. I will have to create those anyway, right?

 

[bob fitz]

I don't think that defining relationships is an absolute necessity but I always do that in any db that I create. I feel that they help with maintaining data integrity.
Good luck with your project. Post back if you have any problems