By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,646 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Creating new rows for multiple records in the same row

P: 8
In Access 2000, I have a table with the following columns:

ID(pk); DateTime; CallerID; DNIS; AcctNumber1; ... AcctNumber15;

The final 15 columns have different account numbers listed for the same ID/DateTime/CallerID/DNIS. However, not every ID has 15 account numbers, some IDs only have one, some have several (with the most being 15), and some have no account numbers at all.

Example:

ID: 1
DateTime: 200709230818411
CallerID: 5555559999
DNIS: 12345
AcctNumber1: 234901245789
AcctNumber2: 5437828340
AcctNumber3: 23904823405928

ID: 2
DateTime: 200709231131511
DNIS: 54321
AcctNumber1: 239482345

I need the table to only have one account number listed for each ID, so for every ID with multiple account numbers I need a new row created with a new ID that contains the same DateTime/CallerID/DNIS but only contains one of the account numbers.

I would do this manually but there are about 2000 IDs, so that would take forever. I was wondering if anyone could give me a direction to go to do this automatically, I planned to use a query but I didn't really know where to start. If anyone could give me an example of what I should do I'd really appreciate it, thanks in advance!

(This may have been discussed before but I'm posting because I don't know what to search under to find what I want to do).
Oct 17 '07 #1
Share this Question
Share on Google+
6 Replies


Rabbit
Expert Mod 10K+
P: 12,327
In Access 2000, I have a table with the following columns:

ID(pk); DateTime; CallerID; DNIS; AcctNumber1; ... AcctNumber15;

The final 15 columns have different account numbers listed for the same ID/DateTime/CallerID/DNIS. However, not every ID has 15 account numbers, some IDs only have one, some have several (with the most being 15), and some have no account numbers at all.

Example:

ID: 1
DateTime: 200709230818411
CallerID: 5555559999
DNIS: 12345
AcctNumber1: 234901245789
AcctNumber2: 5437828340
AcctNumber3: 23904823405928

ID: 2
DateTime: 200709231131511
DNIS: 54321
AcctNumber1: 239482345

I need the table to only have one account number listed for each ID, so for every ID with multiple account numbers I need a new row created with a new ID that contains the same DateTime/CallerID/DNIS but only contains one of the account numbers.

I would do this manually but there are about 2000 IDs, so that would take forever. I was wondering if anyone could give me a direction to go to do this automatically, I planned to use a query but I didn't really know where to start. If anyone could give me an example of what I should do I'd really appreciate it, thanks in advance!

(This may have been discussed before but I'm posting because I don't know what to search under to find what I want to do).
You should have 2 tables.

tbl_ID
ID; PK
DateTime
CallerID
DNIS

tbl_Accounts
ID; FK
AcctNumber

The first table can be created by making a copy of the table and stripping off the AcctNumber columns.

The second table can be created by multiple append queries that leave just the ID and AcctNumber and taking out records where it's Null.
Oct 17 '07 #2

P: 8
You should have 2 tables.

tbl_ID
ID; PK
DateTime
CallerID
DNIS

tbl_Accounts
ID; FK
AcctNumber

The first table can be created by making a copy of the table and stripping off the AcctNumber columns.

The second table can be created by multiple append queries that leave just the ID and AcctNumber and taking out records where it's Null.
Thank you for the reply,

I created both the tables you listed.

For the append query I am trying to run, I have the 15 account columns from the original table appending to AcctNumber in tbl_Accounts. This brings up the error Duplicate Output Destination.

I have a decent understanding of Access, but I'm not too experienced using queries. Could you possibly give me an example of what I should be doing, using a smaller version of what I am working with. If I had a table with only three IDs, the first having no AcctNumbers, the second having one AcctNumber, and the third having 4 AcctNumbers, how would I append these accounts to separate rows with the same Date/CallerID/DNIS that they had when they were part of the same row?

Also, if the ID has no AcctNumbers it would not be eliminated, null values are allowed.

Thanks again!
Oct 17 '07 #3

Rabbit
Expert Mod 10K+
P: 12,327
Thank you for the reply,

I created both the tables you listed.

For the append query I am trying to run, I have the 15 account columns from the original table appending to AcctNumber in tbl_Accounts. This brings up the error Duplicate Output Destination.

I have a decent understanding of Access, but I'm not too experienced using queries. Could you possibly give me an example of what I should be doing, using a smaller version of what I am working with. If I had a table with only three IDs, the first having no AcctNumbers, the second having one AcctNumber, and the third having 4 AcctNumbers, how would I append these accounts to separate rows with the same Date/CallerID/DNIS that they had when they were part of the same row?

Also, if the ID has no AcctNumbers it would not be eliminated, null values are allowed.

Thanks again!
You don't have all the columns appending to the same column. What you have to do is do one column at a time. So you'll have to run the query 15 times, changing the row each time.

The ID wouldn't be eliminated. It would still have a record in tbl_ID but it would have no records in tbl_Accounts. As it should be. The two tables are linked by the ID field. So even though tbl_Accounts doesn't have all the information that's in tbl_ID, you join the two tables by the ID field and you'll have all the information. Having two tables is just more efficient and flexible than repeating your information multiple times.
Oct 18 '07 #4

Jim Doherty
Expert 100+
P: 897
Thank you for the reply,

I created both the tables you listed.

For the append query I am trying to run, I have the 15 account columns from the original table appending to AcctNumber in tbl_Accounts. This brings up the error Duplicate Output Destination.

I have a decent understanding of Access, but I'm not too experienced using queries. Could you possibly give me an example of what I should be doing, using a smaller version of what I am working with. If I had a table with only three IDs, the first having no AcctNumbers, the second having one AcctNumber, and the third having 4 AcctNumbers, how would I append these accounts to separate rows with the same Date/CallerID/DNIS that they had when they were part of the same row?

Also, if the ID has no AcctNumbers it would not be eliminated, null values are allowed.

Thanks again!

Here use this..... paste this into into a module and save it. In the module bring up the immediate window (menubar VIEW...Immediate Window) and merely type into the immediate window the word..........

GrabAccounts

and then hit enter


Pay particular attention before doing so in terms of the field names for the second table you created tbl_Accounts. I say this because I have added another field called 'WasField' defined as text. Read the function carefully and it should work for you when you run it fom the immediate window.

This 'WasField' will populate with data telling you 'which' field provided the respective relevant data value from the original tbl_ID table when you run the function. (you may or may not find this column useful you can always delete it)

If I can suggest for the purposes of this function (it won't work unless you do) that you create a field called RowID as the primary key in the tbl_Accounts table and set it to autonumber and then let the ID field accept duplicates as the foreign key. You can always manipulate the data once it is in there to return a single account number using a select query statement

The accounts table will receive and contain all of the account numbers data transposed for you in a single column...you can then simply run a SELECT query against that table if you wish, to return whichever data value you want, whether that be the first, top, last account number 'or whatever else' defines itself as the ONLY one account number you require for a particular ID?

Regards

Jim


Expand|Select|Wrap|Line Numbers
  1.  Option Explicit 
  2. Function GrabAccounts()
  3. On Error Resume Next
  4. Dim strSQL As String, curdb As DAO.Database, rst As DAO.Recordset, rst1 As DAO.Recordset
  5. Dim i As Long, intmess As Long, msg As String
  6. 'define the SQL to use for the first recordset rst
  7.  
  8. strSQL = "SELECT tbl_ID.ID, tbl_ID.AcctNumber1, tbl_ID.AcctNumber2, tbl_ID.AcctNumber3," & _
  9. "tbl_ID.AcctNumber4, tbl_ID.AcctNumber5, tbl_ID.AcctNumber6, tbl_ID.AcctNumber7," & _
  10. "tbl_ID.AcctNumber8, tbl_ID.AcctNumber9, tbl_ID.AcctNumber10, tbl_ID.AcctNumber11," & _
  11. "tbl_ID.AcctNumber12, tbl_ID.AcctNumber13, tbl_ID.AcctNumber14, tbl_ID.AcctNumber15" & _
  12. " FROM tbl_ID ORDER BY tbl_ID.ID;"
  13.  
  14. Set curdb = CurrentDb()
  15. 'Open the first recordset
  16. Set rst = curdb.OpenRecordset(strSQL)
  17. 'open a second recordset rst1 for appending new records taken from rst
  18. Set rst1 = curdb.OpenRecordset("Select * from tbl_Accounts WHERE false")
  19. rst.MoveFirst
  20. 'Process whilst ever there is a record
  21. Do While Not rst.EOF
  22.     'loop through each field in the row grabbing values
  23.     For i = 0 To rst.Fields.Count - 1
  24.         If rst.Fields(i).Value <> "" Then
  25.             rst1.AddNew
  26.             rst1!ID = rst.Fields("ID").Value
  27.             rst1!AcctNumber = rst.Fields(i).Value
  28.             rst1!WasField = rst.Fields(i).Name
  29.             rst1.Update
  30.         intmess = intmess + 1
  31.         Else
  32.         'skip over blanks fields only interested in those with data
  33.         End If
  34.     Next i
  35. 'move to next row in the set
  36. rst.MoveNext
  37. Loop
  38. 'Return a messagebox close recordsets and release object references
  39. If intmess > 0 Then
  40.     msg = "The function has taken " & intmess & " account numbers" & vbCrLf
  41.     msg = msg & "from tbl_ID table and appended them into tbl_Accounts"
  42.     MsgBox msg, vbInformation, "Function Message"
  43. Else
  44.     MsgBox "The GrabAccounts function failed!", vbExclamation, "System Message"
  45. End If
  46. rst.Close
  47. rst1.Close
  48. Set rst = Nothing
  49. Set rst1 = Nothing
  50. End Function
  51.  
Oct 18 '07 #5

P: 8
You don't have all the columns appending to the same column. What you have to do is do one column at a time. So you'll have to run the query 15 times, changing the row each time.

The ID wouldn't be eliminated. It would still have a record in tbl_ID but it would have no records in tbl_Accounts. As it should be. The two tables are linked by the ID field. So even though tbl_Accounts doesn't have all the information that's in tbl_ID, you join the two tables by the ID field and you'll have all the information. Having two tables is just more efficient and flexible than repeating your information multiple times.
Multiple append queries worked, thanks a lot!
Oct 19 '07 #6

Rabbit
Expert Mod 10K+
P: 12,327
Multiple append queries worked, thanks a lot!
Not a problem, good luck.
Oct 19 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.