Help creating a database

[Carolyn Schultze]

Hi,

I'm having trouble and have been trying to create this database for over a
week now. I'm not very up to speed with creating databases and have been
trying to figure this out on my own. But I'm not doing a very good job at
it.

I need to create a database for our receptionist.Currently she has to record
all calls on a sheet of paper that has the following fields

LINE / NAME / COMPANY / TAKEN BY / CALL STATUS

Beneath each field would be the line that the customer is calling on (1 thru
5), their name, their company name, the employee or sales person taking the
call, and the status of the call (if the sales person took the call (check
mark or "x"), if the call went to voicemail (vm), if the customer hung up
(hu), if the customer will call back (wcb), if the customer asked for the
sales persons cell phone (cp), or if if the message went to email (em).

At the end of the day, a count is done of how many calls were either taken
by each sales person and how many went to voicemail. All of the other call
statuses are not counted.

Basically I need a database that will allow me to enter up to 5 records at
once (because of the 5 line capability on the phone switchboard) including
all of the fields listed above. I would like this to look like a form (not a
spreadsheet). I also need to be able to create a report at the end of the
day with the total call count (mentioned above).

Any help would be GREATLY appreciated. I really would like to present this
to my supervisor when he comes back from his vacation.


Best Regards,
Carolyn

 

[Guest]

You should be able to do this with one table (with the fields you listed), one data-entry form and then whatever reports you want. Use the form to ensure you get good data (ie: only let a 1, 2, 3, 4 or 5 be entered the line number) and you should be able to use one form to enter all the calls (far easier than jumping between 5 similar forms!)

Use the wizards to help you build your table, form, queries and reports


----- Carolyn Schultze wrote: ----

Hi

I'm having trouble and have been trying to create this database for over
week now. I'm not very up to speed with creating databases and have bee
trying to figure this out on my own. But I'm not doing a very good job a
it

I need to create a database for our receptionist.Currently she has to recor
all calls on a sheet of paper that has the following field

LINE / NAME / COMPANY / TAKEN BY / CALL STATU

Beneath each field would be the line that the customer is calling on (1 thr
5), their name, their company name, the employee or sales person taking th
call, and the status of the call (if the sales person took the call (chec
mark or "x"), if the call went to voicemail (vm), if the customer hung u
(hu), if the customer will call back (wcb), if the customer asked for th
sales persons cell phone (cp), or if if the message went to email (em)

At the end of the day, a count is done of how many calls were either take
by each sales person and how many went to voicemail. All of the other cal
statuses are not counted

Basically I need a database that will allow me to enter up to 5 records a
once (because of the 5 line capability on the phone switchboard) includin
all of the fields listed above. I would like this to look like a form (not
spreadsheet). I also need to be able to create a report at the end of th
day with the total call count (mentioned above)

Any help would be GREATLY appreciated. I really would like to present thi
to my supervisor when he comes back from his vacation


Best Regards
Caroly

 

[lbrinkman]

I agree with "Cranky": In the table ("tblCALLS"), I would set rigid criteria
for certain fields.
For example: LINE should have: (Between 1 and 5) or null.

I would also ADD another field to the TABLE: "DateOfCall" (as a Date type
field) with a default of =Date() in the field's properties, with a format of
"m/d/yyyy"; and the DateOfCall control "locked" on the data entry form,
forecolor of 128 (to let you know it's locked and cannot be changed). (I
would also split the caller's name into CallerFirstName and CallerLast Name,
but that is your call.)

I would also have COMBO BOXES for ALL of the controls (fields) on the FORM,
EXCEPT the "DateOfCall" field/control. You can use the Wizard to set up
lists for
Line, TakenBy, and CallStatus. (Be sure to set their "Limit To List"
properties to "Yes"; and possibly write code for each's "Not-In-List"
event.)

Personally, I would set up a lookup table for the "Company" field - I would
call the table tblCompany_Lookup. (The "Company" field in the table should
NOT allow duplicates!!)
I would then create a query, sorted by "Company" based on tblCompany_Lookup:
qryCompanyForCbo. Then create on the data entry form a combo box based on
that query. The tblCompany_Lookup table will have no records at first, but
the cboCompany combo box will have it's "Limit-To-List" property set to
"Yes" and have code for the "Not-In-List" event.
================
Private Sub cboCompany_NotInList(NewData As String, Response As Integer)
On Error GoTo YIKES
'
Dim rs As Recordset
Dim strMsg As String
Dim strTitle As String
Dim intUserResponse As Integer
'
strMsg = NewData & " is not on the list of Companies." & vbCr & "Would you
like to add it?"
strTitle = "Please Verify"
intUserResponse = MsgBox(sMsg, vbYesNo + vbDefaultButton2 + vbQuestion,
strTitle)
'
If intUserResponse = vbYes Then 'user wants to add a NEW company
Set rs = CurrentDb.OpenRecordset("tblCompany_Lookup")
With rs
.AddNew
![Company] = NewData
.Update
End With
Response = acDataErrAdded
rs.Close
Set rs = Nothing
Set db = Nothing
Else 'user does NOT want to add a new company
Response = acDataErrContinue 'same as: Response = 0
Me![cboCompany].Undo
Me![cboCompany] = Null
End If
'
'
'========= Error Handling ==============
Exit_YIKES:
DoCmd.Hourglass False
DoCmd.Echo True
Exit Sub
'
'
YIKES:
DoCmd.Hourglass False
DoCmd.Echo True
MsgBox Err.Description
Resume Exit_YIKES
'
End Sub
================ ==============================
I would also add this code to that field/control on the form

Private Sub cboCompany_GotFocus()
DoCmd.Requery "cboCompany"
End Sub
=======================================
YOUR QUESTION: Why do all this for the "Company" field in "tblCALLS" with
the control called "cboCompany" on the form, but whose Control Source
property is still the field
"Company" in tblCALLS?

Because data entry people make mistakes, even putting in the same data. For
example: WITHOUT such a combo box, various users could type in Verity
Software,
Verity Software, Inc. and Verity -- all for the SAME company. When you go to
print
out reports, this would not look good!! WITH such a combo box, as soon as
the user
typed in "ver", the "Verity Software, Inc." would appear. No errors, faster,
and no duplicates for the same company. When a customer from a NEW company
calls, as
you type in the company's name, it will NOT appear in full. When you hit the
enter key,
it will ask if you want to add that company name. If you answer "Yes", then
it will
be added to tblCompany_Lookup and thus will be on the list in the future.

---Phil Szlyk

You should be able to do this with one table (with the fields you listed),

one data-entry form and then whatever reports you want. Use the form to
ensure you get good data (ie: only let a 1, 2, 3, 4 or 5 be entered the line
number) and you should be able to use one form to enter all the calls (far
easier than jumping between 5 similar forms!).

 

[lbrinkman]

I agree with "Cranky": In the table ("tblCALLS"), I would set rigid criteria
for certain fields.
For example: LINE should have: (Between 1 and 5) or null.

I would also ADD another field to the TABLE: "DateOfCall" (as a Date type
field) with a default of =Date() in the field's properties, with a format of
"m/d/yyyy"; and the DateOfCall control "locked" on the data entry form,
forecolor of 128 (to let you know it's locked and cannot be changed). (I
would also split the caller's name into CallerFirstName and CallerLast Name,
but that is your call.)

I would also have COMBO BOXES for ALL of the controls (fields) on the FORM,
EXCEPT the "DateOfCall" field/control. You can use the Wizard to set up
lists for
Line, TakenBy, and CallStatus. (Be sure to set their "Limit To List"
properties to "Yes"; and possibly write code for each's "Not-In-List"
event.)

Personally, I would set up a lookup table for the "Company" field - I would
call the table tblCompany_Lookup. (The "Company" field in the table should
NOT allow duplicates!!)
I would then create a query, sorted by "Company" based on tblCompany_Lookup:
qryCompanyForCbo. Then create on the data entry form a combo box based on
that query. The tblCompany_Lookup table will have no records at first, but
the cboCompany combo box will have it's "Limit-To-List" property set to
"Yes" and have code for the "Not-In-List" event.
================
Private Sub cboCompany_NotInList(NewData As String, Response As Integer)
On Error GoTo YIKES
'
Dim rs As Recordset
Dim strMsg As String
Dim strTitle As String
Dim intUserResponse As Integer
'
strMsg = NewData & " is not on the list of Companies." & vbCr & "Would you
like to add it?"
strTitle = "Please Verify"
intUserResponse = MsgBox(sMsg, vbYesNo + vbDefaultButton2 + vbQuestion,
strTitle)
'
If intUserResponse = vbYes Then 'user wants to add a NEW company
Set rs = CurrentDb.OpenRecordset("tblCompany_Lookup")
With rs
.AddNew
![Company] = NewData
.Update
End With
Response = acDataErrAdded
rs.Close
Set rs = Nothing
Set db = Nothing
Else 'user does NOT want to add a new company
Response = acDataErrContinue 'same as: Response = 0
Me![cboCompany].Undo
Me![cboCompany] = Null
End If
'
'
'========= Error Handling ==============
Exit_YIKES:
DoCmd.Hourglass False
DoCmd.Echo True
Exit Sub
'
'
YIKES:
DoCmd.Hourglass False
DoCmd.Echo True
MsgBox Err.Description
Resume Exit_YIKES
'
End Sub
================ ==============================
I would also add this code to that field/control on the form

Private Sub cboCompany_GotFocus()
DoCmd.Requery "cboCompany"
End Sub
=======================================
YOUR QUESTION: Why do all this for the "Company" field in "tblCALLS" with
the control called "cboCompany" on the form, but whose Control Source
property is still the field
"Company" in tblCALLS?

Because data entry people make mistakes, even putting in the same data. For
example: WITHOUT such a combo box, various users could type in Verity
Software,
Verity Software, Inc. and Verity -- all for the SAME company. When you go to
print
out reports, this would not look good!! WITH such a combo box, as soon as
the user
typed in "ver", the "Verity Software, Inc." would appear. No errors, faster,
and no duplicates for the same company. When a customer from a NEW company
calls, as
you type in the company's name, it will NOT appear in full. When you hit the
enter key,
it will ask if you want to add that company name. If you answer "Yes", then
it will
be added to tblCompany_Lookup and thus will be on the list in the future.

---Phil Szlyk

You should be able to do this with one table (with the fields you listed),

one data-entry form and then whatever reports you want. Use the form to
ensure you get good data (ie: only let a 1, 2, 3, 4 or 5 be entered the line
number) and you should be able to use one form to enter all the calls (far
easier than jumping between 5 similar forms!).

 

[lbrinkman]

I agree with "Cranky": In the table ("tblCALLS"), I would set rigid criteria
for certain fields.
For example: LINE should have: (Between 1 and 5) or null.

I would also ADD another field to the TABLE: "DateOfCall" (as a Date type
field) with a default of =Date() in the field's properties, with a format of
"m/d/yyyy"; and the DateOfCall control "locked" on the data entry form,
forecolor of 128 (to let you know it's locked and cannot be changed). (I
would also split the caller's name into CallerFirstName and CallerLast Name,
but that is your call.)

I would also have COMBO BOXES for ALL of the controls (fields) on the FORM,
EXCEPT the "DateOfCall" field/control. You can use the Wizard to set up
lists for
Line, TakenBy, and CallStatus. (Be sure to set their "Limit To List"
properties to "Yes"; and possibly write code for each's "Not-In-List"
event.)

Personally, I would set up a lookup table for the "Company" field - I would
call the table tblCompany_Lookup. (The "Company" field in the table should
NOT allow duplicates!!)
I would then create a query, sorted by "Company" based on tblCompany_Lookup:
qryCompanyForCbo. Then create on the data entry form a combo box based on
that query. The tblCompany_Lookup table will have no records at first, but
the cboCompany combo box will have it's "Limit-To-List" property set to
"Yes" and have code for the "Not-In-List" event.
================
Private Sub cboCompany_NotInList(NewData As String, Response As Integer)
On Error GoTo YIKES
'
Dim rs As Recordset
Dim strMsg As String
Dim strTitle As String
Dim intUserResponse As Integer
'
strMsg = NewData & " is not on the list of Companies." & vbCr & "Would you
like to add it?"
strTitle = "Please Verify"
intUserResponse = MsgBox(sMsg, vbYesNo + vbDefaultButton2 + vbQuestion,
strTitle)
'
If intUserResponse = vbYes Then 'user wants to add a NEW company
Set rs = CurrentDb.OpenRecordset("tblCompany_Lookup")
With rs
.AddNew
![Company] = NewData
.Update
End With
Response = acDataErrAdded
rs.Close
Set rs = Nothing
Set db = Nothing
Else 'user does NOT want to add a new company
Response = acDataErrContinue 'same as: Response = 0
Me![cboCompany].Undo
Me![cboCompany] = Null
End If
'
'
'========= Error Handling ==============
Exit_YIKES:
DoCmd.Hourglass False
DoCmd.Echo True
Exit Sub
'
'
YIKES:
DoCmd.Hourglass False
DoCmd.Echo True
MsgBox Err.Description
Resume Exit_YIKES
'
End Sub
================ ==============================
I would also add this code to that field/control on the form

Private Sub cboCompany_GotFocus()
DoCmd.Requery "cboCompany"
End Sub
=======================================
YOUR QUESTION: Why do all this for the "Company" field in "tblCALLS" with
the control called "cboCompany" on the form, but whose Control Source
property is still the field
"Company" in tblCALLS?

Because data entry people make mistakes, even putting in the same data. For
example: WITHOUT such a combo box, various users could type in Verity
Software,
Verity Software, Inc. and Verity -- all for the SAME company. When you go to
print
out reports, this would not look good!! WITH such a combo box, as soon as
the user
typed in "ver", the "Verity Software, Inc." would appear. No errors, faster,
and no duplicates for the same company. When a customer from a NEW company
calls, as
you type in the company's name, it will NOT appear in full. When you hit the
enter key,
it will ask if you want to add that company name. If you answer "Yes", then
it will
be added to tblCompany_Lookup and thus will be on the list in the future.

---Phil Szlyk

You should be able to do this with one table (with the fields you listed),

one data-entry form and then whatever reports you want. Use the form to
ensure you get good data (ie: only let a 1, 2, 3, 4 or 5 be entered the line
number) and you should be able to use one form to enter all the calls (far
easier than jumping between 5 similar forms!).

 

[Carolyn Schultze]

Thank you both for your replys and suggestions. I'll give it a try. It
sounds a little confusing to me, especially since I'm not real up to par
with creating databases, but I'm a fairly persistant person. So, hopefully
I'll figure it out.

Thanks again guys!
~ Carolyn

I agree with "Cranky": In the table ("tblCALLS"), I would set rigid criteria
for certain fields.
For example: LINE should have: (Between 1 and 5) or null.

I would also ADD another field to the TABLE: "DateOfCall" (as a Date type
field) with a default of =Date() in the field's properties, with a format of
"m/d/yyyy"; and the DateOfCall control "locked" on the data entry form,
forecolor of 128 (to let you know it's locked and cannot be changed). (I
would also split the caller's name into CallerFirstName and CallerLast Name,
but that is your call.)

I would also have COMBO BOXES for ALL of the controls (fields) on the FORM,
EXCEPT the "DateOfCall" field/control. You can use the Wizard to set up
lists for
Line, TakenBy, and CallStatus. (Be sure to set their "Limit To List"
properties to "Yes"; and possibly write code for each's "Not-In-List"
event.)

Personally, I would set up a lookup table for the "Company" field - I would
call the table tblCompany_Lookup. (The "Company" field in the table should
NOT allow duplicates!!)
I would then create a query, sorted by "Company" based on tblCompany_Lookup:
qryCompanyForCbo. Then create on the data entry form a combo box based on
that query. The tblCompany_Lookup table will have no records at first, but
the cboCompany combo box will have it's "Limit-To-List" property set to
"Yes" and have code for the "Not-In-List" event.
================
Private Sub cboCompany_NotInList(NewData As String, Response As Integer)
On Error GoTo YIKES
'
Dim rs As Recordset
Dim strMsg As String
Dim strTitle As String
Dim intUserResponse As Integer
'
strMsg = NewData & " is not on the list of Companies." & vbCr & "Would you
like to add it?"
strTitle = "Please Verify"
intUserResponse = MsgBox(sMsg, vbYesNo + vbDefaultButton2 + vbQuestion,
strTitle)
'
If intUserResponse = vbYes Then 'user wants to add a NEW company
Set rs = CurrentDb.OpenRecordset("tblCompany_Lookup")
With rs
.AddNew
![Company] = NewData
.Update
End With
Response = acDataErrAdded
rs.Close
Set rs = Nothing
Set db = Nothing
Else 'user does NOT want to add a new company
Response = acDataErrContinue 'same as: Response = 0
Me![cboCompany].Undo
Me![cboCompany] = Null
End If
'
'
'========= Error Handling ==============
Exit_YIKES:
DoCmd.Hourglass False
DoCmd.Echo True
Exit Sub
'
'
YIKES:
DoCmd.Hourglass False
DoCmd.Echo True
MsgBox Err.Description
Resume Exit_YIKES
'
End Sub
================ ==============================
I would also add this code to that field/control on the form

Private Sub cboCompany_GotFocus()
DoCmd.Requery "cboCompany"
End Sub
=======================================
YOUR QUESTION: Why do all this for the "Company" field in "tblCALLS" with
the control called "cboCompany" on the form, but whose Control Source
property is still the field
"Company" in tblCALLS?

Because data entry people make mistakes, even putting in the same data. For
example: WITHOUT such a combo box, various users could type in Verity
Software,
Verity Software, Inc. and Verity -- all for the SAME company. When you go to
print
out reports, this would not look good!! WITH such a combo box, as soon as
the user
typed in "ver", the "Verity Software, Inc." would appear. No errors, faster,
and no duplicates for the same company. When a customer from a NEW company
calls, as
you type in the company's name, it will NOT appear in full. When you hit the
enter key,
it will ask if you want to add that company name. If you answer "Yes", then
it will
be added to tblCompany_Lookup and thus will be on the list in the future.

---Phil Szlyk

You should be able to do this with one table (with the fields you

listed),
one data-entry form and then whatever reports you want. Use the form to
ensure you get good data (ie: only let a 1, 2, 3, 4 or 5 be entered the line
number) and you should be able to use one form to enter all the calls (far
easier than jumping between 5 similar forms!).

Use the wizards to help you build your table, form, queries and reports.



----- Carolyn Schultze wrote: -----

Hi,

I'm having trouble and have been trying to create this database for over a
week now. I'm not very up to speed with creating databases and have been
trying to figure this out on my own. But I'm not doing a very good job at
it.

I need to create a database for our receptionist.Currently she has

to

record
all calls on a sheet of paper that has the following fields

LINE / NAME / COMPANY / TAKEN BY / CALL STATUS

Beneath each field would be the line that the customer is calling

on
(1 thru

5), their name, their company name, the employee or sales person taking the
call, and the status of the call (if the sales person took the call (check
mark or "x"), if the call went to voicemail (vm), if the customer hung up
(hu), if the customer will call back (wcb), if the customer asked

for

the
sales persons cell phone (cp), or if if the message went to email (em).

At the end of the day, a count is done of how many calls were

either

taken
by each sales person and how many went to voicemail. All of the

other

call
statuses are not counted.

Basically I need a database that will allow me to enter up to 5 records at
once (because of the 5 line capability on the phone switchboard) including
all of the fields listed above. I would like this to look like a

form
(not a

spreadsheet). I also need to be able to create a report at the end of the
day with the total call count (mentioned above).

Any help would be GREATLY appreciated. I really would like to

present

this
to my supervisor when he comes back from his vacation.


Best Regards,
Carolyn

 

[lbrinkman]

Carolyn,
Another useful tip: When you generate REPORTS using this data,
you will
want to limit the data to certain dates, e.g., from Jan 1st to Dec31st, etc.
To do so, in all QUERIES for reports, add the "DateOfCall" field to the
queries. In the
DateOfCalls "criteria" slot (below the field name in the query design mode),
put:
Between [Enter Start Date] and [Enter End Date]

When you run the query (and the reports based on it), it will ask you to
enter a Start Date
and to enter an End Date. The data that shows up for your query and reports
will
include ONLY the data between these two dates!
Further, you can add the controls =[Enter start date] and =[Enter end
date] on
your reports - do the start date and end date will show up on the reports.
---Phil Szlyk

Thank you both for your replys and suggestions. I'll give it a try. It
sounds a little confusing to me, especially since I'm not real up to par
with creating databases, but I'm a fairly persistant person. So, hopefully
I'll figure it out.

Thanks again guys!
~ Carolyn

I agree with "Cranky": In the table ("tblCALLS"), I would set rigid criteria
for certain fields.
For example: LINE should have: (Between 1 and 5) or null.

I would also ADD another field to the TABLE: "DateOfCall" (as a Date type
field) with a default of =Date() in the field's properties, with a

format

of
"m/d/yyyy"; and the DateOfCall control "locked" on the data entry form,
forecolor of 128 (to let you know it's locked and cannot be changed). (I
would also split the caller's name into CallerFirstName and CallerLast Name,
but that is your call.)

I would also have COMBO BOXES for ALL of the controls (fields) on the FORM,
EXCEPT the "DateOfCall" field/control. You can use the Wizard to set up
lists for
Line, TakenBy, and CallStatus. (Be sure to set their "Limit To List"
properties to "Yes"; and possibly write code for each's "Not-In-List"
event.)

Personally, I would set up a lookup table for the "Company" field - I would
call the table tblCompany_Lookup. (The "Company" field in the table should
NOT allow duplicates!!)
I would then create a query, sorted by "Company" based on tblCompany_Lookup:
qryCompanyForCbo. Then create on the data entry form a combo box based on
that query. The tblCompany_Lookup table will have no records at first, but
the cboCompany combo box will have it's "Limit-To-List" property set to
"Yes" and have code for the "Not-In-List" event.
================
Private Sub cboCompany_NotInList(NewData As String, Response As Integer)
On Error GoTo YIKES
'
Dim rs As Recordset
Dim strMsg As String
Dim strTitle As String
Dim intUserResponse As Integer
'
strMsg = NewData & " is not on the list of Companies." & vbCr & "Would you
like to add it?"
strTitle = "Please Verify"
intUserResponse = MsgBox(sMsg, vbYesNo + vbDefaultButton2 + vbQuestion,
strTitle)
'
If intUserResponse = vbYes Then 'user wants to add a NEW company
Set rs = CurrentDb.OpenRecordset("tblCompany_Lookup")
With rs
.AddNew
![Company] = NewData
.Update
End With
Response = acDataErrAdded
rs.Close
Set rs = Nothing
Set db = Nothing
Else 'user does NOT want to add a new company
Response = acDataErrContinue 'same as: Response = 0
Me![cboCompany].Undo
Me![cboCompany] = Null
End If
'
'
'========= Error Handling ==============
Exit_YIKES:
DoCmd.Hourglass False
DoCmd.Echo True
Exit Sub
'
'
YIKES:
DoCmd.Hourglass False
DoCmd.Echo True
MsgBox Err.Description
Resume Exit_YIKES
'
End Sub
================ ==============================
I would also add this code to that field/control on the form

Private Sub cboCompany_GotFocus()
DoCmd.Requery "cboCompany"
End Sub
=======================================
YOUR QUESTION: Why do all this for the "Company" field in "tblCALLS" with
the control called "cboCompany" on the form, but whose Control Source
property is still the field
"Company" in tblCALLS?

Because data entry people make mistakes, even putting in the same data. For
example: WITHOUT such a combo box, various users could type in Verity
Software,
Verity Software, Inc. and Verity -- all for the SAME company. When you

go

to
print
out reports, this would not look good!! WITH such a combo box, as soon as
the user
typed in "ver", the "Verity Software, Inc." would appear. No errors, faster,
and no duplicates for the same company. When a customer from a NEW company
calls, as
you type in the company's name, it will NOT appear in full. When you hit the
enter key,
it will ask if you want to add that company name. If you answer "Yes", then
it will
be added to tblCompany_Lookup and thus will be on the list in the future.

---Phil Szlyk

listed),
one data-entry form and then whatever reports you want. Use the form to
ensure you get good data (ie: only let a 1, 2, 3, 4 or 5 be entered the line
number) and you should be able to use one form to enter all the calls (far
easier than jumping between 5 similar forms!). for
over a have
been good
job at

has

to on
(1 thru call
(check for either other form
(not a end
of the present