none
Dlookup a value in a specific table according to a transaction date RRS feed

  • Question

  • Hi,

    I have a table named "Mouvement" on which I have a field named "PériodeFiscale" and another field named "DateTransaction". I wish the "PériodeFiscale" to lookup for the value from a table named "Périodes fiscales" based on the field DateTransaction.

    For example, if my DateTransaction is on 2017-04-27, then the PériodeFiscale should be 09-2017 according to the table

    "Périodes fiscales"

    If it is not possible that a table is looking for a value in another table, then, I can use a query... see at the end of this question.

    Thanks for your help

    Claude

    and for a query to lookup in a table:


    Claude Larocque

    Thursday, April 27, 2017 10:07 AM

Answers

  • Hi Claude Larocque,

    did you try to write query like below?

    UPDATE movement SET movement.period_fiscal=(select fiscal.period_fiscal from fiscal WHERE movement.[DateTransection] >=fiscal.[start_date] And movement.[DateTransection]<=fiscal.[end_date]);
    

    you can try to test and modify it according to your needs.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 28, 2017 8:43 AM
    Moderator
  • You could create a small form based on tblSettings, with a label containing instructions and a text box to enter the number of months.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, April 28, 2017 9:10 AM

All replies

  • You can calculate the Période fiscale without needing a lookup table: it is Format(DateAdd("m",5,[DateTransaction]),"mm-yyyy")

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, April 27, 2017 11:27 AM
  • Thanks Hans,

    However I don't think I was clear enough, the data I want to retrieve is not in the same table, that's why I need to lookup in another table.

    Table1: Mouvement

    Table2: Périodes fiscales

    The data I need in the mouvement.PériodeFiscale must be the [Périodes fiscales].[PériodeFiscale] data and that data is based from the field mouvement.DateTransaction between [Périodes fiscales].[DébutPériode] and [Périodes fiscales].[FinPériode]

    Look at the images, I think with these above explanations, you will be able to understand.

    Claude


    Claude Larocque


    Thursday, April 27, 2017 11:43 AM
  • Have you tried what I posted? The PériodeFiscale can be calculated without looking at the Périodes fiscales table, since it simply 5 months ahead of the calendar month.

    PériodeFiscale: Format(DateAdd("m",5,[DateTransaction]),"mm-yyyy")

    Similarly, the AnnéeFiscale can be calculated as

    AnnéeFiscale: Year(DateAdd("m",5,[DateTransaction]))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, April 27, 2017 11:58 AM
  • Yes Hans, but some customers its 4 months ahead, other 3, other 7...

    :)

    Claude


    Claude Larocque

    Thursday, April 27, 2017 12:14 PM
  • You could store the number of months in a settings table. That's much less work than creating and populating the Périodes Fiscales table.

    You can then use

    PériodeFiscale: Format(DateAdd("m",DLookup("FiscalMonths","tblSettings"),[DateTransaction]),"mm-yyyy")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, April 27, 2017 12:19 PM
  • You're right, it does provides me with the right period, now, it should be easy to calculate the fiscalMonths in the table settings, no?

    thanks


    Claude Larocque

    Thursday, April 27, 2017 12:38 PM
  • Sorry, I don't understand your question.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, April 27, 2017 12:43 PM
  • I have created the tblSettings as you proposed...

    Can you find automatically the 5 in the field fiscal months based on the data in the périodes fiscales?


    Claude Larocque

    Thursday, April 27, 2017 12:53 PM
  • To be more clear, if a customer has his fiscal year beginning in June instead of August, then the April 2017 fiscal period would be 11-2017 instead of 09-2017 so the number in the tblsetting would have to be 7, not 5, how can I calculated that field to be 7.

    Here is the fiscal year starting on June 1st instead of August 1st for another customer

    as you can see now the period for April is 11-2017 not 09-2017 anymore, bu changing the 5 for 7 in the tblSettings, everything is calculating perfectly, BUT, I have to change manually the 7, can I avoid that?

    The period is ok when putting 7 in the tblsettings as you can see

    Claude


    Claude Larocque

    Thursday, April 27, 2017 1:21 PM
  • Scrap the entire Périodes fiscales table! You don't need it.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Thursday, April 27, 2017 1:39 PM
  • Hi Claude Larocque,

    did you try to write query like below?

    UPDATE movement SET movement.period_fiscal=(select fiscal.period_fiscal from fiscal WHERE movement.[DateTransection] >=fiscal.[start_date] And movement.[DateTransection]<=fiscal.[end_date]);
    

    you can try to test and modify it according to your needs.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 28, 2017 8:43 AM
    Moderator
  • Hi Hans,

    May be you're right, however, tell me how to change the tblSettings number automatically without it. then I could consider to scrap the table?

    If you look at this discussion, we put a lot of work designing a form that works. If I have to put manually the number in the tblsetting, then, its no use because when customers will buy it on the Internet, then, they will need to know which number to put in according to their fiscal year.

    Claude

    Thanks


    Claude Larocque

    Friday, April 28, 2017 8:56 AM
  • You could create a small form based on tblSettings, with a label containing instructions and a text box to enter the number of months.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, April 28, 2017 9:10 AM
  • ok, Here is what I put in the query "Mouvement Query"

    PériodeFiscale: (Select [PériodeFiscale] FROM [Périodes fiscales] WHERE [DateTransaction] BETWEEN [Périodes fiscales].[DébutPériode] AND [Périodes fiscales].[FinPériode])

    AnnéeFiscale: (Select [AnnéeFiscale] FROM [Périodes fiscales] WHERE [DateTransaction] BETWEEN [Périodes fiscales].[DébutPériode] AND [Périodes fiscales].[FinPériode])

    NoPériode: (Select [NoPériode] FROM [Périodes fiscales] WHERE [DateTransaction] BETWEEN [Périodes fiscales].[DébutPériode] AND [Périodes fiscales].[FinPériode])

    That way, I can distribute my software online and my new customer just have to enter their fiscal year, then, they wrote for how many years the calendar should be created (default = 10) end everything works perfectly

    Thank you Hans, however, I did keep my table Périodes fiscales, I needed it to automate completely the software.

    Thank  you to all.


    Claude Larocque

    Friday, April 28, 2017 5:08 PM