Design Question

[kousotsu]

Here's my dilemma: I need to have eight datatypes (complaint types), organized by one field (feeder number - PK), separated by month. I'm trying to think of a strealined way to make this happen, but so far I have only come up with creating 12 identical tables, named for the month. Maybe I'm not thinking laterally enough?

I am sure that this would work (I can use very complex layered queries to access the information), but I don't think it's the most efficient way to do it.

Also, as far as the Form is concerned (want to use one form to enter all the data), I haven't yet figured out how to efficiently change the RecordSource property by each month's table. As the tables have the same datatypes (and feeder numbers), I'm not seeing the disconnect.

Any help or suggestions will be greatly appreciated.

- John

 

[ghudson]

Creating a table just to store a "month" of data goes against the normalization of a db. You should only have to add a "month" field to your table to store the month value for each record.

 

[kousotsu]

Here's a for-instance that might make more sense of my question:

in January, there were 10 complaints in three types on a singler feeder number

then, in February, there were 16 new complaints in the same three types on the same feeder.

If I were to just add the month to the table, I wouldn't be able to get this data as separate entities. Think of it this way: I have an Excel workbook with each month as a worksheet and each page's layout is identical. The only difference is the data entered into each. Also, there are a few worksheets placed in the WB for computations (I'm going to worry about these later as they can all be reported by queries).

Does that clarify at all?

 

[kousotsu]

BTW - your avatar is an energy powerup from Descent, isn't it? ^_^

 

[ghudson]

If I were to just add the month to the table, I wouldn't be able to get this data as separate entities.

Not sure I understand how your data structure is set up but I imagine a flat file with all the columns of data...add a month column to the same data and key the month that the record [row] is for. All the data is the same yet you have identified the row [record] with the month it belongs to with the new month field [column].

If that is not close to what you have then I suggest you provide more details and or examples of your data. Maybe someone else can pickup what you are after.

Good luck!

 

[Pat Hartman]

Finally, a member who can spell separate I'll bet you're over 40 or english is not your native language

You need only a single table to hold your data. You may also want a lookup table to hold the 8 complaint types. You would use the tblComplaintType to populate a combobox on your forms. Using a lookup table of this type helps you to ensure data integrity by only allowing valid selections.

Your main table would be tblComplaint. It would contain several columns:

tblComplaint:
ComplaintID (autonumber primary key)
ComplaintTypeID (foreign key to tblComplaintType) - identifies the complaint being made.
ComplaintDate - date complaint was made.

This very simple table makes for very simple queries. You would use selection criteria if you wanted to limit the rows returned to a single year and month. You could also create crosstabs using the crosstab wizard if you wanted a report view that would look like a spreadsheet.

 

[kousotsu]

A little less confused

Finally, a member who can spell separate I'll bet you're over 40 or english is not your native language

I'm actually a 27 year old American Mutt with a penchant for learning and language. I do my best not to mangle the English language - gotta represent, y'know ^_^.

As far as the DB goes, I've *tried* to address the issue I'm having with my superiors; however, there is tremendous resistance here to anything "new." They are still thinking in Excel (and don't really know Access) so I'm quite limited in what I can do. I'm a persuasive fellow - and patient - but it takes time for me to work my mojo ^_^.

I agree that a flatfile would be easier, but the higher-ups have themselves convinced that a separate table is required for each (they're looking at the print function, I'm guessing, and thinking that they won't be able to get each month alone without it on its own "sheet" - silly Excel novices ^_^).

Here's what I have so far (in my own time at home, as a spare project for "convincing" them):

tblSchedule
· (linked from another DB, contains PK [feederNumber]
and pertinent temporal data)
tblY14ByMonth
· feederNumber (PK)
· genDate {displays month only, defaults to Now()}
· ctDebris {count of Debris-related complaints}
· ctXsvPoorTrim {count of Excessive/Poor Trimming-related complaints}
· ctHangers {count of Hangers Left on Site-related complaints}
· ctLackPoorNotify {count of L/P Notification-related complaints}
· ctPropDamage {count of Property Damage-related complaints}
· ctMisc {count of Miscellaneous complaints}
frmY14
· RecordSource = tblY14ByMonth
· VBA code allows input of 'feederNumber' with AfterUpdate kicking out
prior inputs to screen or throwing error for "not in list."
qryY14ByMonth
· controlled by Form & Macro that changes SQL based on desired data.

Sound a little more streamlined and "correct?"

Thanks for the input (and the compliment! ^_^)!

 

[ghudson]

but the higher-ups have themselves convinced that a separate table is required for each (they're looking at the print function, I'm guessing, and thinking that they won't be able to get each month alone without it on its own "sheet"

You need to convince them that only one table is necessary and that you can filter the records based on the date [month] for viewing or printing. Creating a table for each month is a waste [of resources and design time] and will increase the amount of work you will be required to do to maintain the db. There is a standard called "normalization" and you should really research it and present the facts to your higher ups.

 

[Mile-O]

tblY14ByMonth
· feederNumber (PK)
· genDate {displays month only, defaults to Now()}
· ctDebris {count of Debris-related complaints}
· ctXsvPoorTrim {count of Excessive/Poor Trimming-related complaints}
· ctHangers {count of Hangers Left on Site-related complaints}
· ctLackPoorNotify {count of L/P Notification-related complaints}
· ctPropDamage {count of Property Damage-related complaints}
· ctMisc {count of Miscellaneous complaints}

Sound a little more streamlined and "correct?"

You have a repeating group here. Too many counts (and these should be saved for a query, form, or report.

 

[Mile-O]

Finally, a member who can spell separate

I take offence at that, Pat. Some people can still spell it.

 

[Pat Hartman]

OK, so there are three of us I'd be willing to wager a beer that if you did a count, the number of instances of "seperate" would exceed the number of instances of "separate".

BTW, The reason I mentioned the over 40 was because teaching has changed so much in the last 30 years. I am a very poor speller but I remember that there is "a rat" in separate.

 

[KKilfoil]

kousotsu:

I assume your application is for right-of-way tree management issues. I have an access db that does that same thing, and it is structured more or less as Pat has suggested.

Right now, you have 8 defined complaint types, and you could probably design complex queries for summing up the different types of complaints, but your forms and reports are going to be painful. What are you going to do when someone identifies a 9th complaint type? a 10th? a 25th?

I have only three tables in my db:

tblComplaints:
CompID: autonumber PK
CompDate: date/time
CompType: long, Foreign key (FK) to tblComplaintTypes
CompLocation: text
CompNotes: text, in case something about a partucular complaint is unique
Complainant: long, FK to tblComplainants

tblComplaintTypes:
CompTypeID: autonumber PK
CompTypeDesc: text, descripton of complaint type
CompTypePriority: long

tblComplainants:
WhoID: autonumber PK
Telephone
Address
etc...

This structure allows for new complaint types, easy querying by month or type, etc.

You could easily add in a field (or a lookup table) for the feeder.

HTH

 

[kousotsu]

Just what the doctor ordered

You guys really are the best - don't let anyone tell you otherwise. ^_^

KK: That's the application, exactly. I've built the trimming/forestry part already (got them on the bandwagon, finally, for normalization), but this Y14 complaints bit has been a runaround.

I'll put it together like you have it (using the lookup table for this years trimmed feeders), and see if I can pitch it as having come from the forum - that would at least tell me if they're arguing because they just don't like me. ^_~

Thanks a ton for the help, guys. It's most appreciated!

Oh, and you're right that teaching has changed for the worse. Can you believe that I went (mostly) to public school and still escaped a free-thinking and learned individual? ^_^*