Counting records and other fun stuff

[VP7799]

Hey all,

I am building a database to help my unit in Iraq. Here are the fields I have so far.

Field 1: Date
Field 2: Time
Field 3: Location
Field4: SIGACT (Significant Act)

Well that is the basics look like. I would like to be able to count the number of times a type of SIGACT occured in durring the day, week, and month. I would like to have this come out in a report I could then take to Excel and graph. The SIGACT has a couple different options like IED, and SAF. Thanks for any help you can provide

 

[seth_belgium]

This would be a nice query to start with:

SELECT Table1.SIGACT, Count(Table1.SIGACT) AS [Number]
FROM Table1
WHERE (((Table1.Date) Between [Date From:] And [Date To:]))
GROUP BY Table1.SIGACT
ORDER BY Table1.SIGACT;

Just create a new query, open the SQL-view and paste the above query into it. Enter design-mode and look at what I've done.

PS : Change the "Table1" in the code to the name of your table before opening the design-view.

PS2 : You don't need to go to excel to create a graph, you can create a graph from within your query!

Greetz,

Seth

 

[VP7799]

Thanks I will give that a try

 

[seth_belgium]

Hey Pat,

Nice advice about the date/time values in one column. I gotta remember that.

And about the DateValue, does TimeValue also do the trick then to get the time out of the value?

Seth