Results 1 to 13 of 13
  1. #1
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autofill blank records

    First of all, I have serious doubts about your database design if this isn't just an exercise to see if you can do it. The whole point of relational design is to only have records that hold data, and I don't see a sign of data in your spreadsheet.

    Are you basing these records on one table (or a spreadsheet) and creating them in another or are you trying to add records to the same table, or what? Is field2 a field name? Is RowID a field name? What exactly are you trying to accomplish (not the same as what you're trying to do!)? I looked at the spreadsheet and still couldn't see what you wanted to do. There's no earthly reason to recreate that spreadsheet in an Access table.[img]/w3timages/icons/crazy.gif[/img]
    Charlotte

  2. #2
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autofill blank records

    <P ID="edit"><FONT SIZE=-1>Edited by jazman2001 on 01/02/19 22:31.</FONT></P>The original post and attachment were so bad that I had to replace it with a second one. The attachment on that post contains actual data.
    Please see the third post in the thread.

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add records-Update

    <P ID="edit"><FONT SIZE=-1>Edited by jazman2001 on 01/02/20 05:13.</FONT></P>I need to write a module that creates 38 records for each group listed in the CompanyID field. The qry_distinct does the job isolating the groups.

    Each group should have 38 records. There are 652 groups.

    I'm trying to get the last number in that group from the value of Field3 [RowID] and increase the number value by 1. It's a Double as a datatype.

    Example: in attached spreadsheet CompanyID AFFI has four records, so the count should start at 5.00 and proceed to 38.00

    'Thanks for any help given...
    Attached Files Attached Files

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add records-Update

    I've almost got the picture and then it gets blurry. [img]/w3timages/icons/alien.gif[/img] Where is your existing data coming from? Are you importing it one time only or will it be updated via import from time to time to add the possible 38 fields ? I'm thinking perhaps you should consider building the database in a conventional relationship fashion and bend the incoming data to fit the Access model. But then you mention asp and that makes me go kind of blank. I don't know a lot about active server pages if that's what we're talking about. [img]/w3timages/icons/dizzy.gif[/img]

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add records-Update

    The existing data is already in it's own table. The 38 records [per companyID] will be added now as placeholder records. It will eventually be exported to a tab-delimited text file and re-inserted. There will be no more than 38 records for each CompanyID [Field3]. Ever.

    Apparently, they want these placeholder records so that the customer can just fill in the blank ones and just update the table.

    The ASP code using this table requires this many records to successfully run the script. Anything less, trips the code. So each group must have 38 records to even meet the criteria of the code.

  6. #6
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill blank records

    It is a database table that I exported to Excel for easier viewing.
    The Field names are ID-SurveyNo-CompanyID-RowID-Company-Location
    The row under that just lists the datatypes for each (hence, the italics)

    The customer wants a table that has 38 placeholder records for each Customer that may be filled in/updated later. Apparently, they don't want to redo the ASP code that is tripping up the 'reponse'form.

    I wish I knew more code. It seems so simple...figuratively.

    Use a Distinct query to get each indexed listing in Field3 write it to a varA...go to the table which holds the records (or another query which uses this value to retrieve all records fitting this criteria) Use varA and find the highest value in Field4 which will be varB...add new records filling in Field2 with a simple string value and loop until varB < 39. Start over with the next index listing from the Distinct query and do it again until each listing in the distinct query has been used.

    Unfortunately, explaining why I want to do this has been the hardest part for this forum to digest, while placing my own pseudocode has done a great diservice to the entire thread. I think that was my biggest folly.

    Thanks for your patience

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Add records-Update

    What is wrong with something along the lines of

    For CompanyNo = 1 to 652
    For ItemNo = 1 to 38
    If record not found
    Create the record
    endif
    Next
    Next

    There is another way using SQL with a table containing records 1 to 38 and what I think is called an OUTER JOIN but I do not have the SQL skills to tell you how to do it.
    David Grugeon
    Brisbane Australia

  8. #8
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add records-Update

    Actually, that was precisely what I needed.
    Well, envisioning it your way helped me to realize that I use the DISTINCT to make it's own table and index the entries.
    Create a relationship- then a new make-table query.
    This ties the company number down...then just use the simple script to add new records.[img]/w3timages/icons/blush.gif[/img]

    Thanks for the push!! G-man

  9. #9
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Get Row num

    I created a basic script that allows me to add records and fill values, but I'm still fuzzy on the concept of getting the row number then increasing the value by 1 till I hit 38. Going beyond 38 is fine, but I don't need duplicate RowIDs for existing records.

    I went from Acccess 97(work) to 2k(home) and I was getting tripped up in the object variables. I read that thread pertaining to the different libraries. Thanks for that one.

    Public Sub AddRec()
    Dim db As Database
    Dim rs As Recordset
    Dim CoNum As Integer
    Dim CompanyID As String
    Dim RowID As Long

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tbl_All")

    For CoNum = 1 To 4
    For RowID = 1 To 38
    <font color=red>Here's where I need to grab the existing value</font color=red><font color=448800> for each</font color=448800><font color=blue> CoNum</font color=blue><font color=448800> from </font color=448800><font color=blue>RowID</font color=blue><font color=red> and step 1 to 38-or at the very least add 38 from that value</font color=red>
    'If rs![ID] = Null Then
    rs.AddNew
    rs![SurveyNo] = "5002001"
    rs![CoNum] = CoNum
    rs![RowID] = RowID
    rs.Update
    'End If
    Next
    Next
    End Sub

    Does this place have a 'bunny slopes' for us beginning programmers?

    Thanks again

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Get Row num

    Sorry, but I'm not sure what row number and column number are in your example. It would be more usual to nest the CoNum loop inside the RowID loop, not the other way around. I don't understand what your RowID and CoNum are supposed to be related to, however. There are no actual row numbers in Access, although rst.CurrentRecord will give you a virtual row number, but you have to use rst.MoveNext to move through the recordset. If your want to refer to the column number, you can't because there is none in DAO. You can refer to the fields collection, if that's what you're trying to do, and you can use rst(CoNum) to refer to a specific field/column, assuming you're only dealing with columns 2 to 5. Since collections start numbering at zero, you would need to use 0 to 3 to get the first 4 fields.

    <pre>'this steps through 38 iterations
    'of RowID and does something for each
    For RowID = 1 To 38
    'for each RowID, this steps
    'through 4 iterations of CoNum
    For CoNum = 1 To 4
    'Here's where I need to grab the existing value
    'for each CoNum from RowID and
    'step 1 to 38-or at the very least add
    '38 from that value
    'If rs![ID] = Null Then
    rs.AddNew
    rs![SurveyNo] = "5002001"
    rs![CoNum] = CoNum
    rs![RowID] = RowID
    rs.Update
    'End If
    Next CoNum
    Next RowID</pre>

    Charlotte

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Get Row num

    Charlotte, See the spreadsheet attached to the third post. The code above is based on my suggestion which may not have been fully thought out. It was meant to give a general clue rather than be a complete answer.

    What needs to be done is to step through all the records of the database, comparing the CompanyID with the CompanyID of the previous record. At the same time counting the number of rows for that company. If the CompanyID is different and count<38 insert a record until the count is 38.

    (Insert a record will necessitate putting at least the CompanyID in and setting the rowID = Count)
    David Grugeon
    Brisbane Australia

  12. #12
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get Row num

    <P ID="edit"><FONT SIZE=-1>Edited by jazman2001 on 01/02/23 23:21.</FONT></P>Just had a friend show me how remarkably easy it was with VB. Worked like a charm. Naturally, my approach was all wrong as far as loop diagrams go. It even took me an hour to explain what I actually wanted to him, so I apologize to you all for bogging you down in this tedious process and my confusing submissions.[img]/w3timages/icons/clown.gif[/img]
    Thanks for all of your help.

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Get Row num

    Not a problem, but it shouldn't be any easier in VB than in Access if you're using a Jet table. The code is virtually identical between the two and Jet is Jet regardless.
    Charlotte

Posting Permissions

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