Checklist in database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

D. M. - 25 Aug 2006 16:21 GMT

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

Reply to this Message

Rod - 25 Aug 2006 17:04 GMT

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.

> I have created a Lease Management database for my employer. This database
> houses lease and rental information for over 100 retail stores.
[quoted text clipped - 26 lines]
>
> D

Reply to this Message

D. M. - 25 Aug 2006 18:24 GMT

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,

> Have you tried to just import the spreadsheet as a table?
>
[quoted text clipped - 33 lines]
> >
> > D

Reply to this Message

Duane Hookom - 25 Aug 2006 19:59 GMT

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

Signature

Duane Hookom
MS Access MVP

>I have created a Lease Management database for my employer. This database
> houses lease and rental information for over 100 retail stores.
[quoted text clipped - 29 lines]
>
> D

Reply to this Message

D. M. - 27 Aug 2006 18:41 GMT

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.

> You might want to check out this thread found through google groups search
> on:
[quoted text clipped - 33 lines]
> >
> > D

Reply to this Message

J. Goddard - 27 Aug 2006 20:50 GMT

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

> 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
[quoted text clipped - 63 lines]
>>>
>>>D

Reply to this Message

Duane Hookom - 27 Aug 2006 20:57 GMT

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

Signature

Duane Hookom
MS Access MVP

> 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
[quoted text clipped - 71 lines]
>> >
>> > D

Reply to this Message

D. M. - 28 Aug 2006 13:52 GMT

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!

> 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
[quoted text clipped - 94 lines]
> >> >
> >> > D

Reply to this Message

Duane Hookom - 28 Aug 2006 14:35 GMT

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

Signature

Duane Hookom
MS Access MVP

> 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!
[quoted text clipped - 108 lines]
>> >> >
>> >> > D

Reply to this Message