Difficult to find phrasing: Way to associate year of events with entire table of data

[andylynchpe]

I've had trouble googling it because I don't know exactly how to describe it.



I have an Access database created where I am inputting data related to two previous events (tblPreviousEvent1 and tblPreviousEvent2). I need to have the year in which each event took place to be easily update-able so I can use this database on different projects. For example, tblPreviousEvent1 happened in 2008 and tblPreviousEvent2 happened in 2015.

What is the best way to have this done? Obviously I don't want to have a separate field in each table where the user has to manually input the year along with each record.

I have tried setting up a calculated field with the expression simply being the year, but then I can't create relationships based on that field and that's more difficult than I'd like for the end user to have to do.

I have tried setting up a macro that sets that field, but then again the variable is set in the macro code, and I don't want to expect the end user to be going in there.

If I haven't asked this clearly, please let me know and I'll do my best to explain. Thanks.

[davegri]

There is a function to extract the year from a date. For example:
Chosen = Year(tblPreviousEvent1.EventDate)

Chosen is whatever you want to call it.
EventDate is a field in tblPreviousEvent1.

[andylynchpe]

Thank you for your response.

The issue with that approach is that requires the user to enter the year/date for every record in the table. I am looking to create a relationship where the date is associated with each record because the record exists in the particular table, i.e. everything in tblPreviousEvent1 took place in 2007. That's what I'm hoping to accomplish.

[isladogs]

Do you just mean if a date is 10/05/2008 you want the value 2008?

If not, suggest you give some example data and say what value you want for each.

[andylynchpe]

For example, I want to be able to create a report that says "[Event A Title] occurred in [Year] and included the following: [other data from the table]"

One way I could easily do that is if each record in the tblPreviousEvent1 table contained a separate instance of the same year value, because all events in this particular table will have occurred in the same year. I'm just looking for a way to eliminate the redundancy of having to repeat the year value in each record in a table.

[davegri]

Are all the reports the same format except for the Year being listed in the heading?
If so, you can have code in the report_open event to modify the label or listbox in the heading:

If Me.Recordsource = "tblPreviousEvent1" then
    me.headinglabel = "Data for 2008"
endif

[isladogs]

For example, I want to be able to create a report that says "[Event A Title] occurred in [Year] and included the following: [other data from the table]"

One way I could easily do that is if each record in the tblPreviousEvent1 table contained a separate instance of the same year value, because all events in this particular table will have occurred in the same year. I'm just looking for a way to eliminate the redundancy of having to repeat the year value in each record in a table.

That doesn't help. Please answer my question.

Do you just mean if a date is 10/05/2008 you want the value 2008?

[andylynchpe]

No, I don't mean that. Sorry - I don't know how to explain it any differently. I appreciate you taking the time to try to help.

[davegri]

So you want a report that groups the events within the year?
Apparently the date is not in the table at all. Is that correct? So the only way you know that the event is in a particular year is by knowing what table it is in.
Do you want the report to cover all the tables, or just one table at a time?

Other thoughts:

The issue with that approach is that requires the user to enter the year/date for every record in the table.

You can provide for a DateCreated field in the tables and have code automatically insert the date that the record was created. No user typing required.