Hi Jamie,
I don't think you really need a 'year' table unless you intend to use a
code for the year other than the current four digit style in common use.
;) From your description I think you need a faculty table as you
described and a publications table that has faculty id, name of
publication, type of publication, date of publication and text of
publication. You should perhaps consider an 'internal' autonumber field
as the key to the publication table (PublicationId?), it is much easier
to manage in the situation you describe. To get the publications for a
particular faculty member in a particular year the query would be
something like:
<cfset LastDayLastYear=createDate(year(now())-1, 12, 31)>
<cfset FirstDayNextYear=createDate(year(now()+1, 1, 1)>
<cfoutput>
<cfquery name="getPublications" datasource="MyDatasource" dbtype="ODBC">
select * from publication
where facultyid = #YourSelectedFacultyId#
and publicationDate > #CreateODBCDateTime(LastDayLastYear)#
and publicationDate < #CreateODBCDateTime(FirstDayNextYear)#
</cfquery>
</cfoutput>
<cfoutput query="getPublications">
#formatDate(publicationDate, "DD MMM YYYY")# #publicationName#
</cfoutput>
I've taken a few liberties here but it should get you started...
Good Luck
Brett Payne-Rhodes
B)
Jamie Symonds wrote:
>
> This is a multi-part message in MIME format.
> --------------A05047AA17E94D5BB8533278
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> Here's the situation: I understand normalization and can
> easily create a database to suit my needs in Access. But
> using it on the web with cold fusion has me confused - what
> I have is: faculy members. Every faculty member is in a
> table, their email is the primary key which derives their
> name, office location, phone, etc. Each faculty member each
> year publishes articles, chapters, books, etc., and right
> now I just have it set up simply as a Textarea to submit it
> to a memo block in my db - ideally, I would like to select
> the year, and input data in a block for that year, etc.
> Does this sound clear? I would create a faculty table, a
> year table, and a year/faculty table where year and faculty
> id identify publications for that year. Just looking for
> someone to give me very simple direction, and I can work
> from there. Thanks
>
> --------------A05047AA17E94D5BB8533278
> Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
> Content-Transfer-Encoding: 7bit
> Content-Description: Card for Symonds, Jamie
> Content-Disposition: attachment; filename="vcard.vcf"
>
> begin: vcard
> fn: Jamie Symonds
> n: Symonds;Jamie
> adr: Box 780;;St. F.X.U.;Antigonish;Nova Scotia;B2G 2X1;Canada
> email;internet: [EMAIL PROTECTED]
> title: Bachelor of Information Systems, Major (student)
> x-mozilla-cpt: ;0
> x-mozilla-html: TRUE
> version: 2.1
> end: vcard
>
> --------------A05047AA17E94D5BB8533278--
>
> ------------------------------------------------------------------------------
> Archives: http://www.eGroups.com/list/cf-talk
> To Unsubscribe visit
>http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
>message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
