Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    50

    Unanswered: Need Help With A Query

    Hi there I need some help with a query within an Access 2000 database

    I have a database to do with fuelcard which is called "BP+"
    and i need to beable to run a Query to find out if any of the fuelcards
    have been used 2 or more time on the same day,

    The field with the date in is called "Date Time" and the field with the card number in is called "Card Number"

    if any one has any ideas PLEASE HELP!

    Kind Regards

    KM

  2. #2
    Join Date
    May 2004
    Posts
    16

    Lightbulb Query By Card Number

    Unless i'm missing something this seems like a basic query.

    1 - create a query using the wizard, based on the table containing the data

    2 - open the query in design view

    3 - on the tool bar click the sum symbol

    4 - an extra row will appear in the query design table, ensure that the card number says "group by"

    5 - under the date field sort by assending order

    when you open the query in data sheet view you should have all data grouped by card number under the same date so any reoccurrances of the same card number under one date will show what you are looking for.

    give it a try, it's quite easy

  3. #3
    Join Date
    Apr 2004
    Posts
    50
    Yeah I think you are missing somthing !.

    My database's are that big i dont have time to go though the whole database
    looking for this information i want the query to pull this information out and get rid of the rest, with a formula some thing like this

    SELECT DateValue([Date Time]) AS myDate,[Card Number], Count([Card Number]) AS num_used_per_day
    FROM mytable
    GROUP BY DateValue([Date Time]), [Card Number]
    HAVING (Count([Card Number])>2);

  4. #4
    Join Date
    May 2004
    Posts
    16

    Post

    maybe i should have been clearer

    if you then add an expression in design view that is set to count records in card number field and then set the SUM row of the design grid to count and list in decending order with >2 in the where coloumn you will have all card uses greater than two on each date listed.

    alternativly you can alter this SQL statement by adding your table names, i have tried it and it does the job on any amount of data.

    SELECT <<TABLE NAME>>.date time, <<TABLENAME>>.card number, Count(<<TABLE NAME>>.card number) AS [record count]
    GROUP BY <<TABLE NAME>>.date time, <<TABLE NAME>>.card number
    HAVING (((Count(<<TABLE NAME>>.card number))>2))
    ORDER BY Count(<<TABLE NAME>>.card number) DESC;


    i'm hope this helps

    regards

    andy

Posting Permissions

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