new database

[slimjen1]

Hi all,
I need help on a database design. Currently, I have agents taking calls entering the call stats into a spreadsheet. I have attached a picture of the spreadsheet. I've set up a sheet for each agent with 31 days to enter their stats. I then have a summary sheet to roll up into one form on another spreadsheet. From this information; we're able to find out how many calls, type of call, from what agent on any given day, week, month or year to date. I would like to build a database which I feel would be easier to create the type of reports managment wants to see. I am at a loss of how should I design this. The agents have been working with this spreadsheet for a year so I don't want to change the form too much do to familiarlity. I have about 12 agents entering info daily. I know I need a data entry form perhaps designed the way the see it now. As long as I have the data, I can create the reports and charts the way management sees it now. I have also attached a spreadsheet picture of one of the forms management sees. Please help me get started with the design.
Thanks you

 

[wilpeter]

I've looked at the pics and am attempting to establish tables. Couple of questions: Obviously the meanings of the columns OPPT and NO OPPT are immaterial but TotalCallRec is a total field and need not be in the table. Is there a connection between the "NoOpt" and any other columns? Is it a subtotal, for example. How do the agents enter their data (as tick marks on a count sheet, for example) before filling in the totals on this daily sheet?

 

[wilpeter]

Here's a quick version using three tables: tblAGENTS, tblSOURCE, and tblCALLSmade. One form to input data. Three queries: qrySummary (all entries input), qrySummaryTTL (totals by rep), and qryTotals (final totals by Source). I only input 14 records for the 17th Dec. and I didn't format the date field to "dd", which you would do in a report.

 

[slimjen1]

Thank you for your reply. The agents enter a total for calls received in each source under TotalCallRec. The No Oppt is a calculated field totalling the last four columns: OOA,3rd party, Renter and UnderContract. I've attached another sample copy of the form filled in. They fill this form in on a daily basis. Thank you for helping.

 

[slimjen1]

It looks like what I need from the picture. Can you give me the table structures you used. What does the input form look like? I need to get this done for the new year. Thanks for your help

 

[wilpeter]

I'll have to make the changes that you've given (subtotalling the NoOppts and Oppt) before doing so. In Canada it's after 10pm so it'll be tomorrow, sorry.

 

[wilpeter]

The only missing info is the PC-BB handling, but you can get it sorted. At the moment they are included in the Final Totals. Not all records are entered, and you would have over a hundred per day.
Here's a picture of the Relationships, fieldnames, and input form used. And the reworked query outputs - a detail query, from that a summary by agent (by date), and from that a Sum query for the bottom row. A report could better replace the summary and total queries.

 

[slimjen1]

Ok Thanks. I will try to follow the pics and build the tables and forms.
Thanks

 

[slimjen1]

Wilpeter,
Can you send me the design for the queries please. I can't see how you did the calculations for subtracting the noopts.
Thanks

 

[wilpeter]

Try downloading this attachment.

 

[slimjen1]

Thanks for all your help. Can you show me how I can have the agents open the form and it defaults to their name. I would like to make it very simple and error proof. Would the agents be able to enter their stats at the same time with multiple copies open?
Thanks again

 

[wilpeter]

I'm assuming you are all inputting at one location rather than remote. If there are just a dozen agents, you could open the form and SaveAs a different name, eleven times, giving each copy a different Default Value in the Form Data Properties (of an agent's name). Each could open his own version, but that's rather cumbersome if it is just to save typing the first letter of his name (it self-completes). How did you picture the computer knowing who was opening the program?

 

[slimjen1]

I was thinking by logging in with a password. I think I designed previous database with this functionality. I'll have to do some research. Thanks for your help.

 

[wilpeter]

If, as I suspect, there is no reason for security, a password might be overkill. An unbound form with 12 command buttons--one for each Agent--might suffice. By setting the Tools\Startup\Display Form to the command form, the input form would be opened from such a command and its default value set for the Agent field.