Call Management Database Access 2007

[JBrown]

Hello Everyone,

I have spent time online doing training and have just finished Oreilly's
Missing Manual for Access 2007. While I am better now than when I started I
am still stumped on what I think should be a very simple DB.

I am an account manager for an insurane company. Basically a travelling
salesperson for all intents and purposes. I need a database that I can input
all my daily activity into and then run reports that pull out the important
bits of that activity.

These are what I think my tables would (titles only, detail if needed):

Customers
(My list of customers)

Incident Report
(every time there is an issue with one of my companies policies)

Supplies
(I drop off insurance certificates etc)

Training
(we provide training to retailers)

Sales Reports
(we provide customers with reports so they know how their doing)

I need to run a weekly report with bits and pieces from all these above
areas. I have attempted to design this DB several times but I can never ever
get the queries I want. I think it is because I always design them with the 1
single common thread (customers).

Any help would be greatly appreciated!

 

[Jeff Boyce]

To get really good use of Access' relationally oriented features/functions,
your data will need to be well-normalized. If "relational" and "normalized"
aren't familiar, plan to spend some time working your way up that learning
curve.

The tables you'll want to use may not (intuitively) be the same as your
daily activities.

For example, you describe "XXXX reports" as part of what you do. In Access,
a "report" is a printed set of data. You wouldn't need to keep the reports,
per se, but the underlying data.

Try this -- take paper and pencil and write down the "things" about which
you need to keep information/data. It sounds like "customers" is one of
those. When you have those categories, then jot down the specific pieces of
data you need to keep, under the relevant categories (e.g., FirstName fits
under Customer, but not under Incident). Then jot down lines between the
categories... for example, can you have one Customer with multiple
Incidents? If so, that's a one-to-many relationship (we're back to
"relational" again).

When you have that done (the "entities" and "relationships"), fire Access
back up and use the entities to design the structure of your tables.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Jeff Boyce]

The newsgroups provide free assistance. Your solicitation is contrary to
the posted newsgroup etiquette.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John... Visio MVP]

Hello Mr. Brown,

I provide help with Access applications for a very reasonable fee. I could
build this database for you for a modest fee. If you want my help, contact
me at (e-mail address removed).

Steve

These newsgroups are provided by Microsoft for FREE peer to peer support.
Stevie is a known troll who has been harrassing posters for years offering
questionable solutions an unreasonable rates. There are many excellecnt
Access developers who gladly help for free. Stevie is not one of them. If he
was any good, his past victims (customers) would be giving him enough work
that he would not need to grovel fro scraps in a free support newsgroup.

John.. Visio MVP

 

[Gina Whipp]

JBrown,

There are some FREE Access 2007 databases available from Microsoft that ight
assist you, such as...

http://office.microsoft.com/en-us/templates/CT101428241033.aspx

or the Call database...

http://office.microsoft.com/en-us/templates/TC102068791033.aspx?CategoryID=CT101428241033

 

[Fred]

A few specific notes in addition to Jeff and Gina's great advice....

I've been on both (actually all 3) sides of the fence on that one....done,
used, and had my folks use dbs for that type of thing.

I'm assuming that you want to learn Access and do this yourself.. (there are
lower cost commercially available softwares that do this e.g. "Act!" and
"Goldmine". I found it easier to create these in Access than in trying to
deal with not-very-open "black boxes" such as these.

You didn't mention it, but I assume that you need to track/record multiple
people at a company. Here's my guess at a good foundation for you: (shorten
my long explanatory names)

Make a "Companies" table with fields for all of the basic "one to one" type
info for that company. Also an autonumber PK field "CompanyIDNumber"

Make a "People" table, FK = "CompanyIDNUmber" linked to it's namesake in the
"Companies" table. Include fields for all of the "one-to-one" type info on
people. Make an autonumber PK field "PersonIDNumber"

Most likely it would be best to roll the Incidents/Supplies/Training
entities into a single "Actions" with a field to identify which of those
three things it is. You could easily add a 4th or 5th later. Also a filed
to desigate whether it is "things to do" vs. "things done", and maybe a due
date for "things to do". Decide whether these items will be tied to a person
or to a company, and insert a FK "CompanyIDNumber" or "PersonIDNumber" field
accordingly, and link it to it's namesake. Either way has it's pro's and
con's, I generally choose to link them to companies.

"Reports" would be a way of outputting data, not a table or data.

Sincerely,

Fred

 

[Fred]

Unless by "reports" you meant recording that you gave them a report in which
case such IS data, and could also be rolled into that "Actions" table.

 

[JBrown]

WOW-

What an amazing forum (trolls aside .

So I think I should provide more information. I am somewhat familiar with
access and do have a working DB at this time.

As per my course I started with pen and paper, drew my relationship and
eventually filled in tables using OneNote. I ran this by a few people in the
company and then started making tables. To be honest I have no idea why it
wasn't working at first - I just kept playing with Join Types and it
eventually worked (with a few strange issues that I can work around - these
issues are no doubt my fault I should mention).

The company I work for is huge. The database I am working on is designed to
do two very specific things: A) Show them what is possible if we get away
from paper and B) Make me look damn good!

So far I have sent out a few screen shots of forms and a screen of a report
and people have been amazed. Imagine being the manager of a large gregraphic
area with many territory managers and the only way you can track what they
are doing is by hand written reports (no standardization) and receipts from
their travels.

I won't go into the advantages of a proper DB - you all understand this
better than I.

Before I go on I am going to see if I can paste graphics into this forum.
I'll be back shortly with better information.

Thanks for everything!

JB

 

[John W. Vinson]

Before I go on I am going to see if I can paste graphics into this forum.

Please DON'T! This is a text only forum; NNTP does support graphics
attachments but they are inappropriate and unwelcome here.

If you do need to convey information that simply cannot be put into words (and
most problems here *can* be put into words), put an image up on some webpage
such as a blog or picture-sharing site and post a link here.

And good luck getting your company dragged from the 1950s into the 21st
century... sheesh...

 

[JBrown]

OK,

So a quick glance around and at help seems to indicate no pics or
attachments (this of course makes sense).

Here is my setup:

tblDealerships
ID (All other table are linked here in a 1 to many relationship)
Dealership
StreetAddress
City
Province
PostalCode
Phone
Fax
DateAquired
Chemicals
Insurance
Warranty
DealerPrinciple
GeneralManager
FinanceManager
Controller
ServiceManager

tblIssueLog
ID
DealershipID (Linked to tblDealerships/ID)
CertificateID
Customer
Product
CertificateID
VehicleModel
VehicleYear
Issue
DateIssueReported
Completion%
IssueResolved
Notes

tblReportingProgram
ID
DealershipID (linked to tblDealerships/ID)
ReviewDate
DPReview
BMReview
GMReview
SVMReview
ControllerMeeting
Notes

tblTraining
ID
DealershipID (linked to tblDealerships/ID)
Type
When
Notes

tblSupplies
ID
DealershipID (linked to tblDealerships/ID)
Product
CertificateNumber
DateSupplied

OK so there are my table linked in a 1 to many relationship (Dealerships to
everything else).

I was then able to build a simple query to pull out what I think of as the
most important information. After reviewing it with a few others I realized
that I need a mini report and a regular report. Mini report provides a
snapshot of crucial data (IssueLog stuff and ReportingProgram stuff) while
the regular report is basically a report of everything during a set period of
time.

A little background:
IssueLog:
This is where account managers record anything that goes wrong. This is why
I have a job. One of my dealerships calls me and says why didn't you pay for
claim x in Insurance/Warranty/Chemicals. I need to then get a ton of
information so I can take it back to my company and get the other half of the
story and get the issue resolved. Some claims take minutes while others take
months (this is what happens when very very busy doctors get involved).

ReportingProgram:
We keep data of all sales. We take this data and put it into a report. It is
then my job to go over the report with the stakeholders at the dealership
(owner, finance manager etc). In the tblReprotingProgram it is mostly just
check boxes but this task is considered vital and of extreme importance.

Training:
We provide sales training. Various levels and lengths. An account manager is
capable of training a new finance manager while we have specialized courses
run by our training department for more seasoned veterans. While training is
important, management is so involved in training that this information won't
really be any news to them.

Supplies:
In order to sell our products they need our forms. Legally we need enough
cash in the bank to cover every unsold (blank) form we provide so tracking
these is very important. This tbl is mostly for the account manager so at the
end of the month they can send a quick report off to head office so head
office can update their records on how many outstanding forms we have. At the
end of the year there is an audit.

Issues I am dealing with right now:

1) When reports are printed they need to list ALL dealerships no matter
what. If there is no activity of any sort we need to know. I seem to have
this working as long as I put the tables in a specific order. Originally I
had tblIssueLog/Issue as the next column after Dealerships/Dealership but
then only the dealerships with 'Issues' appeared even though they did have
values for other tables. All 'Join Types' are set to #2 which seems to have
done the trick (that and I have switched the order of the columns).

2) When a report is 'Run' I need it to ask the user what range of dates they
would like. This was covered in the course I just took so I think I will get
there on this one it will just take some time. I believe I actually adjust
the related Query (criteria filter)?

3) Formatting Reports
This is where my lack of knowledge really comes through. For my big report I
think I am simply going to run 4 reports (Issues, Reporting Program, Training
and Supplies). This is fine and it will work. What I had hoped for was a
report with sections:
-----------------------------------------------
ABC Insurance CO

ISSUES SECTION
Dealership 1
Dealership 2
Dealership 3

REPORTING SECTION
Dealership 1
Dealership 2
Dealership 3

TRAINING SECTION
Dealership 1
Dealership 2
Dealership 3

SUPPLIES SECTION
Dealership 1
Dealership 2
Dealership 3
_________________________________________

From what I can tell the above isn't really possible. It would just be nice
to click 1 button and get everything I need. If I am wrong please let me know.


4) Data entry. This is a douzy (I think). I am hoping that I can either log
on to a server from the web and fill out forms or do it on my own machine and
send the froms in as emails to a server (Sharepoint?). Preferrably I would
like the option of either or. This was part of the advanced section of my
access course that I have put off until I have the basics down (for my
demonstration purposes as long as it can be done that is fine).
Anyway that is where I am at. If I had to stop now and present I could. I’m
just having such a good time that I thought I would keep going. Even just
reading all the forum post I have learned a ton.

Thanks for all the help!

JB

PS: Off to see if I can return the favor in the nubes section!

 

[JBrown]

Great idea John.

And you are right words worked just fine - thanks.

 

[Fred]

You have a good structure, including being a good foundation for the eports
that you want to generate.

To make that one big one, make 4 sub reports (issues, reporting, trining,
supplies), then make your main Dealership report, and put those 4 in as
subreports.

Your #4 is probably too broad and vague to answer. One note is that you're
going to have to clarify to yourself / forum what you mean by sending a
"Form". In Access, a form is a portal for entering/viewing data into/from
tables, so you would not be sending an Access "form". Outside of databases,
a form is a document with questions and blanks for answers, and a completed
form (which is what I assume you mean) is one set of answers to the
questions, plus a copy of the questions to provide context for the answers.
In Access, that would normally be a REPORT with that stuff on it, limited to
that one set of answers. You could store it as a document (e.g. pdf) and
send it.

 

[JBrown]

Fred,

Thanks so much. It feels great to have the basics right. Sub-reports sounds
great.

I just finished the last part of my course that talks about the different
types of access databases. To start of with I will just be using access as I
will be the Beta-Tester and eventually there will be a couple more account
managers that join me.

I will set up a switchboard and have access default to this switchboard upob
startup (brilliant feature by the way). So as you indicated there will be no
'sending'.

I have to send off a presentation pretty quick - I'll let you know how it
goes.

Thanks again,

Jude