SQL error

hejan11

Registered User.
Local time
Today, 14:16
Joined
Dec 2, 2016
Messages
7
Can someone please help me with this error. When i try to select the query that [Delivery?] is from it wont work. It gives the SQL error. What code should i write in SQL to fix this error.
2016-12-03_14-42-15.png 2016-12-03_14-42-39.png 2016-12-03_14-43-32.png
 
the error is self explanatory. You have two tables, both with a field called Delivery? (note using non alphanumeric characters (like ? and spaces) in field names is a bad idea, they will cause you problems at some point). If you want users to see them, use the field caption property in table design.

So you need to specify which one to use in your Delivery column - the one in Orders, or the one in Daily Query.

In your second error, the table line should be blank because it is a calculation (per your first image). But also, you cannot refer to aliased calculations (OrderTotal) in a group by query. Instead you need repeat the calculation (sum in this case). The table line should be blank because it is a calculation.

go back to your first image and put [Order].[Delivery?] or [Daily Order].[Delivery?] instead of just [Delivery?], whichever is supposed to apply

A tip for the future, when showing code, copy and paste it and surround with code tags, don't send an image. Then responders can edit it for you.
 
ghrfjf.png

Im not sure what you mean, this error shows up
 
Heres the code

SELECT Orders.[Order ID], Sum([Daily Query].[Total Cost]) AS OrderTotal, IIf([Order].[Delivery?]=Yes,[OrderTotal]+1.5,[OrderTotal]) AS Delivery
FROM [Daily Query] INNER JOIN Orders ON [Daily Query].[Order ID] = Orders.[Order ID]
GROUP BY Orders.[Order ID];
 
CJ appears to be offline. Double check the table name. You forgot the "s".
 
I suspect you want to change Expression to Sum.
 
see my comment in my first response

But also, you cannot refer to aliased calculations (OrderTotal) in a group by query. Instead you need repeat the calculation (sum in this case).

I'm not sure what you are trying to do but think you are trying to add 1.5 if delivery is true. if so, try this

Code:
SELECT Orders.[Order ID], Sum([Daily Query].[Total Cost]) AS OrderTotal, Sum([Daily Query].[Total Cost]) +IIf([Orders].[Delivery?]=Yes,1.5,0) AS Delivery
FROM [Daily Query] INNER JOIN Orders ON [Daily Query].[Order ID] = Orders.[Order ID]
GROUP BY Orders.[Order ID];
 

Users who are viewing this thread

Back
Top Bottom