Email notification query (many to many) :: multiple directions

[jason]

I could sure use some conceptualization and query help with a Page Watch
System I am building in Access 2000 and Asp.

I need to cycle through databae and generate a compiliation query email that
notifies a person of yacht(s) that have changed on our website:


Key database tables
----------------------------------------------------------------------------
Customer (1) --->> (many) Customer_Boats (many)<<---- Boat (1)
----------------------------------------------------------------------------

Person A is watching: Boat1, Boat 2, Boat 3

Person B is watching: Boat3, Boat 4

Person C is watching: Boat 1, Boat 2, Boat 3, Boat 4

Thus:

Boat 1 has the following persons: A C
Boat 2 has the the following persons: A, C
Boat 3 has the following persons: A B C
Boat 4 has the following persons: B, C



Thus:

My dynamic page has to compile a report for each customer based on the boats
he is watching:

eg:
LOOP
sToAddress = sTo
Page Notification for Person" & firstName

Boat 1 price has changed:
Original price: " & varOriginalPrice
Reduced to:" & varNewPrice

Boat 2 location has changed:
Old location: " & varOldLocation:
New Location: & varNewLocation

END LOOP

BUT

I have to do this for all my customers at the same time and then send out
the emails one after the other in a batch.

I am completely lost on how to assemble this email report based on the
overlapping selections of the user.

I could just pull out a boat and send changes to people watching that boat
but then a person may bet 50 emails in one day if he is watching 50 boats.

I have to assemble all his choices dynamically in one email via some magic
queries.

Is this harder or easier than I am making out!

Really, really appreciate some help here.

- Jason

 

[Curt_C [MVP]]

You've given a long description but which part of that specifically is the
issue? Generating the SQL query?

 

[jason]

I guess so. I need to assemble a page with different boat choices for
different users as I send out the emails.

The query seems beyond me at the moment - I need help on how to structure
it...

- Jason

 

[jason]

Hey Bmb - awesome - that is the closest I have come to conceptaulizing the
problem. I was originally thinking of creating audti trail tables for my
primary table - Listings - but was still unsure of how to handle the
many-to-many possibilities of a particular person and all the boats he was
watching and do this on the fly when sending bulk email.....

Could I could confirm your alternative:

1. Store customer info in table Customer; store yacht information in table
Listing
2. Store customer selections in a PageWatch table: [CustomerID] [YachtID]

CHANGE TO BOAT

3. Store Name of boat of in table [ChangedBoats]

* 4 * Develop a query in Microsoft Access Query - qryBoatsChangedCustomer
that JOINS all the customer selections in table [PageWatch] with the
similiar boats in table [Listings]

5. Once a day cycel trhough qryBoatsChangedCustomer and write out the boats
to the customer email on the fly dynamically for each and every customer in
an eg: 800 email send out.

CONFUSED ON THE FOLLOWING:

Ok, If I got the above right - Could you confirm - there is just one more
issue. What if need to actually spell out what changed for the particular
boat(s) for each and every customer email according to the boat(s) he is
watching...... I would need to summarize all the changes to his particular
boat(s) in one email and do this for every customer.

Would I merely be storing only the fields that changed in table
[ChangedBoats]. If so, how would I know *WHICH* apect of the boat record
changed:
eg: Location AND/or Price AND/or MarketStatus....

In other words I would need to compare what was in the record before to what
the changed record was. Is there a simple way to to do this?

Jason

 

[Bite My Bubbles]

What you said is pretty much what i envisioned. Exept rather Store Name of
boat of in table [ChangedBoats] , you should store the IDNO of that boat
record.

I guess for the remaining problem, notify customer of what changed, I
suggest this.

Before boat 426 record gets changed in listing, copy the entire existing
record to the [ChangedBoats], along with a datetime stamp. Then change
the record.

Now, when it's time to send out your mailing, Say
Here is what the boat is now:
Select boat 426 from Listings

Here is what your boat was:
select boat 426 from [ChangedBoats], using the record with the hightest
datetime stamp.

If you need to specify exactly which field(s) changed, then that is
possible, but I suggest starting a new thread and post a new question. It's
more than I want to think about right now.

The question could be something like: How can I compare two rows and list
only the columns that differ.

Hey Bmb - awesome - that is the closest I have come to conceptaulizing the
problem. I was originally thinking of creating audti trail tables for my
primary table - Listings - but was still unsure of how to handle the
many-to-many possibilities of a particular person and all the boats he was
watching and do this on the fly when sending bulk email.....

Could I could confirm your alternative:

1. Store customer info in table Customer; store yacht information in table
Listing
2. Store customer selections in a PageWatch table: [CustomerID] [YachtID]

CHANGE TO BOAT

3. Store Name of boat of in table [ChangedBoats]

* 4 * Develop a query in Microsoft Access Query - qryBoatsChangedCustomer
that JOINS all the customer selections in table [PageWatch] with the
similiar boats in table [Listings]

5. Once a day cycel trhough qryBoatsChangedCustomer and write out the boats
to the customer email on the fly dynamically for each and every customer in
an eg: 800 email send out.

CONFUSED ON THE FOLLOWING:

Ok, If I got the above right - Could you confirm - there is just one more
issue. What if need to actually spell out what changed for the particular
boat(s) for each and every customer email according to the boat(s) he is
watching...... I would need to summarize all the changes to his particular
boat(s) in one email and do this for every customer.

Would I merely be storing only the fields that changed in table
[ChangedBoats]. If so, how would I know *WHICH* apect of the boat record
changed:
eg: Location AND/or Price AND/or MarketStatus....

In other words I would need to compare what was in the record before to what
the changed record was. Is there a simple way to to do this?

Jason

no comprende. Why do you need to assemble any pages at all when sending out
emails?


everytime a boat changes, stick the name of the boat in a "ChangedBoats"
table.

Then, once a cycle look thru the 'ChangedBoats" table with view showing
Persons / boats

select person, boat from ChangedBoats join People on ChangedBoats.boat =
people.BoatsI'mWatching


For each person in that list send an email showing all his boats from this
list.

Then delete the guy from the 'ChangedBoats" table.
Then move to the next guy.

Are you going to set up some continusously running process on SQL server?

What





is
the

 

[Bite My Bubbles]

an additonal benefit when you copy the entire existing record to the
[ChangedBoats] table, is that you get a record of all changes. whether you
need that or not...

What you said is pretty much what i envisioned. Exept rather Store Name of
boat of in table [ChangedBoats] , you should store the IDNO of that boat
record.

I guess for the remaining problem, notify customer of what changed, I
suggest this.

Before boat 426 record gets changed in listing, copy the entire existing
record to the [ChangedBoats], along with a datetime stamp. Then change
the record.

Now, when it's time to send out your mailing, Say
Here is what the boat is now:
Select boat 426 from Listings

Here is what your boat was:
select boat 426 from [ChangedBoats], using the record with the hightest
datetime stamp.

If you need to specify exactly which field(s) changed, then that is
possible, but I suggest starting a new thread and post a new question. It's
more than I want to think about right now.

The question could be something like: How can I compare two rows and list
only the columns that differ.

Hey Bmb - awesome - that is the closest I have come to conceptaulizing the
problem. I was originally thinking of creating audti trail tables for my
primary table - Listings - but was still unsure of how to handle the
many-to-many possibilities of a particular person and all the boats he was
watching and do this on the fly when sending bulk email.....

Could I could confirm your alternative:

1. Store customer info in table Customer; store yacht information in table
Listing
2. Store customer selections in a PageWatch table: [CustomerID] [YachtID]

CHANGE TO BOAT

3. Store Name of boat of in table [ChangedBoats]

* 4 * Develop a query in Microsoft Access Query - qryBoatsChangedCustomer
that JOINS all the customer selections in table [PageWatch] with the
similiar boats in table [Listings]

5. Once a day cycel trhough qryBoatsChangedCustomer and write out the boats
to the customer email on the fly dynamically for each and every customer in
an eg: 800 email send out.

CONFUSED ON THE FOLLOWING:

Ok, If I got the above right - Could you confirm - there is just one more
issue. What if need to actually spell out what changed for the particular
boat(s) for each and every customer email according to the boat(s) he is
watching...... I would need to summarize all the changes to his particular
boat(s) in one email and do this for every customer.

Would I merely be storing only the fields that changed in table
[ChangedBoats]. If so, how would I know *WHICH* apect of the boat record
changed:
eg: Location AND/or Price AND/or MarketStatus....

In other words I would need to compare what was in the record before to what
the changed record was. Is there a simple way to to do this?

Jason

I guess so. I need to assemble a page with different boat choices for
different users as I send out the emails.

no comprende. Why do you need to assemble any pages at all when

sending
out

emails?


everytime a boat changes, stick the name of the boat in a "ChangedBoats"
table.

Then, once a cycle look thru the 'ChangedBoats" table with view showing
Persons / boats

select person, boat from ChangedBoats join People on

ChangedBoats.boat

=

specifically

is

--------------------------------------------------------------------------

-------------------------------------------------------------------------- on
the on
the