Access Database Question
Results 1 to 20 of 20
  1. #1
    Misfit Toy
    Reputation: snapdragen's Avatar
    Join Date
    Jan 2004
    Posts
    23,416

    Access Database Question

    I've got a table with a gazillion records. I want to split the records into batches, 94 records in each batch. I also want to number the batches -- the first 94 records would be 001, second 94 002 etc....

    Help? Point me in the right direction to figure it out myself?
    It's all fun and games until someone ends up in a cone.

    Don't make me go all honey badger on your ass

  2. #2
    Banned forever.....or not
    Reputation: MR_GRUMPY's Avatar
    Join Date
    Aug 2002
    Posts
    24,154
    Quote Originally Posted by snapdragen
    I've got a table with a gazillion records. I want to split the records into batches, 94 records in each batch. I also want to number the batches -- the first 94 records would be 001, second 94 002 etc....

    Help? Point me in the right direction to figure it out myself?

    45's or LP's???????

    What is this table made out of?....Must be pretty sturdy.
    If your opinion differs from mine, ..........Too bad.
    .
    How would you like it if Hitler killed you
    Dogbert.

    I>U

    Buying parts to hang on your bike is always easier than getting fit.

    If you feel wimpy and weak, get out and train more, ya wee lassie!

    If Jesus had a gun, he'd be alive today!

  3. #3
    RoadBikeReview Member
    Reputation: elcameron's Avatar
    Join Date
    Aug 2002
    Posts
    253
    I don't think anyone actually knows how Access works.

    Sounds like you need to add a new column to add your 001, 002 etc. Then create new tables and cut and paste. But only if they do not have any relationships.

    Luck to you: http://office.microsoft.com/en-us/ac...948231033.aspx
    You can not plan sincerity; you have to make it up on the spot.

  4. #4
    Collin's Dad
    Reputation: phil.'s Avatar
    Join Date
    Aug 2004
    Posts
    1,858
    I don't have access in front of me right now, but will it round up? If so, create a field in the table and set it as AutoNumber type. Then insert another field that divides the autonumber field by 94 and roundups to the nearest whole integer (ex: roundup(autonumber/94,0) )

    edit to add: if autonumber doesn't work into an exisiting table, you can just copy the table structure as a new table, add autonumber to it, and the paste the old table into the new copied structure table. My work laptop w/ access had a hard drive failure yesterday or I could check this out for you right now.

  5. #5
    Collin's Dad
    Reputation: phil.'s Avatar
    Join Date
    Aug 2004
    Posts
    1,858
    Oh, if access can't round up, just use round(autonumber/94 + 0.5,0) instead.

  6. #6
    Palm trees & sunshine!
    Reputation: KenB's Avatar
    Join Date
    Jul 2004
    Posts
    24,200
    What is your end goal? I mean, I know you want to split the records up but why? Do you want to chunk them out into separate tables or just modify the existing table with a "counter" field where you insert a 001, 002, etc..incrementing every 94 records?

    I haven't used Access since v2. IIRC, it had limited SQL syntax support back then. If that has been expanded upon, I'd probably build a cursor to run through the table, counting each record and inserting the grouping values into the new field.

    Knowing why you're looking to do it may help with possible solutions.




    /oh, I forgot to add: Eeeeewwww.... Access?!?


    supervillain

  7. #7
    Misfit Toy
    Reputation: snapdragen's Avatar
    Join Date
    Jan 2004
    Posts
    23,416
    I'm creating a file to submit to our hospital patient accounting system. Batches can only contain 94 records each, so modifying the existing table. Once it's done, it gets exported out as a text file.
    It's all fun and games until someone ends up in a cone.

    Don't make me go all honey badger on your ass

  8. #8
    Jerkhard Sirdribbledick
    Reputation: DrRoebuck's Avatar
    Join Date
    May 2004
    Posts
    27,035
    Quote Originally Posted by snapdragen
    I'm creating a file to submit to our hospital patient accounting system. Batches can only contain 94 records each, so modifying the existing table. Once it's done, it gets exported out as a text file.
    I know how I'd do this in Filemaker, but I don't Access for sh!t unless I'm sitting in front of it figuring it out on the fly.

    The "solution" would have something do with creating a field representing the record number, then at export time run a looping script that would find the first 94, export, hide, and repeat.
    "He groaned when we hung the rope over the tree but was relieved to see the white pinata."
    -- Gut
    _____________________
    [ CaliFotos ]

  9. #9
    RoadBikeReview Member
    Reputation:
    Join Date
    Feb 2004
    Posts
    8,047
    What kind of accounting system can only handle 94 records at a time?

  10. #10
    Collin's Dad
    Reputation: phil.'s Avatar
    Join Date
    Aug 2004
    Posts
    1,858
    Are you needing the final txt files to only contain 94 records, or is it okay for 1 txt file to contain the gazillion records as long as they are grouped using batch numbers containing only 94 recrords each. If this is the case, my solution above should work no problem.

    If you are needing to export out a gazillion/94 files, I think it'd still be convenient to do the solution I provided above and then just use a looping script like DrRoebuck suggested. You'd just run it from 1 to gazillion/94, incrementing the batch number by one each time.

    But isn't all this what minions are for? I thought I remember you having a minion....

  11. #11
    Ethical Nihilist
    Reputation: macalu's Avatar
    Join Date
    Jan 2003
    Posts
    1,068
    You need to create a count field. Then you need to write a module (program) that goes through your table and assigns a value like 001 to the count field while counting then, after it counts 94 records, it assigns a value of 002 to the next 94, etc. until it has worked through the table. So of course while its counting the records it keeps a running total going in a counter variable and resets the variable to zero after it gets to 94 and moving on to the next count number. Make sense?
    Work?! -- Maynard G. Krebs

  12. #12
    gazing from the shadows
    Reputation: QuiQuaeQuod's Avatar
    Join Date
    Jan 2003
    Posts
    26,800
    Quote Originally Posted by filtersweep
    What kind of accounting system can only handle 94 records at a time?
    A legacy mainframe based system is my guess. The record limit is probably a workaround to keep things working with code that is 20 years old.
    .
    Stout beers under trees, please.

  13. #13
    Collin's Dad
    Reputation: phil.'s Avatar
    Join Date
    Aug 2004
    Posts
    1,858

    This should work

    Snap, I've made sure this works in a dummy database:

    Below will assign batch numbers with 94 records each to your table w/ a gazillion records. If you need to export out separte txt files with 94 records each, you'll need to do scripting, have some sort of program written to do it, or a lot of manual work.

    1. Open up the table w/ a gazillion records in design view and insert two new columns/fields. Label one "AUTO_TEMP", you want the type to be "Autonumber". The second field added will be "BATCH" for your batch number, set the length to 3 and the type to text. Save an exit the table design view. AUTO_TEMP will now be filled in counting each row of the table in sequential order.

    2. Run an update query to assign BATCH numbers on the table with the gazillion records that was modified in step 1. The SQL would look like this assuming the table is named "GAZILLION" and you used the added field names above:

    UPDATE GAZILLION SET GAZILLION.BATCH = Format(Round(([GAZILLION].[AUTO_TEMP]/94)+0.49,0),"000");

    3. If you want to verify that only each batch number is assigned to 94 records, just do a summary pivot table grouping BATCH and counting AUTO_TEMP.

    Hope that helps,
    Phil

    edited to fix SQL in step2 as it had "TEMP_AUTO", instead of "AUTO_TEMP" as I named the field in step1.
    Last edited by phil.; 08-11-2009 at 09:44 AM.

  14. #14
    Misfit Toy
    Reputation: snapdragen's Avatar
    Join Date
    Jan 2004
    Posts
    23,416
    I can have a gazillion records, grouped in batches of 94 records. We try not to go over 999 batches per file....it's only happened once. I'm going to have to play around with the different ideas here and see if I can make it work. My problem is I'm more of a visual learner -- if someone shows me I catch it. Otherwise, I have to read instructions over and over until it finally sinks in.....

    I wish minion could help, unfortunately his db knowledge is limited to one Access class.
    It's all fun and games until someone ends up in a cone.

    Don't make me go all honey badger on your ass

  15. #15
    Misfit Toy
    Reputation: snapdragen's Avatar
    Join Date
    Jan 2004
    Posts
    23,416
    I'll give it a try, thanks phil!
    It's all fun and games until someone ends up in a cone.

    Don't make me go all honey badger on your ass

  16. #16
    Collin's Dad
    Reputation: phil.'s Avatar
    Join Date
    Aug 2004
    Posts
    1,858
    I can send screenshots of my post#14 process if you need them.

  17. #17
    Misfit Toy
    Reputation: snapdragen's Avatar
    Join Date
    Jan 2004
    Posts
    23,416
    It's a Siemen's product called Invision -- it's the main system for the hospital, Patient Accounting, Patient Management along with a s-load of other things.
    It's all fun and games until someone ends up in a cone.

    Don't make me go all honey badger on your ass

  18. #18
    Collin's Dad
    Reputation: phil.'s Avatar
    Join Date
    Aug 2004
    Posts
    1,858
    Quote Originally Posted by snapdragen
    I'll give it a try, thanks phil!
    You're welcome to email me if you have any problems or want screen shots of this. You can use the email on Collin's caringbridge site.

  19. #19
    Strained coccyx etc etc
    Reputation: haiku d'etat's Avatar
    Join Date
    Apr 2001
    Posts
    21,050
    Quote Originally Posted by MR_GRUMPY
    45's or LP's???????

    What is this table made out of?....Must be pretty sturdy.
    LOL!!!!!!!!!!!!!!!!
    One morning I shot an elephant in my pajamas. How he got into my pajamas I'll never know.

  20. #20
    Misfit Toy
    Reputation: snapdragen's Avatar
    Join Date
    Jan 2004
    Posts
    23,416
    Thanks all!

    Your solution worked phil!
    It's all fun and games until someone ends up in a cone.

    Don't make me go all honey badger on your ass

Posting Permissions

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

THE SITE

ABOUT ROADBIKEREVIEW

VISIT US AT

© Copyright 2020 VerticalScope Inc. All rights reserved.