return latest date in a record from a set of fields

[MexCham]

I am running a query on a dues billing which has quarterly payment dates.
However, some will pay annually, some semi-annually, and some quarterly.
These are the pertinent fields:

[Member No] [January] [April] [July] [October]

Each of the months contains the date paid. I need to be able to input
beginning and ending dates to check whether each member number has a date in
any month that falls in the specified time frame, and I need to return only
the latest date. I tried looking around here and I think I have a general
idea of how to choose the max date, but I don't know how to apply it across
four fields. In the past I have used four separate queries, but I would like
to consolidate. Thanks!

 

[John W. Vinson]

I am running a query on a dues billing which has quarterly payment dates.
However, some will pay annually, some semi-annually, and some quarterly.
These are the pertinent fields:

[Member No] [January] [April] [July] [October]

Your table design is wrong, then. Does [January] mean 2007? 2008? Or do you
create a new table - or a new database - every year?

Each of the months contains the date paid. I need to be able to input
beginning and ending dates to check whether each member number has a date in
any month that falls in the specified time frame, and I need to return only
the latest date. I tried looking around here and I think I have a general
idea of how to choose the max date, but I don't know how to apply it across
four fields. In the past I have used four separate queries, but I would like
to consolidate. Thanks!

You will need four separate queries, and a UNION query to consolidate them -
but only if you stick to the current "spreadsheet" instead of correctly
normalizing your table into something more like

[MemberNo] [PaymentDate] [Amount] [Comments]

This structure would make your query very simple.

 

[MexCham]

I'm sorry, I must not have been very clear in my description - I write over
the database every year, but I do need to keep the quarters (January, April,
July, and October) separate for reporting and budgeting purposes and keep
them for an entire (rolling) year. I need to pull them out by specific dates
to send thank you notes on a more timely basis, but since there are always
people that pay late, I need to be able to check all four quarters. Unless I
am misreading what you are saying, your version would still be just the one
quarter worth of information. I was hoping there would be a simpler way than
five queries, but it sounds like not. Thanks anyway!

I am running a query on a dues billing which has quarterly payment dates.
However, some will pay annually, some semi-annually, and some quarterly.
These are the pertinent fields:

[Member No] [January] [April] [July] [October]

Your table design is wrong, then. Does [January] mean 2007? 2008? Or do you
create a new table - or a new database - every year?

Each of the months contains the date paid. I need to be able to input
beginning and ending dates to check whether each member number has a date in
any month that falls in the specified time frame, and I need to return only
the latest date. I tried looking around here and I think I have a general
idea of how to choose the max date, but I don't know how to apply it across
four fields. In the past I have used four separate queries, but I would like
to consolidate. Thanks!

You will need four separate queries, and a UNION query to consolidate them -
but only if you stick to the current "spreadsheet" instead of correctly
normalizing your table into something more like

[MemberNo] [PaymentDate] [Amount] [Comments]

This structure would make your query very simple.

 

[John W. Vinson]

I'm sorry, I must not have been very clear in my description - I write over
the database every year, but I do need to keep the quarters (January, April,
July, and October) separate for reporting and budgeting purposes and keep
them for an entire (rolling) year. I need to pull them out by specific dates
to send thank you notes on a more timely basis, but since there are always
people that pay late, I need to be able to check all four quarters. Unless I
am misreading what you are saying, your version would still be just the one
quarter worth of information. I was hoping there would be a simpler way than
five queries, but it sounds like not. Thanks anyway!

Sorry, I wasn't clear.

You are limititing yourself to one year of information. My suggestion is not
limited; YOURS is the one that is limited! My suggestion lets you store
information for CENTURIES.

Each quarter will be *A NEW ROW IN THE TABLE*. If you have another quarter,
you just add another row. If you have a quarter in another year, you just add
another row. If you have ten years of data, you add forty rows. When you do so
you will then need *ONLY ONE QUERY* for any range of dates whatsoever.

You're using a relational database! Use it relationally!

 

[swjunkie]

We don't seem to be communicating very well. Yes, I am limiting my
information to a year, because I choose to, and I actually needed assistance
for the problem I mentioned. I'm sorry to have upset you with my design, but
thank you for looking at my problem. I've built the union query and
everything is working fine. Have a great day!