Posting in IMSF:

[QUOTE=Fakharuddin,February 14, 2010 08:19 pm] While trying to upload it has produced an error msg 'You cannot upload this type of file'. So I have changed the file extension from .accdb to .doc. Please rename the extension as .accdb. [/QUOTE] In these cases (and also in general, in order to save server space), upload files to 4shared using the specified button below the post and then paste the 4shared link in the post.

In these cases (and also in general, in order to save server space), upload files to 4shared using the specified button below the post and then paste the 4shared link in the post.

Hi Friends! I have solved this problem using VB. The codes are as below. Please check it and do comment. [CODE] Option Compare Database Private Sub cmdtblCountMember_Click() Dim db As Database Dim rst As DAO.Recordset Dim strSQL As String Dim rCount As Long Set db = CurrentDb() strSQL = "SELECT mid FROM tblMember" Set rst = db.OpenRecordset(strSQL) 'If the table has no recored If rst.BOF = True Or rst.EOF = True Then MsgBox ("Total Record Count = 0 : Can not Update") If rst.BOF = True Or rst.EOF = True Then Exit Sub 'Else move to the last record rst.MoveLast 'Record count rCount = rst.RecordCount 'Let us display msg for total record count MsgBox ("Total Record Count = " & rCount & "") db.Close 'Call updateCount procedure Call updateCount End Sub Private Sub updateCount() Dim db As Database Dim rsCount As DAO.Recordset Dim strCount As String Dim rsUpdate As DAO.Recordset Dim strUpdate As String Dim str As DAO.Recordset Dim id As Long Dim total As Long Dim counter As Long Set db = CurrentDb() counter = 0 Set str = db.OpenRecordset("SELECT mid from tblMember") str.MoveFirst While (str.EOF = False)    id = str.Fields!mid    strUpdate = ("SELECT * FROM tblCountMember WHERE mid= " & id & " ")    Set rsUpdate = db.OpenRecordset(strUpdate)    strCount = ("SELECT mid FROM tblMember WHERE sponsorid=  " & id & " ")    Set rsCount = db.OpenRecordset(strCount)        If rsCount.RecordCount = 0 Then total = 0 Else rsCount.MoveLast        total = rsCount.RecordCount    'Let us display msg for total count, we will off it later    'MsgBox ("Total Member Count under sponsor ID =" & id & " is '" & total & "'")    With rsUpdate        .Edit        .Fields("totalCount") = total        .Update    End With        counter = counter + 1    str.MoveNext Wend MsgBox ("Total " & counter & " Record(s) Updated") db.Close End Sub [/CODE] I have uploaded an Access database file relating to this topic. Pl. download & check it. [URL=http://cid-6469c0cfe43e047c.skydrive.live.com/self.aspx/.Public/MCA/MS%20Access%20Database/MLM.accdb]MLM[/URL]

Hi Friends!
I have solved this problem using VB. The codes are as below. Please check it and do comment.

I have uploaded an Access database file relating to this topic. Pl. download & check it.

MLM

You are right Santanu. I tried Chakrapani's code but it does not work. It produces an error - 'An updateable query is required'. So only VB code can help us.

You are right Santanu. I tried Chakrapani's code but it does not work. It produces an error - 'An updateable query is required'. So only VB code can help us.

In MS Access, I think you have to write VBScript code for looping through records.

In MS Access, I think you have to write VBScript code for looping through records.

@Fakharuddin The following query updates your requirement as shown in the output (I practically did it with ORACLE) UPDATE TBLCOUNTMEMBER A SET A.TOTALCOUNT = (SELECT COUNT(B.MID) FROM TBLMEMBER B WHERE A.MID = B.SPONSORID GROUP BY B.SPONSORID) / 10 rows updated. SQL> SELECT * FROM TBLCOUNTMEMBER; Hit return to continue....        MID  SPONSORID TOTALCOUNT ---------- ---------- ----------          0         -1          1          1          0          3          2          1          2          3          1          1          4          1          2          5          2          6          2          7          3          8          4          9          4 10 rows selected.

@Fakharuddin
The following query updates your requirement as shown in the output
(I practically did it with ORACLE)
UPDATE TBLCOUNTMEMBER A SET A.TOTALCOUNT =
(SELECT COUNT(B.MID) FROM TBLMEMBER B
WHERE A.MID = B.SPONSORID
GROUP BY B.SPONSORID)
/
10 rows updated.

SQL> SELECT * FROM TBLCOUNTMEMBER;
Hit return to continue....

       MID  SPONSORID TOTALCOUNT
---------- ---------- ----------
         0         -1          1
         1          0          3
         2          1          2
         3          1          1
         4          1          2
         5          2
         6          2
         7          3
         8          4
         9          4

10 rows selected.

The query written by santanu does the same thing provided you loop it for each mid's. This looping can be done with the help of any programming language. But as far as i know MS Access does not support such complex queries(Pl/sql) to be written directly and executed(@others, am i right? ). In Oracle we can write Cursors to handle such requirements. You'll learn more about Cursors in your fourth sem.

The query written by santanu does the same thing provided you loop it for each mid's. This looping can be done with the help of any programming language. But as far as i know MS Access does not support such complex queries(Pl/sql) to be written directly and executed(@others, am i right? ).

In Oracle we can write Cursors to handle such requirements.
You'll learn more about Cursors in your fourth sem.

The query should update the totalCount field of tblCountMember as [dohtml] <table> <tr><td>mid<td>sponsorid<td>totalCount</tr> <tr><td>0        <td>-1   <td>1</tr> <tr><td>1        <td>0    <td>3</tr> <tr><td>2        <td>1    <td>2</tr> <tr><td>3        <td>1    <td>1</tr> <tr><td>4        <td>1    <td>2</tr> <tr><td>5        <td>2    <td>0</tr> <tr><td>6        <td>2    <td>0</tr> <tr><td>7        <td>3    <td>0</tr> <tr><td>8        <td>4    <td>0</tr> <tr><td>9        <td>4    <td>0</tr> </table> [/dohtml] mid 1 and sponsorid 1 is the same person.  A member becomes a sponsor when he joins member under him. Member 1 is sponsor of member 2, 3 and 4. So memberCount is 3 for mid 1.

The query should update the totalCount field of tblCountMember as

mid	sponsorid	totalCount
0	-1	1
1	0	3
2	1	2
3	1	1
4	1	2
5	2	0
6	2	0
7	3	0
8	4	0
9	4	0

mid 1 and sponsorid 1 is the same person.  A member becomes a sponsor when he joins member under him. Member 1 is sponsor of member
2, 3 and 4. So memberCount is 3 for mid 1.

[QUOTE=santanu,February 05, 2010 10:19 pm] [SQL]update tblCountMember set totalCount= (select count(sponsorid) from tblCountMember where sponsorid='1') where mid='1' [/SQL] [/QUOTE] A small change, instead of tblCountMember, tblMember can be used to take the count, if the second table is to be built dynamically containing a subset of mid's. [SQL]update tblCountMember set totalCount= (select count(sponsorid) from tblMember where sponsorid='1') where mid='1' [/SQL]

A small change, instead of tblCountMember, tblMember can be used to take the count, if the second table is to be built dynamically containing a subset of mid's.

I have doubts that the design of the table is correct. First of all, 1-to-1 relations does not have much meaning. These tables could be merged. Are sponsorid entries the same as mid...I mean is mid being used as sponsorid too? If so, it could be like this [SQL]update tblCountMember set totalCount= (select count(sponsorid) from tblCountMember where sponsorid='1') where mid='1' [/SQL] Now, this will fill up the totalCount against only mid='1' and sponsorid='1'. This query has to be looped for all records, taking mid from 0..9

I have doubts that the design of the table is correct. First of all, 1-to-1 relations does not have much meaning. These tables could be merged.

Are sponsorid entries the same as mid...I mean is mid being used as sponsorid too?

If so, it could be like this

Now, this will fill up the totalCount against only mid='1' and sponsorid='1'. This query has to be looped for all records, taking mid from 0..9

Help need for designing UPDATE query : -------------------------------------------------------------------------------- Hi all! Pl. help me in designing an UPDATE query in MS Access 2007. I have 2 tables. Table 1: tblMember(mid, sponsorid, mname) Table 2: tblCountMember(mid, sponsorid, totalCount) Here mid in both tables are primary and joined with 1-to-1 relationship. Each member has a sponsor and a member becomes sponsor for other members. I want a query which will count members under each sponsor and update totalCount field in table tblCountMember with one go. Here is an example how it will be looked after data entry. In tbleMember - [dohtml]       <table> <tr><td>mid</td><td>sponsorid</td><td>mname</td></tr> <tr><td>0</td><td>-1</td><td>OOOO</td></tr> <tr><td>1</td><td>0</td><td>AAAA</td></tr> <tr><td>2</td><td>1</td><td>BBBB</td></tr> <tr><td>3</td><td>1</td><td>CCCC</td></tr> <tr><td>4</td><td>1</td><td>DDDD</td></tr> <tr><td>5</td><td>2</td><td>EEEE</td></tr> <tr><td>6</td><td>2</td><td>FFFF</td></tr> <tr><td>7</td><td>3</td><td>GGGG</td></tr> <tr><td>8</td><td>4</td><td>HHHH</td></tr> <tr><td>9</td><td>4</td><td>IIII</td></tr> </table> [/dohtml] Thanks in advance.

Help need for designing UPDATE query :

--------------------------------------------------------------------------------
Hi all!

Pl. help me in designing an UPDATE query in MS Access 2007. I have 2 tables.

Table 1: tblMember(mid, sponsorid, mname)
Table 2: tblCountMember(mid, sponsorid, totalCount)

Here mid in both tables are primary and joined with 1-to-1 relationship.
Each member has a sponsor and a member becomes sponsor for other members. I want a query which will count members under each sponsor and update totalCount field in table tblCountMember with one go.

Here is an example how it will be looked after data entry.
In tbleMember -

      

mid	sponsorid	mname
0	-1	OOOO
1	0	AAAA
2	1	BBBB
3	1	CCCC
4	1	DDDD
5	2	EEEE
6	2	FFFF
7	3	GGGG
8	4	HHHH
9	4	IIII

Thanks in advance.