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

    Lightbulb 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
    Feb 2004
    Location
    Hamburg, Germany
    Posts
    22
    Try this:

    SELECT [Date Time], [Card Number], COUNT([Card Number]) AS num_used_per_day
    FROM mytable
    GROUP BY [Date Time], [Card Number]
    HAVING (COUNT([Card Number]) > 1)

    If Time Values are stored in your [Date Time] field you will have to eliminate these first for the query to work.

  3. #3
    Join Date
    Apr 2004
    Posts
    50
    Is there any way to keep the time values as it could be imported if some one is over using there card to give them times and dates

  4. #4
    Join Date
    Apr 2004
    Posts
    50

    Need help with a query

    kbk Cheers thats works great

    I need some other thing added to that now, hope am not being a pain,

    in the same database there are a few more fields which i need added in so i can see what the person is buying with his card and the costs

    i need the "net" and the "VAT" with a total but also "Product" and the "Quantity"

    with this i need to add all the spendage of that card on that day to get a grand total

    Hopes this make sense

    KM

    Please see below of what the lay out might look like

    Date Time | Card Number |Num_Used_Per_Day| Products | Quantity | Net | Vat | Total | Grand Total Of All The Products Added Together |

  5. #5
    Join Date
    Feb 2004
    Location
    Hamburg, Germany
    Posts
    22
    O.K., use this query to convert the date-time field on the fly

    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])>1);

    Regards

    KBK

  6. #6
    Join Date
    Feb 2004
    Location
    Hamburg, Germany
    Posts
    22
    KlansMan,

    now this is getting more complex, you are looking into a mix of detailed data and aggregated data in one recordset which is not necessarily effective. If your task is to generate a report look at the "reports" designer in access. I'm sure you can easily work out a report that will display something like the following:

    DATE | Card Number |Num_Used_Per_Day| Grand Total Of All The Products Added Together
    Date Time | Products | Quantity | Net | Vat | Total

    where the first line displays the aggregated values and the second line the details.

    KBK

Posting Permissions

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