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

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.

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

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 |

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

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