Results 1 to 15 of 15
  1. #1
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74

    Create Multiple Export based on number of records

    Good morning everyone.


    I have to transfer in Excel a table which may contains more or less records. I have 2 problems which I should find a way to solve when i have many records:
    1. If the number of records to be transfer is more than 4000, I need to create multiple excel file with max (more or less) 4000 records. Example: if my table has 3000 record, the export will be one, if my tables has 6000 records, export should be 2, if my table has 20000 records, export should have 5 templates, etc.
    2. In the case of multiple excel files, the field TechIdentNo (see below my table example) cannot not be present in the next excel file so the list should be cut every more or less 4000 records depending from the TechIdentNo'
    This is my table (you can see how the TechIdentNo is repeated:

    Class Type TechIdentNo Class Characteristic Char Value
    3 OM.GAS.00246.GW.MBRNE-10-CS EP_F_GENERAL EP_FL073 1
    3 OM.GAS.00246.GW.MBRNE-10-CS EP_F_GENERAL EP_FL111 25.10.2020
    3 OM.GAS.00246.GW.MBRNE-10-CS EP_F_GENERAL EP_FL107 25.10.2020
    3 OM.GAS.00246.GW.MBRNE-10-CS EP_F_GENERAL EP_FL002 10
    3 OM.GAS.00246.GW.MBRNE-10-CS EP_F_GENERAL EP_FL074 NONE
    3 OM.GAS.00246.GW.MBRNE-10-CS EP_F_GENERAL EP_FL106 N
    3 OM.GAS.00246.GW.MBRNE-10-CS EP_F_GENERAL EP_FL110 N
    3 OM.GAS.00246.GW.MBRNE-11-CS EP_F_GENERAL EP_FL073 1
    3 OM.GAS.00246.GW.MBRNE-11-CS EP_F_GENERAL EP_FL111 25.10.2020
    3 OM.GAS.00246.GW.MBRNE-11-CS EP_F_GENERAL EP_FL401 12190966
    3 OM.GAS.00246.GW.MBRNE-11-CS EP_F_GENERAL EP_FL002 10
    3 OM.GAS.00246.GW.MBRNE-11-CS EP_F_GENERAL EP_RV001 CTRL SYS
    3 OM.GAS.00246.GW.MBRNE-11-CS EP_F_GENERAL EP_FL107 25.10.2020
    3 OM.GAS.00246.GW.MBRNE-12-CS EP_F_GENERAL EP_FL073 1
    3 OM.GAS.00246.GW.MBRNE-12-CS EP_F_GENERAL EP_FL111 25.10.2020
    3 OM.GAS.00246.GW.MBRNE-12-CS EP_F_GENERAL EP_FL107 25.10.2020
    3 OM.GAS.00246.GW.MBRNE-12-CS EP_F_GENERAL EP_FL084 I
    3 OM.GAS.00246.GW.MBRNE-12-CS EP_F_GENERAL EP_FL110 N
    3 OM.GAS.00246.GW.MBRNE-12-CS EP_F_GENERAL EP_FL002 10
    3 OM.GAS.00246.GW.MBRNE-12-CS EP_F_GENERAL EP_FL074 NONE
    3 OM.GAS.00246.GW.MBRNE-12-CS EP_F_GENERAL EP_FL300 GG100MBR0440
    3 OM.GAS.00246.GW.MBRNE-15-CS EP_F_GENERAL EP_FL073 1
    3 OM.GAS.00246.GW.MBRNE-15-CS EP_F_GENERAL EP_FL111 25.10.2020
    3 OM.GAS.00246.GW.MBRNE-15-CS EP_F_GENERAL EP_FL002 10
    3 OM.GAS.00246.GW.MBRNE-15-CS EP_F_GENERAL EP_FL300 GG100MBR0440
    3 OM.GAS.00246.GW.MBRNE-15-CS EP_F_GENERAL EP_FL072 DUMMY
    3 OM.GAS.00246.GW.MBRNE-15-CS EP_F_GENERAL EP_FL074 NONE
    3 OM.GAS.00246.GW.MBRNE-15-CS EP_F_GENERAL EP_FL084 I
    3 OM.GAS.00246.GW.MBRNE-15-CS EP_F_GENERAL EP_FL106 N

    Anyone so kind to suggest me a function to do it ? I tried to find around the net, but didn't find anything who can help me.
    Thank you for usual and evaluable support.
    Cheers.
    L.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    So if the 4000th record has A as your tech field and the 4001st to 4005th has the same value, you want to ignore the 4001st to 4005th records?
    Do you have a unique PK?

    Not suprised you could not find anything, bit of a unique request TBH
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74
    Quote Originally Posted by Welshgasman View Post
    So if the 4000th record has A as your tech field and the 4001st to 4005th has the same value, you want to ignore the 4001st to 4005th records?
    Do you have a unique PK?

    Not suprised you could not find anything, bit of a unique request TBH
    Thanks for the answer.
    Not clear your question about the 4000th and next but, if well understand, if the 4000 is equal to 4001, 4002, until 4005, the file should be cut as soon as the Tech field change so include until 4005. So the 4000 records are not a fixed amount but is the limit where to start to "cut", can be more until the next Tech is the same.
    About key no, i don't have, i know i should use but is too late. If it can help, I cannot have duplication between the first 4 fields of the table (Char Value Excluded)
    Cheers.
    L.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    No, you can add an autonumber, make sure no duplicates, as adding after, that appears to be the default.
    Try on a copy of your table.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74
    Thanks, Yes sure i can do, however this will not solve the problem i believe, anyway i need to check the Tech field and be sure it is included in the same file and not in the next.
    Cheers,
    L.

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by lmarconi View Post
    Thanks, Yes sure i can do, however this will not solve the problem i believe, anyway i need to check the Tech field and be sure it is included in the same file and not in the next.
    Cheers,
    L.
    Well I believe it would?
    My thoughts are to create a recordset that groups by your tech field and has MAX(ID) and Count(ID)
    That way you know how many of each there are and where they start and finish.?

    Then you can read that recordset, and with some math, work out your start and end IDs to select all records up to or around 4000 records.
    You can check how far they got over, or under?

    Up to you.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure I understand the part about how not having the field TechIdentNo cuts out thousands of records as those records have other fields?
    Anyway, for 11,000 records; thinking that if you calculate as either int(11000/2) or 11000\2 you get 2. That means there will be 2 groups of 4000 records. The remainder is 11000 MOD 4000, or 3000 records. You probably only need this if you were to do this by using recordsets. However, I think there might be an easier way.

    Using example of 11,000 records, add a counter field to a query/sql or modify a query def where the counter field criteria is Between 1 and 4000, run the query and you have 4000 records for export. Repeat with sql criteria as Between 4001 and 8000 for the next export and > 8000 for the last export. Might make sense to do this in a loop and pass the Between values, in which case you probably need to do the calculations as noted above, or perhaps could just subtract 4k from the record count value on each pass. Regardless, some sort of calculation would be required in order to figure out what the Between values would be for any given count of records.

    IMO the underlying table must have an autonumber field that you can sort ascending in the query, otherwise I don't know if you can guarantee that each execution of the query will not return any records that were already returned.
    Last edited by Micron; 05-29-2022 at 09:39 AM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Micron,
    The O/P wants to keep groups of the tech field together, so does not want anything in the first 4000, appearing in the 2nd 4000 etc? The first batch mighe even be 3980 as the next 25 records are all the same and a new value.?
    That is why I thought we need to find the start, stop and how many of each, so we can ensure that does not happen?
    However to do that, I can only think of a number that numbers each record. Autonumber would appear to be ideal, as when I added one to a table, it was in sequence and increments of 1?
    Not sure if one could sum as well in that query, which would reduce the recordset to 3 or 4 records to process?

    At least that was my understanding?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The O/P wants to keep groups of the tech field together, so does not want anything in the first 4000, appearing in the 2nd 4000 etc
    So sort first by tech group then by the counter? But I think you're saying if the first group has many records where tech numbers are repeated and ended with tech25 the next group should not have tech25 in it at all. That would be weird but doable I guess.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74
    Good morning and thanks for all your idea/suggestion. So, based on your comments/suggestion, the following idea coming in my mind. Probably i need support to build the function if this idea looks good:
    1. Lets assume i will add the autonumber, it can help absolutely;
    2. Append the records which i need in a temp-table1; this will allow me to avoid to work with main table which may include some records which i don't need. From now, my source is temp-table1.
    3. Calculate how many possible "extraction" i should have (int(tot record/4000)) which i will need for loop (point 5);
    4. Group by Tech field;
    5. Start the loop using (3) : take the top 4000 of the list and append in a temp-table2;
    6. Loop from from record 4001 (max id of top 4000+1 of temp-table1), checking if the Tech field is the same of the MAX(ID) of temp-table2 and, if is the same, add in the temp-table2. Repeat until Tech field is different;
    7. Create first xls template based om temp-table2;
    8. Delete from temp-table1 (my table) records appended in temp-table2, empty temp-table2 and continue the loop until i complete all records.

    How it sound as general idea?
    Cheers.
    L.

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't get number 6. If you only want max 4k records in the result, why would you add records to the 4k records that are already in temp2? To delete TOP n records will require a subquery AFAIK - not a big deal I guess.

    Your plan will require append, delete and possibly select queries to manage the tables. Not sure if you know, but you can use select queries instead of tables to filter, sort, calculate, group, etc. - not that I see a need to group at this point because there's no mention of using an aggregate function. Be wary of thinking that records have inherent characteristics such as order or numbering, because they don't. You have to impose these characteristics on them. One aspect you might be relying on is the autonumber - not sure to what degree, but do note that when you delete from your table and append a new set, numbering continues from 4000. Not sure what effect that will have on your plan to loop but it's why I suggested using a counter.

    I think the aspect of splitting records somehow based on tech values still isn't clear. IMO you should post a few sets of records that illustrate the sections/breaks in the records (copy/paste from Excel is an easy way to get a table into a post). For all I know, you might be wanting to stay below 4k in a file, but if the last Tech36 record is at 3,995 and the next 6 are Tech37 that would make 4001. In that case the recordset is cut off at 3995 and the next recordset starts with Tech37. That is an entirely different kettle of fish, as they say.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74
    [QUOTE=Micron;495708]I don't get number 6. If you only want max 4k records in the result, why would you add records to the 4k records that are already in temp2? To delete TOP n records will require a subquery AFAIK - not a big deal I guess.

    Good morning and thanks for the message.

    As I explain (sorry if i was not clear) it is true that i need to "cut" at 4000 records but i need to be sure that in the next 4000 i don't have any Tech field already present in the first 4000. So the idea is: cut at 4000 and loop from 4001 ahead until the Tech Field is the same of the last of the first 4000 present in the first batch (which i can identify with the max(ID). So, my first (and also next) list will be more or equal to 4000 depending from the last Tech Field.

    About numbering: to create the temp-table1 (which include the list of records to be split), i use a query (ordered by Tech Field) where i added a calculated field (i build a function for this) which give me a consecutive number. So, if i have 9000 records, my number field will start from 1 to 9000 and so on. In this way when i cut to 4000 i can use the Tech Field associated to this number to compare with the next (as explain above). Once i finish the first batch, i will continue and the next batch it may be from 4000 (or 4001 or 4002,...) until 8000 (more or less) and continue the loop.

    Below is the function (sorry if i add as picture but is the only way I know to show in correct way the script) i build to solve the problem which works fine. Not sure is the best way, but it works.

    Click image for larger version. 

Name:	Capture.JPG 
Views:	18 
Size:	113.6 KB 
ID:	47954

    Thanks to all for supporting.
    Cheers,
    L.
    Attached Thumbnails Attached Thumbnails Capture.JPG  

  13. #13
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    You copy and paste within code tags #
    You will lose the colouring, but retain the indentation.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  14. #14
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Might be a bit late to the party and I don't have a table with thousands of records,

    But if you create a query that groups on techID and gets the count for each one you can then run this query which tells you which workbook each group needs to go to. Substitute Table1 with the name of your group by query and change field names to match

    Code:
    SELECT A.Techid, A.rCount, Sum(B.rCount) AS SumOfrCount, (Sum([b].[rcount])\4000)+1 AS BookNo
    FROM Table1 AS A INNER JOIN Table1 AS B ON A.Techid >= B.Techid
    GROUP BY A.Techid, A.rCount
    ORDER BY A.Techid;
    Note this uses a non standard join highlighted in red which cannot be displayed in the query GUI. Simply create your query in the GUI with a standard join, then go into sql design view and change = to >=.

    The query produced this result from my sample data

    Techid rCount SumOfrCount BookNo
    a 1222 1222 1
    b 1380 2602 1
    c 650 3252 1
    d 453 3705 1
    e 1188 4893 2
    f 2060 6953 2
    g 1234 8187 3
    h 1555 9742 3


    now join this query back to your table on TechID and have your export loop filter on BookNo for the data to export

    Databases don't deal with 'more or less'. If you can allow 'up to' 4025, use 4025 instead of 4000 in the query.

    This also does not optimise to get as close to 4000 as possible - for example 'swapping' b and h between books 1 and 3 would increase the records in book1 from 3705 to 3880 and reduce book 3 accordingly.

    It also doesn't try to make each book contain roughly the same amount of records

  15. #15
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74
    Thank you first of all, sorry for late answer. Yes this is another solution and I will try to incorporate in my process to see if it is more smooth (i'm quite sure it is).
    About your note:
    - I don't have problem to increase the 4000 to a reasonable amount which, based on my data, will be never more than +/- 20/30 records;
    - I don't have problem of optimization, important i'm inside a reasonable number of records which, as i said, will be not more than 4025-4030 (i can even arrive to 4100 , but it will never be);
    - I don't care about size of each book, important the rules in term of number of records as explained.

    Cheers,
    L.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 08-02-2021, 08:17 AM
  2. Replies: 7
    Last Post: 04-21-2014, 12:26 PM
  3. Replies: 16
    Last Post: 06-20-2013, 09:25 AM
  4. Export multiple records based on a field
    By dskulman in forum Import/Export Data
    Replies: 0
    Last Post: 03-03-2011, 02:44 PM
  5. export multiple reports based on table records
    By steve2000 in forum Reports
    Replies: 3
    Last Post: 10-03-2010, 03:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums