|
I am having problems building a Query and would really appreciate if anybody had any ideas. I need to build a query where I select only the new people that have come in during a quarter that have children ages 0 to 5. (ie The person does not get counted if they came in during another quarter that year) I have been making multiple queries and runnning this report using VB6 code and it is very slow. Below is a list of my tables and other info: (I've simplified the tables to make it easier to list.)
Language\DB: VB6, DAO 3.6, Access Database
Tables\Queries:
tbl_Customer
l_Mem_id (Key)
dt_DateCreated
str_First_nm
str_Last_nm
Qry_Child
l_ID_id (Key)
l_Mem_id
dt_DOB
str_Name
l_age (l_Age: Int((DateDiff("d",[dt_DOB],Now()+1)/365.25))
Qry_Visit
l_ID_id (Key)
l_Mem_id
dt_Date
str_Reason
l_Quarter (l_Quarter: DatePart("q",[dt_Date])
Any help would be appreciated! Thanks 
|
|
|
|
|
Not sure if the exact syntax will work in Access, but here goes:
SELECT * FROM tbl_Customer WHERE
l_Mem_id IN (SELECT l_Mem_id FROM Qry_Child WHERE l_age BETWEEN 0 AND 5) AND
l_Mem_id IN (SELECT l_Mem_id FROM Qry_Visit WHERE dt_Date BETWEEN @startdate AND @enddate)
What a piece of work is man, how noble in reason, how infinite in faculties, in form and moving how express and admirable . . . and yet to me, what is this quintessence of dust? -- Hamlet, Act II, Scene ii.
|
|
|
|
|
Many thanks. I added a NOT IN statement to exclude them if they came in during another Quarter. One problem that I am having is the number of people is inconsistent if you run the query a couple of times. I even added people during earlier quarters that showed up during the specified dates and it would not show the person after I did a requery but the number of people would sometimes stay the same or change to 1 less and sometimes 2-3 times less people. I have inserted the revised SQL statement below Any suggestions?
SELECT * FROM tbl_Customer WHERE l_Mem_id In (SELECT l_Mem_id FROM Qry_Child WHERE l_age BETWEEN 0 AND 5) And l_Mem_id In (SELECT l_Mem_id FROM tbl_Visit WHERE dt_Date BETWEEN #4/1/2003# AND #6/30/2003#) And l_Mem_id Not In (SELECT l_Mem_id FROM tbl_Visit WHERE dt_Date BETWEEN #1/1/2003# AND #3/31/2003#)
|
|
|
|
|
I think I see a possible issue here.
If a member has a visit between 4/1/2003 and 6/30/2003 and the same member has a visit between 1/1/2003 and 3/31/2003, is that member included or excluded?
Also, after I wrote this last night, I realized that the same query might be feasible with an INNER JOIN...
SELECT * FROM tbl_Customer
INNER JOIN Qry_Child ON tblCustomer.l_Mem_id = Qry_Child.l_Mem_id
AND Qry_Child.l_age BETWEEN 0 AND 5
INNER JOIN tbl_Visit ON tblCustomer.l_Mem_id = tbl_Visit.l_Mem_id
AND (tbl_Visit.dt_Date BETWEEN #4/1/2003# AND #6/30/2003#)
AND NOT (tbl_Visit.dt_Date BETWEEN #1/1/2003# AND #3/31/2003#)
What a piece of work is man, how noble in reason, how infinite in faculties, in form and moving how express and admirable . . . and yet to me, what is this quintessence of dust? -- Hamlet, Act II, Scene ii.
|
|
|
|
|
They are excluded. They are only counted once a year. I tried to run your query and it returned no results. I tried playing with it a little and inner joining the Qry_Child and running subqueries on the tbl_Visit (IN and NOT IN) but only recieved errors.
Also, could you possibly explain the difference of when to join tables and when to use a subquery. I am still having trouble understanding the logic. Thanks.
|
|
|
|
|
Yeah, I suppose joins are best applied when you want to include fields from table on both sides of the join statement, but they can be applied to form the basis for including or excluding rows. Since it was late last night, the idea of INNER JOINS might be flawed... I always seem to try several things until I get the one thing that works consistenly.
* thinking out loud *
SELECT table1.*, table2.* FROM table1 RIGHT JOIN table2 ON table1.key = table2.fkey WHERE ...
-- all rows from table2 and matching rows from table1
SELECT table1.*, table2.* FROM table1 LEFT JOIN table2 ON table1.key = table2.fkey WHERE ...
-- all rows from table1 and matching rows from table2
SELECT table1.*, table2.* FROM table1 INNER JOIN table2 ON table1.key = table2.fkey WHERE ...
-- only matching rows from table1 and table2
SELECT table1.*, table2.* FROM table1 FULL JOIN table2 ON table1.key = table2.fkey WHERE ...
-- all rows from table1 and table2
Ok, so that's the difference, using IN or EXISTS simply forms the basis for inclusion or exclusion of rows from the table specified in the FROM clause, whereas using a join does as described above.
So, it seems like your example should work consistently, unless the underlying data are changing so often that the results change accordingly. If you like, you can send me some or all of the data and I can try it out.
What a piece of work is man, how noble in reason, how infinite in faculties, in form and moving how express and admirable . . . and yet to me, what is this quintessence of dust? -- Hamlet, Act II, Scene ii.
|
|
|
|
|
I ran the query a few more times today and it seemed to work fine without the small discrepancies. I can't send any of the data b/c some of the data falls under HIPAA. Again, thank you for all your help.
-Garrett
|
|
|
|
|
hello
I have created a windows application with visual c++ 6.0 which connects to a database.
The application executes SQL queries and I want to set some triggers or the insert, update and delete queries.
However, Where should i place my triggers?
sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=s",sDriver,sFile);
TRY
{
CDatabase ComboDatabase;
// Open the database
ComboDatabase.Ope(NULL,false,false,sDsn);
// Build the SQL statement
SqlCommand = "INSERT INTO BandSetting "
"VALUES('" + str25 + "'" ",'" + str1 + "'" ",'" + str2 + "'"
",'" + str3 + "'" ",'" + str4 + "'" ",'"+ str5 + "'" ",'"+ str6 + "'" ",'" + str7 + "'" ",'" + str8 + "'" ",'"+ str9 + "'" ",'" + str10 + "'" ",'" + str11 + "'" ",'"+ str12 + "'" ",'" + str13 + "'" ",'" + str14 + "'" ",'" + str15 + "'" ",'" + str16 + "'" ",'" + str17 + "'" ",'"+ str18 + "'" ",'" + str19 + "'" ",'" + str20 + "'" ",'"+ str21 + "'" ",'" + str22 + "'" ",'" + str23 + "'" ", + str24 + "'" ")" ;
ComboDatabase.ExecuteSQL(SqlCommand);
// Close the database
ComboDatabase.Close();
}
where do i put the trigger???
hope you guys can help me out on this! thanks (^_^!!)
|
|
|
|
|
solostar*** wrote:
where do i put the trigger???
Trigger does not run from your application , you have to create it in your SQLServer(don't know if access suport triggers) , then it will call automaticlly.
Mazy
You're face to face,
With the man who sold the world - David Bowie
|
|
|
|
|
I don't know if I should put this under php or sql, but it is a little of both. I have secure info that I do not want published to a file on the server at any time even a few seconds. I just want to take it strait out of the data base and have the person click a link, to this php file below and when they go to it it prompts them to download. I want the sql info in the file. I think I have most of the code right, but it isn't quite working. As of now, it is downloading a blank file.
<br />
function show_file_information($filename)<br />
{<br />
@readfile($filename);<br />
}<br />
<br />
if(!class_exists(MySQL))<br />
{<br />
require '../home/MySQL.php';<br />
require '../home/conf_global.php';<br />
$mysql = new MySQL();<br />
$mysql->connect($INFO['sql_user'],$INFO['sql_pass'],$INFO['sql_database'],$INFO['sql_host']);<br />
}<br />
<br />
$news_result = $mysql->query("SELECT * FROM newsletters WHERE id='1'");<br />
$news = mysql_fetch_assoc($news_result);<br />
<br />
$filename = $news['add_id'];<br />
<br />
header("Pragma: public");<br />
header("Expires: 0");<br />
header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); <br />
header("Content-Type: application/force-download");<br />
header("Content-Type: application/octet-stream");<br />
header("Content-Type: application/download");<br />
header("Content-Disposition: attachment; filename=".basename(emaillist).".txt");<br />
header("Content-Transfer-Encoding: binary");<br />
header("Content-Length: ".strlen($filename));<br />
<br />
<br />
?> <br />
LOSTTWARE.com
My site allows for people to develop private programming teams, with password protected forums for each team.
|
|
|
|
|
I'm trying to connect to an MSDE database that is running on my local machine and I've tried the following connection strings:
Provider=[SQL Server]; Server=(local); Trusted_Connection=no; Database=users; Uid=me; Pwd=123456; When I use that one I get an error that "Provider cannot be found. It may not be installed properly." I did install a SQL Server prodvider though the ODBC control panel, but I don't know if that was the right thing to do (this is my first time trying to program for a database). The other connection string I tried was:
Provider=MSDASQL; Server=(local); Trusted_Connection=no; Database=users; Uid=me; Pwd=123456; The error I get on that string is "[Microsoft][ODBC Driver Manager]Data source name not found an no default driver specified." Could someone point out what I'm doing wrong here. Thanks in advance for the help. I'm using the ADO classes written by Carlos Antollini (http://www.codeproject.com/database/caaadoclass1.asp[^]) to try to connect.
- monrobot13
|
|
|
|
|
|
Hi Gurus:
i m new to database programing , so i m in problem even at first step ,
i have two tables (using MS Access as DBMS)
Purchase_Detail
OrderId | ProductID | QtyPurchased
1====== | == 1 ======== | == 20
1====== | == 2 ======== | == 20
And
Purchase_Return
OrderId == | == ProductID == | == QtyReturn
1 ======= | ====== 1 ====== | ==== 5
now i want to get the result from Product_Detail table as
using query
OrderId == | == ProductID == | == QtyPurchased
1 ======== | ===== 1 ======= | == 15
1 ======== | ===== 2 ======= | == 20
i m using the following query
SELECT Purchase_Detail.*
FROM Purchase_Detail LEFT JOIN Purchase_Return ON [Purchase_Detail].[pdOrderID] = [Purchase_Return].[prOrderID] AND
(Purchase_Detail.pdProductID = Purchase_Return.prProductID) AND (Purchase_Detail.pdOrderID = Purchase_Return.prOrderID)
WHERE (((Purchase_Detail.pdOrderID = '4')AND(Purchase_Return.prOrderID) Is Null));
the result of which is
OrderID == | == ProductID == | == QtyPurchased
1 ======== | ==== 2 ======== | ==== 20
missing the product ID 1 which has remaing 15 items in the stock....
can any one help me..
Thanks in advance.....
Seefou
|
|
|
|
|
Try this:
SELECT Purchase_Detail.OrderId, Purchase_Detail.ProductId, Purchase_Detail.QtyPurchased - ISNULL(Purchase_Return.QtyReturn, 0)<br />
FROM Purchase_Detail <br />
LEFT JOIN Purchase_Return ON Purchase_Detail.OrderId = Purchase_Return.OrderId <br />
AND Purchase_Detail.ProductID = Purchase_Return.ProductID
Basically this is returning everything from Purchase_Detail as it is a left join, but the QtyPurchased has been deducted from Purchase_Return.QtyReturn (if there is any, otherwise it is deducted by 0).
I hope it works 
Edbert P.
Sydney, Australia.
|
|
|
|
|
Hi:
The problem remains same and i m getting the message that wrong number of arguments in the Function IsNull
can any one help me ,,,.........
Thanks
"Winner's don't do different things , they do things differently "
|
|
|
|
|
Sorry..I forgot that you're using MSAccess.
You have to change the IsNull function into IIF(IsNull(Purchase_Return.QtyReturn), 0, Purchase_Return.QtyReturn)
So from
- ISNULL(Purchase_Return.QtyReturn, 0)
to
- IIF(IsNull(Purchase_Return.QtyReturn), 0, Purchase_Return.QtyReturn)
Edbert P.
Sydney, Australia.
|
|
|
|
|
I am writing a program in C# to access a SQL Server database using the ODBC ADO.NET classes. I am having a problem with it timeing out before the dataset is returned. I have tried to increase the connect timeout (60) and the command timeout (300 to as much as 48000) but I am still getting the timeouts. In fact, it seems that it is ignoring the timeout I am setting. Is there a way to disable the command timeout and just have it process until finished.
NOTE: The command consistently returns in less than 45 seconds and I am getting partial data, depending on the network traffic, in the dataset upon return from the call.
NOTE: I don't know if this helps any but I am also converting reports, originally written in MS XL to Crystal Reports. The XL version of the reports has the same timeout problem but the Crystal Reports version does not, and I think this is due to the fact that the Crystal Reports version runs considerably faster.
Additional info: yesterday, the database had just over 1 million records in it. It returned the requested number of records, but was accompanied by a timeout. today, there are over 2 million records and it times out after the same amount of time, but no records are returned, when it should have returned the same records as yesterday.
|
|
|
|
|
I've personally seen problems like this crop up. There are actually many layers of timeout not just between the client and the DB.
The general problem is that between one of the layers a timeout is being reached. You've found the most obvious one in the client connection. But there is one from the DB too. Depending if you are using a transport like HTTP that has a timeout too. And so on and so on.
By your description it seems that your query succeeded but ran out of time trying to return data to the client. The time taken to run the query and complete dumping the data to your client is passing some timeout. A time out which you might not be able to change.
What I would do is "by hand" run the query. Plug it into Query Analyzer and see how long it takes for it to complete on the server side. If the query is too long or returns way to much data you might want to consider a different approach. Instead of batching the entire query waiting minutes for it to return the result you may want to think of a small selection set or using cursors.
ps. Are you catching the exception? What does it report?
|
|
|
|
|
Thanks. I finally found the solution. It turns out that it was something simple, but I wouldn't have known it without digging through the endless dbConnect values in the debugger. In essence, I was doing the following:
dbCommand = new OdbcCommand(Query, dbConnect);
dbCommand.CommandTimeout = 300;
dbAdapter = new OdbcDataAdapter();
dbData = new DataSet();
dbAdapter.SelectCommand = dbCommand;
dbAdapter.Fill(dbData,"Play");
What I found, after going through the 'dbCommand' parameters in debug, is that the
'dbAdapter.SelectCommand = dbCommand'
RESETS THE TIMEOUT VALUE BACK TO THE DEFAULT!!! This is why it would ignore any timeout values I set. AAAARRRRGGGGHHHH!!!! So, you MUST set the timeout AFTER calling that line, preferrably just before the "Fill()" call. When I did this...
dbCommand = new OdbcCommand(Query, dbConnect);
dbAdapter = new OdbcDataAdapter();
dbData = new DataSet();
dbAdapter.SelectCommand = dbCommand;
dbCommand.CommandTimeout = 300;
dbAdapter.Fill(dbData,"Play");
it worked fine. No timeout whatsoever. GEEEEZZZZEEE!! It even worked great setting a timeout of 90, but 60 timed out. Microsoft should not reset values like this. Putting initial values should be left up to constructors and explicitly called initializers ONLY!!!
I have noticed other people on this board complaining of the same problem. They were probably doing the very same thing as I was.
|
|
|
|
|
Hi,
table containing two fields one is code and another is name. i need to fetch repeated names only.(more than one occurance of name and code is not same). any one help to me. thanks in advance.
Have A Nice Day!
Murali.M
|
|
|
|
|
SELECT name
FROM table
GROUP BY name
HAVING COUNT(name)>1
Cheers,
_____________
stefan bornuz
|
|
|
|
|
hi stefan,
thanks for ur help.
Have A Nice Day!
Murali.M
|
|
|
|
|
in C# /w asp.net using SQL... (which forum, lol)
i have a dataset that i bind to a html datagrid, is there anyway i can manipulate the data in between the grab from the database, and the posting into the table?
i just want to do some simple replaces... to replace some strings with images, etc... things i can't do with simple formatting... this is NOT for posting back to the db...
|
|
|
|
|
I feel your question is not clear enough to tell us your requirement. Can you explain it bit more detail?
If I understand your requirement correctly, I feel you can use template columns in which you can have the images.
|
|
|
|
|
the data in the database in the columns i want to "interject" is signed integers.... i want negetive integers to have a upwards arrow (html img tag) replace the negative sign, and positive integers to have a downwards facing arrow in front of the number
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
