Multiple dates in report (1 Viewer)

[Ronald de Graaf]

Hey,

I have a problem that I cannot resolve. Been on it for a few days now.

What I want is a report with all the days of the month. For example in April, day 1 to day 30. All these days must be included on the report.

I also have a table (tblEvents) with events in it. An event has a date.

In addition, an event record can have 3 option dates: Option1, Option2, Option3

What I want is that all events where the date (eventdate or option1 or option2 or option3) is equal to the day number in April (on the report) are shown. I tried to create a subreport and link it with the day number to the main report. But that does not work.

As an example: suppose an event has as event date April 3 and option date (option1) for example April 1.

This event must then be shown in the report on April 1 as well as April 3

Hope it's a little clear.

Anyone have any idea? Can someone help me on my way? Thanks in advance!!!

Greetings, Ronald

 

[June7]

A UNION query can rearrange fields into normalized structure.

SELECT EventID, EventDate AS ED, "Primary" AS Category FROM tablename
SELECT EventID, Option1, "Option1" FROM tablename
SELECT EventID, Option2, "Option2" FROM tablename
SELECT EventID, Option3, "Option3" FROM tablename;

Now, including dates that do not have an event is another issue.

 

[plog]

I think you need to fix your table. Instead of 3 fields for Options, you should have an Options table and in it you should put 3 records. Tables should accomodate data vertically (with more records) and not horizontally (with more fields).

Another issue is that you can't create data for which there is none. You need a table with all possible dates you want to report on. That might mean making a dummy table with all those dates unless you can somelhow create a query that does it for you. Do you have a table--any table, not just the ones for this issue--that has every date you will want to report on?

 

[arnelgp]

see this demo

 

[Ronald de Graaf]

see this demo

Thanx thanx thanx!!!

I had indeed made a table with all occurring days (1 to 31). This table is "leading" and ensures that all days are shown in the report.
What I have not worked with before is a UNION query. But with the example you have given me it should certainly work!

Top! Super! I will try again today if I will succeed. But I think so !!

 

[arnelgp]

I had indeed made a table with all occurring days (1 to 31)

i think you have the idea about the demo.

 

[isladogs]

@Ronald de Graaf
If you just want a quick fix, @arnelgp has kindly shown you how to use a union query for the purpose.

However, you would be better advised to change your table structure as @plog suggested.
To do so just use one date field but add an extra number field called Option. Leave that blank for the main event and enter 1, 2 or 3 for Option dates as appropriate.
Your report query would then be much more straightforward.

Alternatively, consider one table tblEvents with one record for each event but no dates.
Have a second table tblEventDates with fields EventID, EventDate, Option.
Link the two tables in your query.

This approach is called normalisation and you would be well advised to read about this concept and apply it in your databases.

 

[Ronald de Graaf]

@Ronald de Graaf
If you just want a quick fix, @arnelgp has kindly shown you how to use a union query for the purpose.

However, you would be better advised to change your table structure as @plog suggested.
To do so just use one date field but add an extra number field called Option. Leave that blank for the main event and enter 1, 2 or 3 for Option dates as appropriate.
Your report query would then be much more straightforward.

Alternatively, consider one table tblEvents with one record for each event but no dates.
Have a second table tblEventDates with fields EventID, EventDate, Option.
Link the two tables in your query.

This approach is called normalisation and you would be well advised to read about this concept and apply it in your databases.

Thanks for your explanation. I understand it would have been better to put the dates in a separate table. However, I did not design it. I was only asked to create a calendar that will show all the events of that month. So even if the option date of the event falls in the selected month.
And that now works perfectly with the union query

Thanks for all your suggestions! I can move forward