Grouping by Year

[monkeytunes]

After lurking around the forums for a couple weeks, I've decided to undertake some DB redesign, and I could use some guidance towards achieving my goals.

At my company, we send out a contract (consisting of many exhibits) to our consultants every year, while tracking correspondance with those consultants. In a perfect world, I'd like the users to see a form in single record view set up like this:

a. At the top, the consultant's name and some contact info. A combo list would let users flip from consultant to consultant (We have about 50).
b. A subform which allows the user to see which components of the contract have been executed and which are pending. This should be in single form view, with a different year apparent when you switch records.
c. A second subform (maybe/maybe not within the first subform?) which shows the correspondance relating to that year's contract.

My problem is with that pesky year criteria. Currently I have three tables, set up like this:

tblContact - the usual garbage, w/r/t phone numbers, addresses, etc., with an indexed "consultants" field
tblContracts - linked by "consultants", this has a series of dates entered into fields like "ContractSent", "ExhibitA", "ExhibitB", etc.
tblCorrespondance - This has no unique index (except for the Autonumber), and keeps a running tally of which consultant we spoke to, the date we spoke to them, and a Memo field for a summary of what was talked about. It's not unsual to correspond with consultants many, many times over the contract, are many consultants in a single day, or the same consultant twice in the same day. As a result, this table has many repeating consultants, and many repeating dates.

Right now I have everything set up so that users can flip from year to year no problem for contract viewing, but since I can't relate dates from one table to dates from another table by year, they see ALL the correspondance from previous years for every consultant.

I've tried using an text extra field in tblContracts and tblCorrespondance and making users enter 2004 or whatever and relating the tables/queries that way. However, I'd rather relate it by the year within the Date/Time fields from both tables, but I can't figure out how to relate a date field (like "ContractSent" which would probably be 1/1/2004 for every consultant's contract in 2004, 1/1/2005 for every consultant's contract in 2005, and so on) to many records in a date field in another table which will have a constant differences in days and months. All I want is that pesky year! Any ideas?

-Matt

 

[FoFa]

Sounds like what you really need is a connection is foreign key from tblCorrespondance back to tblContracts. Sounds more like a design issue to me.

 

[monkeytunes]

Sounds like what you really need is a connection is foreign key from tblCorrespondance back to tblContracts. Sounds more like a design issue to me.

But what is that foreign key going to be? An extra field in tblCorrespondance and tblContracts where users have to put in "2004", as well as putting in the actual date of their correspondance as it occurs? That's what I'm currently using, and while it's working, it would be an improvement if I could use the actual Date/Time fields to sort by year. One less thing for users to fill in (and they inconveniently forget it all too often), and one less redundant field of data.

If you link Date in one table to Date in another, your join properties still don't allow you to join JUST by the year (i.e. XX/XX/yyyy), they require the whole dd/mm/yyyy to match.

 

[KKilfoil]

You have some normalization issues. As soon as you find yourself making fields like ThingA, ThingB, etc, you should be adding at least one more table.

I would make something like the following:

[tblContacts] much like you have. Ensure there is a unique ID Field, preferably an autonumber but definitely should be type LONG if not.

[tblContracts]
ContrID
ContrYear
whatever else is unique to a particular contract

[tblExhibits]
ExhID
ExhContractFK (is a foreign key to tblContracts to make a 1:many relationship)
exhDescription
whatever else

[tblConsultantContractLink]
CCL_ID
CCL_ContractFK
CCL_ContactFK
This table creates a many-to-many link between a contract and a contact

[tblCorrespondance]
CorrID
Corr_Date
Corr_Memo
Corr_ConsultantFK
whatever

Now, if a particular correspondance can only deal with one contract, then you could add a linking field here to the contracts table. If a particular record can only deal with one exhibit, then add a linking field here to tblExhibits.

I suspect that a single correspondance can deal with multiple exhibits, so you need another many:many relationship:

tblCorrespondanceExhibitLink
CEL_ID
CEL_CorrFK
CEL_ExhFK

which ties everything together.

The 'current year' requirement can be handled by filtering on your main form for Contracts (with a list box or an edit box). Relatively simple code can do this atuomatically.

HTH.

 

[monkeytunes]

You're probably right about the normalization issues; it gets away from me more often then I'd like.

But there are some Real World restraints that made me set it up as I did, and I probably could use some help breaking out of the spreadsheet mentality. EVERY consultant with whom we work gets one contract, consisting of 4 parts (3 exhibits and the cover agreement). They have to sign and return all 4, or we don't work with them.

When we call and correspond, it might be about anything, or we might just be taking a note of something we received, or who knows what, so I have the correspondance linked to contacts because sometimes we need to see ALL the correspondance over several years. What I want is to be able to query all the correspondance by year, and link it to contracts so it's viewable on the form. Right now there are two subforms, and they're not dependant on each other at all.

I'm attaching a dummy database with the tables I have, along with a form which is what the users see. I'll take any and all tips/corrections/ideas with appreciation.

-Matt

 

[Pat Hartman]

If you want coorespondance to be related to contracts rather than consultants, change the foreign key. In the coorespondance table store the key to the contract rather than the key to the consultant.

 

[monkeytunes]

If you want coorespondance to be related to contracts rather than consultants, change the foreign key. In the coorespondance table store the key to the contract rather than the key to the consultant.

That makes sense, since each contract (actually its exhibits) will be related to each consultant, but I still want the chain to be grouped by year, according to the Date/Time fields of the tblCorrespondance and the year of the contract in tblContract, and I don't know VBA at all to pare down the Date/Time fields as someone else suggested. Is there a way to do this, or am I stuck making users fill in an extra "year" field on tblCorrespondance for each record?

(And, of course, I'll take any normalization tips/ideas with gratitude! I posted a sample DB a post or two back.)

-Matt

 

[Pat Hartman]

No. Do not use an extra field to store year. Use the Year() function to extract the year part of a date.

 

[monkeytunes]

No. Do not use an extra field to store year. Use the Year() function to extract the year part of a date.

Oh man, I bust out laughing when I read that - that was all I wanted from the beginning, and I had no idea what the function would be! I'm so oblivious; that's as obvious as eating food when you're hungry, or taking a nap when you're sleepy. WHY DIDN'T ACCESS HELP SHOW ME THAT AFTER HALF AN HOUR OF SEARCHES?!

Thanks!

 

[Pat Hartman]

Access is not very helpful when it comes to giving you an overview of functions. You need to be aware that Access help comes in two flavors - Interface and VBA and you get one or the other depending on where you are when you ask for help. From the db container window, you will get interface help, from the VBA window you will get VBA help (such as it is).

This is such a problem for everyone, me included, that I made a glossary of Access functions. It doesn't describe them, it just lists them in a sortable list. You can sort by name or by category so you can see all the "date" functions together.

Function Glossary

If you see a function that you want to learn more about, look it up by name from help in the VBA window.

You can get a list of functions from Access help but it is only in alpha sequence so it is virtually impossible for someone who doesn't have any idea what the name of the function he is looking for is. But to get to it, you need to use the table of contents from help in the VBA window. I've included a picture.