Big question. Need help desparatly!

[Guest]

Alrighty. I am currently trying to design a database for a Fire prevention
company. What we want the database to do is track the extinguishers that we
service according to the companies that we service. What i had in mind was to
have a company table, that would have the name, address, mailing address,
contact, ph, fax, email... stuff like that. With that, i would create a
relationship thru the customer name/id (if i make one) and relate that with
the extinguisher list. Now, each customer may have a lot of extinguishers. So
for the extinguisher list i had all the extinguishers in it. The fields for
that are Customer name, facility (because teh customer may have different
bldg's. Like a business that owns multiple apt bldgs.) location (of the ext
in the building), type, manufacturer, serial #, chem type, mfg date, last
h-test (a type of service. done every 12 years), last 6-year (another
service... done between h-tests). Im wondering if this is the best layout. As
well, i want to be able to query the extinguisher list to find the ones that
need to be done for that company. I presently have a query that will do that
according to the present year(needed to calculate what service is req'd.)
what company, and what facility. When the query is run, it asks these
questions to the user, but i wanted to be able make a drop down list for the
customer name (to help eliminate spelling mistakes as you need to enter the
name correctly, and some company names are rather long.) I also wanted to be
able to enter multiple facilities for the same business, so i could find all
the extinguishers owned by a single company. As well i have a lots of sheets
in excel that i need to import and add to the extinguisher list. I have that
mostly made up so it works. I have an excel sheet linked into access that i
just add the info too, edit it, then its all in access, and i have a premade
append query that i run and it adds the info directly into the extinguisher
table. I think thats about it. Also, i need info on what kind of history (if
any) the database keeps. As in what past services have been done on a certain
extinguisher.

 

[tina]

suggest you start by modeling your tables/relationships. after those are
structured correctly, then you can move on to queries, forms, reports.
recommend you read up on data modeling, so you can do this step right,
otherwise you're going to have a real mess on your hands. for a number of
excellent resources, see
http://www.ltcomputerdesigns.com/JCReferences.html
starting with the "Database Design 101" and "Starting Out" links.

suggest the following tables as a minimum (this is *not* intended to replace
your own study of data modeling):

tblCustomers
CustomerID (primary key)
CompanyName
ContactFirstName
ContactLastName

tblAddresses
AddressID (pk)
CustomerID (foreign key from tblCustomers)
Address1
Address2
City
State
Zip
AddressType(business, mail, property location, biz/property)
the address type field allows you to store all addresses in one table.
Business type is the customer's street address; Mail type their business
mailing address; property location is the address of a property with
extinguishers that you service; Biz/Property is for when the company's
business address also has extinguishers that you service.)

tblCommunications
CommID (pk)
CustomerID (fk from tblCustomers)
Type (phone, fax, cell, email)
Number (the actual phone number or email address)

tblExtinguisherTypes
ExtinguisherTypeID (pk)
Type
Manufacturer
ChemicalType
(this is a generic list of all the extinguisher types your company
services.)

tblLocationExtinguishers
ExtinguisherID (pk)
AddressID (fk from tblAddresses)
ExtinguisherTypeID (fk from tblExtinguisherTypes)
(one property probably has multiple extinguishers, so this table allows you
to list each extinguisher without listing the property address multiple
times. you may need additional fields here to specify the exact location of
the extinguisher, such as floor, wing, which building at a multi-building
address, etc.)

tblServiceTypes
ServiceTypeID (pk)
ServiceName (h-test, 6-year test, etc)

tblExtinguisherServices
ServicesID (pk)
ExtinguisherID (fk from tblLocationExtinguishers)
ServiceDate
ServiceTypeID (fk from tblServiceTypes)
TechID (fk from tblServiceTechnicians)
(if multiple tests may be done on the same extinguisher on the same date,
move the ServiceTypeID, and possibly ServiceTechID, into a separate table
and add the ServicesID as a foreign key to that table.)

tblServiceTechnicians
TechID (pk)
FirstName
LastName
(other fields that describe a service technician.)

the relationships are:
tblCustomers 1:n tblAddresses
tblCustomers 1:n tblCommunications
tblAddresses 1:n tblLocationExtinguishers
tblExtinguisherTypes 1:n tblAddresses
tblLocationExtinguishers 1:n tblExtinguisherServices
tblServiceTypes 1:n tblExtinguisherServices
tblServiceTechnicians 1:n tblExtinguisherServices

hth

 

[Jeff Boyce]

Stuart

I'm having trouble envisioning your "layout", so I'll try to paraphrase what
I understand from your description...

Your business has customers.

Your customers may have more than one physical location/address.

Each physical location will have one/more extinguishers.

Extinguishers are described by some "static" characteristics (e.g., serial
number, capacity, type, manufacturer, ...).

Extinguishers have zero-to-many "service" characteristics (e.g., service
date, type of service, comments on that service, ...).

If this is a reasonable paraphrase of your situation, it could also a serve
as a description of the tables you could use:

tblCustomer

trelCustomerLocation (a "relation/resolver" table)

trelLocationExtinguisher (a "relation/resolver table)

tblExtinguisher ("static" characteristics)

trelExtinguisherService (zero-to-many from tblExtinguisher)

Or have I misunderstood your situation?

Good luck

Jeff Boyce
<Access MVP>

 

[Margaret Bartley]

You will probably have to do some modifications to your Excel import
routines, because you will probably be importing one Excel row into several
tables. One thing you could do is change your Excel spreadsheet so that
instead of the company name, it has the company number from your Access
table.

If you have one Excel row that has the company name, the fire extinquisher
location, the fire extinquisher characteristics, and the service
information, you will have to do several imports to connect the service
information to the fire extinquisher ID, and the rest of the information
will be imported in separate steps into the other tables.