Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    kishino is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2020
    Posts
    14

    Pairing data based on quantity

    Hi i need help currently, i have 2 rows on data that i need to pair up according to the quantity. For those with quantity i would need to pair them with the 3rd value from the current value. I have an excel module that was written by someone who left the company and i need to convert it to access module as i need to retreive data from access table to generate the quantity for certain items.

    i have attached the excel file and need help with converting the module vba code to access module vba codes. i am clueless about vba codings and i am still learning and i tried out coding some in access and attached it too. Thanks any help is much appreciated.
    Attached Files Attached Files

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You will need to provide a bit more info as the files you provided are not really self explanatory. In the Access file you have a link ODBC table (looks like Oracle) that will not work, you need to copy and paste it as a local table (make sure you remove any confidential/sensitive data). Show us an example (mock up in Excel is fine) what do you want the module to achieve using the exact field names you have in the Access tables.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    kishino is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2020
    Posts
    14
    i have uploaded some mock data and an explainatory on what actually i need to achieve basically in the attachments.

    Attachment 41579
    Fig 1
    Having sample data as shown in fig 1 I need to pair those with quantity > 0. And each Frequency can only pair to + or – 3kHz depending on the user input when prompted from a msgbox like fig 2
    Attachment 41580
    Fig 2
    Based on the input it should pair it to the furthest freq possible and deduct the quantity from each frequency each time one pair is formed and write into a table . As an example would be if the frequency spec entered is 3. It will go through the data to find the first record with quantity which is 374 kHz and try to pair to 3 frequency downwards from 374kHz which is 377kHz but there is no quantity available then it will move to 2 frequency downwards from 374kHz which is 376kHz but there is also no quantity available then move on to 1 frequency downwards from 374kHz which is 375kHz but there is also no quantity available. This means there is no pairing for this frequency 374kHz and we will skip it.
    Moving on to next freq which has quantity available would be 378kHz having 2 quantity. With the same process it will pair 3 frequency downwards from 378kHz which is 381kHz which has 6 quantity available and pairing is possible. So we will take it as pairing successful and deduct 1 quantity each from both 378kHz and 381kHz which will result in 378kHz having 1 quantity left and 381kHz having 0 quantity. When reach 0 quantity we will move on to next pairing 2 freqency down which is 380kHz and has 6 quantity. 378kHz having 1 quantity left will be pair successfully with 380kHz.so that will leave 378kHz with 0 quantity and 380kHz with 5 quantity left after the successful pairing. This will go on until there is no more pairing left for all the frequencies with quantity.
    Attachment 41581
    fig 3
    An example of the pairings when frequency spec 3 is entered by the user. The results should be input into a table so that can be retreive to be used as a report etc.
    Attachment 41582
    Fig 4

    Figure showing the pairings possible for + and – 3 kHz
    Attachment 41583
    Fig 5

  4. #4
    kishino is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2020
    Posts
    14
    Thanks for the reply i have the zip file attached with sample data inside.
    Attached Files Attached Files

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry but not of the attachments work, could you please try to put them all in a zip file and upload that?
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Moving on to next freq which has quantity available would be 378kHz having 2 quantity. With the same process it will pair 3 frequency downwards from 378kHz which is 381kHz which has 6 quantity available and pairing is possible. So we will take it as pairing successful and deduct 1 quantity each from both 378kHz and 381kHz which will result in 378kHz having 1 quantity left and 381kHz having 0 quantity. When reach 0 quantity we will move on to next pairing 2 freqency down which is 380kHz and has 6 quantity. 378kHz having 1 quantity left will be pair successfully with 380kHz.so that will leave 378kHz with 0 quantity and 380kHz with 5 quantity left after the successful pairing. This will go on until there is no more pairing left for all the frequencies with quantity.
    Please explain.
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    kishino is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2020
    Posts
    14
    sorry there was a typo error on my explanation it should be 1 quantity instead for 381kHz.

    Moving on to next freq which has quantity available would be 378kHz having 2 quantity. With the same process it will pair 3 frequency downwards from 378kHz which is 381kHz which has 1 quantity available and pairing is possible. So we will take it as pairing successful and deduct 1 quantity each from both 378kHz and 381kHz which will result in 378kHz having 1 quantity left and 381kHz having 0 quantity. When reach 0 quantity we will move on to next pairing 2 frequency down which is 380kHz and has 6 quantity. 378kHz having 1 quantity left will be pair successfully with 380kHz.so that will leave 378kHz with 0 quantity and 380kHz with 5 quantity left after the successful pairing. This will go on until there is no more pairing left for all the frequencies with quantity.

  8. #8
    kishino is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2020
    Posts
    14
    have attached the attachements in zip
    Attached Files Attached Files

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    What is the Item_No in Pairing_Summary?
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Please have a look and let me know if that is what you are looking for.

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    kishino is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2020
    Posts
    14
    Item_no in pairing_summary would refer to the column MLITNO in table item_freq.

    Quote Originally Posted by Gicu View Post
    What is the Item_No in Pairing_Summary?

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have you looked at the file?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    kishino is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2020
    Posts
    14
    yes. i did and it works exactly correct. thanks alot Gicu for the help,You are a lifesaver, now i just have to make a report from the 2 tables.

    Quote Originally Posted by Gicu View Post
    Have you looked at the file?

    Cheers,

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Glad to hear, good luck with your project!
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  15. #15
    kishino is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2020
    Posts
    14
    Quote Originally Posted by Gicu View Post
    Glad to hear, good luck with your project!
    Cheers,
    Vlad
    Hi Gicu,

    just a question on the pairing based on the file u uploaded, are we able to do it so that once the pairing is done for this pair of frequency the quantity used is deducted off from the total_qty such that after each pair the total qty is subtracted and it will continue until it finished all the frequency.lets use the freq 390khz as example

    Click image for larger version. 

Name:	2020-05-04 17_48_00-Access - pairing _ Database- C__Users_yuetan_Desktop_pairing.accdb (Access 2.png 
Views:	27 
Size:	21.0 KB 
ID:	41729
    this is the original qty that was available. so after our pairing is done using our module it becomes this.

    Click image for larger version. 

Name:	2020-05-04 17_52_34-Access - pairing _ Database- C__Users_yuetan_Desktop_pairing.accdb (Access 2.png 
Views:	27 
Size:	23.5 KB 
ID:	41730

    even though there was only 7 qty but the total pairs we have is 18 which also means 18 qty of 390khz paired with some other frequency. but i need it to be only paired based on the quantity it has and not to reused the quantity again once its quantity hits 0. Is this doable or the code should be changed somewhere?i'm guessing the logic is somewhere here but not sure what to add in.

    lFreq_2 = rst("Freq")
    'now lets check the quantity for the potential pair
    iQ2 = rst("Total_QTY")

    y = Min2(iQ1, iQ2)
    Do Until y = 0
    sInsertSQL = "INSERT INTO Pairing_Details ( Freq_1, Freq_2 )SELECT " & lFreg_1 & "," & lFreq_2 & ";"
    CurrentDb.Execute sInsertSQL, dbFailOnError
    iQ1 = iQ1 - 1
    iQ2 = iQ2 - 1
    y = y - 1
    Loop
    i = i - stepValue
    Loop


    Appreciate the great help
    Last edited by kishino; 05-04-2020 at 07:37 AM.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Assigning a position based on quantity
    By chestnuthill in forum Programming
    Replies: 6
    Last Post: 02-13-2020, 12:38 PM
  2. Allocate quantity per team based on member count
    By critusodem in forum Access
    Replies: 4
    Last Post: 07-25-2016, 11:44 AM
  3. Replies: 3
    Last Post: 02-09-2016, 04:36 PM
  4. Pairing records from excell
    By Jen0dorf in forum Access
    Replies: 6
    Last Post: 07-29-2015, 03:42 AM
  5. Large Quantity Of Data
    By dr4ke in forum Queries
    Replies: 3
    Last Post: 01-18-2013, 11:19 AM

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