var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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!
SELECT [Date Time], [Card Number], COUNT([Card Number]) AS num_used_per_day
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
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
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
GROUP BY DateValue([Date Time]), [Card Number]
HAVING (Count([Card Number])>1);
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.