|
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
|
|
|
|
|
1) 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).
|
|
|
|
|
Hi Dave,
Thanks for the info it was useful, sorry for late reply
In the end we're all just the same
|
|
|
|
|
HOW TO DEPLOY *.SDF FILE OF SQL COMPACT VERSION
is i need to install sql server on client machine to deploy this file ?
For more help : pranayamr.blogspot.com
|
|
|
|
|
Here:
MSDN[^]
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
|
Hi all
I am using VB 2008.
I am developing a program that writes millions of lines to a database.
The type of database isn't decided yet.
I think I'll be using SQL databases (sdf) that VB provides.
I need to add millions of rows at one time.
Can I do that by using multiple threads?
I mean I'll divide the data in ,say, 6 parts and write each part using a different thread.
My whole purpose is to save time.
As fast as the writing occurs my boss is that happier.
He doesn't want to know how that is being done, he just wants to know that its been done.
I know sqlite can't do this.
Is there any other database which is able to do that.
It must not use any dll.
My boss wants the app to be independent.
Would that result in data loss?
even if the rows are written out of order, no problem.
-- Modified Sunday, May 16, 2010 7:27 AM
|
|
|
|
|
It's hard to find any database engine that does not use .dll files. SQLite can be linked to your program so that a .dll file is not needed. However, you mentioned that it's not sutible for your project.
You can use multiple threads to write to an SQL Server database, but the actual performance of the writing operation may not be better than single-threaded programs. The speed is limited by the performance of the hard drive and whether your database is setup to run in distributted environment. If your database resides on a single hard drive, I doubt that you will see any performance improvement by using multi-threading.
You can do a lot more on high-end database products. The question is whether or not your boss is willing to spend a lot of money on high-end database products?
|
|
|
|
|
thanks for answering
the reason that I don't want to use sqlite are:
1. multi-thread writing is not supported.
2. it requires a sqlite.dll file in the exe folder to work.
If you think that performance wouldn't differ much using multiple threads then
i can very well use a single thread.
I'll carry out the tests.
If you know a method so that my app can become a two file app ie one exe and one database file
then please tell me.
in the above post ANDY_L_J has pointed out that .sdf databases need 7 dlls.
if that's that then using sqlite is a better option as it requires only 1 dll file.
|
|
|
|
|
I am afraid I don't have a suggestion to your case. Since you are using VB, the need for dll seems to be unavoidable.
Note that in the case of SQLite, C/C++ programs can link the whole SQLite into the program so you end up with just one exe file with no need for any dll files. That meets your "no dll" requirement but it's not using VB. Too bad.
|
|
|
|
|
Do you need a database at all?
is your app going to have multiple users, sharing the data?
If you don't need the existing data to generate new data, you could (each) create a simple file, then perform a "bulk insert".
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).
|
|
|
|
|
Hullo Luc
I need a database as I need to perform some operations that only a database can do effectively.
no its not a multiple user program.
I have a large quantity of data that I want to write to the database
as fast as possible.
I thought of dividing the data into ,say, 5 parts and use 5 threads to put these 5 parts
so that my work is completed in 1/5th time.
Am I correct in thinking so?
If I do that would that result in data loss?
|
|
|
|
|
If all that happens on a single machine, I doubt having multiple threads will really help, but I haven't tried it. I'd rather go for fewer SQL commands, so I am in favor of bulk insert, if that fits the application.
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).
|
|
|
|
|
Jack_18 wrote: I have a large quantity of data that I want to write to the databaseas fast as possible.
A database isn't optimized for bulk logging in realtime. If "as fast as possible" takes five seconds, would that be considered acceptable?
Jack_18 wrote: I thought of dividing the data into ,say, 5 parts and use 5 threads to put these 5 partsso that my work is completed in 1/5th time.
Adding a thread doesn't mean that your work is done in half the time. How many threads can your system run simultaneously?
If you need to log a lot of data, consider dumping it as suggested and have a second proces write it to a database.
I are Troll 
|
|
|
|
|
Have a Google on '.net embedded database'.
The first link offered has several suggestions and the fifth (judging purely from the subject line) might fit your needs.
Good luck!
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
Why do programmers often confuse Halloween and Christmas?
Because 31 Oct = 25 Dec.
|
|
|
|
|
Hi Henry,
not sure referring to Google results by their position on the screen works well for different countries. By default, in Belgium we get a localized Google site for starters; then there may be payed results on top; and of course, items move around when they become more/less popular.
This is what I am currently getting for your suggested Google action (using www.google.com, not .be):
Raima Embedded Databases
eXtremeDB Embedded DBMS
Embedded Database
We need an Embedded Database for .NET applications - Jon Galloway
SQLite - ALMOST a great embedded database solution for .NET ...
Embedded SQL database engine for .NET developers - VistaDB
Embedded .Net database shootout
DotNetFirebird: Using Firebird SQL in .NET: Embedded Firebird and
...
do you see the same list?
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).
|
|
|
|
|
Luc Pattyn wrote: not sure referring to Google results by their position on the screen works well for different countries
Er........ 
I should have known that.
As far as the list goes mine (google.co.uk) starts with the 4th item in your list and then follows exactly. The first two do not appear on the first page but the 3rd is.
Still, if the OP did search he should have got some ideas at the very least.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
Why do programmers often confuse Halloween and Christmas?
Because 31 Oct = 25 Dec.
|
|
|
|
|
yep, Google is American, and very NTSC at that, Never The Same Content. As is most of the internet.
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).
|
|
|
|
|
i Have Self join Table in Sql server Database(accounting database)
this is table columns(Accounts Table)
AccountID
ParentAccountID
AccountNo
AccountName
and i have another table (Accounts Balance Table)
AccountBalanceID
AccountID
FirstBalanceCredit
FirstBalanceDebit
TotalCredit
TotalDebit
in accountbalance i add balance for accounts in last level which is not parent for any other accounts
so my question is how to get sum of debit or credit in any level
for example if i have accountx which is parent so it doesn't have any rows in accountbalance because it's not last level i need to get total debit for that account which is the total accounts for all accounts under him which may not be the last level thanks for any help
md_refay
|
|
|
|
|
with Account(AccountID)
As
(
select AccountID from Accounts A where AccountID = #AcountNumber#
union all
select A.AccountID from Accounts A, Account B
where A.ParentAccountID = B.AccountID
)
SELECT sum(totaldebit) TotalDebit FROM Account, AccountsBalance where
Account.AccountID = AccountsBalance.AccountID
replace #AcountNumber# with your account number (probably use variable/parameter).
Hope this will help you 
|
|
|
|
|
In the stored procedure,
I have a UDT table which have one column.
and also a UDF(multi valued) that returns five columns , this UDF have one parameter of string type
How can i call that UDF(multi valued) in the select statement ?
like following mentioned.
Select from tablename T
Inner Join UDF(column) F on F.id = T.ID
The ID is common column in both tables and also i want to create join between them
|
|
|
|
|
Hi,
My requirement is, i have to update a column with replace funtionality
eg., column1
-------
this is columnA
columnA is here
after update,
column1
-------
this is columnB
columnB is here
i have to update only A with B...
Any idea or code samples will be very much appreciated.
|
|
|
|
|
I think I may have misunderstood (esp. due to reference to ColumnB), but replace will do it:
SQL> select C from dave;
C
--------------------------------------------------------------------------------
this is columnA
columnA is here
SQL> update dave set c = replace(C, 'columnA', 'NewText');
2 rows updated.
SQL> select C from dave;
C
--------------------------------------------------------------------------------
this is NewText
NewText is here
SQL>
Dave
----
Data Exploration and Data Profiling Tools[^]
|
|
|
|
|
padmanabhan N wrote: i have to update only A with B...
If that`s the case then simply update columnA with columnB
eg:
update mytable set mytable.columnA=mytable.columnB where .....
When you fail to plan, you are planning to fail.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
