11. februar 2002 - 20:50Der er
8 kommentarer og 1 løsning
Bruge ID'et fra recorden i samme SQL-sætning
Jeg har følgende SQL-sætning, som bruges fra ASP mod en Access 2000 database:
strSQL = "SELECT Posts.ID, Posts.Posted, Posts.Name, Posts.Subject, [her_skal_værdien_fra_Posts.ID_bruges] FROM Posts WHERE Posts.ParentDebate=" & DebateID & " AND Posts.ParentPost=0 AND Posts.Deleted=False ORDER BY Posts.Posted DESC"
Jeg skal bruge den værdi, der står i Posts.ID i en SQL-sætning, der er placeret, hvor der står: [her_skal_værdien_fra_Posts.ID_bruges]. Den ekstra sql-sætning skal lave en Count på samme tabel (Posts). Hvordan går man det?
strSQL = "SELECT Posts.ID, Posts.Posted, Posts.Name, Posts.Subject, Dcount("*", "Posts") as Cnt FROM Posts WHERE Posts.ParentDebate=" & DebateID & " AND Posts.ParentPost=0 AND Posts.Deleted=False ORDER BY Posts.Posted DESC"
Dcount("*", "Posts") will return the total records in Posts if you only want to see those selected then you need to include a where clause in the dcount (you can see how thats done in HELP)
Same as in your SQL (WHERE Posts.ParentDebate=" & DebateID & ") Dcount("ParentID","Posts", "ParentID = " & somefield)
But ther eis something I dont understand! you say ParentID=[the value in Posts.ID]) !!! Posts.ID?
Do you have a table which is related to this table which has a field = Posts.ID? If this is the case then I would suggest you look at JOIN This is used to select data from two or more related tables
I have a table "Posts" that looks like this: ID [Unique indentifier.] ParentPost [0 = top post; >0 = id of parent post.] Posted [Time of the posting.] Name [Name of the person posting.] Subject [Subject for the post.]
Each post can either be the top post or a child post (depending on the value in ParentPost). What I want to count is the number of childposts for each of the parentposts, but don't know how to do it when I don't have the ID of the parentpost in a variable (it is selected in the same SQL-sentence) ...
This query counts the children which ARE NOT deleted. SELECT Posts.ID, Posts.Posted, Posts.Name, DCount("*","Posts","ParentPost = " & [ID] & " and [Deleted] = false ") AS Svar FROM Posts WHERE (((Posts.ParentPost)=0) AND ((Posts.Deleted)=False));
This counts all children for the ID.
You need to add Youw ParentDebate = .... bit
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
