Results 1 to 3 of 3
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Date action

    Click image for larger version. 

Name:	BusPol.png 
Views:	17 
Size:	193.8 KB 
ID:	33994
    There are multiple “pricing” tables or other tables that works similar. I.e. VatRate, Salary or Wage Revision, Purchase Invoice and Business Policy table. Rates or Purchase prices may change at certain dates. In those tables we use STARTDATES and ENDDATES. We thought it the best way since if ever historical documents is called up it will use those dates again to return the correct price or rate for that day. There are more challenges, i.e. if a supplier was initially not registered for vat, but register later, our setup has a checkbox whether a supplier registered for VAT, when the box is not checked, no vat is raised. The challenge is when checking the box when they register later, historical invoices will be affected and it should not. It happens very seldom but it does, we have to bring in STARTDATE and ENDDATE again. During time we thought of multiple reasons to store invoices with pricing and VatRates not connected to “RATE” Tables. We found reasons and preferred the strategy where rates of all stored invoices and other stored documents like payslips are connected to “RATE”tables, if ever a historical document is called up the STARTDATE and ENDDATE is needed.

    Maybe an expert can give me a better opinion on our strategy.

    In the image I posted three Enddates, can be seen in a table.
    Enddate17. When an item is revised it means a new record is entered. Currently we have to enter the Enddate17 when an item is revised. This thread is about his date.
    Enddate18. Calculated field which stores Enddate19 if no date in Enddate17. This date is mainly used as EndDate everywhere.
    EndDate19. Some date in the extreme future since we still have to learn of an expression or a way to use an open ENDDATE?

    I hope to be understood here. Our forms that we fill in in the event of a change of any of these many various items needs an ENDDATE. When we complete these forms, we have to go back to the previous form to enter the ENDDATE. We need help to find VBA or any way where when the previous ENDDATE can be filled in as soon as the new form is completed. On the image I posted see item POL000003. It was revised on 1Dec2017 see POL000006. We had to edit form POL000003 manualy to enter ENDDATE. On the new form POL000006 the ENDDATE is now open although as you may see we use 31dec5000.

    My question for this thread is. We want an automatic way for the ENDDATE to be filled in on form POL000003 when POL000006 is completed. The ENDDATE currently is one day before the STARTDATE on the new form. Purchase of the same product may happen twice on the same day. We will have to use time as well in that case. If purchase of an Inventory item happened 14May2018 11H00 then 14May 15H00 our system must provide for that.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    We found reasons and preferred the strategy where rates of all stored invoices and other stored documents like payslips are connected to “RATE”tables, if ever a historical document is called up the STARTDATE and ENDDATE is needed.

    Maybe an expert can give me a better opinion on our strategy.
    think we have been here before - it is accepted and standard practice to store key data such as vat rate, product price, discount, wage rate etc with the primary data so that in the event of changes, historical data of record is not affected. Yes you can use start date/effective from date (end date not required) in records so when looking up a historical record you find the relevant data that was effective on the day, but it will slow the process down and can get horribly messy.

    with regards end date, personally I would leave as null for the latest record - although for these rates you don't really need an end date anyway - as a type of rate, they will never end - your calculation would then be something like

    with an end date: select somerate from mytable where ratetype='somevalue' and somedate between startdate and nz(enddate,date())

    without an enddate: select top 1 somerate from mytable where ratetype='somevalue' and startdate<=somedate order by startdate desc

    your depreciation data appears to have two rates with different start dates and no end dates other than the other enddate columns which I don't understand so not sure how your data is supposed to be used.

    With regards your data, I would have expected to see a table of BusPolItems and the column where you have the text would instead show a FK. At the moment (presumably) the only way you can find the history is by relying on a text value - which could be mistyped or accidentally changed.

    Turning to VBA, since this is not a way I would do things, I guess you will need to use an update query, using dmax to find the previous record - perhaps something like

    currentdb.execute "update mytable set enddate=" & me.startdate-1 & " where ratetype='" & me.ratetype & " and startdate=dmax('startdate','mytable','startdate<#" & format(me.startdate,"mm/dd/yyyy") & "#")

    you mention time issues, in which case use the appropriate format

  3. #3
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thank you. Sorry Depreciation; the first record should have a Enddate17 of 30Nov2017 as well. The data in this table is fake and for test purposes, but it does represent what what we want to do.
    What I understand now, I maybe falling a bit short, but I would like to write further, and Ruben and me will discuss this later today.

    Yes, you and me discussed this a little before. I don’t know how to store Prices, Vat Rates, Wage rates or discounts per invoice or salaryslip or any document, without the the rate being connected to some table. Either through expression or join. So, when the table change all historical stuff change if you don’t use STARTDATE and ENDDATE. We have 6 “Rate Tables”, all using StartDates and enddates. Maybe you already described how you return the correct rate without ENDDATE and maybe I miss something because my mind only understand saving invoices with data connected to the Rate tables.

    1. Salary Review; when any salary or wage is reviewed and changed.
    2. Purchase invoice; Multiple products may be bought on one invoice but this subquery keeps every line separate of course. Discount may be given on one product even if there multiple other products on the invoice.
    3. BusinessPolicy. In my first post you can see the items we cater for here.
    4. Salary and Wage Extras. If the court orders an employer to deduct 250 p.m. for 24 months from an employee, you only need to complete a form once and the deduction will be done for that 24 months. Some businesses have a cafeteria fund which again has an open ENDDATE, so it must be drawn every month. A staff loan also have an enddate. Once completed no user should have to look at that ever. Extra earnings like travel allowance, bonus wait all its possible variances.
    5. Leave. Standard rules in SA is 15 days Holiday leave after a years service, and 15 days paid sick leave even during the first year. Many businesses choose to appoint employees first three months probation, which means “no work no pay”. Ruben and me are quite proud that our system does all these automatic. Almost no work for the user. Leave taken or leave credits display on payslips. It is also common in SA that weekly wagers working on public holidays or weekends get paid at a higher rate. Our system sees again almost no work for the user.
    6. Tax(PAYE). Our Tax(PAYE) rate table for 8 years hold more than 30 000 records, but calculates that correct on wage slips.

    My insight leads me to understand that if an Inventory price changed 10 times through a year, and the policy that our program must underwrite is to mark up lets say 30% to get to the standard sales price. On sales invoices our users select the customer, the product and the quantity. No click on vat or sales prices, the system returns the sales price. But after every purchase invoice there should be an EnDDate(incl Time) registered when the next purchase is done on the same product. If we ever return to that invoice it must of course display the same information.
    I am paranoid on user focus. We want to spare the user every possible click of the mouse or every energy they may use to have to think. Main aim is to minimize human error. Our current development must include the following features or abilities.
    - Purchase invoice: Unit Price and quantities must be entered. Totals and vat is calculated. In South Africa we had changes in VAT Rates on 1 April. Users shouldn't care one second about it accept entering the changes into the Vat Table. Invoices from 1 April should always show the new VATrates, when recalling invoice before that it should show the old rate of course. As said I simply don’t know how to store it without being connected to the tables. I have ideas of a complicated process storing all data of an invoice but tell me about the easy way.
    - Sales Price: The current system for a retail business works on a choice to add the Markup in Business Policy table to the latest purchase price of a product. There is an adjustment field where on a specific product the price might be adjusted. This also becomes complicated if we want the system to handle a price adjustment for a certain period. Of course there may be many reasons why businesses want to deviate from normal strategy on one product, and then return to normal strategy. Most retail businesses have hundreds of products.

    We will study a bit later today whether you taught us the way to have these results without an EndDate. Right now, I still don’t know how.

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

Similar Threads

  1. Show Date Action First Appeared
    By ecoman007 in forum Queries
    Replies: 3
    Last Post: 08-17-2017, 09:16 AM
  2. Add an action
    By jajoju in forum Macros
    Replies: 10
    Last Post: 11-22-2016, 11:00 PM
  3. Replies: 2
    Last Post: 01-11-2015, 11:45 PM
  4. Replies: 3
    Last Post: 09-02-2014, 06:45 PM
  5. Can't run this action???
    By wwg77 in forum Programming
    Replies: 2
    Last Post: 02-02-2011, 03:22 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