Data Duplication

[Palemo]

Hi,

This has been bothering me for some time, so I will attempt to explain the situation.

I run a subscription database, that basically holds user information and their relevant subscriptions. I have all the data in one table.

The problem is this: if a member subscribes for a year, the database records it as say 1 to 4 (edition numbers). These numbers are stored in different fields. On the last edition they receive an email stating that their subscription is up for renewal. This is run by a query, that asks for the edition number, looks at the expiration number and if they match it sends the email. If however, they resubscribed prior to this from 4 - 7 then their details still get pulled for notification that their subscription is about to expire. They therefore have two entries in the database, 1 - 4 and 4 - 7.

I can see what is happening, it looks in the expiry field and matches the numbers, it does not look to see if they have resubscribed or not.

Can anyone help?

 

[Jack Cowley]

You need to normalize your table. You do not want all the data in one table but a table for Customers and a related table for Subscriptions. Once you normalize your tables then your problems should clear up.

hth,
Jack

 

[databasedonr]

Yes, Jack is right.

If you put all your subscription information in a Subscription table, link it using CustomerID to the Customer Table, then you can query the subscription based on the customer ID. If all the data is in one record, then do you have multiple entries for one customer? That'd be bad.

You could manage your subscription so that the subscription information is updated when the subscription is renewed, so you retrieve the record and update the field, and that way you would only ever get the match on the actual, most up-to-date subscription information.

Does that make sense?

 

[Palemo]

I do have two tables, one called members, and the other subscriptions. All subscibers are in one table, and all transactions (ie subscriptions) are in the subscriptions tables.

The problem with updating their record is that we need to keep history reports for each subscriber. I have a subform in my members form that shows all their transactions with us. Therefore, we can see that someone has subscribed from 1 - 4, then resubscribed from 4 - 7 for instance.

 

[Jack Cowley]

I assume that every transaction in the subscription table gets a new record rather then changing the data in a single record. If you have multiple records you can add a Yes/No field to your table and when the customer renews then set the field to true. When it is time to send out the email check the value of this field and the date and you will know if they have renewed or not.

I hope I understood what it is you want to do...

Jack

 

[Palemo]

Correct, every transactions gets a new record. We record their memberid, and all subscription related data (price, quantity, expiry etc.).

I run a query that asks for subscription end so we can send out a renewal email. It looks in the subscription end field, and if the values match then it emails them, regardless of whether they have renewed or not. I do have a renewal check box, but am unable to figure out how to incorporate it into my query.

How do incorporate the renewals box and the subscription end value into the query?

 

[Jack Cowley]

In the criteria field for the check box put False and on the same line in the Date field put the criteria that you usually put. Now you will see only records where the date is the date you want and they have not renewed.

hth,
Jack

 

[Palemo]

The problem is that if I do renewal = false and edition end = 4 then I still get the same information.

For example:
Name1 expire 3 renewal no
Name1 expire 6 renewal yes

Name1 expires on issue 3, but renewed from 4 - 6. I run the query looking for 3 as a last issue, and renewal = false, and I still get Name1 showing because the renewal on that initial transaction was false (new subscription).

Does this make sense?

 

[Jack Cowley]

I am having trouble following just what you are doing and I have to leave now, but I will say that you may need to only look at the last two records for each subscriber so the query is not fooled by previous records. I'm sorry that I can't be more explicit and if someone else does not jump in then I would suggest you repost your question so others will see it....

Jack