Find least date greater than another date

[kpfunf]

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]

Bump .

[NeoPa]

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 :

Table Name=[tblStudent]
 Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[kpfunf]

Table Name = [Zenith]
 Field; Type; IndexInfo
Invoice Number; Number
Transaction Date; Date/Time
Tail Number; Text
Quantity; Text
Charge; Text

Table Name = [Fuel]
 Field; Type; IndexInfo
FuelID; Autonumber; PK
FuelType; Number
Price; Currency
VendorID; Number
Date; Date/Time

[NeoPa]

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.

[FishVal]

Table Name = [Zenith]
 Field; Type; IndexInfo
Invoice Number; Number
Transaction Date; Date/Time
Tail Number; Text
Quantity; Text
Charge; Text

Table Name = [Fuel]
 Field; Type; IndexInfo
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.

[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.

[NeoPa]

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.

[FishVal]

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

[kpfunf]

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.

[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.

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

[NeoPa]

OK.

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

[kpfunf]

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!

[NeoPa]

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 :)

[kpfunf]

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

[FishVal]

Hi, kpfunf.

Though I've promised not to argue on the tables structure I would stay it once more. [Price Quote] -> [Sales] is so classic example of one-to-many relationship ... , you just have a very improper tables design. ;)
I would strongly encourage you to look for a more intelligent import routine to store incoming quotes in normalized tables. Really, tables designed to satisfy incoming data format is like swallowing candies in wrappers.

Best regards,
Fish.

P.S. If you want to reconsider you db design, then start a new thread and post hyperlink to it here.