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?

--
----------------------------------------------------------
Curt Christianson (Software_AT_Darkfalz.Com)
Owner/Lead Designer, DF-Software
http://www.Darkfalz.com
---------------------------------------------------------
...Offering free scripts & code snippits for everyone...
---------------------------------------------------------
"jason" <ja***@catamaranco.com> wrote in message
news:#0**************@TK2MSFTNGP09.phx.gbl...

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

[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
"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
news:uZ**************@TK2MSFTNGP10.phx.gbl...

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

--
----------------------------------------------------------
Curt Christianson (Software_AT_Darkfalz.Com)
Owner/Lead Designer, DF-Software
http://www.Darkfalz.com
---------------------------------------------------------
..Offering free scripts & code snippits for everyone...
---------------------------------------------------------
"jason" <ja***@catamaranco.com> wrote in message
news:#0**************@TK2MSFTNGP09.phx.gbl...

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

[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" <bm*@mybubbles.com> wrote in message
news:#g**************@TK2MSFTNGP11.phx.gbl...

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 =
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

"jason" <ja***@catamaranco.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

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
"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
news:uZ**************@TK2MSFTNGP10.phx.gbl...

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

--
----------------------------------------------------------
Curt Christianson (Software_AT_Darkfalz.Com)
Owner/Lead Designer, DF-Software
http://www.Darkfalz.com
---------------------------------------------------------
..Offering free scripts & code snippits for everyone...
---------------------------------------------------------
"jason" <ja***@catamaranco.com> wrote in message
news:#0**************@TK2MSFTNGP09.phx.gbl...
> 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
>
>
>
>

[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.
"jason" <ja***@catamaranco.com> wrote in message
news:OL*************@TK2MSFTNGP11.phx.gbl...

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" <bm*@mybubbles.com> wrote in message
news:#g**************@TK2MSFTNGP11.phx.gbl...

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 =

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

"jason" <ja***@catamaranco.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

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
"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
news:uZ**************@TK2MSFTNGP10.phx.gbl...
> You've given a long description but which part of that specifically is the

> issue? Generating the SQL query?
>
> --
> ----------------------------------------------------------
> Curt Christianson (Software_AT_Darkfalz.Com)
> Owner/Lead Designer, DF-Software
> http://www.Darkfalz.com
> ---------------------------------------------------------
> ..Offering free scripts & code snippits for everyone...
> ---------------------------------------------------------
>
>
> "jason" <ja***@catamaranco.com> wrote in message
> news:#0**************@TK2MSFTNGP09.phx.gbl...
> > 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
> >
> >
> >
> >
>
>

[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...

"Bite My Bubbles" <bm*@mybubbles.com> wrote in message
news:Oo**************@TK2MSFTNGP11.phx.gbl...

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.
"jason" <ja***@catamaranco.com> wrote in message
news:OL*************@TK2MSFTNGP11.phx.gbl...

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" <bm*@mybubbles.com> wrote in message
news:#g**************@TK2MSFTNGP11.phx.gbl...

>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 = 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

"jason" <ja***@catamaranco.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
> 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
> "Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
> news:uZ**************@TK2MSFTNGP10.phx.gbl...
> > You've given a long description but which part of that specifically is the
> > issue? Generating the SQL query?
> >
> > --
> > ----------------------------------------------------------
> > Curt Christianson (Software_AT_Darkfalz.Com)
> > Owner/Lead Designer, DF-Software
> > http://www.Darkfalz.com
> > ---------------------------------------------------------
> > ..Offering free scripts & code snippits for everyone...
> > ---------------------------------------------------------
> >
> >
> > "jason" <ja***@catamaranco.com> wrote in message
> > news:#0**************@TK2MSFTNGP09.phx.gbl...
> > > 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
> > >
> > >
> > >
> > >
> >
> >
>
>