473,320 Members | 1,821 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Adding data to groups of records

Hello again;

I've got a small invoicing database I'm trying to create.

One of the tables is set up as follows:

Client# Date Claim# Amount
1001 10/10/03 22.50
3003 10/11/03 18.00
1001 10/22/03 99.95
4011 10/21/03 39.00
1001 10/22/03 32.50

I want to bill the clients by month, and then (after I've created a claim)
add in a claim number for each client.

So the above table would change to look like this:

Client# Date Claim# Amount
1001 10/10/03 1 22.50
3003 10/11/03 2 18.00
1001 10/22/03 1 99.95
4011 10/21/03 3 39.00
1001 10/22/03 1 32.50

Any ideas on how to insert a claim number for each client, with the same
number given to the same client?

Thanks,

Scott
Nov 12 '05 #1
2 1977
I will approach this with the idea that each month would give a new
claim number for each client (as opposed to assigning a constant claim
number to each client. From this perspective you first want to group
your clients into a list of distinct clients and assign claim numbers
accordingly, then you can use an update query to update the actual data
table with the respective claim numbers. Here is how to do that:

Sub AppendClaimNumbers()
Dim strSq As String, i As Integer, arrClients() As variant
Dim RS1 As Recordset
strSql = "Select Clients From tblData Group By Clients"
Set RS1 = CurrentDB.OpenRecordset(strSql)
RS1.MoveLast
RS1.MoveFirst
i = RS1.RecordCount 'get count of distinct clients
Redim arrClients(i, 1)
'populate array with client names and assign claim# i
For i = 0 To Ubound(arrClients)
arrClients(i, 0) = RS1(0)
arrClients(i, 1) = i
RS1.MoveNext
Next
'now update data tables with claim numbers for each client
For i = 0 to Ubound(arrClients)
strSql = "Update tblData Set Claim# = " & arrClient(i, 1) _
& " Where Client = '" & arrClient(i, 0) & "'"
DoCmd.Run strSql
Next
End Sub

So you first get the list of distinct clients, assign a claim number to
each client (I started with a claim number of 0 for the sake of the
array, but you could say

arrClients(i, 1) = i + 1

This would start with a claim of 1... Note on arrays, this is a 2
dimensional variant array to hold a string and a numeric value. The
first dimension holds the client's name, the 2nd dimension holds the
claim number (I could have made the array a string array but then the
claim number would be a text value instead of a numeric value even
though it was a digit). Once you populate the array you then loop
through the array in the 2nd for loop and update your data table using
the Update query. Set Claim# = " & arrClients(i, 1) sets the claim
number (note no delimeters needed for a numeric value and this is the
2nd dimension of the array - i, 1) and the condition for where is where
Client = '" & arrClients(i, 0) & "'" --this is the first dimension of
the array i, 0 (0 is first dimension, 1 is second dimension). Note also
that since Client is text you have to delimit is with single quotes = '"
& ... & "'".

This is one way to do it. There may be a more elegant/efficient way to
do this, but off the top of my head, this was the easiest.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #2
Rich, Thanks for your help!

Sadly, I'm still having trouyble with this.

What do you mean in the line "strSql = "Select Clients From tblData Group By
Clients" "?

Would this be an SQL statement that groups data? Help!

Thanks,

Scott
"Rich P" <rp*****@aol.com> wrote in message
news:3f*********************@news.frii.net...
I will approach this with the idea that each month would give a new
claim number for each client (as opposed to assigning a constant claim
number to each client. From this perspective you first want to group
your clients into a list of distinct clients and assign claim numbers
accordingly, then you can use an update query to update the actual data
table with the respective claim numbers. Here is how to do that:

Sub AppendClaimNumbers()
Dim strSq As String, i As Integer, arrClients() As variant
Dim RS1 As Recordset
strSql = "Select Clients From tblData Group By Clients"
Set RS1 = CurrentDB.OpenRecordset(strSql)
RS1.MoveLast
RS1.MoveFirst
i = RS1.RecordCount 'get count of distinct clients
Redim arrClients(i, 1)
'populate array with client names and assign claim# i
For i = 0 To Ubound(arrClients)
arrClients(i, 0) = RS1(0)
arrClients(i, 1) = i
RS1.MoveNext
Next
'now update data tables with claim numbers for each client
For i = 0 to Ubound(arrClients)
strSql = "Update tblData Set Claim# = " & arrClient(i, 1) _
& " Where Client = '" & arrClient(i, 0) & "'"
DoCmd.Run strSql
Next
End Sub

So you first get the list of distinct clients, assign a claim number to
each client (I started with a claim number of 0 for the sake of the
array, but you could say

arrClients(i, 1) = i + 1

This would start with a claim of 1... Note on arrays, this is a 2
dimensional variant array to hold a string and a numeric value. The
first dimension holds the client's name, the 2nd dimension holds the
claim number (I could have made the array a string array but then the
claim number would be a text value instead of a numeric value even
though it was a digit). Once you populate the array you then loop
through the array in the 2nd for loop and update your data table using
the Update query. Set Claim# = " & arrClients(i, 1) sets the claim
number (note no delimeters needed for a numeric value and this is the
2nd dimension of the array - i, 1) and the condition for where is where
Client = '" & arrClients(i, 0) & "'" --this is the first dimension of
the array i, 0 (0 is first dimension, 1 is second dimension). Note also
that since Client is text you have to delimit is with single quotes = '"
& ... & "'".

This is one way to do it. There may be a more elegant/efficient way to
do this, but off the top of my head, this was the easiest.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Riegn Man | last post by:
I have a problem with access and our time clocks. We have time clocks that put out a .log file with the badge swipes for everybody. There is one .log file for each day. I am pulling that data...
1
by: Jeff Smeker | last post by:
I have, over the past few years, developed a Access system that is in use in our companies engineering, purchasing, and sales departments. It is a fairly complex system that runs great. So, I am...
3
by: brian kaufmann | last post by:
Hi, I had sent this earlier, and would appreciate any suggestions on this. I need to make calculations for unemployment rate for three different data sources (A,B,C) for many countries and age...
0
by: brijeshmathew | last post by:
Hi I use Visual Basic 6, Service Pack 6, Microsoft ActiveX Data Objects 2.8 Library(msado15.dll) and access 2000 database using JET 4 OLE. I have an application that adds records simultaneously...
3
by: Jim Heavey | last post by:
Trying to figure out the technique which should be used to add rows to a datagrid. I am thinking that I would want an "Add" button on the footer, but I am not quite sure how to do that. Is that...
2
by: Jon Turlington | last post by:
When in access the SQL Statement works as expected; in ADO it does not. I have replaced the * with the % so I am sure that's not the problem. Anyone have any idea? <% Dim objConn, objRS,...
9
by: Kadett | last post by:
Hi all, I have following problem: I'm creating a ListView (Details) control at run-time and filling it with some records (let's say 10 000). This operation seems to be quite fast, but when I call...
0
by: EricLondaits | last post by:
Hi, I have an ASP.NET page with a ListBox that is data bound to a table with a single field (it holds a list of valid IDs). The page also has a textBox into which you can add new valid IDs, one...
3
by: jonniethecodeprince | last post by:
Hi all, I have trouble getting an array of data stored in a separate javascript file i.e. a file called books.js into a table of data for a .xhtml file. There are 50 Records in this file....
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.