Date function issues

[maxxev]

To give a background I am not a programmer, so I wont understand lots of complicated code all that well, though I will try.

I have a table with ingredients in it, and a table with prices in it by date, these tables are both linked via a crossover table

Relevent fields are:
Ingredient database: tblIng_Spec_Detail
Ing_specID - Primary field

Prices Database: tblIngredientPrices
IngredientPricesID - Primary field of the actual prices database
Then I could like to combine the text on the other records in this table, so I have called this result (in the query)
"Price" (there are three records, ammount, currency and mesurement, so basically I am combining the recordss to say £1.00 P/Kg, etc.))

The Crossover table: tblPricePerIngredient
PricePerIngredientID - Primary key
Both the above tables primary keys
"Date price goes live" this is the date I want to query.

The table will contain future dates as well as past dates.
I would like the query to be able to return the price for the latest date before "today".

So basically I want to be able to say have the query return the three records:
Ing_SpecID, "Date price goes live", "Price"

Displaying
Ing_SpecID, Latest date previous to today, "Price".

Can anyone help me to do this please.

Thank you.

 

[fneily]

Following normalization, (see: Fundamentals of Relational Database Design

http://r937.com/relational.html

if you're not sure what that is), your DatePriceGoesLive (try not to use spaces in table or field names) shouldn't be where it is. Also, you might want to change tblPricePerIngrediant to tblMixture. Your prices are in tblIngrediantPrices.
I'd make one more table:
tblGoesLive
GoesLiveID
IngrediantID
DatePriceGoesLive

This will be like a history table of price changes for each ingrediant. Now you can have a query built from that table with fields IngrediantID and DatePriceGoesLive. Click on the Total button to add another row Total. Under ingrediantID it will say Group By. Under DatePriceGoesLive, change it to Max and add the criteria <now(). This will give you the latest DatePriceGoesLive for each IngrediantID.
Then you can connect query to the tables tblIngrediantPrices and tblMixture to get the concatenated "Price" and Ing_SpecID.

 

[maxxev]

Hi, I have got this working now, EXCEPT that the <now() is not working. I have used date() instead BTW

Max works, >Date() works, but <Date() does not work...

Any thoughts?

The Dates I have setup are
23/09/08
26/09/08
28/09/08

Only the 28th is currently being picked up (with either Max or >Date()

Cheers

 

[maxxev]

Ah, never mind I got it !

Thank you for the help.

Cheers