|
|
I created a .mdf file of MS-Access which contains some tables.
Now i want data from two or more table in single table.
So i created queries in .mdf file with in Ms-Access. And querying these from my application.
Suppose Details is query name in .mdf file, then from application
i am using
"Select [nName], [nDate] from [Details]" .
Now i have some question on this:
a) If i use above way i.e. (create query on access then use this virtual table in application) It increases file size.
here question is- Will this going to slow application.
b) If i don't create query and from application with help of joins and sub-queries get data in DataTable object then use this DataTable.
But here question is- this DataTable will grow in size by time. this will also create load on application. what if this DataTable be in MB size?
So, Is creating Query(virtual-table / view) in .mdf file is good ?
Or Directly get data in DataTable object, then use this data ?
I am using C#.
any help/comment !!
|
|
|
|
|
Hum Dum wrote: So, Is creating Query(virtual-table / view) in .mdf file is good ?
Yes, in a real relational database (SQL Server, Oracle, MySQL etc) this is called a view and has no cost in disk space. This is probably NOT true for Access as Access is a POS, however using queries/views is still the correct way to go.
You need to evaluate your use of Access as your datastore and make sure it is valid. If more than one user is to access the data then use SQL Server.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hum Dum wrote: I created a .mdf file of MS-Access which contains some tables.
.mdf is the default extension for SQL Server... you sure you didn't use SQL Server? And if you used Access, renaming it .mdf won't make it SQL Server... 
Saving a query in Access will not increase the filesize dramatically, it's not like it's making a copy of the actual data tables...
Access is going to be fine as a database engine if you have up to 10 users that are located on the same local network, and not using wireless network connections.
For database size, Anything up to a couple of hundred meg in Access will still give you reasonable performance (depending on your data structures of course)
|
|
|
|
|
_Damian S_ wrote: you sure you didn't use SQL Server?
Yup i am sure on that ,
Only here i mistype .mdb to .mdf.
I have some more question
a) A .mdb file we can store anywhere we want. Like this can we store .mdf(sql server DB file).
b) How can i transfer .mdb file to .mdf along data inside. Or more like moving to sql server. Is there any feature in SQL server to import and convert.
c)At present i am storing mdb file path in my connection string by OpenFileDialog(built in C#.net). Like this Can i search for .mdf file?
and using this file on different pc(LAN network) with my application.
In sql server where can we find our DB file( i hadn't explore much on myself)
regards
|
|
|
|
|
Yes, you can store your mdb file wherever you like. So long as it can be accessed by your front end software (on the same LAN, as I said earlier).
Yes, you can transfer Access data to SQL Server. You can either use the "upsizing wizard", or simply create a blank SQL Server database and import from Access. (Note - this doesn't work with SQL Express - but you can create SQL Express tables and link to Access, then use queries to get your data across).
Yes, you can do similar for SQL Server. Check out www.connectionstrings.com for comprehensive details on how to connect to SQL Server. I wouldn't worry about letting the user search for the .mdf file though - that's not how it works. They simply connect to the SQL Server and your userid/password/login that is set up handles the rest.
In SQL Server, your default location for data files is under MSSQL\Data, and depending on what version you have installed it may be directly under C:\, or in c:\program files etc... have a look around for it. Of course, you could simply open SQL Server and look at the file details of a created database.
|
|
|
|
|
_Damian S_ wrote: I wouldn't worry about letting the user search for the .mdf file though - that's not how it works. They simply connect to the SQL Server and your userid/password/login that is set up handles the rest.
Well for file search i am doing only for first time. If user installing application for first time then it asks for mdb file then i create a text file on local pc, then from next time onward i simply read this file for getting DB file path.
Now Can SQL express do the work(As its freeware i hope so , SQL server is not). If i install Sql Express on one machine then create DB. After this can i use this DB from LAN ?
By providing userid/pass as u said and it handle rest?
regards
|
|
|
|
|
With SQL all you are doing is providing the Servername and Database name to connect. This is the same no matter where you connect from. Directly connecting to the underlying files is something that is only of use in on-the-fly demos and not to be used for serious applications.
SQL Express will work fine for this, though it has some limitations See this Microsoft article for details[^]. The two main issues will be a reduced functionality Management Studio GUI and the 4GB data limit.
|
|
|
|
|
Hi,
I am working with an SSIS package(2005), which dumps records from a csv file into TABLE1. After making some alterations to thses records, it is gain inserted into another TABLE2 in the DB.
One of the columns is a date column. TABLE2 has all the datatypes fixed and it has a col with datatype "smalldatetime". TABLE1 is created each time the package is run. So at first i made the TABLE1.Rdate col datatype datetime but when i try converting this col to smalldatetime for inserting into TABLE2 it gives an error "The conversion of a datetime data type to a smalldatetime data type resulted in an out-of-range datetime value."
I tried changing the date col datatype to VARCHAR but again at the time of converting this col to smalldatetime for inserting into TABLE2 it gives an error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.". The date in the TABLE1.Rdate col is in the format '2009-10-13'.
Can anyone help me with this Please?
Thanks & regards,
Payal
|
|
|
|
|
I always just chuck everything into a varchar field when using SSIS and let the down stream stored proc do all the transforms. I can deal with problems better in a stored proc than insinde an SSIS package.
Look in BOL for CONVERT and use one of the modifiers, 111 - Japan looks like the most likely
CONVERT(DATETIME, varcharfield,111)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hi guys, thanks for all the help recently, this is really a great forum! 
Today I was trying to write what seems like a simple query statement, but unbelievably after hours of trying I still can't get it to work. 
My table looks like the following:
ID Ticker SlopeofLine
1 MSFT 5
2 MSFT 2
3 MSFT 1
4 MSFT 0
5 MSFT -3
6 MSFT 1
1 DELL -2
2 DELL -1
3 DELL 2
4 DELL 4
5 DELL 3
6 DELL 4
...
...
I want to retrieve, for each Ticker, the last/largest ID where SlopeofLine is less than 0. So ideally, the query should retrieve this from the above table:
ID Ticker SlopeofLine
5 MSFT -3
2 DELL -1
Please forgive me if this is a stupid question; I'd greatly appreciate any help.
My query which doesn't work currently look like this:
Select Ticker, ID, SlopeOfLine
From Table
Where SlopeofLine < 0
AND ID = (SELECT MAX(ID) FROM Table t1 Where Table.Ticker = t1.Ticker)
|
|
|
|
|
Hi,
I would try:
Select Ticker, MAX(ID) as MAXID, SlopeOfLine
From Table
Where SlopeofLine < 0 GROUP BY Ticker
Not tested!
[ADDED]
Now tested, and considered incorrect: Ticker and MAX(ID) are fine, SlopeOfLine is not.
conclusion: you need two selects.
[/ADDED]
modified on Wednesday, January 13, 2010 8:22 PM
|
|
|
|
|
You may want to try something like this:
select last(ID),Ticker,last(SlopeofLine) from
(select ID,Ticker,SlopeofLine from YourTableName where SlopeofLine <0 order by Ticker,ID)
group by Ticker;
Happy querying!
|
|
|
|
|
USE [TempDB]
GO
CREATE TABLE #Tracker(
[Id] [int] NOT NULL,
[Ticker] [nvarchar](4) NOT NULL,
[SlopeOfLine] [int] NOT NULL,
)
GO
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (1, 'MSFT', 5)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (2, 'MSFT', 2)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (3, 'MSFT', 1)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (4, 'MSFT', 0)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (5, 'MSFT', -3)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (6, 'MSFT', 1)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (1, 'DELL', -2)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (2, 'DELL', -1)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (3, 'DELL', 2)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (4, 'DELL', 4)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (5, 'DELL', 3)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (6, 'DELL', 4)
GO
SELECT Id, Ticker, SlopeOfLIne
FROM #Tracker t
WHERE Id = (SELECT TOP 1 Id
FROM #Tracker
WHERE Ticker = t.Ticker
AND SlopeOfLine < 0
ORDER BY Id DESC)
GO
DROP TABLE #Tracker
GO
|
|
|
|
|
This works too! Thanks Russell.
|
|
|
|
|
CREATE TABLE #Tracker(
[Id] [int] NOT NULL,
[Ticker] [nvarchar](4) NOT NULL,
[SlopeOfLine] [int] NOT NULL,
)
GO
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (1, 'MSFT', 5)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (2, 'MSFT', 2)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (3, 'MSFT', 1)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (4, 'MSFT', 0)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (5, 'MSFT', -3)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (6, 'MSFT', 1)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (1, 'DELL', -2)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (2, 'DELL', -1)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (3, 'DELL', 2)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (4, 'DELL', 4)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (5, 'DELL', 3)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (6, 'DELL', 4)
GO
SELECT T1.Id, T1.Ticker, T1.SlopeOfLIne
FROM #Tracker t1
LEFT OUTER JOIN (SELECT * FROM #Tracker WHERE SlopeOfLine<0) T2 ON
T2.Ticker = t1.Ticker
AND T2.Id > t1.Id
WHERE T2.Id IS NULL
AND t1.SlopeOfLine < 0
GO
This may or may not be faster than the subquery method used above.
|
|
|
|
|
Yes it works! Thanks a lot.
|
|
|
|
|
Thanks for all the help guys. I'll try each one of them soon! 
|
|
|
|
|
Even this works
SELECT X.Id,X.Ticker,Y.SlopeOfLine FROM
(SELECT MAX(Id)Id,Ticker
FROM @Tracker
WHERE SlopeOfLine < 0
GROUP BY Ticker)X JOIN @Tracker Y ON X.Id = Y.Id AND X.Ticker = Y.Ticker
Niladri Biswas
|
|
|
|
|
Hi all, I have a question about avg function.
How do I query from 6 different tables and avegrage them.
All of the tables have same number and type of coloumn.
thanks in advance.
|
|
|
|
|
I'm not sure if these work, but they should worth a try:
Select (t1.col1, t2.col1,t3.col1, t4.col1, t5.col1, t6.col1)/6
From table1 as t1, table2 as t2, table3 as t3, table4 as t4, table5 as t5, table6 as t6
or
use nested Select statement (use the sql AVG function on a grand total, and in the nested select statement you can define that the grand total is sum of individual columns).
or
use User-Defined-Function (you can create a function that takes in 6 parameters and return an average).
|
|
|
|
|
Hi James Shao, Thanks for your answer, I tryied the first one it seems like not wroking with sqlite.
I am not sure how to implement the second statement since I am (sorry to say) not good at sql yet.
this is the first time I am working with sql. reading books and getting help form online this is how
I am getting my work done. May be after this app I will have some knowledge about sql.
if second statement does not wrok then third option is the one I will have to do.
thanks very much for your feedback and help.
modified on Wednesday, January 13, 2010 11:19 AM
|
|
|
|
|
You may want to use a sub-query to solve this problem. Try something like this:
Suppose you have six tables t1, t2, ..., t6, with a column called c1 you want to average.
select avg(c1) from
(
select c1 from t1 where ...<put your condition here>
union all
select c1 from t2 where ...<put your condition here>
union all
select c1 from t3 where ...<put your condition here>
union all
select c1 from t4 where ...<put your condition here>
union all
select c1 from t5 where ...<put your condition here>
union all
select c1 from t6 where ...<put your condition here>
)
That will return the average of the column from all six tables.
|
|
|
|
|
Try this
Assuming the tables have the following structure with data
Tbl1
ID1 NUM1
1 10
2 20
Tbl2
ID2 NUM2
1 11
2 22
Tbl3
ID3 NUM3
1 1
2 2
3 3
Tbl4
ID4 NUM4
1 41
Tbl5
ID5 NUM5
1 109
Tbl6
ID6 NUM6
1 61
2 62
Query:
SELECT AvgNum = AVG(N)FROM(
SELECT N=NUM1 FROM Tbl1 UNION ALL
SELECT NUM2 FROM Tbl2 UNION ALL
SELECT NUM3 FROM Tbl3 UNION ALL
SELECT NUM4 FROM Tbl4 UNION ALL
SELECT NUM5 FROM Tbl5 UNION ALL
SELECT NUM6 FROM Tbl6
)X
Output:
AvgNum
31
Niladri Biswas
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
