Help me or otherwise i'll commit suicide

[Locomotive]

I have 4 hours left to submit my work in access.. which is a 1.5 pt course required to continue.. I should have done it 2 days ago but I asked for another 2 days from the professor.

Okay so all of people who really use access should probably now it... I have 3 tables:
- Drivers - License #, Name, Adress, Date of License, Birthday.
- Fines - No#, Code, Date, Hour, Place, No. of Policement
- Fine Description: Code, Type, Charge...

There are a lot of things to do there, most of them I done. but I don't now this:

1. "Make a query that will show drivers who have more than 3 fines".
2. "Drivers between the age of 25-35 with 0 fines"
3. "Total amount of fines per driver".

So basicaly I don't understand how to relate and count fines per driver! Because those are two different tables, and even if you try to put them in the query you see:

"John Doe ...." 25$
"John Doe ...." 25$
"John Doe ...." 25$
"Baba Sali" 10$

But I do no need to see this - I need it to be:
"John Doe ... 3" 75$
"Baba Sali - 1"10$

Please guys this is urgent! it's 10:00 now and need to know this by 15:00 or they'll probably kick me from the university for missing so much...

 

[Rich]

Use a Totals query, GroupBy driver and Sum Fine.
and please don't post the same question under multiple topics

 

[Locomotive]

okay thank you very much for this information i'll try it now...
and I'm sorry for posting it in two forums - but this is an emergency!!!

 

[Locomotive]

by the way - it works!
now I have

"John Doe - 3"
"Baba Sali - 1"

Wow Wonderful thank you very much! you save me!

I added a "control number" in the fines table - always "1". so when it summarized it in the query I can see the number of times the driver parked illegaly.

Now I can find the drivers between 25-35 using the "Is Null" criteria... What is the function that calculates their age based on their birthday date?

 

[Jon K]

Formula for calculating age
http://www.access-programmers.co.uk/forums/showthread.php?threadid=36541&highlight=calculate+age


Edit:-
To use the formula in the query grid, you must change the Date to Date(). Date() returns the system date on which the query is run.


>> I added a "control number" in the fines table - always "1". so when it summarized it in the query I can see the number of times the driver parked illegaly. <<

Instead of adding the control number of "1", you can use Count in the Totals query.

 

[Locomotive]

thanx. by the way - i have a new problem.
i need to show all the drivers between 25-35 with 0 fines.

which means all those who do note appear in the table of fines.
so now that I know to calculate the age, how do I choose those with 0 fines?

only people who actualy got a fine appear in the Fines table - those with 0 do not! - they are on the drivers table - which is general and shows both people with fines and without fines without actually telling whether they have a fine or no.

so how do I make a query which will show all those people WITHOUT fines?

 

[HockeyNut]

Wouldn't a 'select' query (with your 25-30 criteria obviously) using a "not equal" condition to compare the 2 tables do the trick?

David

 

[Locomotive]

i'm sorry i'm new to access, i'll admire if you'll explain it more widely to me.