SQL help please

Results 1 to 2 of 2

Thread: SQL help please

  1. #1
    Jon Guest

    Default SQL help please

    Hello,<BR><BR> Here is my problem. I have 2 tables one with a group of id&#039s, another with a group of notes with the id&#039s associated with them. how can i write a sql statement for access that counts the total number of notes per id and returns them with the id name.<BR><BR>Thanks!

  2. #2
    Join Date
    Dec 1969

    Default RE: SQL help please

    There are several ways of doing this. Here is one.<BR><BR>Create a query in Access that combines the two tables or write a SQL statement that joins the two tables. Make sure that you order by ids<BR><BR>Then you loop through the recordset using a counter to count how many times that you have gone through the loop. When the id changes you know that you are done with that ID. Display whatever information that you want, reset the counter to zero and continue on. <BR><BR>Here is some sample code to get you started.<BR><BR>rst.open "Select * from qryname Order by ID"<BR>If not rst.eof then<BR>TempID = rst("ID")<BR>Counter = 0<BR><BR>Do while not rst.eof<BR> If TempID = rst("ID") Then<BR> Counter = Counter + 1<BR><BR>else<BR> write out whatever you want to<BR> counter = 0<BR> TempID=rst("ID")<BR>End If<BR>rst.movenext<BR>Loop<BR>End If<BR><BR>Hope this helps get you started.<BR><BR>Dennis<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts