Year over Year comparison

[rdg0351]

Thanks in advance for any help. I would like to create a query where I provide an As Of date to be compared to a number of fields from a table, and create an entry if ANY of the date/time fields match the month/day criteria. For example:

1 3/31/2012 4/05/2012 4/15/2012
2 3/31/2013 4/06/2013 4/15/2013
3 3/31/2014 4/08/2014 4/15/2014
4 3/31/2015 4/11/2015 4/15/2015
5 3/31/2016 4/12/2016 4/15/2016

If I provide a month/day of 4/10, then I can do a Count() within a report do provide the following totals:

Totals 5 3 0

Thank you

 

[plog]

I think you might have a structure issue with your table. What are those 3 date fields's names? Then explain what the dates represent. Are they tasks of a process? For example, [ProjectStart], [ProjectFunded], [ProjectComplete]?

 

[rdg0351]

Yes, the 3 fields represent status dates for an event. For example, an accountant wants to track the progress of tax preparation during the tax season, by comparing this year to prior years.

 

[rdg0351]

Sorry, I should have mentioned that the event is a [Tax Year] for a client. Perhaps that offers further insight

 

[plog]

Yes, you have a structure issue then. Tables should grow vertically (with more rows) and not horizontally (with more fields). That means you need a table to hold your status dates like so:

Events
EventID, autonumber, primary key
ForeignKey, number, links to your existing table that dates are in currently
EventType, text, describes the event occuring, currently that is the field name in table
EventDate, date, date of event

Then instead of 3 values in your existing table you have 3 records (or less if any are null) in the Events table.

Get your data like that and your query is simple:

SELECT EventType, COUNT(EventID) AS EventTotals
FROM Events
WHERE (Month(EventDate)<4) OR ((Month(EventDate)=4) AND (DAY(EventDate)<=10))

 

[rdg0351]

Plog, while I appreciate your insight, I didn't have the luxury to break the master file down via normalization. I'll take this under advisement

 

[plog]

I didn't have the luxury to break the master file down via normalization

Yes you do. You can always build a process to normalize data. You can also do it by proxy using a UNION query:

SELECT  InitialTable.ID, InitialTable.Date1 AS EventDate, "Event1Name" AS EventType
FROM InitialTable
WHERE InitialTable.Date1 IS NOT NULL
UNION ALL
SELECT  InitialTable.ID, InitialTable.Date2 AS EventDate, "Event2Name" AS EventType
FROM InitialTable
WHERE InitialTable.Date2 IS NOT NULL
UNION ALL 
SELECT  InitialTable.ID, InitialTable.Date3 AS EventDate, "Event3Name" AS EventType
FROM InitialTable
WHERE InitialTable.Date3 IS NOT NULL