Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2006
    Posts
    15

    Red face Unanswered: Grouping multiple date fields on report

    Desperate for any help. I have a database with one table and one form. It is for tracking news events. The events may be posted out to staff up to 12 times so I have 12 separate date fields. I have to create a report to show the event and what dates it has been posted. I don't want to put 12 date fields on the report. I need to show the event and one date field listing all the dates it has been posted. Is this possible please?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    I am afraid your mistake was having only one table.
    The below might look daunting but please read through - you only have one table and are already struggling so imagine how hard things will get when you are asked to throw together more databases.
    http://r937.com/relational.html

    However - to answer the question - try concatenation. For your report's recordsource query:
    Code:
    SELECT DateField1 & ", " & DateField1 & ", " & DateField1 AS MySingleDateField
    FROM MyTable
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2006
    Posts
    15
    Hi
    Thanks for replying. Do I have to split my table into two now: one for the events and one for the dates? Also, do I type the - SELECT Datefield bit - in the query (I believe this will be a union query?). Thanks for your help.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    You don't have to do anything however the link I posted you to is about good relational design. A good relational design (based on the little we know) would suggest that you should have two tables - so you certainly made good sense of the information.

    I am not certain precisely what you require. Given your current structure, 12 Uniion queries would provide an output similar to a good, relational design. Perhaps you could provide some sample data and how (with that data) you would like your report to look. This would help give a definitive answer.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2006
    Posts
    15
    Hi
    The table is called EventTable and the form is called EventForm.

    On the form I have an event field and 12 date fields:

    Record 1
    Event: Stress Management

    Date1: 22/05/2005 Date2: 30/05/2006 Date3: Date4: (up to Date12)

    Record 2
    Event: Mentoring Programme

    Date1: 15/05/2005 Date2: 20/05/2006 Date3: 22/05/2006 (up to Date 12)


    The 22/05/2006 is in field Date1 and field Date3. I need a report whereby I can search on a particular date (say 22/05/2006) and it will find all the events for that date. This date could be in any one of the 12 date fields. I can put parameters in but I would have to type the start and end date in 12 times before the report opened.

    I would like the report to look like:

    Event: Date:
    Stress Management 22/05/2006
    Mentoring Programme 22/05/2006

    Is there anyway I can do this?
    Thanks a lot.

  6. #6
    Join Date
    Jun 2006
    Posts
    103
    Quote Originally Posted by jfblack
    Hi
    The table is called EventTable and the form is called EventForm.

    On the form I have an event field and 12 date fields:

    Record 1
    Event: Stress Management

    Date1: 22/05/2005 Date2: 30/05/2006 Date3: Date4: (up to Date12)

    Record 2
    Event: Mentoring Programme

    Date1: 15/05/2005 Date2: 20/05/2006 Date3: 22/05/2006 (up to Date 12)


    The 22/05/2006 is in field Date1 and field Date3. I need a report whereby I can search on a particular date (say 22/05/2006) and it will find all the events for that date. This date could be in any one of the 12 date fields. I can put parameters in but I would have to type the start and end date in 12 times before the report opened.

    I would like the report to look like:

    Event: Date:
    Stress Management 22/05/2006
    Mentoring Programme 22/05/2006

    Is there anyway I can do this?
    Thanks a lot.
    Try this:

    SELECT EventTable.Event FROM EventTable
    WHERE (((EventTable.Date1)=DateValue("05/15/06")) or ((EventTable.Date2)=DateValue("05/15/06")));

    This SQL statement only has 2 date fields, if u want 12 date fields, just continue with the OR keywords, u will get the picture. and here is using a fix date which is '05/15/06', u might need to change it to

    SELECT EventTable.Event FROM EventTable
    WHERE (((EventTable.Date1)=DateValue([dateParameter])) or ((EventTable.Date2)=DateValue([dateParameter])));

    Good luck

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - in that case you defo want to sort out your schema. You want an Event table (that includes any general information regarding events that are not related to any specific event posting) and an EventsPosted table which will include a single date field to record the dates that events are posted (and any information specific to an event posting).

    The query would then be trivial:
    Code:
    SELECT Event, EventDate
    FROM EventPostingTable 
    WHERE EventDate = #22/05/2006#
    If you used surrogate keys (can't remember if they are mentioned in the article...) then the query would a be a little bit more complex but not much.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Minor addition:
    Code:
    SELECT EventTable.Event, [dateParameter] AS EventDate FROM EventTable
    WHERE EventTable.Date1=DateValue([dateParameter]) or EventTable.Date2=DateValue([dateParameter])
    I would still go with the redesign if at all possible.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2006
    Posts
    15
    Hi Guys
    Fantastic - the SQL statement works brilliantly. Eternal thanks for all your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •