Query to find cost at time of order (1 Viewer)

C

Cowlers

Access Virgin
Local time
Today, 07:36
Joined
Dec 19, 2013
Messages
67
Hi All,

CJ_London very kindly helped me to build the structure of my database so that I can avoid storing calculated costs with my order records.

I have stored the costs in my database with a date they apply from so I have a series of costs which I can then refer back to using the order date to see what cost I should use at any one time.

My problem is although the above sounds simple enough I could do with a hand to actually query this, please could you provide me with a suggestion?

My table structure is as follows:

Code: 
 tblExtruderCosts
 ExtruderID PK - identifies the extruder
 FinishID FK - identifies the finish
 Cost - the cost for that finish from that extruder
 DateFrom - the date the cost applies from
  
 tblExtHead
 ID PK
 ExtruderID FK
 FinishID FK
  Orderdate
Many thanks,

Dan
 
Last edited:
C

CJ_London

Super Moderator
Staff member
Local time
Today, 15:36
Joined
Feb 19, 2013
Messages
16,607
Try this - I've used your names so you should just be able to post into a new query
Code: 
Select tblExtHead.*, Cost 
FROM tblExtruderCosts INNER JOIN tblExtHead ON 
tblExtruderCosts.ExtruderID=tblExtHead.ExtruderID AND tblExtruderCosts. FinishID =tblExtHead. FinishID AND tblExtruderCosts.DateFrom=(SELECT Max(DateFrom) FROM tblExtruderCosts AS Tmp WHERE ExtruderID=tblExtHead.ExtruderID AND tblExtruderCosts.FinishID =tblExtHead. FinishID AND DateFrom<=tblExtHead.OrderDate)
[COLOR=red]WHERE tblExtHead.ID=1[/COLOR]

The bit in red can be removed or changed depending on what orders you want to see
 
C

Cowlers

Access Virgin
Local time
Today, 07:36
Joined
Dec 19, 2013
Messages
67
Much appreciated, thanks CJ_London for your lightning quick response!!

regards,

Dan
 
C

Cowlers

Access Virgin
Local time
Today, 07:36
Joined
Dec 19, 2013
Messages
67
Hi again,

I keep getting an error on this statement, I get a message telling me that there is an invalid . ! or () in the statement. The part that gets highlighted is the full stop after the ON statement between tblExtruderCosts and ExtruderID,

I tried to alter this but to no avail,

Please could you suggest a revised statement?

Many thanks,

Dan
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 15:36
Joined
Feb 19, 2013
Messages
16,607
Think it might be this bit

tblExtruderCosts. FinishID =tblExtHead. FinishID

It's added a space after the .

There is also a space in the subquery part as well
 
C

Cowlers

Access Virgin
Local time
Today, 07:36
Joined
Dec 19, 2013
Messages
67
Thanks once again CJ_London,

Onto the next task!

Regards,

Dan
 
You must log in or register to reply here.

Similar threads

raziel3
Most Recent Cost of an Item Query returning Duplicates
Replies
7
Views
595
arnelgp
arnelgp
N
Solved Query creating false records
Replies
15
Views
559
nashaz
N
Kitticus_Kattus
Cross Tab Query not accepting Form ComboBox as filter criteria
Replies
8
Views
538
CJ_London
C
MsAccessNL
Solved How to make editable query of Contacts linked to table Notes and Table EmailAccounts?
Replies
14
Views
709
Pat Hartman
P
3
Solved Find all races where at least 2 horses from current race have competed in last 5 years
Replies
15
Views
894
3DogLegs
3

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom