Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 11th, 2008, 12:44 PM
Authorized User
Join Date: Mar 2008
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts

Thanks for the help, it does help, some of it was a bit much for me but either ways "I learned something new". The DB is screwy in the sense that I would have to add [2010] when it comes around. I did not design the DB, but am now trying to work with it (I made my peace with those type of issues). I went ahead and used what you guys said and came up with this, which is what I wanted.
I would also like to combine two of these (2 queries pertaining to different years [2006]) with the same mind set. The reason is that I don't want to make a query for [2006], [2007], [2008].....

WHERE ((([2007]='Full - Time' OR [2007]='Part - Time') AND StartYear=2007 AND Starts='Spring') OR 
((([2007]='Full - Time' OR [2007]='Part - Time') AND startYear=2006 AND Starts='Fall');
Old July 11th, 2008, 01:28 PM
Friend of Wrox
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts

Time to redesign the database.

You should have a *MINIMUM* of two tables here, instead of just one.

But without seeing all the fields involved and maybe some sample data it's hard to know exactly where to go.

You are doomed to creating longer and longer queries--and to creating a NEW query for each new year and situation--and and and...well, it's just too ugly for words...if you stick with this braindead design.

If you didn't follow all the stuff I talked about, having to do with operator precedence and all, then maybe you aren't the one to do the redesign. But maybe you don't have any choice. Clearly, you don't want to go back to the person who created the existing horrible design.
Old July 11th, 2008, 03:48 PM
Friend of Wrox
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts

Can you at least explain the meaning of the fields???

This *FEELS* to me like it's for an academic institution.

You have "Class Years" (or "Fiscal Years") of 2006, 2007, etc.

    [2007] = 'Full Time'
    "A full time student during the 2007-2008 academic year."

No? And then
    StartYear=2007 AND Starts='Spring'
means that the student started classes during the Spring quarter/semester of 2007. And unless I miss my guess, "Spring 2007" is really part of the [2006] academic year. So if all that is true, I don't understand why you are testing
    [2007] IN ('Full - Time','Part - Time')
    AND startYear=2006
    AND Starts='Fall'
Wouldn't "Fall 2006" be part of the [2006] academic year, too???

The whole system is screwy.

If you are going to base SOME things on academic year (e.g., [2007]='Part - Time') then why are other things based on physical year (e.g., startYear=2007)????

On top of that, how can you do an ORDER BY that makes sense, based on startYear and starts????
    ORDER BY startYear, starts
will get you
    2006, Fall
    2006, Spring
    2006, Summer
    2006, Winter
which doesn't make sense from either a physical *OR* academic year standpoint.

Personally, assuming that you are indeed talking about an academic institution here, I would have based *EVERYTHING* on academic year.

So I'd have
    term = 2006.1 [Fall, 2006 academic year, September 2006 start]
    term = 2006.2 [Winter, 2006 academic year, January 2007 start]
    term = 2006.3 [Spring, 2006 academic year, April 2007 start]
    term = 2006.4 [Summer, 2006 academic year, July 2007 start]
[Or, if at this institution Summer is the first quarter/term, just change the numbering to match]

And then you'd have a one-to-many relationship from the main table (which specifies when that student started, using my "term" notation) to the "worked" table. So it might look like this:

TABLE: mainWork
   student : termStarted
       17  : 2007.1
       32  : 2006.3

TABLE: worked
   student : academicYear : fullOrPartTime
      17   :    2007      : F
      17   :    2008      : P
      32   :    2006      : P
      32   :    2007      : F
      32   :    2008      : P

And so on.

Similar Threads
Thread Thread Starter Forum Replies Last Post
How Run .sql Script file in MS SQL Server 2000? aarkaycee SQL Server 2000 5 October 12th, 2009 05:43 AM
creating ssis packagte for sql server to sql serer Laxmikant_it ASP.NET 3.5 Professionals 0 November 26th, 2008 12:23 AM
Converting from MS SQL 2005 to Sql Epress edition saif44 SQL Language 0 February 16th, 2007 04:17 PM
Failed to copy objects from SQL server to SQL Serv monfu SQL Server 2000 4 December 4th, 2005 05:54 PM
Move SQL DB from one sql to another sql server Israr SQL Server 2000 3 January 24th, 2005 02:13 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.