Call Management Database Access 2007

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!

Great idea John.

And you are right words worked just fine - thanks.

"John W. Vinson" wrote:

On Tue, 30 Dec 2008 08:43:01 -0800, JBrown
wrote:

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...
--

John W. Vinson [MVP]

"Steve" schreef in bericht
m...
A snake crawled out of his hole !!!!


As a said earlier... Nicely spoken Steve... as always...

Regards,
Arno R

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.

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