Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24
  1. #16
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Maybe I'm looking at this wrong.



    I could replace the whole query in the listbox idea, and just dynamically create the listbox data using VBA and DLookups into the rowsource? Is that possible.

    What if I had one listbox1, using a query that just grabs the ID / POL / POD / Carrier / Contact Type / Contract / Valid From / Valid To fields.

    Then put another listbox2 right next to is so it looks like the same listbox, and the columns in listbox2 have 20GP All In / 40GP All In / 40HC All In, but the rows are dynamically built in VBA by cycling through listbox1 rows and manually checking the dates via VBA and putting in the correct values to line up with the rows in listbox1....

    Would that work? It would be a lot of code but I think I can manage it, I'm fairly good at VBA just not overly good at Access

  2. #17
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Certainly the coding is possible. Is it worth it? Only you know.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #18
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    I smashed this up really quick on a test form to see if the concept works:

    Code:
    Private Sub List3_AfterUpdate()
    
    If List3.ListCount > 0 Then
        ListArrayRowSource = "20GP All In;40GP All In;40HC All In;"
        For i = 1 To List3.ListCount - 1
            ValidFromDate = SQLDate(List3.Column(6, i))
            ValidToDate = SQLDate(List3.Column(7, i))
            
            List3RowID = DLookup("[ID]", "FRT_Additionals_Table", "[Carrier] = '" & List3.Column(3, i) & "' AND " & ValidFromDate & " Between [Valid From] AND [Valid To]")
            
            Found20FRTValue = DLookup("[20GP Cost]", "FRT_Table", "[ID] = " & List3.Column(0, i))
            Found20BAFValue = DLookup("[20GP BAF]", "FRT_Additionals_Table", "[ID] = " & List3RowID)
            Found20GRIValue = DLookup("[20GP GRI]", "FRT_Additionals_Table", "[ID] = " & List3RowID)
            
            Cal20GPAllInValue = Found20FRTValue + Found20BAFValue + Found20GRIValue
            
            Found40FRTValue = DLookup("[40GP Cost]", "FRT_Table", "[ID] = " & List3.Column(0, i))
            Found40BAFValue = DLookup("[40GP BAF]", "FRT_Additionals_Table", "[ID] = " & List3RowID)
            Found40GRIValue = DLookup("[40GP GRI]", "FRT_Additionals_Table", "[ID] = " & List3RowID)
            
            Cal40GPAllInValue = Found40FRTValue + Found40BAFValue + Found40GRIValue
            
            Found40HCFRTValue = DLookup("[40HC Cost]", "FRT_Table", "[ID] = " & List3.Column(0, i))
            Found40HCBAFValue = DLookup("[40HC BAF]", "FRT_Additionals_Table", "[ID] = " & List3RowID)
            Found40HCGRIValue = DLookup("[40HC GRI]", "FRT_Additionals_Table", "[ID] = " & List3RowID)
            
            Cal40HCAllInValue = Found40HCFRTValue + Found40HCBAFValue + Found40HCGRIValue
            
            ListArrayRowSource = ListArrayRowSource & Cal20GPAllInValue & ";" & Cal40GPAllInValue & ";" & Cal40HCAllInValue & ";"
        Next i
        
        List5.RowSource = ListArrayRowSource
    Else
    End If
    
    
    SelectedValue = List3.ListIndex + 1
    List5.Selected(SelectedValue) = True
    
    
    End Sub
    Seems to work, just need to add formating in etc.

    But I need to working on the date selection part, to see if I can get what I want out of it.

    I'm surprised that Access doesnt have a more elegant want to handle this sort of thing though, surely I'm not the first in the world to want to apply values based on valid dates.

  4. #19
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    currently my query ignores the dates, thats what I need to add in somehow.
    the difficulty you are experiencing is caused by your process being back to front. you have written your query and are trying to "add in" the consideration of dates, whereas the correct process is to consider dates first, and treat them as a fundamental aspect of your database design.

    step 1. SELECT records by the date range of interest
    step 2. process them


    when you have date ranges, the solution I have found that works is to have a table that:

    1. names each date range. call it a period, term, financialmonth, call it whatever you like but each period has a name. financial years can be named 2015, 2016, 2017, school terms have names, and so forth.

    2. the table specifies a start and end date for each period. so your table needs a minimum of three fields.

    here is an example from a budgeting database I use.

    tbl_period has three fields, tbl_period.period, tbl_period.perstart, tbl_period.perend

    period..... perstart................. perend
    2013.......01 Apr 2013.......... 31 Mar 2014
    2014.......01 Apr 2014.......... 31 Mar 2015
    2015.......01 Apr 2015.......... 31 Mar 2016
    2016.......01 Apr 2016.......... 31 Mar 2017

    the first step in any data processing then becomes adding the period name as a field in the query, so that each record has the name of the period to which it applies. analysis then becomes a trivially easy AGGREGATE function with GROUP BY on the period name.

    to check whether the date on each record falls within the date range, I find BETWEEN is most useful here, and a typical query will look like this:

    Code:
    SELECT tbl_period.period, tbl_costs.effdate, other fields as required...
    FROM tbl_period, tbl_costs 
    WHERE (((tbl_costs.effdate) Between [tbl_period].[perstart] And [tbl_period].[perend]));
    surprisingly, there is no need to join the two tables for this to work, which means the dates do not have to be an exact match, they just need to be within the range defined by your table.



    good luck with your project,


    Cottonshirt

  5. #20
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Thanks for your help.

    The issue I see above is that there are not set periods per see, for example, the FRT rate entered could have any start and any end date, it could be 1 month, it could be a whole quarter, it could be a few weeks.

    So one record might be valid 1/5/20 to 30/5/20, but another could be 1/5/20 to 14/5/20, or 1/5/20 to 30/06/20, they could be anything.

    In regards the additionals table, they are usually whole months, but again could literally be anything as we have no idea what the suppliers will dictate beforehand.

    Does the table for periods still work?

    So the FRT table date ranges will likely straddle a few of the FRT_Additionals date ranges, how would that work in regards to a period table?

  6. #21
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    New database attachment
    Attached Files Attached Files

  7. #22
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    if I understand you correctly, you have set up your tables with multiple primary keys specifically to solve the problem of duplicate records. well I'm sorry but that's a bit like a bank robber saying, "I know it's wrong to rob banks, but I needed the money."

    please don't imagine that you are the only person who has had to deal with duplicate records; every database that exists or ever will exist deals with the problem of duplicate records and there are a number of ways to deal with them. having multiple primary keys is not a solution to this problem, it has just made it look like the problem has gone away while giving you a much bigger problem to solve, which is why you are tearing your hair out over a simple matter of dates.

    I like to think of a database as a way to answer questions. a database doesn't just hold data for the sake of it, it holds data to answer questions. the types of questions you can answer depends on the data you hold, you might want to know how many times you shipped stuff from San Francisco last month, or you might want to know how the average cost of shipping stuff via Paris has changed over the last nine months. to get a database to the point where you can answer questions, you need to go through a simple 3-step process: step 1, enter the data. step 2, confirm data integrity. step 3, ask questions.

    if I understand your situation correctly, you have users importing data from Excel spreadsheets, and your problem is preventing them from importing records more than once. this is a universal problem that all databases have. finance databases have to prevent users inputting invoices more than once. stock control databases have to prevent deliveries being entered more than once. banking databases have to prevent transactions being entered more than once. everyone solves this problem in a different way depending on their situation.

    in your case the solution that is probably the simplest and most effective is to start by accepting that you cannot prevent users from importing data that already exists. there is no simple procedure that I am aware of for preventing the import into ACCESS of records that already exist, when importing from an Excel spreadsheet.

    the solution I suggest for you is to have an import table. the users import their data not into the database itself, but into the import table. once the data is in the import table you can carry out step 2 I mentioned earlier, check for data integrity. you carry out various checks on the data they have imported, before you APPEND those records to the actual database itself. you can check to make sure that carrier names are spelled correctly (or at least you could, if you had a carrier table). you can check to make sure that city names are spelled correctly (or at least you could, if you had a city table), and that part numbers agree with your stock taking data. you can check to make sure that the send date is on or before the receipt date. you can check that values that are meant to be numbers are actually numbers and not just strings of numeric characters that look like numbers. you can check that carriers are within the dates and terms of their contract. and you can check for duplicate records.

    if I understand you correctly, a duplicate record can be identified by the fields you have set as the primary keys on the table. by setting these as the primary keys, you have essentially told ACCESS that no two records can have the same values in these seven fields: [pol name], [pod name], [carrier], [contract type], [contract], [valid from], [valid to].

    when you have carried out your data integrity checks, you set up a SELECT query with import table on the left and database on the right. join the two tables on those seven fields. make the join a type 2 join, "include all records from import table and only those records from database where the joined fields are equal."

    click fields in the import table to appear in the field row at the bottom of the screen.

    click a field in the database that is not one of the seven. in the criteria row for this field put "Is not Null"

    click RUN!

    the records that come up are the ones that are in the import table and are already in the database. these are your duplicates.

    now change the criteria to "IS Null"

    the records that come up are those that are in the import table and are NOT in the database.

    run this a few times, play around with it, and make sure you that it is giving you correct answers.

    make sure you have the criteria set as "Is Null"

    click the APPEND button to turn this into an APPEND Query.

    a window appears asking you to select the table you want to APPEND records to. select database.

    make sure the field names that appear in the APPEND TO row at the bottom of the screen are correct. if not you can edit them.

    click RUN!

    empty import table and start again.


    good luck with your project,


    Cottonshirt

  8. #23
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Quote Originally Posted by Cottonshirt View Post
    the solution I suggest for you is to have an import table. the users import their data not into the database itself, but into the import table. once the data is in the import table you can carry out step 2 I mentioned earlier, check for data integrity. you carry out various checks on the data they have imported, before you APPEND those records to the actual database itself. you can check to make sure that carrier names are spelled correctly (or at least you could, if you had a carrier table). you can check to make sure that city names are spelled correctly (or at least you could, if you had a city table), and that part numbers agree with your stock taking data. you can check to make sure that the send date is on or before the receipt date. you can check that values that are meant to be numbers are actually numbers and not just strings of numeric characters that look like numbers. you can check that carriers are within the dates and terms of their contract. and you can check for duplicate records.
    This was one of the suggestions I found when originally researching it, it was the next steps I couldn't figure out haha.

    I assume I can code the above process into Access so when a user "imports data" it will do all the above steps automatically? I dont want to have to manually do it each time?

    Quote Originally Posted by Cottonshirt View Post
    if I understand you correctly, a duplicate record can be identified by the fields you have set as the primary keys on the table. by setting these as the primary keys, you have essentially told ACCESS that no two records can have the same values in these seven fields: [pol name], [pod name], [carrier], [contract type], [contract], [valid from], [valid to].

    when you have carried out your data integrity checks, you set up a SELECT query with import table on the left and database on the right. join the two tables on those seven fields. make the join a type 2 join, "include all records from import table and only those records from database where the joined fields are equal."

    click fields in the import table to appear in the field row at the bottom of the screen.

    click a field in the database that is not one of the seven. in the criteria row for this field put "Is not Null"

    click RUN!

    the records that come up are the ones that are in the import table and are already in the database. these are your duplicates.

    now change the criteria to "IS Null"

    the records that come up are those that are in the import table and are NOT in the database.

    run this a few times, play around with it, and make sure you that it is giving you correct answers.

    make sure you have the criteria set as "Is Null"

    click the APPEND button to turn this into an APPEND Query.

    a window appears asking you to select the table you want to APPEND records to. select database.

    make sure the field names that appear in the APPEND TO row at the bottom of the screen are correct. if not you can edit them.

    click RUN!

    empty import table and start again.
    Thanks for the detailed steps above, I will give it a try.

    EDIT: Did the above and it seems to have worked flawlessley, I have attached the updated database so you can see what I've done.



    In the meantime, I have amended my database removing all the primary keys etc. As attached.

    This seems to have thrown out my form a bit but I can fix that later, I really need to tackle the valid date issue as that is holding the whole project up at the moment.
    Attached Files Attached Files
    Last edited by stildawn; 06-15-2020 at 05:29 PM.

  9. #24
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Any ideas on this? Working with the valid date ranges?

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

Similar Threads

  1. Replies: 1
    Last Post: 08-21-2017, 03:07 PM
  2. Replies: 10
    Last Post: 12-15-2016, 05:52 PM
  3. Replies: 4
    Last Post: 04-16-2015, 05:01 AM
  4. Query Date Range Based on Calculated Fields
    By wilsoa in forum Queries
    Replies: 6
    Last Post: 03-08-2012, 02:41 PM
  5. Date range not working
    By victoria61485 in forum Queries
    Replies: 4
    Last Post: 09-08-2011, 08:56 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