Find least date greater than another date

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • kpfunf
    New Member
    • Feb 2008
    • 78

    Find least date greater than another date

    I have one table of transactions, another table of price quotes. Transactions are nearly daily; quotes are periodic, roughly once per week. In a query, I want to pull the oldest (or least date) price quote whose date is greater or equal to the transaction date (trying to find the nearest price quote to compare to actual price paid). I have criteria for the price quote date as >=Trans Date, and tried setting the field to min or last, but these don't do anything. Can someone fill in the missing piece? Thanks.
  • kpfunf
    New Member
    • Feb 2008
    • 78

    #2
    Bump .

    Comment

    • NeoPa
      Recognized Expert Moderator MVP
      • Oct 2006
      • 32636

      #3
      Can you provide table metadata for us to work with (both tables please).

      Here is an example of how to post table MetaData. Please use this format in your post :
      Code:
      Table Name=[[U]tblStudent[/U]]
      [I]Field; Type; IndexInfo[/I]
      StudentID; AutoNumber; PK
      Family; String; FK
      Name; String
      University; String; FK
      Mark; Numeric
      LastAttendance; Date/Time

      Comment

      • kpfunf
        New Member
        • Feb 2008
        • 78

        #4
        Code:
        Table Name = [[U]Zenith[/U]]
        [I]Field; Type; IndexInfo[/I]
        Invoice Number; Number
        Transaction Date; Date/Time
        Tail Number; Text
        Quantity; Text
        Charge; Text
        Code:
        Table Name = [[U]Fuel[/U]]
        [I]Field; Type; IndexInfo[/I]
        FuelID; Autonumber; PK
        FuelType; Number
        Price; Currency
        VendorID; Number
        Date; Date/Time

        Comment

        • NeoPa
          Recognized Expert Moderator MVP
          • Oct 2006
          • 32636

          #5
          Nicely done :)

          I think I'm going to need to play with this a little first though, to determine the best way of linking the tables together.

          Give me a while to look at it at home over the weekend, but feel free to bump any time 24 hours have passed with no activity.

          Comment

          • FishVal
            Recognized Expert Specialist
            • Jun 2007
            • 2656

            #6
            Originally posted by kpfunf
            Code:
            Table Name = [[U]Zenith[/U]]
            [I]Field; Type; IndexInfo[/I]
            Invoice Number; Number
            Transaction Date; Date/Time
            Tail Number; Text
            Quantity; Text
            Charge; Text
            Code:
            Table Name = [[U]Fuel[/U]]
            [I]Field; Type; IndexInfo[/I]
            FuelID; Autonumber; PK
            FuelType; Number
            Price; Currency
            VendorID; Number
            Date; Date/Time
            Hi, kpfunf.

            Relations between the tables are, to say the least, not obvious. Would you clarify how a record in [Zenith] table could be related to a correspondent record in [Fuel] table?

            Regards,
            Fish.

            Comment

            • kpfunf
              New Member
              • Feb 2008
              • 78

              #7
              My fault for not clarifying that. Zenith is a specific vendor, so it would be matched on Vendor ID (criteria). The reason I left out most detail was I think somewhere I've seen a solution to this type of problem before (or similar), and thought it easy (and seperate from the actual data, etc.), just some sort of criteria on the date selection.

              Comment

              • NeoPa
                Recognized Expert Moderator MVP
                • Oct 2006
                • 32636

                #8
                Originally posted by kpfunf
                My fault for not clarifying that. Zenith is a specific vendor, so it would be matched on Vendor ID (criteria). The reason I left out most detail was I think somewhere I've seen a solution to this type of problem before (or similar), and thought it easy (and seperate from the actual data, etc.), just some sort of criteria on the date selection.
                That is SO wrong.

                While the solution may be as simple as that, without clear information to work from, finding that solution will be difficult with incorrect information.

                It's really best to answer the questions with the actual answers if we're not to find problems in the process.

                Comment

                • FishVal
                  Recognized Expert Specialist
                  • Jun 2007
                  • 2656

                  #9
                  Originally posted by kpfunf
                  Zenith is a specific vendor, so it would be matched on Vendor ID .........
                  You definitely need to read Database Normalization and Table structures article. There is no sense to build queries, until you redesign your table structure to look more like normalized one.

                  Kind regards,
                  Fish

                  Comment

                  • kpfunf
                    New Member
                    • Feb 2008
                    • 78

                    #10
                    NeoPa,
                    You are completely correct. I will try to be specific with my future posts.

                    FishVal,
                    The Zenith table is just one of many tables that are data downloaded/ submitted by the vendor and cannot simply be changed. I understand the table structure is not perfect but that's what I have to work with (you should see some of the other tables :). Unfortunately, because each vendor has various formatting and verbage, I have to have seperate tables and queries. This surely isn't "optimal", but it does work for what we need.

                    Comment

                    • FishVal
                      Recognized Expert Specialist
                      • Jun 2007
                      • 2656

                      #11
                      Originally posted by kpfunf
                      The Zenith table is just one of many tables that are data downloaded/ submitted by the vendor and cannot simply be changed. I understand the table structure is not perfect but that's what I have to work with (you should see some of the other tables :). Unfortunately, because each vendor has various formatting and verbage, I have to have seperate tables and queries. This surely isn't "optimal", but it does work for what we need.
                      Ok. I will not argue further. ;) Its your own choice to sacrifice database design for the sake of "easy import". Take a look at Sub Query Problem. The solution I've provided there is not the most beautiful one but seems to work.

                      Regards,
                      Fish

                      Comment

                      • NeoPa
                        Recognized Expert Moderator MVP
                        • Oct 2006
                        • 32636

                        #12
                        OK.

                        Assuming the tables as laid out in your post #4 then, how are the two tables linked?

                        Comment

                        • kpfunf
                          New Member
                          • Feb 2008
                          • 78

                          #13
                          The fields would be linked by limiting the Vendor Id to a specific value. I used FishVal's example and have a decent workaround that will do for now as we rethink some of this. I'm learning as I go that there are so many exceptions, a general rule is getting hard to use. Thanks NeoPa and FishVal for all your help!

                          Comment

                          • NeoPa
                            Recognized Expert Moderator MVP
                            • Oct 2006
                            • 32636

                            #14
                            I sort of get where you're coming from, but I still see no way of linking the two tables, even if [Fuel] is limited by [VendorID] (There's no similar field in your other table - [Zenith]).

                            This may not matter for now as you have a work around, but if/when you decide you'd like to proceed we will need to know :)

                            Comment

                            • kpfunf
                              New Member
                              • Feb 2008
                              • 78

                              #15
                              Thanks again NeoPa. FYI Zenith has a unique Vendor ID, so I'd limit it that way. Again, my lack of clarity on here.

                              Comment

                              Working...