Automated Table Filler

  • Thread starter Thread starter shanna
  • Start date Start date
S

shanna

Need to create a function to fill a table with multiple
records based on an outline from a source table.
The source table has several entries and needs to be
traversed to get the desired output.

So if the source table has
[ID] , [start num] , [end num] , [status]
47 2 5 good
48 8 11 fair

The destination table should end up with
[ID] , [num] , [status]
47 2 good
47 3 good
47 4 good
47 5 good
48 8 fair
48 9 fair
48 10 fair
48 11 fair

This is a simplification of the real task, but if I could
figure out this, I could do the real (big-advanced) one
that we need. It is the logic that I lack to accomplish
this.

Any ideas or suggestions?
Thanks in advance
 
hi,

look at the code below...

the 2 tables are :
'tblSource' with fields 'ID', 'StartNumber', 'EndNumber', 'Status'
'tblTarget' with fields 'ID', 'Number', 'Status'

there must be a reference to the "Microsoft DAO 3.60 library"

Public Sub Demo()
Dim db As DAO.Database, rsSource As DAO.Recordset, rsTarget As DAO.Recordset
Dim lngID As Long, lngStart As Long, lngEnd As Long, lngCounter As Long
Set db = CurrentDb
Set rsSource = db.OpenRecordset("SELECT * FROM tblSource")
If rsSource.RecordCount > 0 Then
Set rsTarget = db.OpenRecordset("SELECT * FROM tblTarget")
rsSource.MoveFirst
While Not rsSource.EOF
lngID = rsSource.Fields("ID")
lngStart = rsSource.Fields("Startnumber")
lngEnd = rsSource.Fields("EndNumber")
For lngCounter = lngStart To lngEnd
rsTarget.FindFirst ("ID = " & lngID & " AND Number = " &
lngCounter)
If rsTarget.NoMatch Then
rsTarget.AddNew
rsTarget.Fields("ID") = lngID
rsTarget.Fields("Number") = lngCounter
Else
rsTarget.Edit
End If
rsTarget.Fields("Status") = rsSource.Fields("Status")
rsTarget.Update
Next lngCounter
rsSource.MoveNext
Wend
rsTarget.Close
End If
rsSource.Close
DoCmd.OpenTable "tblTarget"
End Sub
 
I realize you're trying to be helpful, but please don't post binary files to
this, or any other newsgroup that doesn't have the word "binaries" in its
name.

Either e-mail the database directly to the person, post a message to the
newsgroup offering to e-mail it, or put it on a web page (there are lots of
free web spaces out there!) and post the link to the newsgroup.

Many people pay for their internet connection by the minute (either to their
ISP, or to their telephone company, or both), and many newsreaders are set
to automatically download all articles. Your large posting can cost them
money, whether or not they want your attachment.

Additionally, there is a risk of potential viruses included with them. Most
of the users - especially the regulars - will not even pay attention to
those postings.

And learn to zip things: Access databases tend to compress quite nicely when
zipped.


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Jan Hx. said:
have a look at the attached demo.mdb

greets,
JHx.

shanna said:
Need to create a function to fill a table with multiple
records based on an outline from a source table.
The source table has several entries and needs to be
traversed to get the desired output.

So if the source table has
[ID] , [start num] , [end num] , [status]
47 2 5 good
48 8 11 fair

The destination table should end up with
[ID] , [num] , [status]
47 2 good
47 3 good
47 4 good
47 5 good
48 8 fair
48 9 fair
48 10 fair
48 11 fair

This is a simplification of the real task, but if I could
figure out this, I could do the real (big-advanced) one
that we need. It is the logic that I lack to accomplish
this.

Any ideas or suggestions?
Thanks in advance
Click to expand...
Click to expand...
 
Back
Top