|
Obviously you have simplified the problem, because there is no need for TableA at all in this scenario; all of the information is contained in TableB.
From TableB only:
SELECT belongsto, COUNT(belongsto)
FROM TableB
GROUP BY belongsto
Assuming it needs both tables due to fields we are not seeing:
SELECT a.Title, COUNT(b.belongsto)
FROM TableA a
INNER JOIN TableB b ON a.Title = b.belongsto
GROUP BY a.Title
MS Access might not like the formatting I have used, and may need some parenthesis around some of that (it would work in SQL Server just the way it is formatted).
I like this book:
Guru's Guide to Trasact SQL by Ken Henderson[^]
But it is SQL Server specific (Transact SQL).
|
|
|
|
|
(Scott,)
Thanks for the help. I didnt use the real field names when i posted becuase i wasnt at my development PC. However ive adapted the code and got it part working, as follows:
SELECT a.forumTitle, a.lastPost, a.lastUser, COUNT( b.username) AS count, <br />
FROM tbl_Subforums a<br />
INNER JOIN tbl_Messages b ON a.forumTitle = b.subforum<br />
GROUP BY a.forumTitle, a.lastPost, a.lastUser
However i need to include a count from a third table, i figured it should look something like:
SELECT a.forumTitle, a.lastPost, a.lastUser, COUNT( b.username) AS count, COUNT(c.threadTitle) AS threads<br />
FROM tbl_Subforums a<br />
INNER JOIN tbl_Messages b ON a.forumTitle = b.subforum<br />
INNER JOIN tbl_topics c ON b.subforum = c.subforum<br />
GROUP BY a.forumTitle, a.lastPost, a.lastUser
Unfortunately this doesnt work. Could you please take a look and let me know where i am going wrong. The fields above are all correctly named and the c = b relationships are also correct.
The error i get within access is regarding the Syntaxt, so it would appear my formatting is wrong.
Thanks for any help you can offer!
munklefish
|
|
|
|
|
That looks like it would work in SQL Server, but Access might require paranthesis like:
munklefish wrote: SELECT a.forumTitle, a.lastPost, a.lastUser, COUNT( b.username) AS count, COUNT(c.threadTitle) AS threads
FROM ((tbl_Subforums a
INNER JOIN tbl_Messages b ON a.forumTitle = b.subforum)
INNER JOIN tbl_topics c ON b.subforum = c.subforum)
GROUP BY a.forumTitle, a.lastPost, a.lastUser
Scott
|
|
|
|
|
Thanks Scott!
That did the trick!
Muchos gracias!
munklefish
|
|
|
|
|
Good day everyone,
Please help if you're interested in.
I've got an Access database containing fitness club member profile. I need to make a login session for my webpage. How can I validate user name and password with the ones in database?
I don't have Microsoft SQl Server, so can I do it in VS.Net 2005?
How should I start?
Thanks so much
eric
|
|
|
|
|
|
I am having difficulty setting up my listbox to point to a SQL table (technicians table). I created a dataset that contains the table, but am unable to find enough help sources to create the code to make it work. I have created a Windows form to update a database, which displays the data from a (workorders table). I would like to have one of the fields as a picklist of names to allow the user to change the existing name and re-assign it to another from the list. I know this is basic stuff, but I am new to VB.NET.
Thank you,
LWhite
|
|
|
|
|
How will you take values for an insert statement
To insert the table values some values from one table and some values from open xml this will carried in a Stored Procedure
Help Please
Thanx
-
|
|
|
|
|
I am developing asp.net1.1 application
I have hug number of records in DB( SQLServer ) & want to show in a datagrid
There are thousands of records which need to transfer to client & client can sort & search
What is the best way to handle such type of situation
I am also using Ajax , when I use Ajax to call a function it lock the page until the all data is transfer to client , that’s not good
Did I use xml file to send on client & from xml file show data to client
or if there is any good solution plz....
I will be really thankful
Irfan
|
|
|
|
|
|
Hi,
I have one dataset, that contain one table now how can i retrieve one column in that table can you send me all possible approachments
|
|
|
|
|
if u confirm only one table means
ds.dt[tablename].columnname
-
|
|
|
|
|
Do you really mean 'one column', or do you mean 'one value from a particular row'?
If you really mean 'one column', then:
DataColumn column = dataSet.Tables[string tableName].Columns[columnName]<br />
or<br />
DataColumn column = dataSet.Tables[int tableIndex].Columns[int columnIndex]
If, however, you want the value stored for a particular column in a particular row in a table, then you need to index both the row and the column to get it:
object value = dataSet.Tables[string tableName].Rows[int rowIndex][string columnName (,DataRowVersion rowVersion)]
for example (the first method returns the current value in the described row's value list):
public object GetValueInRow(<br />
DataSet ds, <br />
int tableIndex, <br />
int rowIndex, <br />
string columnName)<br />
{<br />
return GetValueInRow(ds, tableIndex, rowIndex, columnName, DataRowVersion.Current);<br />
}<br />
<br />
public object GetValueInRow(<br />
DataSet ds, <br />
int tableIndex, <br />
int rowIndex, <br />
string columnName, <br />
DataRowVersion rowVersion)<br />
{<br />
return ds.Tables[tableIndex].Rows[rowIndex][columnName, rowVersion]<br />
}
Good luck.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
-- modified at 2:16 Monday 22nd May, 2006
|
|
|
|
|
Good day everyone,
Please help if you're interested in.
I need to make a login session for my webpage. How can I validate user name and password with the ones in database?
How should I start?
Thanks so much
eric
|
|
|
|
|
What type of database are you talking about?
|
|
|
|
|
eric_tran wrote: How should I start?
See this "How To" document on MSDN: How To Use Forms Authentication With SQL Server 2000[^]
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
|
Hi,
I have written a very simple application using VB6 and ADO that connects to a MySQL database. I want to be able to distribute the program to somebody who will not install DCOM (on windows XP), however I understand that DCOM is one of the file dependencies for MDAC to function.
At the moment the program references the 'Microsoft ActiveX Data Objects 2.8' library.
Here is the code I am using to connect to the database :
<code>Set gCnn = New ADODB.Connection
gCnn.ConnectionTimeout = 10
gCnn.CommandTimeout = 400
gCnn.CursorLocation = adUseClient
gCnn.Open "DRIVER={MySQL ODBC 3.51 Driver};" & _
"user=" & txtUser & _
";password=" & txtPassword & _
";database=" & txtDatabase & _
";server=" & txtServer & _
";option=" & (1 + 2 + 8 + 32 + 2048 + 16384)</code>
To be more specific when the program is run on the machine without DCOM a message box is displayed with the message "-2147467259 Unspecified error".
I am curious to know if there is any way to make ADO work without first installing DCOM, or alternatively is there another method that I could use instead?
Thanks in advance for any help,
Mark
|
|
|
|
|
Hello community.
I have a problem and cannot solve it:
How can I measure the query execution time from my C# application?
I need to get a number or a time class to my app. from my database server, but
not measure the time when I got the informations from db but the internal time in db server. So I will display something like "Execution time: 0.0015 ms" in Sybase Interacive SQL.
I'm using Adatptive Server Anywhere v8 and v9
thanx lot
|
|
|
|
|
I have three tables
1) AssignProjectUser(ProjectID,UserID)
2) Task(ID,ProjectID)
3) AssingnTaskUser(UserID,TaskID)
I will create Trigers that after inserting a assignProjectUser
I will select all the task who have the ProjectID inserted in AssignProjectUser and insert list of AssignTaskUser that have the userID same assignProjectUser and TaskID that i have selected
Exemple:
suppose that I have
TASK:
1 1
2 1
3 2
when I insert a assignProjectUser (1,15)
the number of task for this Project is 1 and 2
so the trigers will add
(1,15)
(2,15)
I hope that you understand me
and really need help as soon
thanks
DADAX
|
|
|
|
|
CREATE TRIGGER trg_AssignProjectUser ON [dbo].[AssignProjectUser]
FOR INSERT
AS
INSERT INTO AssignTaskUser (UserID,TaskID)
SELECT inserted.userid,task.id
FROM task INNER JOIN inserted on task.projectid = inserted.projectid
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
|
OK, I think this must be possible, but my head is melting and running out my ears, any help appreciated. I have table structure like this:
Table: Statement
Columns: ID (PK)
Name
TypeCode
PeriodId
Table: Heading
Columns: ID (PK)
StatementID
Name
Table: Line
Columns: ID (PK)
HeaderId
Name
These three tables make up a hierarchal 'tree' of data for a statment (structure simplified for this example). Each statement has a period represented by an id, and there will be a number of statement types for each period. What i need to do is copy each statement for a period and all of it's children to a new period.
After creating the new statements in STATEMENT table (simple), I've been able to do this:
INSERT INTO Heading (STATEMENTID, NAME)
SELECT SNEW.ID,H.NAME
FROM HEADING H
INNER JOIN STATEMENT S
ON H.StatementID = S.ID
LEFT OUTER JOIN STATEMENT SNEW
ON S.TYPECODE = SNEW.TYPECODE
AND SNEW.PeriodID = @NewID
WHERE S.PeriodID = @PrevId
Which inserts all the heading children appropriately. I can't seem to get the join syntax correct though to go down and do the same for the lines, closest i've got is this:
SELECT HNew.ID,L.NAME,
FROM LINE L
INNER JOIN HEADING H
ON L.HEADINGID = H.ID
INNER JOIN STATEMENT S
ON H.StatementID = s.ID
LEFT OUTER JOIN STATEMENT SNEW
ON S.TYPECODE = SNEW.TYPECODE AND SNEW.PeriodID = @NewId
LEFT OUTER JOIN HEADING HNew
ON HNew.StatementID = SNEW.ID
WHERE F.PeriodId = @OldId
This returns too many records. I know my join has gone wrong somewhere, but i can't see it...
any help would be appreciated.
|
|
|
|
|
What are some of the rows in the STATEMENT table?
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
I spent some time on this today, and i don't think the kind of query i want is possible with the current table structure, but thanks for looking...
|
|
|
|
|