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.