|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 28th, 2007, 02:46 PM
posted to microsoft.public.access.queries
|
|||
|
|||
where statement
Hi Guys,
I got the following problem I have a table Nr Country Cost 1234 DE 1.21 1234 NL 1.28 1234 BE 1234 UK 1235 DE 1.98 Now the purpuse of my query should be that if the Cost field is empty it should take the cost of the DE country of the number so this should be the result Nr Country Cost 1234 DE 1.21 1234 NL 1.28 1234 BE 1.21 1234 UK 1.21 1235 DE 1.98 Does anyone have an idea on how to solve this? 
|
|
#2
February 28th, 2007, 03:35 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
where statement
You can do this with two queries. The first one simply finds all the records
where Country='DE'. The second one contains the table and the first query with a left join (show all table records and only those query records that match) on Nr. Output the first two fields from the table, and as the third field use something like this: CostNew: IIF(IsNull([Table].[Cost]),[Query].[Cost],[Table].[Cost]) Dries wrote: Hi Guys, I got the following problem I have a table Nr Country Cost 1234 DE 1.21 1234 NL 1.28 1234 BE 1234 UK 1235 DE 1.98 Now the purpuse of my query should be that if the Cost field is empty it should take the cost of the DE country of the number so this should be the result Nr Country Cost 1234 DE 1.21 1234 NL 1.28 1234 BE 1.21 1234 UK 1.21 1235 DE 1.98 Does anyone have an idea on how to solve this? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200702/1
|
|
#3
February 28th, 2007, 03:37 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
where statement
SELECT Dries.Nr,
Dries.Country, Dries.Cost, IIf(IsNull([Cost])=False, [COST], DLookUp("[Cost]","Dries","[Nr] =[Nr] AND [Country] =[Country] ")) AS TheCost FROM Dries; -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Dries" wrote: Hi Guys, I got the following problem I have a table Nr Country Cost 1234 DE 1.21 1234 NL 1.28 1234 BE 1234 UK 1235 DE 1.98 Now the purpuse of my query should be that if the Cost field is empty it should take the cost of the DE country of the number so this should be the result Nr Country Cost 1234 DE 1.21 1234 NL 1.28 1234 BE 1.21 1234 UK 1.21 1235 DE 1.98 Does anyone have an idea on how to solve this?
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
