Good afternoon,

I am in charge of creating an access database to log phone calls. I have looked at the Contact Management and the Call Tracker templates but neither seem to offer the features that I am looking for and it seems that I would be best served by building one from scratch. I am new to access but have been reading these forums and other websites for a few weeks to gain some background on database design in MS Access 2007.

I have two tables. One to store information about callers and another to store information about calls. Please refer to them below:

tbl_calls
CID (primary key)
calls_PID (foreign key)
calls_date (date/time) Default = Now()
calls_description (memo)
calls_resolution (memo)

tbl_people
PID (primary key)
people_first (text)
people_last (text)
people_phone1 (text)
people_phone2 (text)
people_otherID (text)



One of my goals is to be able to open a person's record and view all the calls he/she has made to my office. Right now, I have one-to-many relationship {enforced referential integrity, cascaded update related fields, and with join type 3} withboth tables between PID (the primary key from tbl_people) and calls_PID (foreign key in tbl_calls).

Question 1:
What is the best relationship to form between these two tables?
I am having difficulty figuring out the best method for inputing data.

Ideally, I'd like the give the receiptionist the ability to type in a person's name to search tbl_people for a matching record using the following criteria: first name, last name, and/or telephone number. If the search finds a matching record, she should be able to hit a "Add call" button that will bring up a form to add a row to tbl_calls. If the search comes back without any results, she should be able to hit a "New person" button that takes her to a form to add a row to tbl_people and then persents her with an "Add call" button.

Question 2:
Any tips on the best way to setup the queries and forms that I need to accomplish what I described above?

Any help is GREATLY appreciated!

What you want is very simple. Make a form for both of the tables. In Access 2007 you can do this with a click. In earlier versions use the wizard. Then on the frm_calls form replace the pid with a combobox on the tbl_persons. Also put in a default value for the date.

Enter the people on the tbl_people form. Receptionist opens the frm_calls form, clicks on *, and enters the information. You can put a button on frm_calls to open frm_people for new people. The combo should provide your type-in function automatically.

Thanks for helping. I did what you said, but there is a problem. When I type a name in the combo box (on tbl_calls form), an error message occurs:

"The value you entered isn't valid for this field. For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits."

This is how I set the combo box up:

Control Source: calls_PID
Row Foruce: tbl_people
Row Source Type: Table/Query
Bound Column: 1
Limit to List: No
Allow Value List Edits: Yes

If I understand correctly, the combo box is giving me the message because I entered text; the control source is the AutoNumber field. How can I change this so that the receptionist types in the caller's name on the call form and if the name isn't found within tbl_people table, it provides a message that says, "Record not found for this caller. Please add a record" and it provides a button that opens to the frm_people form?

What is the Column Count of your Combo Box ?

tbl_people
PID (primary key)
people_first (text)
people_last (text)


You have a Column Count of 3
Bound Column is 1
Lengths 0;.75;.75

If you are using a Value List then the Row Source type would be value List as opposed to Table/Query

Does that help ?

See if the Attached helps, open frmpeople there are two records of people, if you click the list bnox to the right it will go to that persons record, if you need to enter a call for a New person that is not on the listbox to the right just add a New record to the People form. This is one of Many ways to handle it.

HTH

Thanks, but a problem is that we receive too many calls (I'm talking about 100+ per day) for that to really work. Do you mind taking a look at what I have uploaded here? I created a form that basically acts as a search (frm_people).

Our receptionist will be able to open that form when she answers a call to run the person's name through the filter. If no results are returned from the search, she will be able to click the "New" button. If the filter returns with a match, she will be able to hit "add call."

The problem I am now having is getting the buttons to work properly.

First, on frm_people, how can I get the hyperlink to "Add Call" to open frm_calls and have the caller's name automatically added into the combox box (so that the call is associated with the caller).

Second, I can't get either of the two command buttons on frm_add_people to work properly. One should give you the ability to save and clsoe and the other should give you the ability to save and add a new call associated with that person's record.

Any ideas?

Click to view attachment


forgot to upload. =(

No guarantees but I will take a look Yes

Thank you for helping me!

When you do Add Call, you need to open that form on a new record.

Sorry for the delay had to take lunch, see if the attached is close ????

In the file you uploaded, for some reason when I use frm_people to locate the caller, it now pops up with this error:

Type Mismatch

When I hit OK, it says this:

Macro name:
frm_people : cmdGo : OnClick : Embedded Macro

Condition:
True

Action Name:
SetTempVar

Arguments:
strSearch, Replace([Forms]![frm_people]![SearchBox],"""","""""")

OOps i'll take a look and get right back to you

Can't seem to repeat the error, have you tried a Compact and repair Database ? You should NOT have a control on your frm_people named Searchbox I changed that are you sure you are looking at the correct Database ?

Office Button>Manage>Compact and Repair Database

Have reattached as well

It didn't fix anything, but I think I found the problem...

The macro embedded on cmdGo is still pointing towards SearchBox.

BTW-- what is the benefit of having the search box that you added vs the one that I had placed in there? Just trying to assess the change...

No benefit really just a different way to basically do the same thing. Are you now getting the desired results?

I reverted back to the SearchBox...

I also added Save and Close buttons to the form frm_calls.

Everything is working except one feature. I am just running through the "process" that the receiptionist would use, described below.

(assume that the receiptionist just answers a call)

1. Open frm_people
2. Type in the person's name
3. If the person's name isnt' found, click the New Caller link that opens from_add_people
4. Once frm_add_people is filled out, click Save and Add button that opens frm_calls
5. Fill out the call information on frm_calls

The problem I am having lies within step 5: frm_calls doesn't seem to retain the information from the previous form. i.e. when I click "Save and Add Call" from frm_add_people, the person's name doesn't show up in frm_calls. I don't think this is associating the call with the person's record? Furthermore, I get this message when I try to save the call record: "The Microsoft Office Access database engine cannot find a record in the table 'tbl_people' with key matching fields(s) 'calls_PID'

I have a couple of meetings this morning however as soon as I get the chance I will see what I can do.

Ok thats OK

Typing in the persons name in the combo box at the top of the form does nothing ???

OK

OK

OK



I think I have it working now but you can be the judge of that ???

See Attached

BTW: If you don't mind telling us, what is your name ?

I ran emptied all of the tables of data and then compiled and repaired the database.

I ran through the procedures (search for person on frm_people, since there are no records I clicked on "(New)" from the datasheet view, which opens from)add_people. I added a person and then hit "Save and Add Call." On the form taht opens (frm_calls), the Caller combo box was blank (it should have been the name of the caller?). I filled in the rest of the form anyway and hit Save and Close, but it popped up with Run Time Error 2458?

When I hit debug, this is what it said in the visual basic editor:

Private Sub Text14_Click()
DoCmd.OpenForm "frm_Calls", acNormal, , , acFormAdd, acWindowNormal, Me.PID
DoCmd.Close acForm, "frm_people"

End Sub


????

My name is Ryan. :-)

Thanks for all your help again, btw. I am so lost... lol

I re-downloaded the latest version you uploaded (figuring maybe I messed with something when I did the compile and repair?).

I tried rerunning thruogh the process.

I opened frm_people

I typed in a name. When the search back back negative, I clicked (New) on the datasheet view on the split form.

When I clicked (New), frm_add_people opened. On this form, I added a people record and then hit the button Save and Add Call.

The Save and Add Call Button opened up frm_calls.

I filled in the information for the telephone call and then hit Save and Close. I get "The Microsoft Office Access database engine cannot find a record in the table 'tbl_people' with key matching fields(s) 'calls_PID'.

I clicked OK. tbl_calls was not updated...?

Ryan,

From this Attachment

1. Open frm_people
2. If the Caller IS LISTED in the Spreadsheet either
1. Click the Open Record (this will open the initial form frm_add_people that was entered)
2. Click the Add Call (This will open the frm_calls to a NEW record you will notice in the upper right hand corner a Number this number is the Callers PID and is being passed by the openArgs method from frm_people whrn you click the Add Call)

IF The Caller IS NOT listed in the spreadsheet
1. Click the New (frm_add-people will open to a NEW record)
2. Populate the Controls
3. Click the

OR

1. Populate the controls on the frm_people with the NEW Caller last name first name, etc.
2. Then go through the process I described above (Add Call etc. from the spreadsheet)

everything I describe above is working accordingly on this attachment to include the NEW call being saved in the tbl_Calls

Hang in There !

BTW: I get lost myself more frequently than not

I am having the same problem...

That problem being once I use frm_add_people and click "save and add call", frm_calls doesn't work. the same message as i described above.

the main issue seems to be this:

when I need to add a new people record into the database (using the frm_add_people), I want that "Save and Add Call" button to save the people record and then open the form to add a telephone call into the database (frm_calls); however, that call form---when clicked from the frm_add_people, should open with the caller's information that I just added already plugged into the appropriate field. Does that make sense?


thanks so much for your help, again. i know i am a pain. :-)

Are you opening the frm_add_people as a Stand Alone Form or opening it from the frm_people

And you are not being a pain so no worries there we are all in this together

From frm_people




(FYI-- it works fine if I hit Add Call from frm_people to a person who already has a record, so that seems to be a non-issue...)

Ryan,

I will definitely get this to work I finally see what you were trying to tell me and I am trying to find the problem I am pretty tied up at work for awhile but I will get back to you as soon as I can, hopefully with a solution ??? could be 30 minutes could be a few hours ??? but I will get back to you

Maybe the problem is because frm_calls is based on qrycalls which reads 4 tables. It should just be tblCalls. Put comboboxes on the form to reference the other tables where needed.

Thank You projecttoday I had completely forgot abot that little tidbit qryCalls as the record source, the other issue is the opening form frm_calls the OP wants to pass the Callers First and Last name to the txtpfpl control of course they are based on the PID of the Caller (tbl_people) guess one way to handle it would be by a DlookUp

Yes.

Ryan,

See attached

Thanks so much for the help. I have taken what you posted and modified it to better suit our needs (attached here!), but thanks so much for all your help.

The next problem I am having is with a query.

I am trying to create a query (which I will later build a report from) that will allow me to enter in a date range and then return the following results:

It may look something like this:

Office Name: % of Calls: # Calls
CR 70.11% 61
PS 9.20% 8
CT 4.60% 4
HHR 3.45% 3
SCHED 3.45% 3
PRESS 2.30% 2
SOC 2.30% 2
EDU 1.15% 1
PO 1.15% 1
TRAN 1.15% 1
etc. etc. until ALL offices have been listed as such (even those who didn't receive any calls) Also, is it possible to have a pie graph created to supplement the data?


In addition, I am trying to create a second query that shows the frequency distribution of the same data above. It might look something like this:

# Calls # Offices
<4 9
5-9 1
10-14 0
15-19 0
20-24 0
25-29 0
30-35 0
>35 1


Any ideas? :-D

Ryan,

Glad it worked out for you, as far as your latest post I will have to get back to you as soon as I can work has me pretty tied up today

This is what I have setup right now for the first query (in Design view of the Query)

[Office]
qryCalls
Group By
Row Heading

[Office ID]
qryCalls
Group By
Column Heading

[CallID]
qryCalls
Count
Value

Total Calls: [CallID]
qry Calls
Count
Row Heading


This seems to work in so far as returning a total number of calls for each office; however, if an office hasn't receive a call, it doesn't show up int he query. How can I modify this setup to include even offices that have 0 calls?

I did some playing with the query and have managed to get the total number of calls displayed by office using the following query design:

Column 1:
Field: Office ID: OfficeID
Table: Office
Sort:
Show: check
Criteria:
or:


Column 2:
Field: Office
Table: Office
Sort:
Show: check
Criteria:
or:


Column 3:
Field: Number: Nz([CallID],0)
Table:
Sort: Descending
Show: check
Criteria:
Or:


When the query is run, it displays the total number of calls per office. Here is an example:

Column 1: Column 2: Column 3:
Office ID: Office: Number:
3 COMM 2
1 XO 1
11 CP 0
2 PO 0
4 SCHED 0
5 CR 0
etc. etc. etc. until all offices are listed. Those with no calls show 0.

What I want to be able to do is add a 4th column to the query that calculates the percentage of all calls per office.

i.e. if X = number of calls per office, and Y=total calls for all offices, then (X/Y)*100=Z, where Z=the percentage of calls that office received.

How can I set this up to display on the same query?

-Ryan

Hello Ryan,

Can you attach what you currently have ?

Here it is. My apologies for the delay!

FYI it looks like the query info that I posted above isn't worknig out afterall... :-(


Click to view attachment

Morning Ryan,

I will take a look see as soon as I get a chance and get back to you

Ryan,

Couple of things, your Query was not giving you an accurate total of calls by ofice as you were using a select query rather than a totals query. I also added a query to be used in conjunction with a Dlookup to add the Grand Total of calls made to acquire your percentage of calls by office. Open the attached and take a look at your CallsbyOfficeQuery

HTH

Everything works with that except that those offices that didn't receive any calls aren't showing up. I'd like to be able to display 0 no call records are related to an office.

Hello Ryan,

You wil have to be a little more specific, it has been awhile since I looked at this and assume you are using the Last attached DB as your example ??? Anyway are not showing up where ? What exactly am I loking at or not ??? I am a litle tied up at work but will get back to you when I can

I configured QryCallsByOffice to set a parameter so that a user will be able to pick a start date and an end date.

I created a form to ask for input. 2 unbound text boxes and two command buttons (Go, Cancel)

When you hit Go, it opens a report object that displays the data.

Everything works perfectly except one thing: if an office hasn't received a call, it doesn't show up on the report; however, when I copy the exact same query object, remove the parameter from the copied query and run the query, the offices with no calls display 0. This is what I want for the report...

This is the parameter in the QryCallsByOffice query:

Between ([Forms]![PickDate].[txtStartDate]-1) And ([Forms]![PickDate].[txtEndDate]+1)