Hi all I am designing a database for my use as a travel agent. It is intended to be a CRM type database. I want to track my prospects and clients interests and hobbies. I have come up with about 60 different selections. I want to be able to run a query and do a mailing based on these interests and hobbies, eg. all persons who are interested in ballroom dancing.
The only solution I have been able to come up with is have the 60 selections as Yes/No fields in the client table. I thought about have say 15 max fields and then have a lookup where the 60 selections would be in another table and could be selected for each of the 15 fields. However this creates a many-to-many relationship. Keep also in mind that I will sooner or later have this application on my web page so my prospects and clients can enter their own information using a user name and password. Any ideas, opinions, and comments would be most appreciated. James F. "Jay" Beckham, Travel Consultant World Class Cruises & Tours, Inc. 2799 Highland Ridge Road Berkeley Springs, WV 25411 304-258-4451 www.wccruises.com "You're Not Dreaming, You're Cruising" ------------------------ Yahoo! Groups Sponsor --------------------~--> Get Bzzzy! (real tools to help you find a job). Welcome to the Sweet Life. http://us.click.yahoo.com/A77XvD/vlQLAA/TtwFAA/q7folB/TM --------------------------------------------------------------------~-> Please zip all files prior to uploading to Files section. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AccessDevelopers/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
--- Begin Message --- As Tom said, you can set them to run in VBA code, if your tracking
needs don't coincide with a change to the table.
Otherwise, if the tracking is based on a record being
added/edited/deleted, by all means the most efficient way is to call
the sp from a trigger. It's not a requirement, though.
HTH
Bryant
--- In [email protected], "Angelo Antonino"
<[EMAIL PROTECTED]> wrote:
>
> Hi Bryant,
>
> Can you confirm that you fire the sp's from triggers set at the table
> level.
> You don't fire the sp's from VBA?
>
> thanks,
> Angelo
>
>
>
> -----Original Message-----
> From: [email protected]
> [mailto:[EMAIL PROTECTED] On Behalf Of Databasedude.com
> Sent: Tuesday, November 01, 2005 11:45 AM
> To: [email protected]
> Subject: [AccessDevelopers] Re: ADP / SQL Server log tools
>
> Sure can! I like to capture the "before" and "after" versions
> personally.
>
> When a record is edited, SQL actually has the values stored in two
> tables, Inserted (new record & values) and Deleted (old record &
> values). You can also test for certain fields being changed.
>
> Glad you asked this - I'm going to revisit it for an upcoming project,
> and this will give me a head start!
>
> HTH
> Bryant
>
> --- In [email protected], "Angelo Antonino"
> <[EMAIL PROTECTED]> wrote:
> >
> > Thanks Bryant.
> > I have yet to use triggers.
> > If the trigger is activated when a field is modified, can the sp store
> > the value of the field, before and after the change?
> >
> > thanks,
> > Angelo
> >
> >
> >
> > -----Original Message-----
> > From: [email protected]
> > [mailto:[EMAIL PROTECTED] On Behalf Of
> Databasedude.com
> > Sent: Sunday, October 30, 2005 3:13 AM
> > To: [email protected]
> > Subject: [AccessDevelopers] Re: ADP / SQL Server log tools
> >
> > Personally I created something very similar for my projects, and use
> > the stored procedure as a trigger on the tables which I wish to audit.
> > That allows reporting on all changes, regardless of the interface in
> > which they were initiated.
> >
> > HTH
> > Bryant
> >
> > --- In [email protected], "Angelo Antonino"
> > <[EMAIL PROTECTED]> wrote:
> > >
> > > Thanks Tom.
> > >
> > > So you trigger the sp from VBA?
> > >
> > > There are tools available that record changes to the SQL log files,
> > and
> > > allow reporting on these changes.
> > > Any one had any experience with these?
> > >
> > > thanks,
> > > Angelo
> > > ________________________
> > >
> > > Angelo Antonino
> > > Tanzanite Software Solutions
> > > M: 0403317772
> > > E: <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]
> > > W: <http://www.tanzanite.com.au> www.tanzanite.com.au
> > > <mailto:[EMAIL PROTECTED]>
> > >
> > > -----Original Message-----
> > > From: [email protected]
> > > [mailto:[EMAIL PROTECTED] On Behalf Of Tom Oakes
> > > Sent: Saturday, October 29, 2005 9:37 AM
> > > To: [email protected]
> > > Subject: RE: [AccessDevelopers] ADP / SQL Server log tools
> > >
> > > Angelo,
> > >
> > > I do something like this in most of my applications. I have a table
> > > named tblTrack, that looks something like this:
> > >
> > > TrackID int (identity)
> > > TrackIP nvarchar 15
> > > TrackLogID int
> > > TrackType nvarchar 50
> > > TrackAction nvarchar 50
> > > TrackLinkID int
> > > TrackText nvarchar 255
> > > TrackUsername nvarchar 20
> > > dtmTrackStamp datetime 8 (default value = getDate())
> > >
> > > The stored proc that populates it is pretty simple:
> > >
> > > ALTER Procedure spAddTrack
> > > (
> > > @TrackIP nvarchar(15) = Null,
> > > @TrackLogID int = 0,
> > > @TrackLinkID int = 0,
> > > @TrackType nvarchar(50) = Null,
> > > @TrackAction nvarchar(50) = Null,
> > > @TrackText nvarchar(255) = Null
> > > )
> > > As
> > >
> > > INSERT INTO tblTrack (TrackIP, TrackLogID, TrackLinkID, TrackType,
> > > TrackAction, TrackText)
> > > VALUES (@TrackIP, @TrackLogID, @TrackLinkID, @TrackType,
> @TrackAction,
> > > @TrackText)
> > >
> > >
> >
> ------------------------------------------------------------------------
> > > ---------------------------------------------------------------
> > > TrackIP = Many of my applications have a web interface, so I capture
> > the
> > > IP address, if applicable.
> > > LogID = ID of user who is logged into the program. You may want to
> > use
> > > the Windows username (a string), if you're not using custom
> security.
> > > TrackType = this is kind of a "category" that lets me know what
> > > domain/table we're dealing with (i.e. "Product", "User", "Project",
> > > "Invoice").
> > > TrackAction = the action that took place ("Add", "Update", "Delete",
> > > "Failure", ....)
> > > TrackLinkID = If TrackType is specified, this should be the primary
> > key
> > > of the record in question. This is optional.
> > > TrackText = descriptive text of what happened. Optional.
> > >
> >
> ------------------------------------------------------------------------
> > > ---------------------------------------------------------------
> > >
> > > So...when a product is deleted from the database, this proc fires
> > prior
> > > to the actual delete:
> > > EXEC spAddTrack "24.21.140.182", 1900, 20332, "Product", "Delete",
> > > "Widget #4055"
> > >
> > > ...when a new customer is added:
> > > EXEC spAddTrack "24.21.140.182", 1900, 901, "Customer", "Add", "NW
> > Media
> > > Inc."
> > >
> > > ...when someone tries to login and fails:
> > > EXEC spAddTrack "24.21.140.182", 1900, 0, "Login", "Failure", ""
> > >
> > > And so forth.
> > >
> > > You could make this a lot more complicated, obviously. One of my
> > > clients wants to be able to see an audit trail of everything that
> > > happens on a Project/WBS basis; so when a product is deleted, for
> > > instance, we also store that product's project id with the audit
> > record.
> > >
> > >
> > > Hope that helps you.
> > >
> > > Tom Oakes
> > > Personal PC Consultants, Inc.
> > > [EMAIL PROTECTED]
> > > 503.230.0911 (O)
> > > 402.968.6946 (C)
> > > 734.264.0911 (F)
> > >
> > > _____
> > >
> > > From: [email protected]
> > > [mailto:[EMAIL PROTECTED] On Behalf Of Angelo
> Antonino
> > > Sent: Friday, October 28, 2005 3:46 AM
> > > To: 'AccessDevelopers'
> > > Subject: [AccessDevelopers] ADP / SQL Server log tools
> > > Hi,
> > >
> > > I have an ADP connected to a SQL Server 2000.
> > >
> > > I need to keep a log of changes done to the data (ie. Edit's,
> Create,
> > > Delete - who did it, time, field.etc). The more detail logged the
> > > better.
> > >
> > > Has anyone had experience with any tools that do such a thing?
> > >
> > > I could create a stored procedure, that would populate a table every
> > > time data changes, but I want to investigate what is out there
> first.
> > >
> > > Any thoughts appreciated.
> > >
> > > thanks,
> > > Angelo
> > > ________________________
> > >
> > > Angelo Antonino
> > > Tanzanite Software Solutions
> > > M: 0403317772
> > > E: <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]
> > > W: <http://www.tanzanite.com.au> www.tanzanite.com.au
> > > <mailto:[EMAIL PROTECTED]>
> > >
> > >
> > >
> > >
> > >
> > > Please zip all files prior to uploading to Files section.
> > >
> > >
> > >
> > > SPONSORED LINKS
> > >
> > > Microsoft
> > >
> >
> <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+developer&w1=Micro
> > >
> >
> soft+access+developer&w2=Microsoft+access+help&w3=Microsoft+access+datab
> > >
> >
> ase&w4=Microsoft+access+training&w5=Microsoft+access+programming&w6=Micr
> > > osoft+access+tutorial&c=6&s=186&.sig=beZP9veAC8Wk2esgxlpTRQ> access
> > > developer
> > > Microsoft
> > >
> >
> <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+help&w1=Microsoft+
> > >
> >
> access+developer&w2=Microsoft+access+help&w3=Microsoft+access+database&w
> > >
> >
> 4=Microsoft+access+training&w5=Microsoft+access+programming&w6=Microsoft
> > > +access+tutorial&c=6&s=186&.sig=B44I3Zk6lhirfxsAge8nLQ> access help
>
> > > Microsoft
> > >
> >
> <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+database&w1=Micros
> > >
> >
> oft+access+developer&w2=Microsoft+access+help&w3=Microsoft+access+databa
> > >
> >
> se&w4=Microsoft+access+training&w5=Microsoft+access+programming&w6=Micro
> > > soft+access+tutorial&c=6&s=186&.sig=uSZc3LUV_ONpuYljCyPAnA> access
> > > database
> > >
> > > Microsoft
> > >
> >
> <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+training&w1=Micros
> > >
> >
> oft+access+developer&w2=Microsoft+access+help&w3=Microsoft+access+databa
> > >
> >
> se&w4=Microsoft+access+training&w5=Microsoft+access+programming&w6=Micro
> > > soft+access+tutorial&c=6&s=186&.sig=PFhSWgTKPZGWDOojGq8lSg> access
> > > training
> > > Microsoft
> > >
> >
> <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+programming&w1=Mic
> > >
> >
> rosoft+access+developer&w2=Microsoft+access+help&w3=Microsoft+access+dat
> > >
> >
> abase&w4=Microsoft+access+training&w5=Microsoft+access+programming&w6=Mi
> > > crosoft+access+tutorial&c=6&s=186&.sig=pfyn-CEy_tx3-qpe8oivIw>
> access
> > > programming
> > > Microsoft
> > >
> >
> <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+tutorial&w1=Micros
> > >
> >
> oft+access+developer&w2=Microsoft+access+help&w3=Microsoft+access+databa
> > >
> >
> se&w4=Microsoft+access+training&w5=Microsoft+access+programming&w6=Micro
> > > soft+access+tutorial&c=6&s=186&.sig=cByQE6x-t27mwtr-CD_MTg> access
> > > tutorial
> > >
> > > _____
> > >
> > > YAHOO! GROUPS LINKS
> > >
> > > * Visit your group "AccessDevelopers
> > > <http://groups.yahoo.com/group/AccessDevelopers> " on the web.
> > >
> > > * To unsubscribe from this group, send an email to:
> > > [EMAIL PROTECTED]
> > >
> >
> <mailto:[EMAIL PROTECTED]
> > > >
> > >
> > > * Your use of Yahoo! Groups is subject to the Yahoo! Terms
> of
> > > Service <http://docs.yahoo.com/info/terms/> .
> > >
> > > _____
> > >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Please zip all files prior to uploading to Files section.
> > Yahoo! Groups Links
> >
>
>
>
>
>
>
>
>
> Please zip all files prior to uploading to Files section.
> Yahoo! Groups Links
>
--- End Message ---
