Relationships are *FUN* !

[SharonC]

Hello

I'm working on a database for a Homeowner's Association.

I'm creating "Complaint" forms.

There are 15 different types of complaint items. I'd like to set these up as yes/no checkboxes. They are as follows:
Peeling paint
Loose/Missing Roof Shingles
Sheds/Fence in disrepair
Vacant Building
Missing/Deteriorating siding
Gutters Rusted/Missing
Street number not displayed
Grass needs mowing
Trash/Junk in yard
Inoperable Vehicle
Graffitti
Dead Trees/shrubs
Illegal Dumping
Recreational Vehicle in yard
Other

I first started out with just two tables - Address and Complaints - but I've run into the situation where I can't produce two concurrent complaints against one address at a time.

My thinking is that this is a one-to-many-to-many relationship (one address, many types of complaints, many possible complaint letters to send).

I want one table for addresses (address, section)

I need to be able to send a form letter out at least 3 different times before handing the complaint over to the lawyer for further work. Therefore, each record would have date#1, date#2, date#3.

Misc. needs:

I want to have check box to say "Send Letter #1?" and date#1 will be filled in. (I have that working now via update queries)
I want to "store" a picture for each complaint sited. (I have this working now)

I am going to try an attach two .jpgs of the relationships as I've progressed thus far. OldComplaintRel.jpg is what I have working thus far and ComplaintRelation.jpg is where I've broken down the *problem* into three tables, but I just can't seem to get my mind wrapped around correctly.

I sincerely appreciate anyone taking the time to read all this rambling derth of information. If there is some area where I could read further on this type of subject, I'll be glad to bury my nose elsewhere.

 

[Tekime]

Hi SharonC,

I'm no guru myself, but if I had to tackle that I would try to normalize your tables further.

The first thing I noticed is that your complaint types are stored individually in the complaint table as fieldnames. You can make it easier to maintain those types and use them in other relationships by abstracting them a bit. I would set up a separate table "tbl_ComplaintTypes" or what have you, and create two fields: complaintTypeId and cmpTyp_text. The cmpTyp_text field can hold the values "Peeling paint", "Loose/Missing Roof Shingles", etc. and the Id is just an autonumber.

I made up a small .mdb to show you what I'm describing here, if you want to play with it. I'll attach a small screenshot of the table structures, too.

I see no need to repeat the date fields in your ComplaintLog table for each of the three complaints. I am going under the assumption that you send three letters per property; if you are sending three letters per complaint, and each letter is regarding the exact same complaint, you can stilll avoid the additional date fields by creating another table, say "tbl_LetterDates" to store the date each letter for that complaint is sent. Otherwise, you can just create a new complaint for each letter to send on that particular address. (If this last part is too confusing, let me know exactly what decides when you send the letters and I'll try to clarify).

Hope I didn't serve to confuse things even further Good luck!

 

[Tekime]

Here is the table structure in the above DB

 

[SharonC]

Thanks, T!

Thanks for the quick response! I started to work the Complaint Type table as you have suggested, but I've gotten "hung up" with the idea that I wanted checkboxes (True/False). Guess I'll try it another way like "if Complaint_Num = 1 then Paint Peeling = True).


Again, thanks for the quick comeback and I'll look into your suggestion ASAP. If anyone else has an idea, I'd love to hear from them.

 

[SharonC]

Dear T.

Could you revisit my problem once more?

I have taken your database of breaking down the complaints into separate tables and my input forms are working much smoother now. To that end I thank you very much.

However, I want to issue a report as shown in oldform.jpg(checkboxes).

I am grouping the report on the Address and while I could get the one or many complaints to "print out" on the form, but I would rather have checkboxes.

(arrghhh I know what I want I just can't explain it)

such as: 1032 Weathervane Ct. has 3 complaints. See query.jpg

I want the form to print all the 15 complaints that are possible against an address, but I want the 3 complaints that apply to the address "checked".

Is this possible? Or do I have the report based on an incorrect query?

I don't mean to ask you to do my work for me, I just can't seem to figure this one out.

Thanks,

SharonC

 

[Tekime]

Hi Sharon, I hope I'm not too late to the show, I've been battling my own reporting issues as of late

I think I know what you want to do, could you post an .MDB of what you have so I can take a look at how your DB structure is now?

I think you can probably add a check box beside each complaint line, and use a little VBA code to determine whether it applies to the particular address.

I'll do my best to help out, I'm no seasoned pro like some of the gurus around here, but I've gotten my hands nice and dirty with a large project I've been doing for my company.

If I'm too late, I'm sorry!

 

[SharonC]

Hi, T!

Thanks for thinking of me. Do not worry about the timeframe of this project. It's totally a "voluntary" thing on my part (i.e., if it gets done, it gets done...otherwise, "no problem, man!")

I'll post what I've "reconfigured" from what you've given me thus far as soon as possible and I'll also post what I'd like the "report" to be with it.

Thanks for thinking of me...and thanks to this area for the exchange of "ideas".

Peace

Sharon

 

[SharonC]

Okay, T. Here 'tis.

Please take a look at this AT YOUR LEISURE! There's absolutely no rush on this! I just find this problem perplexing yet interesting and would enjoy working through this with you, IF AND WHEN YOU HAVE THE TIME.

 

[Tekime]

Hi Sharon, I don't see an attachment? Is it just me?

 

[SharonC]

Crimeny!

Guess that was a big fat fingered mistake. I'm trying again. Thanks!