Results 1 to 15 of 15
  1. #1
    axg275 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    17

    Query question Dates and Allocations

    Hello guys,

    I have a question that perhaps someone here knows how to solve.

    Question: Is possible for me to multiply two tables together to get allocated production A, B, and C using the allocation factors from 2000 to 2005 using allocation numbers 50/30/20, from 2005 to 2007 50/50/0, and from 2007 onward 20/40/40?

    Let me explain my situation.

    I have a two table.



    The fist table has five fields: Name, Date, Allocation_A, Allocation_B, Allocation_C
    The second table has three fields: Name, Date, production

    In the first table one "Name" can one allocation for a few years and then switches over to a different allocation for another few years and so on.

    For example
    Name Date Allocation_A Allocation_B Allocation_C
    Honda 1/1/2000 50 30 20
    Honda 1/1/2005 50 50 0
    Honda 1/1/2007 20 40 40
    At the same time in table 2 i have total production for "Honda" running on monthly basis from 1990 through 2022

    Question: Is possible for me to multiply these two tables together to get allocated production A, B, and C using the allocation factors from 2000 to 2005 using allocation numbers 50/30/20, from 2005 to 2007 50/50/0, and from 2007 onward 20/40/40?

    Thank you in advance for looking at my question. Any help is greatly appreciated.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    suggest show the result you want from the example data you provided since your description is not making sense to me at the moment.

    Initial thoughts are that the first table is constructed incorrectly - it is an excel construct rather than a database one and perhaps should be two tables rather than one

  3. #3
    axg275 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    17
    @CJ_London

    You are correct i brought in the tables from excel into access. If you know of a way for me to format them for Access please let me know.

    The answer that i want to see is as follows:

    I want to create three tables: Allocated_A, Allocated_B, and Allocated_C (using make table function in query)

    Each table would have three fields: Name, Date, Allocated_Production (A, or B, or C)

    The formula that I would use is as follows:

    equation_1: Allocated_A_Production = Production * Allocation_A

    ***Production (comes from second table) , and Allocation_A comes from the first table. I would link the two tables in the query using "Name" as a key. Table 1 and table 2 have Name and Date as a field.

    My problem comes in with the first table Allocation_A "factors" changes in time so the multiplication in the equation 1 needs to change. In the example i showed in my first post you can see how allocation factors change in 2000 its one number, 2005 as a second number and 2007 is a third number.

    Please let me know if this is still not clear and I will try to elaborate more.

    Thank you very much

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Note that Name and Date are reserved words and should not be used as field names - give them a name that would be unique across the whole app such as AllocationName and AllocationDate

    a correct layout would be

    Name....Date..........Allocation....AllocationValu e
    Honda...1/1/2000........A....................50
    Honda...1/1/2000........B....................30
    Honda...1/1/2000........C....................20


    or perhaps

    tblAllocationHdr
    AllocationHdrPK - autonumber
    AllocationName
    AllocationDate


    tblAllocationRow
    AllocationRowPK - autonumber
    AllocationHdrFK - link back to hdr table
    Allocation A, B C etc
    AllocationValue

    without knowing what the data really represents and what the purpose is, difficult to advise in more detail

  5. #5
    axg275 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    17
    @CJ_London,

    Here are the two tables. Take a look it should make more sense to you now. what I am trying to do is to multiply the two tables out and to create "allocated Per_B", "allocated Per_C", and "allocated Per_D", and "allocated Per_E" production fields. But I need some sort of logic which looks at the date to determine which percent allocation to use.

    W_Name Allocation Date Per_B_Oil_AF, % Per_C_Oil_AF, % Per_D_Oil_AF, % Per_E_Oil_AF, %
    D07 2/1/2007 74.943852854302 0.806907574753965 20.5923747744182 3.65686479652585
    D07 9/7/2008 64 1 30 5
    D07 2/1/2009 57 6 37
    D07 7/12/2009 60 8 28 4
    D07 8/29/2009 60 8 28 4
    D07 12/5/2009 61 6 28 5
    D07 2/1/2010 61 6 28 5
    D07 6/1/2010 63 4 29 4
    D07 12/9/2010 59 5 32 4
    D07 9/8/2012 92.2 7.8



    W_Name YourDate Production
    D07 1/31/2007 0
    D07 2/28/2007 34.6057943301243
    D07 3/31/2007 33.3709676201887
    D07 4/30/2007 28.4486809703653
    D07 5/31/2007 30.320942904638
    D07 6/30/2007 29.124397462699
    D07 7/31/2007 28.5263766385151
    D07 8/31/2007 28.0065257883364
    D07 9/30/2007 13.3727231384229
    D07 10/31/2007 25.5171139561871
    D07 11/30/2007 25.9315845689156
    D07 12/31/2007 24.7056674680483
    D07 1/31/2008 25.7965114187496
    D07 2/29/2008 24.9700660052231
    D07 3/31/2008 23.621287819878
    D07 4/30/2008 24.6666218092726
    D07 5/31/2008 22.1438416505474
    D07 6/30/2008 23.6781801072263
    D07 7/31/2008 22.7020055656012
    D07 8/31/2008 9.14786256794548
    D07 9/30/2008 16.2791357781323
    D07 10/31/2008 19.5785970164222
    D07 11/30/2008 23.075580571164
    D07 12/31/2008 22.9766592932871
    D07 1/31/2009 21.4613890411622
    D07 2/28/2009 21.5484948800903
    D07 3/31/2009 20.2403419448183
    D07 4/30/2009 19.3187729046326
    D07 5/31/2009 20.6179537916603
    D07 6/30/2009 20.1895909477716
    D07 7/31/2009 11.6645640639496
    D07 8/31/2009 22.4077783518777
    D07 9/30/2009 22.2160611251846
    D07 10/31/2009 18.6378807094345
    D07 11/30/2009 14.094906657821
    D07 12/31/2009 15.2367019326677
    D07 1/31/2010 16.7001502290664
    D07 2/28/2010 16.5441707422985
    D07 3/31/2010 19.877529414568
    D07 4/30/2010 19.529580113505
    D07 5/31/2010 21.7806720213499
    D07 6/30/2010 22.5776120410866
    D07 7/31/2010 23.2337989407948
    D07 8/31/2010 21.7415291072171
    D07 9/30/2010 21.4796961785853
    D07 10/31/2010 23.3790022647076
    D07 11/30/2010 21.5957072829615
    D07 12/31/2010 14.5366309175552
    D07 1/31/2011 16.995453301983
    D07 2/28/2011 16.7025750539855
    D07 3/31/2011 16.2723252767188
    D07 4/30/2011 17.9620258814413
    D07 5/31/2011 17.3368176932108
    D07 6/30/2011 17.5366218149808
    D07 7/31/2011 18.4618929123467
    D07 8/31/2011 19.1383383500927
    D07 9/30/2011 18.7531314734238
    D07 10/31/2011 11.3033737448195
    D07 11/30/2011 7.82754119685372
    D07 12/31/2011 9.33800975509838
    D07 1/31/2012 12.9251046876747
    D07 2/29/2012 13.5243058075261
    D07 3/31/2012 13.6004331049036
    D07 4/30/2012 13.3227996976533
    D07 5/31/2012 13.0577802072574
    D07 6/30/2012 12.9105612215727
    D07 7/31/2012 12.6470030344184
    D07 8/31/2012 8.1803325074714
    D07 9/30/2012 0.210478700625202
    D07 10/31/2012 0.449180379030649
    D07 11/30/2012 0
    D07 12/31/2012 0
    D07 1/31/2013 4.46853666340902
    D07 2/28/2013 11.585545663045
    D07 3/31/2013 9.07653758844165
    D07 4/30/2013 7.53211202843884
    D07 5/31/2013 9.76490937313271
    D07 6/30/2013 10.0245128336614
    D07 7/31/2013 9.51015110851587
    D07 8/31/2013 5.06178971432391
    D07 9/30/2013 5.82880993826267
    D07 10/31/2013 4.83366613547514
    D07 11/30/2013 4.75520716210492
    D07 12/31/2013 4.92518036636981
    D07 1/31/2014 4.11388300188179
    D07 2/28/2014 4.66195108529651
    D07 3/31/2014 4.10023488319476
    D07 4/30/2014 2.46760626666975
    D07 5/31/2014 2.52360217291692
    D07 6/30/2014 2.37281441118314
    D07 7/31/2014 2.05570481084933
    D07 8/31/2014 1.57555226343646
    D07 9/30/2014 1.47665248674224
    D07 10/31/2014 1.47883816297949
    D07 11/30/2014 0.285346090273093
    D07 12/31/2014 1.25277060973762
    D07 1/31/2015 1.31790018292372
    D07 2/28/2015 1.34248787569784
    D07 3/31/2015 1.11351217118115
    D07 4/30/2015 1.01854653978588
    D07 5/31/2015 0.831411104087326
    D07 6/30/2015 0.972470006577018
    D07 7/31/2015 0.93160878786285
    D07 8/31/2015 0.779343978088284
    D07 9/30/2015 0.778709159522948
    D07 10/31/2015 0.657609167161307
    D07 11/30/2015 0.712557714469272
    D07 12/31/2015 0.643108768615708
    D07 1/31/2016 0.670680641566684
    D07 2/29/2016 0.741679482911723
    D07 3/31/2016 0.740883993945798
    D07 4/30/2016 0.578260069465611
    D07 5/31/2016 0.711928687837587
    D07 6/30/2016 0.893716156229868
    D07 7/31/2016 0.780107645882453
    D07 8/31/2016 0.681360677400228
    D07 9/30/2016 0.682510236196313
    D07 10/31/2016 0.617250567581418
    D07 11/30/2016 0.341448473955738
    D07 12/31/2016 0.555737584438061
    D07 1/31/2017 0.421489747824214
    D07 2/28/2017 0.381316163385692
    D07 3/31/2017 0.526167067454638
    D07 4/30/2017 0.538246055330665
    D07 5/31/2017 0.61884376179762
    D07 6/30/2017 0.644268519323098
    D07 7/31/2017 0.435820071512477
    D07 8/31/2017 0.458688079068532
    D07 9/30/2017 0.423153212814466
    D07 10/31/2017 0.439024756187809
    D07 11/30/2017 0.396714149751867
    D07 12/31/2017 0.511050917677179
    D07 1/31/2018 0.486508975572058
    D07 2/28/2018 0.500002703034348
    D07 3/31/2018 0.417647758859666
    D07 4/30/2018 0.300572573817699
    D07 5/31/2018 0.302914081211523
    D07 6/30/2018 0.53035492309234
    D07 7/31/2018 0.519307903487464
    D07 8/31/2018 0.537256526681572
    D07 9/30/2018 0.524650141334898
    D07 10/31/2018 0.472069385932316
    D07 11/30/2018 0.18099383362655
    D07 12/31/2018 0.100865041462823
    D07 1/31/2019 0.107478955832423
    D07 2/28/2019 4.47505974480529E-02
    D07 3/31/2019 0.27543625837076
    D07 4/30/2019 0.120689358578557
    D07 5/31/2019 0.181849406637477
    D07 6/30/2019 3.23891216398491E-02
    D07 7/31/2019 0
    D07 8/31/2019 0
    D07 9/30/2019 0
    D07 10/31/2019 0
    D07 11/30/2019 0
    D07 12/31/2019 0
    D07 1/31/2020 0
    D07 2/29/2020 5.02643815492367E-02
    D07 3/31/2020 0.457610985902639
    D07 4/30/2020 0
    D07 5/31/2020 0.022818
    D07 6/30/2020 0.098619
    D07 7/31/2020 0
    D07 8/31/2020 0
    D07 9/30/2020 0
    D07 10/31/2020 0
    D07 11/30/2020 0
    D07 12/31/2020 0
    D07 1/31/2021 0
    D07 2/28/2021 0
    D07 3/31/2021 0
    D07 4/30/2021 0
    D07 5/31/2021 0
    D07 6/30/2021 0
    D07 7/31/2021 0
    D07 8/31/2021 0
    D07 9/30/2021 0
    D07 10/31/2021 0
    D07 11/30/2021 0
    D07 12/31/2021 0
    D07 1/31/2022 0
    D07 2/28/2022 0
    D07 3/31/2022 0
    D07 4/30/2022 0
    D07 5/31/2022 0
    D07 6/30/2022 0
    D07 7/31/2022 0
    D07 8/31/2022 0
    D07 9/30/2022 0
    D07 10/31/2022 0
    D07 11/30/2022 0
    D07 12/31/2022 0

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    still haven't seen what outcome you actually require but to determine the relevant record from the first table in relation the date in the second table you need a query something like below. No names so I'll call them T1 and T2

    Code:
    SELECT *
    FROM T1 INNER JOIN T2 ON T1.W_Name = T2.W_Name
    WHERE T1.[Allocation Date]=(SELECT MAX([allocation date]) FROM T1 A WHERE A.[allocation Date]<T2.[yourdate] AND A.W_Name=T2.W_Name)
    This uses a subquery and looks like you have a lot of data so make sure the name and date fields are indexed otherwise it could be quite slow.

    Not clear whether a T2 record with the same date as the T1 record should have that record or not, I've assumed not. If it should be used, change the < to <= in the criteria

    Edit: query edited to include additional criteria

  7. #7
    axg275 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    17
    @ CJ_London

    Thank you I think that worked. I dont really understand what you did to make it work but thank you. I dont think I would have figured this out on my own.

    Question: when you say the fields should be indexed. Can you tell me what I need to do to get it indexed. Sorry this may be a dumb question but I am new at this.

    Thanks again!

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    go into table design, select a field to be indexed and check - select from the dropdown the type of index required
    Click image for larger version. 

Name:	image_2023-01-30_140855416.png 
Views:	16 
Size:	5.1 KB 
ID:	49590

    You can see what fields are indexed whilst in table design view by clicking on the indexes option in the ribbon
    Click image for larger version. 

Name:	image_2023-01-30_141218643.png 
Views:	16 
Size:	25.2 KB 
ID:	49591

    where you can also set index properties such as sort direction and whether or not to ignore nulls (fields with lots of nulls can still be indexed and makes for a shorter index 'table' so will be faster). Note that there is little benefit in indexing fields with a very limited range of values such as yes/no. See this article about the importance of indexing
    https://www.access-programmers.co.uk.../#post-1516326

  9. #9
    axg275 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    17
    CJ your da man! I appreciate your help on this.

  10. #10
    axg275 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    17
    @CJ_London, i just wanted to say one more time that your my hero today. Thank you for helping me out.

  11. #11
    axg275 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    17
    Hi CJ_London its me again )))

    What you gave me worked great thanks again. I am bothered that I cant figure out what you did. Do you mind explaining it to me? I get to the point of this statment and i can not understand the logic.

    Would you be so kind to quickly tell me what your doing here or maybe you can tell me what I should read to be able to do this myself in the future.

    Thanks in advance.

    WHERE T1.[Allocation Date]=(SELECT MAX([allocation date]) FROM T1 A WHERE A.[allocation Date]<T2.[yourdate] AND A.W_Name=T2.W_Name)

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    CJ may be busy, so I'll offer a suggestion.
    There are several topics and examples at https://www.w3schools.com/sql/sql_intro.asp

    There are examples and the opportunity to try things on the w3schools site.
    Last edited by orange; 01-30-2023 at 03:33 PM. Reason: clarity

  13. #13
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    (SELECT MAX([allocation date]) FROM T1 A WHERE A.[allocation Date]<T2.[yourdate] AND A.W_Name=T2.W_Name)

    is a subquery that returns the maximum allocated date that is less than yourdate. T1 is aliased as A so that it doesn't get confused with the T1 in the main query. And I included the name criteria since I assumed there would be more than one of them even tho' not shown in your example data. If there isn't, that part can be removed. As a basic principle it is worth remembering as it is a common way of finding the previous record

    It is a bit like using the DMax domain function, but more efficient.

    Edit - Orange - thanks for stepping in!





  14. #14
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    Maybe if you attach the two Excel files it's easier to understand how the data in the first table is obtained because you certainly don't understand what you've shown.

  15. #15
    axg275 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    17
    Quote Originally Posted by CJ_London View Post
    (SELECT MAX([allocation date]) FROM T1 A WHERE A.[allocation Date]<T2.[yourdate] AND A.W_Name=T2.W_Name)

    is a subquery that returns the maximum allocated date that is less than yourdate. T1 is aliased as A so that it doesn't get confused with the T1 in the main query. And I included the name criteria since I assumed there would be more than one of them even tho' not shown in your example data. If there isn't, that part can be removed. As a basic principle it is worth remembering as it is a common way of finding the previous record

    It is a bit like using the DMax domain function, but more efficient.

    Edit - Orange - thanks for stepping in!






    CJ thank you for sharing this neat trick with me and explaining the syntax it makes more sense now! I applaud you!

    Thank you and Mr. Orange for sharing the link with me. Its something I will try to go through and understand.

    I appreciate you guys.

    Alex

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

Similar Threads

  1. Question Regarding Dates in a Query
    By NtvTxn72 in forum Queries
    Replies: 4
    Last Post: 05-05-2017, 02:23 AM
  2. Replies: 5
    Last Post: 10-10-2016, 06:20 PM
  3. Question on dates
    By scschuck in forum Access
    Replies: 2
    Last Post: 06-24-2016, 11:45 AM
  4. question|dates calculating
    By mikichi in forum Access
    Replies: 4
    Last Post: 12-05-2013, 09:31 AM
  5. Between dates query question
    By ostroms1 in forum Queries
    Replies: 3
    Last Post: 07-23-2010, 05:04 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