Using access to find the most of records
-
Hi, I am working in conservation and I am sent and record various records of various insects to record. I use MS Access 2007 to import the records into. I have seperate tables for seperate years 2007, 2008, 2009 etc...(all tables have the exact same fields, just different records for each different table) What I need to do is take all this data and find out which species was the most recorded in a year or least recorded etc.. I want to do this with a report and a chart of some sort (Pie or bar I think might be best) I have four fields on the table I wish to use the "Scientific Name" the "Location" the "Quantity" and the "Date" 1) How would I be able to take the Quantity field and find out the total numbers for each species using the Scientific name and creating a report for this? I could have 50 records with the same scientific name, but I would like totals for these. And same for all species recorded 2) Using what I said above, I'd like to create a report and chart on the top highest recorded numbers of 5 different species and another on the lowest recorded species in one table 3) Finally, using what I wanted to do above, I wanted to do this for years, to show how species are doing over years. All the records I have are in different tables with the same fields as I already mentioned, but how would I be able to use all the tables say "Species 2007" "Species 2008" etc.. to create totals for ech seperate species recorded using the "Scientific Name" field to get the species name. Any help would be appreciated. Dave
In the end we're all just the same
-
Hi, I am working in conservation and I am sent and record various records of various insects to record. I use MS Access 2007 to import the records into. I have seperate tables for seperate years 2007, 2008, 2009 etc...(all tables have the exact same fields, just different records for each different table) What I need to do is take all this data and find out which species was the most recorded in a year or least recorded etc.. I want to do this with a report and a chart of some sort (Pie or bar I think might be best) I have four fields on the table I wish to use the "Scientific Name" the "Location" the "Quantity" and the "Date" 1) How would I be able to take the Quantity field and find out the total numbers for each species using the Scientific name and creating a report for this? I could have 50 records with the same scientific name, but I would like totals for these. And same for all species recorded 2) Using what I said above, I'd like to create a report and chart on the top highest recorded numbers of 5 different species and another on the lowest recorded species in one table 3) Finally, using what I wanted to do above, I wanted to do this for years, to show how species are doing over years. All the records I have are in different tables with the same fields as I already mentioned, but how would I be able to use all the tables say "Species 2007" "Species 2008" etc.. to create totals for ech seperate species recorded using the "Scientific Name" field to get the species name. Any help would be appreciated. Dave
In the end we're all just the same
- You should be able to use the query builder to do a group by query. IIRC there's a button with a plus sign that allows you to do that easily. 2) Create a query that sums the number (same as for 1?) and sorts by sum descending and select the top 5 records. Create a similar query but sort by ascending. 3) I think you will need a Union query where each part is a query same as those in 1 and 2 but the source for each part of the union will be the different tables. Two points. First you might change the data model to have one table with a column to store the year instead of separate tables for each year (you could easily do that with an action query to insert records from the existing tables). That could simplify all your reporting. Secondly, not used Access for years so there might be better ways. :)
Regards David R --------------------------------------------------------------- "Every program eventually becomes rococo, and then rubble." - Alan Perlis The only valid measurement of code quality: WTFs/minute.
-
- You should be able to use the query builder to do a group by query. IIRC there's a button with a plus sign that allows you to do that easily. 2) Create a query that sums the number (same as for 1?) and sorts by sum descending and select the top 5 records. Create a similar query but sort by ascending. 3) I think you will need a Union query where each part is a query same as those in 1 and 2 but the source for each part of the union will be the different tables. Two points. First you might change the data model to have one table with a column to store the year instead of separate tables for each year (you could easily do that with an action query to insert records from the existing tables). That could simplify all your reporting. Secondly, not used Access for years so there might be better ways. :)
Regards David R --------------------------------------------------------------- "Every program eventually becomes rococo, and then rubble." - Alan Perlis The only valid measurement of code quality: WTFs/minute.
IMO not using Access is the better way. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
-
- You should be able to use the query builder to do a group by query. IIRC there's a button with a plus sign that allows you to do that easily. 2) Create a query that sums the number (same as for 1?) and sorts by sum descending and select the top 5 records. Create a similar query but sort by ascending. 3) I think you will need a Union query where each part is a query same as those in 1 and 2 but the source for each part of the union will be the different tables. Two points. First you might change the data model to have one table with a column to store the year instead of separate tables for each year (you could easily do that with an action query to insert records from the existing tables). That could simplify all your reporting. Secondly, not used Access for years so there might be better ways. :)
Regards David R --------------------------------------------------------------- "Every program eventually becomes rococo, and then rubble." - Alan Perlis The only valid measurement of code quality: WTFs/minute.
Hi Dave, Thanks for the info it was useful, sorry for late reply
In the end we're all just the same