Consulting

Results 1 to 18 of 18

Thread: Ranking dates in access by order number

  1. #1

    Ranking dates in access by order number

    Hello,

    I have a table that contains a date field and a order field - I'd like to rank the date field chronologically for each order (but not increase the rank if the date value is the same)

    I've attached an example data set and how i'd like access to output the values - I've tried a few various methods with little success so any pointers in the right direction would be great!

    Cheers,
    Tom
    Attached Files Attached Files

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Can I ask why you feel the need for the Ranking field?

    A simple Select Query with the unique order id ascending and the Date ascending will provide them in order.
    Attached Files Attached Files

  3. #3
    I'd like to have a field that contains a 'invoice description' for that Row - if i had a query to update a ranking field i could look at the value for that row (eg 1,2,3) and concatenate a text string with this field and the number of installments to return a string to display on the invoice.

    Invoice 1 of 6
    Invoice 3 of 5

    etc

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK, I think I can see where you are coming from.
    Do you actually have all those duplicate records with the same InvoiceID and Posting Dates?

    I can provide some VBA to keep the Invoice Description up to date along with the Rank for the invoice.
    How many records are currently in the table?

  5. #5
    Yes i do have all the duplicate records with same invoice Id and posting date.

    If you could provide some VBA to keep this field up to date that would be great - the number of records may vary but should never be massive as the table will only contain the daily orders.

    One thing to note is that some rows in my table may not have an installment posting date in which case no ranking/description is required.

    Cheers,
    Tom

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    The next question about the duplicates is "Why"?
    The questions after that are
    1. how many records do you currently have to update?
    2. Is the table that you supplied actually representitive of your real tabel. ie the same field names etc?

  7. #7
    The reason for the duplicates are due to a previous VBA script that i had (that you also helped me with!) that would split the installment lines in a order table by the number of installments for that row's bookings into multiple rows (with same booking ID/posting date)

    I currently have 24 rows to update but this will change each time it needs to run so the VBA would need to apply to the whole tables data set.

    The table supplied is representative in that the field names and data type are the same as my real table but all other columns have been excluded.

  8. #8
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK, I will see what I can do.
    But I thought that the Installment dates were each month not all the same date?

  9. #9
    Thanks

    It is each month not all the same date but for background info i also needed to split the rows on start/end date fields

    So for example for a initial 1 order line with a start date of 01/01/2017 and a end date of 01/01/2018 for £1200 and 6 installments across the year @ £200 each needed to be broken down as follows

    Split the 1 line into 12 (Start/End Date on calendar months and approx £100 per row) - but during this process the ORDERID & Installment posting date gets duplicates into each new row.

    Now those 12 lines have 6 installments apiece so needs to be split into 72 rows - which i then am ranking to work out which 12 rows are for the first installment which 12 are for the 2nd etc.

    Cheers,
    Tom
    Last edited by Tom123456; 11-28-2017 at 09:41 AM. Reason: Clarity.

  10. #10
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    No I am afraid not.
    I understand the first part because I worked on it.
    What I don't understand is where 6 installments per line come from, is that 6 per Month?
    And why wouldn't each one have it's own date?

  11. #11
    So the part you worked on would split a row with installments into multiple lines with each line having the right installment date & amount from it's related source row/columns.

    However that source row for that order id was already duplicated before the 'installment splitter' is ran due to a similar piece of VBA on 2 other fields (which is fine) but does mean in the output the dates/amounts will be also duplicated (again fine) - however this now presents my difficulty in ranking them as the dates/order id are duplicated.

    Hopefully that make's sense - it took me ages on my side to understand the requirement here in splitting the rows as above but hopefully my explanation made sense but if not at the very least there is a 'genuine' reason why my table contains duplicate orderid/instalment posting dates

  12. #12
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Tom, do you think it would make more sense to incorporate the Rank with the Original VBA that did the splitting rather than running seperate code afterwards?

    We can update this table to get the current records ranked, but I am thinking of future inputs.

  13. #13
    Agree it would make more sense to incorporate rank with the original VBA - i just didn't know how to modify this VBA to achieve that and originally thought i could achieve ranking via update queries.

    If a field was created in the 'Output' Table called 'invoice description' or similar that could be populated with the installment rank from the 'DestTable' as part of the module that would be great.

    Attached is the current table structure i have and module I'm running.

    Cheers,
    Tom
    Attached Files Attached Files

  14. #14
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK, I have that version and will take a look at the original VBA, it should be easy enough to modify it with the counter value that is used to control how many records are created for each base record.
    If not I will just create some to update the Table afterwards.

  15. #15
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Ignore this post.

  16. #16
    Hi OBP,

    Have you had a chance to look at the original vba yet for this query yet? Thanks again for all your help so far!

    Cheers,
    Tom

  17. #17
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    No sorry, I have been involved in a Thermodynamics debate on a different kind of Forum.
    I wiil try and get back to it soon.

  18. #18
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Tom, I should be able to take a look at the code now, I haven't forgotten you.
    Tom, does the Output Table need the old DestTables' Installment Fields?

    Tom, where is the VBA code that does the second lot of "Splitting" which supplies the extra records?
    Last edited by OBP; 12-11-2017 at 08:35 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
  •