Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Acess queries!

  1. #1
    Join Date
    Sep 2003
    Posts
    11

    Question Unanswered: Acess queries!

    this (attached pic) is an example db table I created. I need to be able to search for/lookup customer surname and one date (in this format 00/00/0000) out of many, and get the relevant info for that date e.g. seraching for Surnamemeagle, Date:17/01/2002 returns the result 2 .

    Is it possible to do this in access using a query etc? I have tried to create a simple query which can do it but with limited success because in this layout it has to search for 1 out of two or more possible dates and return the relevant ordered details. Eg pick between the two dates, select the one you have searched for and display the relevant results.


    ANY1 GOT ANY IDEAS/KNOW WHAT I'M ASKING ABOUT? ANY HELP IS MUCH APPRECIATED.
    Attached Thumbnails Attached Thumbnails acesseg.jpg  

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select *
    from table
    where surname = 'surname' AND (date1 = x OR date2 = x OR date3 = x)
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Sep 2003
    Posts
    11

    Arrow

    where * and x stand for what?

    P.S The main problem is that i need to be able to get the results form the relevant Ordered column
    Last edited by gazzat; 03-08-04 at 06:12.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you do not need to know what the asterisk stands for, because you should not use that form of the SELECT statement

    x was intended to be the date you are searching for

    try this:
    PHP Code:
    select Ordered
      from test
     where Surname 
    'Smeagle'
       
    and [Date] = #17/01/2002# 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2003
    Posts
    11

    Thumbs up

    Thats good, it works where the first code didn't, but, it brings up the whole column for that date. Is there any way of selecting only the Ordered result that are relevant to the particular surname? And, i need to be able to search for a date in any date column e.g. date1 and come up with the ordered column result relevant to that date e.g. Ordered1.




    Getting Acess to query a result horizontally and vertically is a pain!

  6. #6
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by gazzat
    Thats good, it works where the first code didn't, but, it brings up the whole column for that date. Is there any way of selecting only the Ordered result that are relevant to the particular surname? And, i need to be able to search for a date in any date column e.g. date1 and come up with the ordered column result relevant to that date e.g. Ordered1.




    Getting Acess to query a result horizontally and vertically is a pain!

    SQL and databases dont work quite the way I think you think they do. Unforunately you cannot just simply say "Get all the data where any date column has the correct date, and match it up to the correct ordered column.

    You must explicitly name the date columns which could contain that date.
    R937's query will give you the ordered column where the surname is SMEAGLE and the date column = the date entered. So any row which has a surname of SMEAGLE and a date of whatever you pick will return the ordered value for that row.

    If you need to check other date columns you need to explicitly name them like this:

    Code:
    Select ordered, ordered1
    from your_table
    where surname = 'SMEAGLE'
    and [date] = #17/01/2002# or date2 = #17/01/2002# or date3 = #17/01/2002#
    Hope this clears things up just a bit.

  7. #7
    Join Date
    Sep 2003
    Posts
    11

    Lightbulb

    I know this is a bit out of place, but would it be easier to get it to work in excel?

  8. #8
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by gazzat
    I know this is a bit out of place, but would it be easier to get it to work in excel?

    Maybe a pivot report in excel would work...just filter by a certain customer. Access can be a good tool, but if the tables arent normalized, queries will be confusing and often bring back data you dont want.

  9. #9
    Join Date
    Sep 2003
    Posts
    11

    Talking

    Ok, Thanks.

    Firstly is there a forum/website dedicated to excel etc?
    Secondly what is a pivot table report and how would i use it to do what i need?

  10. #10
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by gazzat
    Ok, Thanks.

    Firstly is there a forum/website dedicated to excel etc?
    Secondly what is a pivot table report and how would i use it to do what i need?
    I think the best forum on the web (in my opinion) is www.mrexcel.com

    If you check that site for Pivot Reports, or search the excel help for pivot reports, it should have good information. Its a good way to create a "matrix" style report.

    Hope that helps.

  11. #11
    Join Date
    Feb 2005
    Posts
    1
    First thing is first: You are using the right software. All of what you want is simple and can be done quickly in Access, forget Excel.
    Second: Guessing from your knowledge displayed in your post, I'm guessing your new at using Access. I recommed you get a beginners book and browse through it for help on problems you'll have later.
    For this though:
    On your main screen that shows your db objects (forms, tables, queries, etc.), select queries - new - design view. Then, display the table you want to pull data from. Next, drag each of the fields you want to view into the boxes below. next enter the criteria for each under the section called "Criteria". Don't bother with raw SQL, the builder works great with simple stuff. Hope this helps.

  12. #12
    Join Date
    Feb 2004
    Posts
    126
    Streamline1000, you're right, first thing first, but the first thing should be to rethink his database design. And since his table is called "Test" I think he is still at the stage where he should have a little flexibility in his database design. And I second your suggestion that he gets a basic book on Access. Some of these concepts are a little difficult because they're not necessarily intuitive, but a little thought now will save you a lot of effort in the future.

    I'm amazed no one has mentioned yet that what he is asking to do would be a lot easier if his database were properly normalized.

    Gazzat, you should read this easy article http://www.phpbuilder.com/columns/ba...31.php3?page=1 on database normalization and rethink the design of your database a bit.

    You should not have multiple columns for the same information in a table in your database. So instead of having Order, Order1, Order2, and Date, Date1, Date2, you should create a new table for Orders with one column each for UUID, Date, and Order. Then you can join between the two using UUID as the key.

    Then you can use this query

    PHP Code:
    SELECT Orders.Ordered
    FROM   Test INNER JOIN Orders ON Test
    .UUID Orders.UUID
    WHERE  Test
    .Surname 'Smeagle' AND 
           
    Orders.[Date] = #17/01/2002# 
    Just think, what happens if someone makes a 4th order??? You have to redesign your database by adding new columns or maybe just not accept their order. However if you use the normalized form, you can easily just add another row to the orders table and you're good to go.

    Note: I'm not 100% sure if [Date] is a reserved word, but I would assume it is, in which case, don't use it as a column name. Use OrderDate or OrdDt or something so you don't have to use [] around it.
    Last edited by grrr223; 02-02-05 at 16:47.

  13. #13
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    aggree with Grr the issue is not SQL it the the DB design. Just Google "Access Tutorial" and you will be on your way to creating a decent DB.
    Darasen

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here is one access tutorial --> Fundamentals of Relational Database Design
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2004
    Posts
    126
    That is an excellent article r937.

    The secion on First Normal Form formalizes exactly what I was trying to say about changing your database design.

Posting Permissions

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