weekly / monthly reports

[tbcwarrior]

Hi

i need to create a weekly report showing the amount of records entered into the database and the value and also need to sort that by region

the fields i have are

Enquiry recieved date
Region
Value


i also need to create the same report for monthly

everytime i create a report based on a query it puts all of the records it has found on the report, i do not want this to happen, i just want the totals

is this possible

thanks for the help

regards
chris

 

[pbaldy]

How about a totals query that groups by region, sums value and has a WHERE clause on date?

 

[tbcwarrior]

hi any chance of an example as i am realy new to this. thanks for the reply

regards
chris

 

[pbaldy]

SELECT Region, Sum(Value) AS RegionTotal
FROM TableName
WHERE DateField Between #6/20/07# And #6/27/07#
GROUP BY Region

 

[tbcwarrior]

Hi i have all of that working now, so thanks very much for that, but is there a way that i can enter a name into a field where someone has left that field blank

so for instance if in region i had a drop down box with the south, north etc but the person that filled out a record left that field blank can i say "not known" in that blank field in a query so that the person reading the report knows that this was not completed properly.

regards

chris

 

[pbaldy]

This may work:

SELECT nz(Region, "Not known"), Sum(Value) AS RegionTotal
FROM TableName
WHERE DateField Between #6/20/07# And #6/27/07#
GROUP BY nz(Region, "Not known")