Checklist in database

[Guest]

I have created a Lease Management database for my employer. This database
houses lease and rental information for over 100 retail stores.

I have been asked to include a table/form which will list the landlord and
tenant responsibilities of the Lease (i.e., who is responsible for repair of
roof, plumbing, etc.) There are approximately 25 standard "responsibilities"
in each lease, but there could be more, or less depending on the lease and
the building type.

This data now resides in an Excel spreadsheet as follows:

Following are all Columns:

Store Number
Item #
Item

The following are all columns with checkboxes:

Provided by Landlord at no cost
Provided by Landlord cost included in rent
Provided by Landlord at Tenants cost
Provided by Tenant at Tenants' cost
Not applicable

Does anyone have ANY idea how I can convert something like this into Access?

Thanks,

D

 

[Guest]

Have you tried to just import the spreadsheet as a table?

From Access: File -> Get External Data ->Import

Honestly, I'm not sure about importing the text boxes but if they import
corrrectly they may come up as either 0s and 1s or Trues and Falses.

 

[Guest]

Thanks for responding Rod. Yes. I've tried importing, but the table is not
"normalized". The table will have to relate to the other tables in the
database, but I'm not sure how to do it. Will each "item" or responsibility
be a new record? If so, do I have to input the standardized 25 items for
each location? The tables are set up as follows:

Stores, StoreID (PK)
Leases, LeaseID (PK), StoreID (FK)
Rents, RentID (PK), LeaseID (FK)
Responsibilities, Response (PK), LeaseID (FK)

Anybody??? This is driving me NUTS!!

Thanks,

 

[Duane Hookom]

You might want to check out this thread found through google groups search
on:
group:*access.queries* insubject:checkbox author:holly

 

[Guest]

Thanks Duane. But I don't think that really answers my question. I'm not
concerned about the actual checkboxes. I'm trying to figure out how to
arrange the data. Maybe I should rephrase my question:

The main tables in the database are Stores and Leases and Payments. Each
store has 1 lease which has a list of at least 25 items that are "standard"
items, there could be more or less items depending on the lease.

Example:
Checkbox Checkbox
Checkbox
Item Landlord Repairs Tenant Repairs
N/A
Plumbing X
Electrical X
Air Conditioning
Heating
Floors
Walls
etc., etc.

Some leases will have addtional items such as elevators, escalators, etc.

I don't know how to arrange the data in a table. The table will obviously
relate to the Lease table. Will each "item" be a record? How can I have
these 25 items recur with each new store/lease and be able to add more items
or edit/remove from the "standard" items?

Again, any help is appreciated.

 

[J. Goddard]

Hi -

It seems as if checkboxes may not be the appropriate data type for your
items. From your description, each item has 5 possible values. You
could use a code value for each, the simplest being numbers 0 to 4, and
having a corresponding lookup table.

I think you should have a separate table for the lease items, where each
each record is a lease item for a particular store lease. That way, it
does not matter how many lease items there are for any given lease.

Your data could be easily displayed or entered in a form-subform type of
arrangement, and printed reports can be grouped by lease.

Converting from your current Excel spreadsheet without some fancy coding
tricks might be difficult - you may have to just bite the bullet and do
it manually.

Hope this helps

John

 

[Duane Hookom]

I think it does apply since Holly's original question seemed to have a table
structure where the "25 items" were columns and the solution was to create
up to 25 related records rather than an un-normalized structure that used
items as fields.

Looking at your example I would create a table like:

tblItems
===================
ItemID autonumber primary key
ItemTitle values like Plumbing, Electrical, AC, Heating,...

tblLeases (one record per lease agreement
==================
LeaseID primary key

tblLeaseItems
======================
LeaseItemID
LeaseID relates to tblLeases.LeaseID
ItemID relates to tblItems.ItemID
TermStatus field to store who provides or whatever

 

[Guest]

I'm sorry Duane, you lost me! I created the tables as you suggested. Now
what? How do I get this list in a form? Oh, I'm so confused!

 

[Duane Hookom]

You use a form based on tblLeases and a continuous subform based on
tblLeaseItems.