query

VBa insert shortcut

I am planning to insert value in a vba access form text box values to the table in same access file Here i will write a insert query on the submit button click event Is there any shortcut methods to add the value into the table? Like seting the datasource for the textbox and inserting values of all textbox in the form using a click o...

Howto merge 2 Django QuerySets in one and make a SELECT DISTINCT

Hi, thats my code: ##### models.py ##### class SinglePoint(models.Model): attributes = models.TextField(blank=True) name = models.CharField(max_length=100) geom = models.PointField() #Kartenposition objects = models.GeoManager() class Connection(models.Model): name = models.CharField(max_length=100) #points = m...

Query to find the running sum of a column(qty) based on type(if 'a' add else subtract)

I have table with data as follows: Store ItemNo Type Billno Qty London 1 A 1 10 London 1 A 2 5 London 1 S 1 7 London 1 A 3 5 London 1 S 2 7 London 2 A 1 19 London 2 ...

MySQL: Merge 2 tables for a SELECT query?

First.. here are the two tables I've created (sans irrelevant columns).. CREATE TABLE users_history1 ( circuit tinyint(1) unsigned NOT NULL default '0', userh_season smallint(4) unsigned NOT NULL default '0', userh_userid int(11) unsigned NOT NULL default '0', userh_rank varchar(2) NOT NULL default 'D', userh_wins int(11) NOT ...

Convert inner join to SQL Subquery

Dear Friend, my SQL Query is as follow: i m using inner join in that query... i want to modify that into subquery select distinct Auditdata.ID, ns.ProviderMaster_ID as CDRComment from Auditdata AuditData inner join AuditMaster am on am.ID = AuditData.AuditMaster_ID inner join HomeCircleMaster hcm on hcm.Ori_CircleMaster...

Delete data from multiple table

I have the below query which will delete data from multiple tables its not working please help me : DoCmd.RunSQL ("delete tblTesting,tblCodes,tblContract,tblScheduled,tblAbsense,tblClock from tblTesting,tblCodes,tblContract,tblScheduled,tblAbsense,tblClock where tblTesting.TestId = " & lstResults.Column(1) & " And tblTesting.Empid = ...

mediawiki: getting the pages in a given category.

using the mediawiki API, I can list the pages in a given category. For example: http://en.wikipedia.org/w/api.php?action=query&amp;list=categorymembers&amp;cmtitle=Category:Physics returns: <?xml version="1.0"?> <api> <query> <categorymembers> <cm pageid="22939" ns="0" title="Physics" /> <cm pageid="24489" ns="0" ti...

Is there a way to create a parameterized query or stored procedure that accepts <= N parameters?

Suppose I have a search screen that is intended for looking up items. There are various optional search options on the screen that will cause the SQL query statement to vary. Here are some example searches: Description search Description search + item supplier id Description search + item supplier id + item hierarchy level 1 id Descri...

MS Access Error: The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

Hi all, [Update: The query works if I hardcode in the parameters - so it has to do with the way I am adding parameters to the query] For the life of me, I cannot figure out what the problem is here. Here is the query being passed to the datareader: SELECT * FROM (SELECT TOP ? StartDate, [ID] FROM (SELECT TOP ? StartDate, [ID]...

MySQL Views: Sub-Joins

I am wanting to have a view table that aggregates rows from four tables. The first primary table is client and is just a primary key unique id and a profile_id. The second table, profile, holds all of the firstname, address, zipcode kind of information. The third table is email; there can be many email objects to one client object but t...

SQL "tree-like" query - most parent group

I'm having some trouble doing a "tree-like" query (what do we call this?) in SQL. Take a look at my diagram below (table and column names are in danish - sorry about that): Using MSSQL Server 2005, the goal is to find the most parent group (Gruppe), for each customer (Kunde). Each group can have many parent groups and many child grou...

what columns to put in MySQL GROUP BY clause

Long story short, what fields should I put after the GROUP BY clause ? SELECT questions.question_id, questions.title, questions.content, questions.view_count, questions.posted_on, users.user_id, users.group_id, users.username, users.first_name, users.last_name COUNT(answers.answer_id) AS answer_count FROM (questions) JOIN answers ON q...

SQL WHERE.. IN clause multiple columns

i need to implement the following query in SQL Server select * from table1 WHERE (CM_PLAN_ID,Individual_ID) IN ( Select CM_PLAN_ID, Individual_ID From CRM_VCM_CURRENT_LEAD_STATUS Where Lead_Key = :_Lead_Key ) but the WHERE..IN clause allows only 1 column. How to compare 2 or more columns with another inner select? ...

GROUP BY for continuous rows in SQL

Given the following table: ID State Date 12 1 2009-07-16 10:00 45 2 2009-07-16 13:00 67 2 2009-07-16 14:40 77 1 2009-07-16 15:00 89 1 2009-07-16 15:30 99 1 2009-07-16 16:00 Question:How can i GROUP by the field "State", while still maintaining the borders between the state changes? SELECT...

MySQL 1 millon row query speed

Hi, I'm having trouble getting a decent query time out of a large MySQL table, currently its taking over 20 seconds. The problem lies in the GROUP BY as MySQL needs to run a filesort but I don't see how I can get around this QUERY: SELECT play_date, COUNT(DISTINCT(email)) AS count FROM log WHERE type = 'play' AND play_date BETWEEN ...

MySQL Query to calculate the Previous Month

Hello I would like to calculate my total order amount in the previous month. I got the query for getting the data for the present month from the current date. SELECT SUM(goods_total) AS Total_Amount FROM orders WHERE order_placed_date >= date_sub(current_date, INTERVAL 1 MONTH); Now how can I get Previous Months Data only, excludin...

OleDb connection to Excel; how do I select fixed width, unbounded height?

I'm using OleDb to select data from excel spreadsheets. Each spreadsheet can contain many small tables, and possibly furniture like titles and labels. So it might look like this, where we have two tables and some titles; A B C D 1 . . . . 2 . . ...

LFL sales query in SQL

This might seem a bit like a do-my-homework-for-me question (and it is), but I think that the sales results I’m trying to get are fairly generic and will be useful for anyone doing sales reporting. I have a basic Sales table (fields: Branch, DateOfSale, SalesAmount) in SQL Server 2005 and I need to build a report with the data in that t...

MySQL Query to calculate Previous Week

Hello I would like to calculate my total order amount in the previous week. I got the query for getting the data for the last 7 days from the current date. SELECT SUM(goods_total) AS Total_Amount FROM orders WHERE order_placed_date >= date_sub(current_date, INTERVAL 7 day); Now how can I get Previous week data only, excluding this ...

MS Access Crosstab Query Question

Is there a way to reference a form's combo/text box within the query like a select query? I usually use something like this in a select query's criteria: like forms!frmMain.qTitleofSomething&* (access adds the brackets for me) but this does not work in a crosstab query?? which i just found out. is there a way to accomplish the same ...