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.
Find least date greater than another date
Collapse
X
-
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
-
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
-
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
-
Originally posted by kpfunfCode: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
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
-
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
-
Originally posted by kpfunfMy 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.
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
-
Originally posted by kpfunfZenith is a specific vendor, so it would be matched on Vendor ID .........
Kind regards,
FishComment
-
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
-
Originally posted by kpfunfThe 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.
Regards,
FishComment
-
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
-
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
Comment