Record with Many Dates, display dates in Date order 1

[vivasuzi]

Hi Guys,

This is what I have. A table with a person and various important due dates (ie 'paper due by', 'test due by', 'meeting by'... etc). There are a lot of dates for each person.

TABLE: person,paperdue,testdue,meeting,secondpaper,etc...
sample: Mike,1/15/05,2/25/05,1/30/05,2/16/05,etc,,,

I want to know if I can query ALL the dates for that person and sort them by date so that in a report I could essentially show:

'Report for person x'
January: Paper due 1/15/05
Follow up Meeting 1/17/05
February: Test 2/25/05
April: Following up test 4/15/05
May: Meeting 5/5/05

Every date in this persons record is specific to the person and therefore this is the only way to make the table. Is it possible to query and sort multiple items from one record?

----------------------------------
As an extra, I would also like to make a Master Report that would display everyone in the DB and all important dates in the same fashion. This part is not as necessary right now, I'm more concerned about the first part.

Please let me know bc this would be an awesome feature!

Thanks :-D

*Suzanne*

http://www14.brinkster.com/vivasuzi

 

[GingerR]

Maybe you can change the structure of your table? I'd have this set up:

tblPerson
Mike
Jim
Steve

tblEvents
PaperDue
TestDue
Meeting
FollowUpMeeting

tblPersonEvents
Mike PaperDue 1/15/05
Mike TestDue 8/1/05
Mike Meeting 9/1/05
Jim PaperDue 1/18/05

etc

Is that doable? I think this way you'll find creating queries and reports thru the rest of your db will be much easier.

Also, if 'Events' are not static (i.e. there will be various and sundry events, each with diff names for diff people as time goes on) then skip the second table. It was just a suggestion.

How does this sound to you? If you can restructure your table, you'll see how easy it then is to write your report.

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[vivasuzi]

Actually sounds really good. I still have time to redo my table, and now I'll have to change the forms and reports I already have, but this would be worth it.

Thanks

*Suzanne*

http://www14.brinkster.com/vivasuzi

 

[GingerR]

Yes it can be a bit irritating right now, but it's best to do it while it's still pretty new. So get it set up like that then get back to us if you still need help.

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[vivasuzi]

This idea is nice but, It is making doing my main form really complicated. I think I might have to pass on sorting by date because I also have non-date fields in the original table. As of now this is what I tried...

ContactsTable
Contact-id
Contact-name
Any-non-date-fields

FieldsTable
Field-id
Field-name
Field-Description

ContactDataTable
Contact-id
Field-id
Field-date

So Field-date is the date for each field for each contact. Although this is great for calendar views, I have over 80 different dates I'm tracking so making a form using this seems to be way to complex. Especially since I don't want the user to select 'Field-id' for each element of the form. My form right now is really easy to use. So I think I'll be leaving it as is.


Is there any other way to sort the dates within one record??

*Suzanne*

http://www14.brinkster.com/vivasuzi

 

[vivasuzi]

Can I do this with a Macro? I'm gonna *try* to make a macro that will get all the fields for the client, create a new temporary table, sort the fields, and display the fields in order. We'll see if this works!

Still up for suggestions!!

*Suzanne*

http://www14.brinkster.com/vivasuzi

 

[GingerR]

You'd have to write some VBA code that loops thru all of your date fields and puts them into a temporary table, with 'headings'. I'd make a table that has your field headings of the date fields in them (i.e. "Follow-Up Meeting"), and a second field that's blank and is Date type. Make a recordset of the row of data that's for that certain client, find each field name that matches the 'title' in this table, then plunk the date in there.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[vivasuzi]

That's what I was thinking just now actually I already made a table with all the field names, now I am going to try this out.

*Suzanne*

http://www14.brinkster.com/vivasuzi