Can I combine like values in a query?

[Funkbuqet]

I am just figuring out how to use Access 2007. I am constucting a database to track productivity of a large number of feild teams. Each team turns in a a report every day detailing their progress. What I need to be able to do is record all of the information for each of the individual teams and be able to produce a report daily and later create reports to analize productivity trends between different crews using different tools and methods to assertain the most effective methods. I have a rough plan for how to structure the database, but in trying to produce a query for the daily report I have hit a wall. I have a datasheet with a line for each team that includes all of the info off of their report. One of the items I need in my report is total number of laborers per county. The problem is that I have 4-5 teams in each county with crews of 2-7 men. How do I get a single line for each county with the totaled number of workers? When I try I get a line for each report. Any help is greatly appreciated.

Thank you.

 

[John Big Booty]

Firstly Welcome to the forum.

You should be able to do what you are after by using a Group By Query. With your query in design view click on the Sigma (Σ), you will see a new row appear in you design grid labelled Totals:, amongst the various option that become available you will find Group By and Sum. You would want to group by your Team and sum their progress.

If you've not already done so it would pay to read up on the subject of normalisation.

 

[Funkbuqet]

Thank you very much! That worked perfectly. I was thinking it was a lot more complicated than that. I imagine that I will be back with more questions later, as I am just learning to use access. Also, thans for the tip on normalization. I will look into it.

 

[Funkbuqet]

I read through the wiki posts on Normalization (Very helpfull BTW) and I have a question. How does Access 2007 do composite keys? Being able to specify a composite key would be a great help. I get a daily log from each team and I want each log entered into a single table as a line item. It would be nice to have a composite primary key of {Date,Team} that would ensure that no teams daily log got entered twice.

Sorry, if some of my questions seem basic. I have read the microsoft step by step book and have the dummies book as well. They are good for teaching the basics, but can't answer all of my situation specific questions. Thanks

 

[JANR]

How does Access 2007 do composite keys?

The same as all versions, but I personally woulden't use it as a primery key but rather make a multi-field unique index : http://www.btabdevelopment.com/ts/mfi

JR