Finding newest expiration dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working on a subscription fulfillment database in Access. I have a main
account table and a relational subscription table. So for a given account,
there may be an older subscription, say March 2003 - Feb. 2004, and then a
newer one, say Nov. 2005 - Oct. 2006. The problem is, when I look at the
accounts and subscriptions in a query, I get the older subscriptions (March
2003 - Feb. 2004) in addition to the current one (Nov. 2005 - Oct. 2006). I
only want the newest subscription in each active account. Otherwise,
duplicate copies will be mailed to the same account. And duplicates are not
desirable in other cases also — reports, etc.

If anyone knows some method that I could only show the latest subscription
in an active account, I would appreciate the information. If it's helpful,
here are the table.field names that are relevant:

CW Main.Account
CW Subscriptions.Exp Date

John
 
How is the information stored in your database? Are you splitting up the
subscription start/end as in

dteSubscriptionStartDate
dteSubscriptionEndDate
 
Thanks for the reply, David. I have the fields "Start Date" and "Exp Date" in
the CW Subscription table. They are time/date fields.

John
 
I'm replying to myself, I don't know if that's the best way to add
information.

I wanted to mention that I want to keep the older subscription records in
the the accounts, that's why I set up the relational database, to have the
option of looking at the older subscription data instead of it being erased
by the newer subscriptions. Anyone that wants to help, I should mention that
I know about the basics in Access, but I do not know about programming, so if
that is the only way to accomplish this, please let me know the steps
involved.
 
If you are only trying to get current records, try adding a filter
condition to your query that reads something like this:

AND now() between [Start Date] AND [Exp Date]
 
I will try this. It sounds like it will work well for a query finding active
accounts. I'll let you know. Thanks a lot, Vince.

But I also need the option of finding only the most current subscription
term for an account, and it might not necessarily include the current date.
Maybe I shouldn't be using the word "current." "Most recent" may be better.
Or "newest."

I may need to, for instance, show all the inactive accounts, in which case
most of the subscriptions would not be current (There would be a few
cancellations with current sub. dates, though). I would only want to show the
most recent subscription in each account; otherwise, the query would show
duplicate accounts because of the older subscriptions. So please let me know
if there are any ideas on this. Or maybe there are other options that I have
not considered that would require a redesign of the database...(I hope not)

John


Vince said:
If you are only trying to get current records, try adding a filter
condition to your query that reads something like this:

AND now() between [Start Date] AND [Exp Date]
Click to expand...
 
Then the DMax() function should do the trick.
Thanks for the reply, David. I have the fields "Start Date" and "Exp Date" in
the CW Subscription table. They are time/date fields.

John


:
Click to expand...
 
Vince, I tried your idea. It has eliminated the duplicate accounts.
Unfortunately, an acount that has a subscription from 12/19/2004 -
12/20/2005, for instance, may also have renewed early and have a newer
subscription, 12/21/2005 - 12/20/2006. In these cases, the subscription that
the query finds is not the newest one. More ideas on solving this problem
would be appreciated.
--
John

jdm003 said:
I will try this. It sounds like it will work well for a query finding active
accounts. I'll let you know. Thanks a lot, Vince.

But I also need the option of finding only the most current subscription
term for an account, and it might not necessarily include the current date.
Maybe I shouldn't be using the word "current." "Most recent" may be better.
Or "newest."

I may need to, for instance, show all the inactive accounts, in which case
most of the subscriptions would not be current (There would be a few
cancellations with current sub. dates, though). I would only want to show the
most recent subscription in each account; otherwise, the query would show
duplicate accounts because of the older subscriptions. So please let me know
if there are any ideas on this. Or maybe there are other options that I have
not considered that would require a redesign of the database...(I hope not)

John


Vince said:
If you are only trying to get current records, try adding a filter
condition to your query that reads something like this:

AND now() between [Start Date] AND [Exp Date]
I'm replying to myself, I don't know if that's the best way to add
information.

I wanted to mention that I want to keep the older subscription records in
the accounts, that's why I set up the relational database, to have the
option of looking at the older subscription data instead of it being erased
by the newer subscriptions. Anyone that wants to help, I should mention that
I know about the basics in Access, but I do not know about programming, so if
that is the only way to accomplish this, please let me know the steps
involved.
--
John


:

Thanks for the reply, David. I have the fields "Start Date" and "Exp Date" in
the CW Subscription table. They are time/date fields.

John


:

How is the information stored in your database? Are you splitting up the
subscription start/end as in

dteSubscriptionStartDate
dteSubscriptionEndDate

jdm003 wrote:
I am working on a subscription fulfillment database in Access. I have a main
account table and a relational subscription table. So for a given account,
there may be an older subscription, say March 2003 - Feb. 2004, and then a
newer one, say Nov. 2005 - Oct. 2006. The problem is, when I look at the
accounts and subscriptions in a query, I get the older subscriptions (March
2003 - Feb. 2004) in addition to the current one (Nov. 2005 - Oct. 2006). I
only want the newest subscription in each active account. Otherwise,
duplicate copies will be mailed to the same account. And duplicates are not
desirable in other cases also - reports, etc.

If anyone knows some method that I could only show the latest subscription
in an active account, I would appreciate the information. If it's helpful,
here are the table.field names that are relevant:

CW Main.Account
CW Subscriptions.Exp Date

John
Click to expand...
Click to expand...
Click to expand...
 
I tried Dmax() in the criteria of the query (under the exp. date field), and
it says , "The expression you entered has a function containing the wrong
number of arguments."
 
In short the DMax() that you tried was malformed. Can you try it again
and if it craps out post the code?
 
Thanks for the reply, David. Should I be typing only "DMax()" in the criteria
under the Exp Date field, or is there more I should type after that?

By posting the code, do you mean copying the code from the Visual Basic view
of the query and pasting it into a message here?
 
No. DMax() is a domain function that requires parameters. Check help in
VBA for the complete list and description of the function. I've never
used it myself, but know that it is there and probably what you're
looking for.
 
Back
Top