Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jun 2007
    Posts
    19

    Unanswered: Query's for Access 2000

    Hi i was wondering if someone can direct me to a website or share their own knowledge regarding common queries with Microsoft access 2000. I'm learning access at school and need to know most queries, for a database test which is coming up.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Queries - SQL - http://w3schools.com/sql/default.asp
    The basic four categories of query are:
    SELECT
    INSERT
    UPDATE
    DELETE
    All this information can be found in the tutorials on the above link

    Feel free to ask any specific questions you come across back here on the forum!
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2007
    Posts
    19
    Sorry i meant Criteria. What type of criteria are most common e.g. Between 0-7.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Most common..?
    Here are a few of the common comparison operators;
    =
    <>
    >
    <
    >=
    <=
    BETWEEN _ AND _
    LIKE
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2007
    Posts
    10
    Say you have 30 rows of records that contain just 3 different dates, how would you display just one example of each date using a query?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is this a homework question?
    I need to know how complex I should make my answer.
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2007
    Posts
    348
    *cough*distinct*cough*

    George, the real question is 'how complex can yo make your answer'? I think I would be impressed and dismayed.

  8. #8
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Take alook here http://www.rogersaccesslibrary.com/TableOfContents3.asp

    Rogers done a good job ofr compiling all sorts of Access "stuff" tha can be of great help to any student at any level

    Gareth

  9. #9
    Join Date
    May 2005
    Posts
    1,191
    Also check out Martin Green's Site. Handy for those new to Access.
    Me.Geek = True

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by starkmann
    George, the real question is 'how complex can yo make your answer'? I think I would be impressed and dismayed.
    Would this solve the requirements of
    Quote Originally Posted by DangerM0use
    Say you have 30 rows of records that contain just 3 different dates, how would you display just one example of each date using a query?
    I tried to make it as ridiculous as possible - not even going to try and run it
    Code:
    SELECT	MyDate
    FROM	MyTable
    WHERE	MyDate =
    	(
    	SELECT TOP 1 MyDate
    	FROM	 MyTable
    	ORDER BY MyDate DESC
    	)
    OR	MyDate = 
    	(
    	SELECT TOP 1 MyDate
    	FROM MyTable
    	WHERE MyDate <>
    		(
    		SELECT TOP 1 MyDate
    		FROM	 MyTable
    		ORDER BY MyDate DESC
    		)
    	ORDER BY MyDate DESC
    	)
    OR	MyDate  =
    	(
    	SELECT TOP 1 MyDate
    	FROM MyTable
    	WHERE MyDate <>
    		(
    		SELECT TOP 1 MyDate
    		FROM	 MyTable
    		WHERE	 MyDate <>
    			(
    			SELECT TOP 1 MyDate
    			FROM	 MyTable
    			ORDER BY MyDate DESC
    			)
    		ORDER BY MyDate DESC
    		)
    	ORDER BY MyDate DESC
    	)
    George
    Home | Blog

  11. #11
    Join Date
    Jun 2007
    Posts
    19
    I have found this website http://www.vtc.com/products/Microsof...-tutorials.htm It looks good, howeve i cannot afford to pay 140, for all the videos. Any other websites similar to this one which are free?

  12. #12
    Join Date
    Jan 2005
    Posts
    31
    Yes.

    MSDN: Data Manipulation Language

    ------------------------------------
    Other Resources:

    Websites:

    http://www.mvps.org/access
    http://allenbrowne.com/
    http://home.bendbroadband.com/conrad...urces.html#Top

    Books: General: Beginner

    Database Design for Mere Mortals by Michael J. Hernandez

    SQL Queries for Mere Mortals by Michael J. Hernandez, John L. Viescas


    Books: General: Intermediate/Advanced

    Advanced SQL Programming, For Smarties, 3rd Edition, by Joe Celko

    SQL Puzzles and Answers, by Joe Celko


    Books: Access : Intermediate

    Access Cookbook by Getz, Litwin, and Baron
    (Compilation of solutions, listed by task-category)


    Books: Access: Advanced

    Building Microsoft Access Applications, by John L. Viescas

    Access Database Design & Programming by Steven Roman (2nd or 3rd Edition)

    Access Developer's Handbook (for your version of Access)


    Sincerely,

    Chris O.

  13. #13
    Join Date
    Feb 2007
    Posts
    348
    Quote Originally Posted by georgev
    Would this solve the requirements of

    I tried to make it as ridiculous as possible - not even going to try and run it

    I'm sorry to say I can't even follow it. I may print that off for later so I can sit and look at it. If nothing else, I might find waldo.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Come on, it's fairly simple really :P
    Select the record with the highest date
    Then select the record with the highest date that does not include the first highest date.
    Then select the record with the highest date that does not include the second highest date or the first highest date
    I have to admit that I had fun writing it - the hardest part was coming up with the rediculous concept
    I think I could probably do worse!
    George
    Home | Blog

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    D'oh! It wasn't accurate... Now it is!
    Here's my complete test code (includes create table, insert and drop so you too can marvel at its stupidity )
    Code:
    CREATE TABLE MyTable(MyDate datetime)
    
    INSERT INTO MyTable(MyDate) VALUES(GetDate())
    INSERT INTO MyTable(MyDate) VALUES(GetDate() + 1)
    INSERT INTO MyTable(MyDate) VALUES(GetDate() + 2)
    INSERT INTO MyTable(MyDate) VALUES(GetDate() + 3)
    INSERT INTO MyTable(MyDate) VALUES(GetDate() + 4)
    INSERT INTO MyTable(MyDate) VALUES(GetDate() + 4)
    INSERT INTO MyTable(MyDate) VALUES(GetDate() + 1)
    
    SELECT	DISTINCT MyDate
    FROM	MyTable
    WHERE	MyDate =
    	(
    	SELECT TOP 1 MyDate
    	FROM	 MyTable
    	ORDER BY MyDate DESC
    	)
    OR	MyDate = 
    	(
    	SELECT TOP 1 MyDate
    	FROM	MyTable
    	WHERE	MyDate <>
    		(
    		SELECT TOP 1 MyDate
    		FROM	 MyTable
    		ORDER BY MyDate DESC
    		)
    	ORDER BY MyDate DESC
    	)
    OR	MyDate  =
    	(
    	SELECT TOP 1 MyDate
    	FROM MyTable
    	WHERE MyDate <>
    		(
    		SELECT TOP 1 MyDate
    		FROM	 MyTable
    		WHERE	 MyDate <>
    			(
    			SELECT TOP 1 MyDate
    			FROM	 MyTable
    			ORDER BY MyDate DESC
    			)
    		ORDER BY MyDate DESC
    		)
    	AND MyDate <>
    		(
    		SELECT TOP 1 MyDate
    		FROM	 MyTable
    		ORDER BY MyDate DESC
    		)
    	ORDER BY MyDate DESC
    	)
    ORDER BY MyDate DESC
    
    DROP TABLE MyTable
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •